How to see the real SQL query in Python cursor.execute using pyodbc and MS-Access

Question

I use the following code in Python (with pyodbc for a MS-Access base).

cursor.execute("select a from tbl where b=? and c=?", (x, y))

It's Ok but, for maintenance purposes, I need to know the complete and exact SQL string send to the database.
Is it possible and how ?

Answer 1

My answer above becomes problematic with the where clauses because of sql injection attacks. cursor.mogrify() seems to resolve the issue.

Try this, it works for me:

query="select a from tbl where b=? and c=?"
values=(x,y)
cursor.execute(query,values)
sql = cursor.mogrify(query, values)
print(sql)

Answer 2

How about this:

query="select a from tbl where b=? and c=?"
values=(x,y)
cursor.execute(query,values)
print(query%values)

Answer 3

Since pyodbc doesn't have a way to see the query BEFORE it is executed. You can pre-populate the query manually just to get an idea of what it will end up looking like. It's not going to work as the actual query, but it helped me figure out if I had any errors in a query that needed more than 40 parameters.

query = """select * from [table_name] where a = ? and b = ?"""

parameter_list = ['dog', 'cat'] # List of parameters, a = 'dog', b = 'cat'.

query_list = list(query) # Split query string into individual characters.

# Loop through list and populate the question marks.
for i in range(len(parameter_list)):
    for idx, val in enumerate(query_list):
        if val == '?':
            query_list[idx] = str(parameter_list[i])
            break

# Rejoin the query string.
query_populate = ''.join(query_list)

#### Result ####
"""select * from [table_name] where a = dog and b = cat"""

Answer 4

The answer is : NO. I posted my question on the project's home Google Code (and in the Google Group) and the answer is:

Comment #1 on issue 163 by l...@deller.id.au: cursor.mogrify return query string http://code.google.com/p/pyodbc/issues/detail?id=163

For reference here is a link to the pyscopg documentation of their "mogrify" cursor method that the reporter is referring to: http://initd.org/psycopg/docs/cursor.html#cursor.mogrify

pyodbc does not perform any such translations of the SQL: it passes parameterized SQL straight through to the ODBC driver verbatim. The only processing involved is translating parameters from Python objects to C types supported by the ODBC API.

Some transformation on the SQL may be performed in the ODBC driver before it is sent to the server (eg Microsoft SQL Native Client does this) but these transformations are hidden from pyodbc.

Consequently I think it is not feasible to provide a mogrify function in pyodbc.

Answer 5

I used Wireshark to see actual SQL string in pyodbc. It may help if you use unprotected server connection for development.

Answer 6

You can use print cursor._last_executed to get the last executed query.

Read in this answer that you can also use print cursor.mogrify(query,list) to see the full query before or after executing.

Answer 7

I'd check cursor._last_executed afterwards, but if you want them printed out in real time without changing every execute try this monkey patch:

def log_queries(cur):
    def _query(q):
        print q # could also use logging
        return cur._do_query(q)
    cur._query = _query

conn = MySQLdb.connect( read_default_file='~/.my.cnf' )
cur = conn.cursor()
log_queries(cur)
cur.execute('SELECT %s, %s, %s', ('hello','there','world'))

It's very dependent on MySQLdb (and could break in later versions). It works because cur._query currently simply calls calls._do_query and returns its result.

Answer 8

For debug purpuse I created a check function that simply replaces ? with the query values... it's not high technology :) but it works! :D

def check_sql_string(sql, values):
    unique = "%PARAMETER%"
    sql = sql.replace("?", unique)
    for v in values: sql = sql.replace(unique, repr(v), 1)
    return sql

query="""SELECT * FROM dbo.MA_ItemsMonthlyBalances
                   WHERE Item = ? AND Storage = ? AND FiscalYear = ? AND BalanceYear = ? AND Balance = ? AND BalanceMonth = ?"""
values = (1,2,"asdasd",12331, "aas)",1)

print(check_sql_string(query,values))

The result:

SELECT * FROM dbo.MA_ItemsMonthlyBalances WHERE Item = 1 AND Storage = 2 AND FiscalYear = 'asdasd' AND BalanceYear = 12331 AND Balance = 'aas') AND BalanceMonth = 1

With this you can log or do whatever you want:

rowcount = self.cur.execute(query,values).rowcount
logger.info(check_sql_string(query,values))

If you need just add some exception catching to the function.

Answer 9

It differs by driver. Here are two examples:

import MySQLdb
mc = MySQLdb.connect()
r = mc.cursor()
r.execute('select %s, %s', ("foo", 2))
r._executed
"select 'foo', 2"

import psycopg2
pc = psycopg2.connect()
r = pc.cursor()
r.execute('select %s, %s', ('foo', 2))
r.query
"select E'foo', 2"

Answer 10

Write the sql string and then execute it:

sql='''select a 
       from tbl 
       where b=? 
       and c=? '''

cursor.execute(sql, x, y)
print 'just executed:',(sql, x,y)

Now you can do whatever you want with the SQL statement.

Answer 11

Depending on the driver you use, this may or may not be possible. In some databases, the parameters (?s) are simply replaced, as user589983's answer suggests (though the driver will have to do some things like quoting strings and escaping quotes within those strings, in order to result in a statement that's executable).

Other drivers will ask the database to compile ("prepare") the statement, and then ask it to execute the prepared statement using the given values. It's in this way that using prepared or parameterized statements helps avoid SQL injections -- at the time the statement is executing, the database "knows" what is part of the SQL you wish to run, and what is part of a value being used within that statement.

Judging by a quick skimming of the PyODBC documentation, it doesn't appear that getting the actual SQL executed is possible, but I may be wrong.