CODEWITHSUNDEEP
pythonmysql
Lostsoul
Lostsoul

Reputation: 25999

TypeError: not all arguments converted during string formatting

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!

Upvotes: 12

Views: 29933

Answers (3)

Timo
Timo

Reputation: 5390

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.

Upvotes: 15

dting
dting

Reputation: 39287

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)

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

Upvotes: 1

kindall
kindall

Reputation: 184181

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.

Upvotes: 5

Related Questions