25

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

Improve this question
5
  • any specific reason you don't want to build the string yourself? Commented Nov 30, 2012 at 14:22
  • I read somewhere it is better to use the substitution method. Don't recall why actually. Commented Nov 30, 2012 at 14:24
  • 1
    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? Commented Nov 30, 2012 at 14:30
  • It is save (search for: sql injection) and in some cases the database is able to "reuse" a query with other parameters while it would have to do more work with a completely new query Commented Nov 30, 2012 at 14:32
  • I guess it also adds the "..." for the strings when needed. Fact is, I would like to avoid re-factoring code for later in production. Moreover, as Argeman points, in some cases it's interesting to keep the structure of your query and your data separated. Commented Nov 30, 2012 at 14:38

4 Answers 4

Reset to default
49

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, e.g. logging.debug or logging.info.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

8

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

Improve this answer

2 Comments

Looks somehow like what I want, but I don't really get it to work? Maybe a more concrete example? thanks in advance
There's an error in the snippet, the line should be: cursor.execute("SELECT " + ", ".join(["quote(?)" for i in args]), args)
1

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)
Improve this answer

Comments

-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)
Improve this answer

1 Comment

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.