Print the actual query MySQLdb runs?

Question

I'm looking for a way to debug queries as they are executed and I was wondering if there is a way to have MySQLdb print out the actual query that it runs, after it has finished inserting the parameters and all that? From the documentation, it seems as if there is supposed to be a Cursor.info() call that will give information about the last query run, but this does not exist on my version (1.2.2).

This seems like an obvious question, but for all my searching I haven't been able to find the answer.

Answer 1

We found an attribute on the cursor object called cursor._last_executed that holds the last query string to run even when an exception occurs. This was easier and better for us in production than using profiling all the time or MySQL query logging as both of those have a performance impact and involve more code or more correlating separate log files, etc.

Hate to answer my own question but this is working better for us.

Answer 2

pymysql >= 1.0.3

According to the docs

cursor.statment

This read-only property returns the last executed statement as a string. The statement property can be useful for debugging and displaying what was sent to the MySQL server.

The string can contain multiple statements if a multiple-statement string was executed. This occurs for execute() with multi=True. In this case, the statement property contains the entire statement string and the execute() call returns an iterator that can be used to process results from the individual statements. The statement property for this iterator shows statement strings for the individual statements.

pymysql < 1.0.3

You can print the last executed query with the cursor attribute _last_executed:

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

Currently, there is a discussion how to get this as a real feature 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 didn't test it by now, but this commit indicates that the following code might work:

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

Answer 3

For me / for now _last_executed doesn't work anymore. 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

For mysql.connector:

cursor.statement

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

Answer 5

cursor.statement and cursor._last_executed raised AttributeError exception

cursor._executed

worked for me!

Answer 6

One way to do it is to turn on 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')

yields

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

Notice the argument(s) were inserted into the query, and that the query was logged even though the query failed.

Another way is to start the server with logging turned on:

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

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

Answer 7

I've had luck with cursor._last_executed generally speaking, but it doesn't work correctly when used with cursor.executemany(). That drops all but the last statement. Here's basically what I use now in that instance instead (based on tweaks from the actual MySQLDb cursor source):

def toSqlResolvedList( cursor, sql, dynamicValues ):
    sqlList=[]
    try:
        db = cursor._get_db()
        if isinstance( sql, unicode ): 
            sql = sql.encode( db.character_set_name() )
        for values in dynamicValues :
            sqlList.append( sql % db.literal( values ) )
    except: pass
    return sqlList    

Answer 8

This read-only property returns the last executed statement as a string. The statement property can be useful for debugging and displaying what was sent to the MySQL server. The string can contain multiple statements if a multiple-statement string was executed. This occurs for execute() with multi=True. In this case, the statement property contains the entire statement string and the execute() call returns an iterator that can be used to process results from the individual statements. The statement property for this iterator shows statement strings for the individual statements.

str = cursor.statement

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

Answer 9

I can't say I've ever seen

Cursor.info()

In the documentation, and I can't find it after a few minutes searching. Maybe you saw some old documentation?

In the mean time you can always turn on MySQL Query Logging and have a look at the server's log files.

Answer 10

assume that 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))