Python SQLite how to get SQL string statement being executed

Question

Let's say we have a SQL statement that just needs to be completed with the parameters before getting executed against the DB. For instance:

sql = '''
      SELECT  id, price, date_out
      FROM sold_items
      WHERE date_out BETWEEN ? AND ?
      '''

database_cursor.execute(sql, (start_date, end_date))

How do I get the string that is 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 it's not very important, but I have other SQL Statements much more complicated, and for debugging purposes I would like to execute them myself in my sqlite manager and check the results.

Thanks in advance

Solution 1

UPDATE. I learned from this web page that since Python 3.3 you can trigger printing of executed SQL with

connection.set_trace_callback(print)

Should you want to revert to silent processing, use

connection.set_trace_callback(None)

You can use another function instead of print.

Solution 2

SQLite never actually substitutes parameters into the SQL query string itself; the parameters' values are read directly when it executes the command. (Formatting those values only to parse them again into the same values would be useless overhead.)

But if you want to find out how the parameters would be written in SQL, you can use the quote function; something like this:

import re
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)
            #s = re.sub(r'(values \(|, | = )\?', r'\g<1>' + quoted_value, s, 1)
    print "SQL command: " + s
    cursor.execute(sql, args)

(This code will fail if there is a ? that is not a parameter, i.e., inside a literal string. Unless you use the re.sub version, which will only match a ? after 'values (' ', ' or ' = '. The '\g<1>' puts back the text before the ? and using '\g<>' avoids clashes with quoted_values that start with a number.)

Comments

[ev-br]

Allegedly, the worry is the SQL injection. I'd guess if you want to debug your queries, it's not yet in production, so do you really need to worry about that?

[bgusach]

Looks somehow like what I want, but I don't really get it to work? Maybe a more concrete example? thanks in advance

[bgusach]

This is what I wanted to avoid. Thanks for commenting anyway.

[user323094]

There's an error in the snippet, the line should be: cursor.execute("SELECT " + ", ".join(["quote(?)" for i in args]), args)