Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Python SQLite how to get SQL string statement being executed

Tags:

python

sql

sqlite

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

like image 984
bgusach Avatar asked Nov 30 '12 13:11

bgusach


People also ask

How do I retrieve data from SQLite database in Python?

First, establish a connection to the SQLite database by creating a Connection object. Next, create a Cursor object using the cursor method of the Connection object. Then, execute a SELECT statement. After that, call the fetchall() method of the cursor object to fetch the data.

How do I see tables in SQLite Python?

To list all tables in an SQLite3 database, you should query the sqlite_master table and then use the fetchall() to fetch the results from the SELECT statement. The sqlite_master is the master table in SQLite3, which stores all tables.


3 Answers

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.

like image 124
holdenweb Avatar answered Oct 01 '22 17:10

holdenweb


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

like image 37
CL. Avatar answered Oct 01 '22 16:10

CL.


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)
like image 28
felixc Avatar answered Oct 01 '22 17:10

felixc