TypeError: not all arguments converted during string formatting

Question

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

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.

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:

TypeError: format requires a mapping

Can anyone help show me what I am doing wrong?

Thank you!

Answer 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.

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:

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.

Answer 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.