Python Sqlite How To Get Sql String Statement Being Executed

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_

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
deflog_and_execute(cursor, sql, *args):
    s = sql
    iflen(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.)

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

defcompile_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 passediflen(args) == 1and (not args[0]):
            number_of_arguments = 0if number_of_arguments != number_of_question_marks:
            returnf"Incorrect number of bindings supplied. The current statement uses {number_of_question_marks}, and there are {number_of_arguments} supplied."# compile queryfor 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)

Solution 4:

What about string formatting it?

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)