Python's mysqldb obscure documentation

Question

There are many escape functions in the Python module mysqldb whose documentation I don't understand, and my efforts at looking them up have revealed nothing.

>>> print _mysql.escape.__doc__
escape(obj, dict) -- escape any special characters in object obj
using mapping dict to provide quoting functions for each type.
Returns a SQL literal string.

This documentation page says the same thing. But what's supposed to be in that "mapping dict"? I tried a couple of (mostly random) things and only go errors back. What's even more frustrating is that, while the escape_string() method works, its documentation string is:

>>> print _mysql.escape_string.__doc__
escape_string(s) -- quote any SQL-interpreted characters in string s.

Use connection.escape_string(s), if you use it at all.
_mysql.escape_string(s) cannot handle character sets. You are
probably better off using connection.escape(o) instead, since
it will escape entire sequences as well as strings.

So, I am better off using _mysql.escape(), am I? Well, uh... okay, but how? What on earth is that "mapping dict"? PHP, in that way at least, was a lot less cryptic.

Answer 1

Actually, you're better off using the higher-level interface, MySQLdb. (See the user's guide)

The _mysql functions are really just wrappers around the C API. They're meant to be an implementation detail, the kind of thing that wouldn't even show up in the documentation in PHP. The developers leave them sparsely documented on purpose to discourage people from using them unnecessarily, and also because you can refer to the MySQL C API documentation for the equivalent function, which is rather more complete.

Answer 2

I learned this by looking in /usr/lib/pymodules/python2.6/MySQLdb/connections.py to see how it called connection.escape. A little sniffing around leads to MySQLdb.converters.conversions. Here is a snippet:

{0: <class 'decimal.Decimal'>,
 1: <type 'int'>,
...
 <type 'dict'>: <built-in function escape_dict>,
 <type 'NoneType'>: <function None2NULL at 0xae9717c>,
 <type 'set'>: <function Set2Str at 0xae9709c>,
 <type 'str'>: <function Thing2Literal at 0xae971b4>,
 <type 'tuple'>: <built-in function escape_sequence>,
 <type 'object'>: <function Instance2Str at 0xae971ec>,
 <type 'unicode'>: <function Unicode2Str at 0xae9710c>,
 <type 'array.array'>: <function array2Str at 0xae9725c>,
 <type 'bool'>: <function Bool2Str at 0xae97294>}

You can use it like this:

import MySQLdb
import MySQLdb.converters
import datetime

now=datetime.datetime.now()
connection=MySQLdb.connect(
    host=HOST,user=USER,passwd=PASS,db=MYDB)
print(connection.escape((1,2,now),MySQLdb.converters.conversions))
# ('1', '2', "'2010-07-24 19:33:59'")

PS. Regarding Bobby Tables: For normal use of MySQLdb, you don't have to manually escape arguments. Just use parametrized arguments when calling cursor.execute, and MySQLdb will automatically quote the arguments for you.

For example:

sql='insert into students (name,grade,date) values (%s, %s, %s)'
args=("Robert'); DROP TABLE Students; --",60,now)   # no manual quotation necessary
cursor=connection.cursor()
cursor.execute(sql,args)