Python => ValueError: unsupported Y character (0x59)

Question

I do not understand ValueError with Y. I am running away 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)) 

Answer 1

You exit %% , but first use the string as formatting:

 "...." % table, 

which returns a new line with %% escaped percentages replaced by single % characters. The MySQL database adapter (ab) also uses string formatting with % , so it will accept this output and expect it to be able to fill %s slots with escaped SQL literals. This is where your part of the '%Y-%m-%d' SQL statement is interpreted again as a string format and an error occurs.

The solution is to 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 double exiting:

 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 %% screens remain untouched; the database adapter will use the %s slot to populate the blockid parameter and return an SQL statement with %% screens turned into single % characters.

Answer 2

Finally, @Martijn Pieters you are absolutely right. And thanks for your helpful answer. Another error comes from SUM and COUNT. Python sometimes works insanely when it comes to JSON. So the full 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,))