Resilient '%' escape for string formatting in Python

Question

I have a string, specifically a SQL query, that goes through multiple formatting passes before it is passed to the backend to be executed, at which point Django performs yet another string format. How can I preserve the placeholders that need to make it to the database throughout all of this? Something like the following is the best I've come up with, but is definitely not as flexible as I need it to be. I am running Python 2.5

from django.db import connection
cursor = connection.cursor()    
cursor.execute("SELECT DATE_FORMAT('2010-01-01 12:00:00', '%%%%H:%%%%i:%%%%s') as %s" % 'foo')

Answer 1

Well, for string values you should be able to use something like this:

cursor.execute("SELECT DATE_FORMAT('2010-01-01 12:00:00', %%s) as %s" % 'foo', ('%H:%i:%s',))

This of course doesn't help for actual placeholders in the query, like you can see on %%s.

Answer 2

If you're running Python 2.6+ you can use str.format() in order to shed one level of percent signs.

Answer 3

I think you should also use ? placeholders in SQL syntax instead of string formatting, e.g. change @Lukás' answer to:

cursor.execute("SELECT DATE_FORMAT('2010-01-01 12:00:00', '?') as ?",('%H:%i:%s',"foo"))

Answer 4

" ".join([r"SELECT DATE_FORMAT('2010-01-01 12:00:00', '%H:%i:%s') as", "foo"])