Managing & Using MySQL, 2nd Edition

Parameterized SQL

DB-API includes a mechanism for executing parameterized SQL statements using the execute( ) method as well as a more complex method called executemany( ) . Parameterized SQL is an SQL statement with placeholders to which you can pass arguments. As with a simple SQL execution, the first argument to execute( ) is an SQL string. Unlike the simple form, this SQL has placeholders for parameters specified by the second argument. A simple example is:

cursor.execute('INSERT INTO COLORS (COLOR, ABBR) VALUES (%s, %s)', 
               ('BLUE', 'BL'));

In this example, %s is placed in the SQL as a placeholder for values passed as the second argument. The first %s matches the first value in the paramter tuple, and the second %s matches the second value in the tuple.

Tip

MySQLdb treats all values as string values, even when their underlying database type is BIGINT, DOUBLE, DATE, etc. Thus, all conversion parameters should be %s even though you might think they should be %d or %f.

DB-API actually has several ways of marking SQL parameters. You can specify the format you wish to use by setting MySQLdb.paramstyle. The above example is MySQLdb.paramstyle = "format". The "format" value is the default for MySQLdb when a tuple of parameters is passed to execute( ) and is basically the set of placeholders from the ANSI C printf( ) function. Another possible value for MySQLdb.paramstyle is "pyformat". This value is the default when you pass a Python mapping as the second argument.

DB-API actually ...