Python MyQLdb execute won't accept parameters

Question

I've found several similar questions but none of them seem to explain what's happening here.

Code:

print type(price)
print type(sale_price)
print type(url)
price=0.0
sale_price=0.0
url="asdf"
dbc.execute("UPDATE logTable SET price=%f, salePrice=%f, modified=NOW() WHERE url=%s", [price, sale_price, url])

Output:

<type 'float'>
<type 'float'>
<type 'str'>
Traceback (most recent call last):
 File "./scrape.py", line 102, in <module>
 dbc.execute("UPDATE logTable SET price=%f, salePrice=%f, modified=NOW() WHERE url=%s", [price, sale_price, url])
 File "/usr/lib/python2.7/dist-packages/MySQLdb/cursors.py", line 210, in execute
 query = query % args
TypeError: float argument required, not str

There aren't stray % characters in the interpolated string. The number of arguments matches the number of placeholders. The types sure seem to be correct.

I tried using ? placeholders instead of printf-style ones, even though docs suggest both are supported. That gives a different exception:

Traceback (most recent call last):
 File "./scrape.py", line 102, in <module>
 dbc.execute("UPDATE logTable SET price=?, salePrice=?, modified=NOW() WHERE url=?", [price, sale_price, url])
 File "/usr/lib/python2.7/dist-packages/MySQLdb/cursors.py", line 210, in execute
 query = query % args
TypeError: not all arguments converted during string formatting

That exception also yields plenty of StackOverflow answers but none really shed light on what's going wrong here. I did try using a tuple instead of a list as the second argument to execute with both style placeholders but still get the same exceptions.

• roganjosh: Can you show the values of [price, sale_price, url]? I was too hasty in my initial comment, this seems a bit strange.
• gohanman: Oddly, using %s for all placeholders does work. That's even more baffling to me. Specifying all the types correctly gives a confusing exception, and omitting all type information gives a confusing exception, but using explicitly incorrect types works.
• gohanman: Added parameter values. Running it w/ 0.0, 0.0, "asdf" does still give the same exception.
• roganjosh: %s is not the same as regular string interpolation in Python, it's a placeholder. That said, I don't know exactly how it differs to give you a definitive answer. However, there is a PEP that details the acceptable parameter styles that can be used - %s is one of them.
• gohanman: I did look at that PEP. I read it as meaning you can use various ANSI printf codes with %s just given as one example of such a code. It doesn't appear to give any detail about which codes are valid.

Answer 1

Your question seems to indicate that there is anything wrong here. This is not the case.

As per the Python database specification, a concrete database library is free to use one of several placeholders for the parameters of a (real or pseudo) prepared statement. Normally, the choice is made with respect to the most simple way to solve the task.

Libraries which use real prepared statements can use the ? placeholders. This allows them to pass the statement directly to the prepared statement engine of the interface, because these usually use ? for the data to be placed into the query.

Other libraries, such as MySQLdb, don't use prepared statements, but another (more or less) safe method to create the queries resp. to embed the data into the statements. In this case, %s (and only %s and no other patterns) can be used. In these cases, the library transforms the given data into an appropriate replacement: strings are enclosed in ' characters and the content is treated appropriately, as would the C function mysql_real_escape_string() would do. Other data are transformed differently, such as numbers which are transformed into their respective string representation.

In all cases, strings are created out of these data and then inserted into the queries. That's why %s is used and nothing else (like %f, %d etc.).

• roganjosh: Interesting. I don't use MySQL so I wasn't aware that there was a definitive reason that only %s was used. It always seemed awkward to me considering SQLite - a run-of-the-mill library, I guess - went to lengths to support multiple ways.
• gohanman: It seems like there's something wrong in that I can't find where this behavior is documented - the DB API PEP does not appear to have any of this information about when different placeholders should be used. It says what placeholders are available, has a footnote about which are preferred, and that's it.
• glglgl: @gohanman What I described is not official, but it is the "most logical" thing, so to say: the placeholder variants are there to help the library choose the simplest way to solve their task. And ? and :XYZ are some which are used by some prepared statement libraries, %s is used for libraries which assemble the query on their own.