Python:将亚信安全防毒墙网络版XG的客户端信息导入MySQL

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])
点赞

发表评论

电子邮件地址不会被公开。必填项已用 * 标注