类型错误：不串中转换的所有参数与MySQL

Question

我的代码工作插入表中的“消息报”的信息从一个档案CSV，但它不工作，当我调试格式，此错误显示：

Connected to pydev debugger (build 171.4694.38) 
Traceback (most recent call last): 
File "C:\Program Files\JetBrains\PyCharm Community Edition 2017.1.4\helpers\pydev\pydevd.py", line 1591, in <module> 
globals = debugger.run(setup['file'], None, None, is_module) 
File "C:\Program Files\JetBrains\PyCharm Community Edition 2017.1.4\helpers\pydev\pydevd.py", line 1018, in run 
pydev_imports.execfile(file, globals, locals) # execute the script 
File "C:\Program Files\JetBrains\PyCharm Community Edition 2017.1.4\helpers\pydev\_pydev_imps\_pydev_execfile.py", line 18, in execfile 
exec(compile(contents+"\n", file, 'exec'), glob, loc) 
File "C:/Users/SoriyAntony/PycharmProjects/cnnbd/cnnbd", line 11, in <module> 
cursor.execute("INSERT INTO noticias(title, link, pubDate) \ VALUES({1},{2},{3})" % tuple(list)) 
TypeError: not all arguments converted during string formatting 

Process finished with exit code 1 

这是归档cnn.csv：

title,link,pubDate 

Hundreds of households to be evacuated in wake of London tower fire,http://www.cnn.com/2017/06/23/world/london-fire-safety-evacuations/index.html,"Fri, 23 Jun 2017 20:22:02 GMT" 

"Ex-Obama official: Handling of Russia 'is hardest thing to defend,' WaPo reports",http://www.cnn.com/collections/intl-wapo-former-obama-official/,"Fri, 23 Jun 2017 17:47:54 GMT" 

"Saudi Arabia, major combatant in Yemen, to tackle spread of cholera",http://www.cnn.com/2017/06/23/middleeast/yemen-saudis-cholera/index.html,"Fri, 23 Jun 2017 18:29:37 GMT" 

Violent homophobia festers in Erdogan's shadow,http://www.cnn.com/2017/06/23/europe/turkey-homophobia-violence/index.html,"Fri, 23 Jun 2017 13:11:52 GMT" 

Qatar given 10 days to comply with 13 demands from Arab states,http://www.cnn.com/collections/qatar-intl/,"Fri, 23 Jun 2017 11:41:52 GMT" 

Al Jazeera: What you need to know,http://www.cnn.com/videos/cnnmoney/2017/06/23/al-jazeera-explainer-mxb-lon-orig.cnnmoney,"Fri, 23 Jun 2017 16:01:18 GMT" 

，这是代码：

import mysql.connector 
import pandas as pd 

cnx = mysql.connector.connect(user='root', password='password', host='localhost', database='cnn') 
cursor = cnx.cursor() 
csv_data = pd.read_csv('cnn.csv') 

for row in csv_data.iterrows(): 
list = row[1].values 
#cursor.execute("""INSERT INTO noticias (title, link, pubDate) VALUES('%s', '%s', '%s');""") 
cursor.execute("INSERT INTO noticias(title, link, pubDate) VALUES({1},{2},{3})" % tuple(list)) 

cursor.close() 
cnx.close() 

我用两行关于curso.execute和第一行进行测试，未检测到调试错误但未写入表中，第二行标记在此解释的错误。 任何关于此错误的想法，我有安装python 3.6和MySQL 5.7

，这是台新闻短片：

mysql> describe noticias; 
+---------+--------------+------+-----+---------+-------+ 
| Field | Type   | Null | Key | Default | Extra | 
+---------+--------------+------+-----+---------+-------+ 
| title | varchar(500) | YES |  | NULL |  | 
| link | varchar(500) | YES |  | NULL |  | 
| pubDate | varchar(500) | YES |  | NULL |  | 
+---------+--------------+------+-----+---------+-------+ 
3 rows in set (0.00 sec) 

Answer 1

当使用% (printf-style) string formatting，占位符没有编号，但使用%s占位符：

cursor.execute(
    "INSERT INTO noticias(title, link, pubDate) VALUES(%s, %s, %s)" % tuple(list)) 

但是，这是错误的方法;这些值不会被正确引用。你想用SQL参数，这可以通过在这里只是你的价值观作为传递可以实现秒参数cursor.execute()：

cursor.execute(
    "INSERT INTO noticias(title, link, pubDate) VALUES(%s, %s, %s)", 
    row[1].values) 

我去掉了名字list;尽量不要用局部变量掩盖内置类型。

可以，事实上，必须使用cursor.executemany()（使用生成器表达式只提取每一行的数据，跳过指数）的MySQL重复使用一个查询：

cursor.executemany(
    "INSERT INTO noticias(title, link, pubDate) VALUES(%s, %s, %s)", 
    (r[1:] for r in csv_data.itertuples()))