Print current MySQLdb query?

Question

I am looking for a way to debug queries as they execute, and I was wondering if there is a way for MySQLdb to print the actual query that it was running after it has finished inserting parameters and all that? From the documentation, it seems that there will be a call to Cursor.info (), which will provide information about the last time the request was run, but this does not exist in my version (1.2.2).

This seems like an obvious question, but for all my searches, I could not find the answer. Thanks in advance.

Answer 1

We found an attribute of a cursor object called cursor._last_executed , which contains the last line of the query, which is run even when an exception occurs. It was easier and better for us in production than using profiling all the time or maintaining MySQL logging, since both of them have a performance impact and include more code or more separate log files correlate, etc.

I hate answering my own question, but it works better for us.

Answer 2

You can print the last executed request using the _last_executed cursor _last_executed :

 try: cursor.execute(sql, (arg1, arg2)) connection.commit() except: print(cursor._last_executed) raise 

It is currently being discussed how to get this as a real function in pymysql (see pymysql issue # 330: Add mogrify to Cursor, which returns the exact string to be executed ; pymysql should be used instead of MySQLdb )

edit: I have not tested it, but this commit indicates that the following code may work:

 cursor.mogrify(sql, (arg1, arg2)) 

Answer 3

For me / at the moment _last_executed no longer works. In the current version, you want to access

cursor.statement .

see https://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursor-statement.html

Answer 4

One way to do this is to enable profiling :

 cursor.execute('set profiling = 1') try: cursor.execute('SELECT * FROM blah where foo = %s',[11]) except Exception: cursor.execute('show profiles') for row in cursor: print(row) cursor.execute('set profiling = 0') 

gives

 (1L, 0.000154, 'SELECT * FROM blah where foo = 11') 

Note that the argument was inserted into the request and that the request was logged even if the request failed.

Another way is to start the server with logging enabled:

 sudo invoke-rc.d mysql stop sudo mysqld --log=/tmp/myquery.log 

Then you need to sift through /tmp/myquery.log to find out what the server received.

Answer 5

For mysql.connector:

 cursor.statement 

https://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursor-statement.html

Answer 6

I can not say that I have ever seen

 Cursor.info() 

In the documentation, and I can not find it after a few minutes of searching. Did you see some old documentation?

At the same time, you can always enable MySQL Query Logging and view server log files.

Answer 7

Suppose your sql is like select * from table1 where 'name' = %s

 from _mysql import escape from MySQLdb.converters import conversions actual_query = sql % tuple((escape(item, conversions) for item in parameters))