TypeError: not all arguments converted during string formatting [duplicate]

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!

What mysql library are you using?

chmullig
– chmullig

2011-03-11 19:58:21 +00:00
Commented Mar 11, 2011 at 19:58 — chmullig
– chmullig, Commented Mar 11, 2011 at 19:58

Timo · Accepted Answer · 2013-08-28 13:45:24Z

15

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.

edited Aug 28, 2013 at 13:45

answered Mar 11, 2011 at 20:02

Timo

5,4086 gold badges38 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Lostsoul Over a year ago

Thanks Timo. I tried the first code you posted(the for statement), and still got this error: TypeError: not all arguments converted during string formatting I think I need a for loop because I need to program some more logic into my script(i.e. change names to id codes, etc..). I don't know if it helps but here's more code: co = db.cursor() dataFile= file( "funds.csv", "rb" ) csvReader= csv.reader( dataFile )

Timo Over a year ago

Sorry for late reply, but you can try to remove the verbatim string (triple quotes) identifiers and try if that fixes it.

Davoud Taghawi-Nejad Over a year ago

You should use ? instead of %s for database internal value insertion

kindall · Accepted Answer · 2011-03-11 20:04:23Z

5

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.

answered Mar 11, 2011 at 20:04

kindall

185k36 gold badges291 silver badges321 bronze badges

Comments

dting · Accepted Answer · 2011-03-12 08:00:31Z

1

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])

edited Mar 12, 2011 at 8:00

answered Mar 11, 2011 at 20:02

dting

39.4k10 gold badges98 silver badges117 bronze badges

Collectives™ on Stack Overflow

TypeError: not all arguments converted during string formatting [duplicate]

3 Answers 3

3 Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Linked

Related