Print the actual query MySQLdb runs?

Solution 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.

Solution 2:

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

Solution 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

Solution 4:

For mysql.connector:

cursor.statement

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

Solution 5:

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.