TypeError：并非在字符串格式化期间转换所有参数

I'm having a bit of trouble loading an CSV file into a mysql database. Here's my code:

我在将CSV文件加载到mysql数据库时遇到了一些麻烦。这是我的代码：

for q in csvReader:
    name, price, LastUpdate, today = q
    co.execute("""INSERT INTO fundata (name, price, LastUpdate) VALUES(name, price, LastUpdate);""",q)

I get an error saying TypeError: not all arguments converted during string formatting.

我收到一个错误，说明TypeError：在字符串格式化过程中没有转换所有参数。

The name column is a string, price is a float, and LastUpdate is a date. I read a bit and saw some scripts that wrapped the values in %(value)s and %(value)d (in my case instead of d I use f) but then I get a different error:

name列是字符串，price是float，LastUpdate是日期。我读了一下，看到一些脚本包含％（值）s和％（值）d的值（在我的情况下，而不是d我使用f），但后来我得到一个不同的错误：

TypeError: format requires a mapping

TypeError：format需要映射

Can anyone help show me what I am doing wrong?

任何人都可以帮助告诉我我做错了什么吗？

Thank you!

谢谢！

3 个解决方案

#1

If I recall correctly, you should use %s with MySQLdb in query to denote positions you want the argument tuple elements to be formatted. This is different from usual ? placeholders used in most other implementations.

如果我没记错的话，你应该在查询中使用带有MySQLdb的％s来表示你想要对参数元组元素进行格式化的位置。这与平时有什么不同？在大多数其他实现中使用的占位符。

for q in csvReader:
    name, price, LastUpdate, today = q
    co.execute("INSERT INTO fundata (name, price, LastUpdate) VALUES(%s, %s, %s);",q)

EDIT: Here is also an example of inserting multiple rows at once, that is more efficient than inserting them one by one. From MySQLdb User's Guide:

编辑：这里也是一次插入多行的例子，这比逐个插入它们更有效。来自MySQLdb用户指南：

c.executemany(
      """INSERT INTO breakfast (name, spam, eggs, sausage, price)
      VALUES (%s, %s, %s, %s, %s)""",
      [
      ("Spam and Sausage Lover's Plate", 5, 1, 8, 7.95 ),
      ("Not So Much Spam Plate", 3, 2, 0, 3.95 ),
      ("Don't Wany ANY SPAM! Plate", 0, 4, 3, 5.95 )
      ] )

Here we are inserting three rows of five values. Notice that there is a mix of types (strings, ints, floats) though we still only use %s. And also note that we only included format strings for one row. MySQLdb picks those out and duplicates them for each row.

这里我们插入三行五个值。请注意，虽然我们仍然只使用％s，但有各种类型（字符串，整数，浮点数）。还要注意我们只包含一行的格式字符串。 MySQLdb选择那些并为每一行复制它们。

#2

From the error message, the execute() method is substituting your parameters into your SQL statement using %. But you haven't indicated where any of them go, so none of your parameters are being used, they are all left over, and therefore you get the message about having some left over (hey, all is some!). Read the documentation for your database driver to find out what it wants you to use as a substitution token; probably %s.

从错误消息中，execute（）方法使用％将您的参数替换为SQL语句。但是你还没有说明它们中的任何一个，所以没有你的参数被使用，它们都被遗留下来，因此你得到关于剩下一些的信息（嘿，一切都是一些！）。阅读数据库驱动程序的文档，找出它想要用作替换标记的内容;可能％s。

#3

format requires mapping is because you are setting a name to the string replacement tokens and a dictionary is expected.

格式需要映射是因为您要为字符串替换标记设置名称，并且需要字典。

if you left them as %s %d %f etc. with out the parenthesis, it will take the arguments in order from a list or tuple (q)

如果你把它们留作％s％d％f等而不用括号，它将从列表或元组（q）中按顺序取出参数

for q in csvReader:
    co.execute("""INSERT INTO fundata (name, price, LastUpdate) VALUES(%s, %f, %s);""",q[:-1])

#1

for q in csvReader:
    name, price, LastUpdate, today = q
    co.execute("INSERT INTO fundata (name, price, LastUpdate) VALUES(%s, %s, %s);",q)