import pymysql
import ipaddress
import pandas as pd
def ip_connect_db():
# 连接 zzszxyy 数据库
return pymysql.connect(host='localhost',
port=3306,
user='root',
password='',
database='zzszxyy',
charset='utf8')
def ipaddr_add(ip_temp):
con1 = ip_connect_db()
cur1 = con1.cursor()
ip_addr = ip_temp[1]
sql = "select count(*) from student_ipaddr where ip_addr = '%s' ;" % ip_addr
cur1.execute(sql)
rows1 = cur1.fetchall()
rows1 = rows1[0][0]
if rows1 == 0:
subnet = str(ipaddress.ip_network(ip_addr + '/24', strict=False))
a = "'%s', '%s', '%s', '%s', '%s', %d, '%s' " % (ip_temp[0], ip_temp[1], ip_temp[2], ip_temp[3], ip_temp[4], 1, subnet)
sql = "INSERT INTO student_ipaddr (hostname, ip_addr, osver, platform, mac, av, subnet) VALUES (%s)" % a
cur1.execute(sql)
con1.commit()
cur1.close()
con1.close()
xls_filename = '/tftpboot/yx/OfficeScan.csv'
os_list = pd.read_csv(xls_filename, encoding='gbk')
get_list = os_list[['终端', 'IP 地址', '平台', '体系结构', 'MAC 地址']]
for li in range(len(get_list)):
ipaddr_add(get_list.iloc[li])
文章评论