从CSV文件将数据插入到MySQL表中

Question

所以我试图从csv文件中提取列，并将它们输入到mysql表中

但是，我得到了以下错误，它的目标是“title”（第3列，第1行）

 'Using Adversarial Autoencoders for Multi-Modal Automatic Playlist Continuation, ' at line 1")

csv数据

1038819,Discoverable,Using Adversarial Autoencoders for Multi-Modal Automatic Playlist Continuation,Conference Proceeding,"Vagliano, Iacopo; Galke, Lukas; Mai, Florian; Scherp, Ansgar",10.1145/3267471.3267476,
1037162,Discoverable,Performance Comparison of Ad-hoc Retrieval Models over Full-text vs. Titles of Documents (Forthcoming),Conference Proceeding,"Saleh, Ahmed; Beck, Tilman; Galke, Lukas; Scherp, Ansgar",,1893/28014

我认为这与作者栏使用的是'，'有关，而且在其他栏中有完全不同的间距和符号组合

Python

import pymysql
import csv

csv_data= csv.reader(open('Book1.csv'))

conn=pymysql.connect("localhost","root", "root", "test")
cursor=conn.cursor()
print ("Done")
for row in csv_data:

    cursor.execute('INSERT INTO output (Output_ID, Status, Title, Type, Authors, DOI, Handle ) VALUES({}, {}, {}, {}, {}, {}, {})'.format(row[0], row[1], row[2], row[3], row[4], row[5], row[6]))
    conn.commit()

cursor.close()

Answer 1

请注意，使用字符串格式编写SQL时，结果在要插入的值周围缺少引号：

In [215]: 'INSERT INTO output (Output_ID, Status, Title, Type, Authors, DOI, Handle ) 
           VALUES({}, {}, {}, {}, {}, {}, {})'.format('manchego', 'brie', 'gruyere', 'stilton', 'camembert', 'bleu', 'asiago')

Out[215]: 'INSERT INTO output (Output_ID, Status, Title, Type, Authors, DOI, Handle ) 
           VALUES(manchego, brie, gruyere, stilton, camembert, bleu, asiago)'

正确的SQL需要引号

INSERT INTO output (Output_ID, Status, Title, Type, Authors, DOI, Handle )
VALUES("manchego", "brie", "gruyere", "stilton", "camembert", "bleu", "asiago")

最好的做法是使用parametrized sql，而不是自己添加正确的引用：

sql = '''INSERT INTO output (Output_ID, Status, Title, Type, Authors, DOI, Handle)
         VALUES(%s, %s, %s, %s, %s, %s, %s)'''
cursor.execute(sql, row)

注意^{} can accept 2 arguments、sql和row。它们作为单独的参数传递，而不是作为一个字符串sql % row。cursor.execute函数将为您处理正确的报价。这不仅对您来说更简单，而且可以防止SQL injection