Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

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 ?
                in psycopg2 there is also the cursor's mogrify() method, which lets you see exactly what command would be executed by a given query without (or before) executing it.
– kindall
                Mar 10, 2011 at 23:39
                In MySQLdb   '_last_executed'  holds the last query string to run even when an exception occurs. The property _executed is None if error. [stackoverflow.com/a/7190914/653372]
– nergeia
                Apr 3, 2013 at 9:52
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.
                It's certainly possible. It just means re-implementing the transformations performed by the ODBC driver. Normally this will just be some escaping.
– Michael Mior
                Oct 26, 2012 at 19:56
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.
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.
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.
                This approach won't work for a query against a native Access table. Such queries are processed by the Access Database Engine and what you would see in Wireshark would be the SMB/CIFS requests (and responses) for pages from the database file, which is something much lower-level than the SQL statement being processed. You could see the SQL text for a query against an ODBC linked table but it would be the raw SQL text with the parameter placeholders (e.g., SELECT * FROM tablename WHERE columnname > @P1), not with the parameter values inserted.
– Gord Thompson
                Jun 11, 2020 at 21:12
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"""
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)
                Not good also for me : I pass the variables in 'execute' for different reasons ,SQL injection but also because the 'execute' function modify the SQL query according to the Database type and to the columns type. Example : I may pass string or integer or date field without worry about quoting them in the query.
– philnext
                Mar 10, 2011 at 22:25
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.