Python: RDBMS: MySQL

3rd July 2021 at 1:52pm

Libraries

  • PyMySQL/mysqlclient-python: fork of MySQLdb1 with Python 3 support. Django recommend using this one, should consider using it first
  • farcepest/MySQLdb1: unmaintained, but still stable. Use mysqlclient-python instead
  • PyMySQL/PyMySQL: pure Python implement. Easier to install and can be "greened" by gevent, but doesn't support low level APIs _mysql provides like data_seek, store_result and use_result

Cursor behavior

All of these libraries use cursor.Cursor() by default (refer), which contains CursorStoreResultMixIn. When cursor.execute() is executed, the cursor would use mysql_store_result() to retrieve all result from server, which may cause MemoryError in a 32-bit Python environment.

You can use cursor.SSCursor instead to avoid this problem. cursor.SSCursor contains CursorUseResultMixIn, which use mysql_use_result() to fetch result set row by row.

conn = MySQLdb.connect(cursorclass=cursor.SSCursor, ...)

字符串转义

MySQL C API 中的 mysql_real_escape_string() 可以将字符串转义成可以安全地在 SQL 语句中使用的字符串。在 Python 的 mysql-client 库中,调用 conn.escape_string() 即是调用了底层的 mysql_real_escape_string()

mysql_real_escape_string() 会根据其所连接的数据库 charset 来做转义。mysql_escape_string() 不会。