22

I don't understand the ValueError with Y. I escape with %...

table = town+"_history"
db.execute("SELECT DATE_FORMAT(snapdate,'%%Y-%%m-%%d') AS date, SUM( population ) AS accountpopulation, count( blockid ) AS number_block FROM %s WHERE blockid =%%s GROUP BY snapdate ORDER BY snapdate DESC LIMIT 7" % table, (blockid))
Mikaël Mayer
  • 10,425
  • 6
  • 64
  • 101
OlZ
  • 346
  • 1
  • 3
  • 11
  • Can you give some more info? What is in table? What is passed to the resulting format string (i.e. what is given to the string that has the "%Y"? – SethMMorton May 13 '13 at 21:08
  • 1
    @SethMMorton: the MySQL `DATE_FORMAT()` function expects a formatting string that uses `%Y`, etc. formatting codes. But so does the Python string formatting operation using the `%` operator, and so does the MySQL database that supports `%s` SQL parameters *by using string formatting with the `%` operator*. – Martijn Pieters May 13 '13 at 21:10

2 Answers2

42

You escape the %% but then use the string as a formatter first:

"...." % table,

that returns a new string with the %% escaped percentages replaced by single % characters. The MySQL database adapter (ab)uses string formatting with % too, so it'll take that output and expect to be able to fill %s slots with escaped SQL literals. It is there that your '%Y-%m-%d' part of the SQL statement is being interpreted again as a string format and the error is thrown.

The solution is to either double the doubling:

db.execute("SELECT DATE_FORMAT(snapdate,'%%%%Y-%%%%m-%%%%d') AS date, SUM( population ) AS accountpopulation, count( blockid ) AS number_block FROM %s WHERE blockid = %%s GROUP BY snapdate ORDER BY snapdate DESC LIMIT 7" % table, (blockid,))

or use str.format() instead and avoid having to doubly-escape:

db.execute("SELECT DATE_FORMAT(snapdate,'%%Y-%%m-%%d') AS date, SUM( population ) AS accountpopulation, count( blockid ) AS number_block FROM {0} WHERE blockid = %s GROUP BY snapdate ORDER BY snapdate DESC LIMIT 7".format(table), (blockid,))

Here {0} is replaced by the table name and the %% escapes are left untouched; the database adapter will use the %s slot to fill in the blockid parameter and return a SQL statement with the %% escapes turned into single % characters.

Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
  • The first solution => TypeError: Decimal('220') is not JSON serializable. The secondone ==> ValueError: zero length field name in format. – OlZ May 13 '13 at 21:29
  • @OlZ: What is turning this into JSON? What is the traceback you get? And if you are using Python 2.6, you need to use `{0}` as the field placeholder. – Martijn Pieters May 13 '13 at 21:30
  • @OlZ: and the first error sounds like the database query *succeeded* but that you are then trying to serialize byte data using JSON. That'd be a *new* problem instead; this code worked. – Martijn Pieters May 13 '13 at 21:36
  • I use a python framework => bottle.py Yes i'm using Python 2.6 – OlZ May 13 '13 at 21:37
  • If all you are getting now is the `TypeError: Decimal('220') is not JSON serializable.` error then that is beyond the scope of where I can help you; you solved the database problem now and found a new one, but you'll have to ask a new question about that. Include code and a *full* traceback (not just the exception message at the end). – Martijn Pieters May 13 '13 at 21:43
  • Thanks Martijn. But you're right and no.. How i can have a decimal on a date ????? – OlZ May 13 '13 at 21:54
  • @OlZ: I think this question may help: [Python MySQLdb returns datetime.time and decimal](http://stackoverflow.com/q/7483363) – Martijn Pieters May 13 '13 at 21:56
4

Finally, @Martijn Pieters you are completely right. And thank you for your useful answer. The other error comes from SUM and COUNT. Python sometimes runs in a crazy way when dealing with JSON. So the complete answer is:

db.execute("SELECT DATE_FORMAT(snapdate,'%%%%Y-%%%%m-%%%%d') AS date, CAST(SUM( population ) AS CHAR ) AS accountpopulation, CAST(count( blockid ) AS CHAR) AS number_block FROM %s WHERE blockid = %%s GROUP BY snapdate ORDER BY snapdate DESC LIMIT 7" % table, (blockid,))
fragilewindows
  • 1,394
  • 1
  • 15
  • 26
OlZ
  • 346
  • 1
  • 3
  • 11