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

Answer 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.

Answer 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.)

Answer 3

I've written a function that just fills in the question marks with the arguments.

It's weird that everyone sends you in the direction of using positional arguments, but no one thought about the need to log or preview or check queries in their totality.

Anyways, the code below assumes

• That there are no '?' tokens in the query that do not serve as postional argument tokens. I'm not sure whether that is always the case.
• That the value of the argument will be wrapped in quotes. This will not be the case when you use an argument for a table name for example. This usecase is unlikely though.

    def compile_query(query, *args):
        # test for mismatch in number of '?' tokens and given arguments
        number_of_question_marks = query.count('?')
        number_of_arguments = len(args)

        # When no args are given, an empty tuple is passed
        if len(args) == 1 and (not args[0]):
            number_of_arguments = 0
        
        if number_of_arguments != number_of_question_marks:
            return f"Incorrect number of bindings supplied. The current statement uses {number_of_question_marks}, and there are {number_of_arguments} supplied."

        # compile query
        for a in args:
            query = query.replace('?', "'"+str(a)+"'", 1)

        return query

Suggested usage

query = "INSERT INTO users (name, password) VALUES (?, ?)"

# sensitive query, we need to log this for security
query_string = compile_query(query, username, password_hash)
fancy_log_function(query_string)

# execute
cursor.execute(query, username, password_hash)