Python's sqlite3 module: get query with escaped parameters [duplicate]

Question

I'm trying to debug a SQL statement generated with sqlite3 python module...

c.execute("SELECT * FROM %s WHERE :column = :value" % Photo.DB_TABLE_NAME, {"column": column, "value": value})

It is returning no rows when I do a fetchall()

When I run this directly on the database

SELECT * FROM photos WHERE album_id = 10

I get the expected results.

Is there a way to see the constructed query to see what the issue is?

Answer 1

To actually answer your question, you can use the set_trace_callback of the connection object to attach the print function; this will make all queries get printed when they are executed. Here is an example in action:

# Import and connect to database
import sqlite3
conn = sqlite3.connect('example.db')
# This attaches the tracer
conn.set_trace_callback(print)
# Get the cursor, execute some statement as an example
c = conn.cursor()
c.execute("CREATE TABLE stocks (symbol text)")
t = ('RHAT',)
c.execute("INSERT INTO stocks VALUES (?)", t)
c.execute('SELECT * FROM stocks WHERE symbol=?', t)
print(c.fetchone())

This produces the output:

CREATE TABLE stocks (symbol text)
BEGIN 
INSERT INTO stocks VALUES ('RHAT')
SELECT * FROM stocks WHERE symbol='RHAT'
('RHAT',)