1

pip install pandas mysql-connector-python

1

2

3

4

5

6

7

8

9

10

11

12

import mysql.connector

# 连接到 MySQL 数据库

mydb = mysql.connector.connect(

    host="localhost",

    user="yourusername",

    password="yourpassword",

    database="yourdatabase"

)

# 创建游标对象

cursor = mydb.cursor()

1

2

3

import pandas as pd

???????# 读取 CSV 文件

df = pd.read_csv("data.csv")

1

2

3

4

5

6

7

# 遍历 DataFrame 中的每一行，并将数据插入到 MySQL 数据库中

for index, row in df.iterrows():

    sql = "INSERT INTO your_table_name (column1, column2, column3, ...) VALUES (%s, %s, %s, ...)"

    values = (row['column1'], row['column2'], row['column3'], ...)

    cursor.execute(sql, values)

???????# 提交更改

mydb.commit()

1

2

3

# 关闭游标和数据库连接

cursor.close()

mydb.close()

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

# -*- coding: UTF-8 -*

import pymysql

import time, datetime

localdatetime= time.strftime('%Y-%m-%d %H:%M:%S',time.localtime(time.time()))

print(localdatetime)

print('开始导入数据！')

def netfw_SQL():

    sql_conn = pymysql.connect(host='127.0.0.1', port=3306, user='root',

                           password='XXXXX', db='fwtest', charset='utf8', connect_timeout=1000)

    #创建数据库对象

    cursor = sql_conn.cursor()

    with open('netfirewalls.csv', encoding='utf-8') as line_1:

        #依次读取CSV文件的每一行

        for line_2 in line_1.readlines():

            #strip() 方法用于移除字符串头尾指定的字符（默认为空格或换行符）或字符序列

            line_2 = line_2.strip()

            #split() 通过指定分隔符对字符串进行切片，这里指定','，而“-1”表示分隔所有

            list_1 = line_2.split(',', -1)

            #执行插入表数据语句

            sql_2 = 'INSERT INTO netfirewalls (fwname, rulename, rulenum, description, action, disable, sourcezone, destzone, sourceip, destip, service) VALUE(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)'

            cursor.execute(sql_2, (list_1[0], list_1[1], list_1[2], list_1[3], list_1[4], list_1[5], list_1[6], list_1[7], list_1[8], list_1[9], list_1[10]))

    sql_conn.commit() #提交事务

    sql_conn.close() #关闭连接

    localdatetime= time.strftime('%Y-%m-%d %H:%M:%S',time.localtime(time.time()))

    print(localdatetime)

    print('网络防火墙原规则数据导入完毕！')

#执行自定义函数CSV_SQL()

if __name__ == '__main__':

    netfw_SQL()