Python SQLite how to get SQL string statement being executed

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.

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

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 securityquery_string = compile_query(query, username, password_hash)fancy_log_function(query_string)# executecursor.execute(query, username, password_hash)