MySQL + Python: Not all arguments converted during string formatting

Question

I am trying to do an INSERT into MySQL using the cursor.execute(sql, args) syntax. This is what I currently have:

# data = {...}
qmarks = ', '.join('?' * len(data))   
query = "INSERT INTO title (%s) VALUES (%s)" %(qmarks, qmarks) 
args = data.keys() + data.values()
print len(args), print len(data)
cursor.execute(query, args)

TypeError: not all arguments converted during string formatting

I checked the length of args (22) and quarks (11 x 2 = 22), and they seem to be the same. What is causing this error, and what do I need to change?

Update: When I try and do the string formatting using %s, it also throws an error:

>>> data={'provider':'asdf', 'vendor_id': '1234'}
>>> format = ', '.join(['%s'] * len(data)) 
>>> query = "INSERT INTO title (%s) VALUES (%s)" %(format, format)
>>> query
'INSERT INTO title (%s, %s) VALUES (%s, %s)'
>>> cursor.execute(query, args)
Traceback (most recent call last):
raise errorclass, errorvalue
_mysql_exceptions.ProgrammingError: (1064, "You have an error in your SQL syntax; 
check the manual that corresponds to your MySQL server version for the right syntax 
to use near ''vendor_id', 'provider') VALUES ('1234', 'asdf')' at line 1")

I think this has to do with the fact that the column definitions are strings:

INSERT INTO title ('vendor_id', 'provider') VALUES ('1234', 'asdf')

instead of:

INSERT INTO title (vendor_id, provider) VALUES ('1234', 'asdf')

Is there a way to use string formatting here but pass a variable instead of a string?

Andrew Clark · Accepted Answer · 2012-02-18 00:32:02Z

4

Assuming you are using MySQLdb, you want to have %s in the final query, not ?, you will also need to enter the column names directly into the query instead of using the %s replacement for them:

# data = {...}
columns = ', '.join(data.keys())
format = ', '.join(['%s'] * len(data))
query = "INSERT INTO title (%s) VALUES (%s)" % (columns, format)
cursor.execute(query, data.values())

edited Feb 18, 2012 at 0:32

answered Feb 18, 2012 at 0:12

Andrew Clark

210k36 gold badges285 silver badges310 bronze badges

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

4 Comments

David542 Over a year ago

Thank you. I've updated the string formatting, but it is still throwing some sort of syntax error (see above).

Andrew Clark Over a year ago

@David542 - I think that is caused by the quotes around the column names, pretty sure you will need to insert the field column names into your query directly and then only have the %s replacement for the values. So it would look like: INSERT INTO title (vendor_id, provider) VALUES (%s, %s).

David542 Over a year ago

But then I lose the ordering of the dict (in the dict I have about 100 k,v pairs)

Andrew Clark Over a year ago

@David542 - Actually no, if you insert the columns using data.keys() and then insert the values using data.values() without any intervening modifications to the dictionary (as in my edit), the columns and values are guaranteed to correspond directly. This is in the documentation for dict.items().

Collectives™ on Stack Overflow

MySQL + Python: Not all arguments converted during string formatting

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related