Python SQLite how to execute an executable string SQL statement

Question

Say we have an SQL statement that needs to be executed only with parameters before executing from the database. For example:

sql = ''' SELECT id, price, date_out FROM sold_items WHERE date_out BETWEEN ? AND ? ''' database_cursor.execute(sql, (start_date, end_date)) 

How to get a line that has been parsed and executed ?, something like this:

 SELECT id, price, date_out FROM sold_items WHERE date_out BETWEEN 2010-12-05 AND 2011-12-01 

In this simple case, this is not very important, but I have other SQL expressions much more complicated, and for debugging purposes I would like to execute them myself in my SQL manager and check the results.

Thanks in advance

Answer 1

SQLite never replaces parameters with the SQL query string itself; parameter values ​​are read directly when the command is executed. (Formatting these values ​​just for reanalysis into the same values ​​would be a futile overhead.)

But if you want to know how the parameters will be written to SQL, you can use the quote function; something like that:

 def log_and_execute(cursor, sql, *args): s = sql if len(args) > 0: # generates SELECT quote(?), quote(?), ... cursor.execute("SELECT " + ", ".join(["quote(?)" for i in args]), args) quoted_values = cursor.fetchone() for quoted_value in quoted_values: s = s.replace('?', quoted_value, 1) print "SQL command: " + s cursor.execute(sql, args) 

(This code will fail if there is ? Which is not a parameter, that is, inside a literal string.)

Answer 2

UPDATE I learned from this webpage that with Python 3.3 you can start printing executed SQL with

 connection.set_trace_callback(print) 

Answer 3

How about formatting the string?

 sql = """ SELECT id, price, date_out FROM sold_items WHERE date_out BETWEEN {0} AND {1} """.format(start_date, end_date) """ database_cursor.execute(sql)