Debugging SQL in Python

When using MySQL with Python, you may want to use the mysqlclient
library, which is what most people do and which will work just fine.
Or you are using the official MySQL 8 Connector/Python package, which will behave slightly differently, but maybe supports the unique MySQL 8 feature already that is not in mysqlclient
, yet.
Your SQL may be hand-writtten, or it may be generated using SQL Alchemy, Django or some other package. If the latter is the case, it may be useful to be able to see the actual SQL string that has been sent to the database in order to facilitate interactive debugging.
Using mysqlclient
If you are using mysqlclient
with Python, the class internally sends the SQL to the server in a method _query(self, q)
in the Cursor class (source
).
The _query(self, q)
method itself updates an _executed
member of the cursor to store the literal query string sent to the server.
But it does so only after having sent the query.
On error, an exception is raised and _executed
is not updated (source
).
To always get access to the actual query string, define a class DebugCursor
, and specify it with your connection **kwargs
. In DebugCursor
, do the needful.
#! /usr/bin/env python3
import MySQLdb
import MySQLdb.cursors
class DebugCursor(MySQLdb.cursors.DictCursor):
def _query(self, q):
print(f"Debug: {q}")
super()._query(q)
db_config = dict(
host="localhost",
user="kris",
passwd="secret",
db="kris",
cursorclass=DebugCursor, # referenced class must be defined before
)
db = MySQLdb.connect(**db_config)
c = db.cursor(()
sql = "select d from testtable where id = 3"
c.execute(sql)
print(c.fetchall())
In our class DebugCursor
, we inherit from our cursor of choice.
We override _query(self, q)
, printing the query string q
, and then calling the parent class implementation.
The output:
$ python3 probe.py
Debug: b'select d from testtable where id = 3'
({'d': 'drei'},)
We can use this to trace-log all literal SQL before it is being executed, even if the query string is invalid or contains syntax errors.
Using MySQL Connector/Python
If you are using Oracle MySQL Connnector/Python to connect to the database, the implementation can internally use Cython or a Pure Python implementation of the protocol.
Both implementations behave slightly differently, unfortunately.
Only the Pure Python implementation can be debugged easily in all circumstances.
It is therefore important that you specify use_pure=True
with your connection **kwargs
.
The raw SQL statement will be found in the cursor’s _executed
member.
If you are using multi-statements (don’t!), they will be logged in the _executed_list
.
We write:
import mysql.connector
import mysql.connector.errors
db_config = dict(
host="127.0.0.1",
user="kris",
passwd="geheim",
db="kris",
use_pure=True,
)
db = mysql.connector.connect(**db_config)
c = db.cursor()
print("=== Valid SQL: ")
sql = "select d from testtable where id = 3"
c.execute(sql)
print(f"Debug: {c._executed}")
print(c.fetchall())
print()
print("=== Invalid SQL: ")
sql = "syntaxerror d from testtable where id = 3"
try:
c.execute(sql)
except mysql.connector.errors.ProgrammingError as e:
print(f"Debug: {c._executed}")
print(f"Error: {e}")
The output looks like this:
$ python3 probe.py
=== Valid SQL:
Debug: b'select d from testtable where id = 3'
[('drei',)]
=== Invalid SQL:
Debug: b'syntaxerror d from testtable where id = 3'
Error: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'syntaxerror d from testtable where id = 3' at line 1
Again, we can use this to trace raw SQL and identify actual generated SQL syntax much easier. This will allow us to pick up the literal SQL string, and debug interactively.
The same program, when run without use_pure=True
, will not update c._executed
properly if the SQL is malformed.
It will produce the following output, which is worthless for debugging:
$ python3 probe.py
=== Valid SQL:
Debug: b'select d from testtable where id = 3'
[('drei',)]
=== Invalid SQL:
Debug: b'select d from testtable where id = 3'
Error: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'syntaxerror d from testtable where id = 3' at line 1
Note how c._executed
still holds the previous statement and not the statement that actually errored.