python - 将csv导入mysql数据库

Question

这是我将 csv 导入 mysql 数据库的代码:

import csv
import mysql.connector

cnx = mysql.connector.connect(user='root', password='',
                              host='localhost',
                              database='jeremy_db')
file = open('C:\\Users\\trendMICRO\\Desktop\\OJT\\test.csv', 'rb')  # open the file in read binary mode
csv_data = csv.reader(file)
for row in csv_data:

    cursor.execute('INSERT INTO jeremy_table_test(sha1, vsdt,trendx,notes )' 'VALUES("%s", "%s", "%s","%s")',row)
#close the connection to the database.
mydb.commit()
cursor.close()
print("Done")

它给了我错误:

   Traceback (most recent call last):
  File "C:\Users\trendMICRO\Desktop\OJT\import.py", line 11, in <module>
    cursor.execute('INSERT INTO jeremy_table_test(sha1, vsdt,trendx,notes )' 'VALUES("%s", "%s", "%s","%s")',row)
NameError: name 'cursor' is not defined

我按照此处的说明进行操作:Connect to mysql with python and upload csv

Answer 1

您忘记创建光标本身。您还混淆了变量名称。 提交仅适用于MySQLConnection。

import csv
import mysql.connector

mydb = mysql.connector.connect(user='root', password='',
                          host='localhost',
                          database='jeremy_db')
file = open('C:\\Users\\trendMICRO\\Desktop\\OJT\\test.csv', 'rb')  # open the file in read binary mode
csv_data = csv.reader(file)

cursor = mydb.cursor()

for row in csv_data:
    cursor.execute('INSERT INTO jeremy_table_test(sha1, vsdt,trendx,notes )' 'VALUES("%s", "%s", "%s","%s")',row)

# close the connection to the database.
mydb.commit()
cursor.close()
print("Done")