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.

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 allows several other formats, but MySQLdb does not support them. This lack of support is particularly unfortunate since it is common practice in database applications in other languages to mark placeholders with a ?.

The utility of parameterized SQL becomes apparent when you use the executemany( ) method. This method enables you to execute the same SQL statement with multiple sets of parameters. For example, consider this code snippet that adds three rows to the database using execute( ):

cursor.execute("INSERT INTO COLOR (COLOR, ABBREV) VALUES ('BLUE', 'BL')");
cursor.execute("INSERT INTO COLOR (COLOR, ABBREV) VALUES ('PURPLE', 'PPL')");
cursor.execute("INSERT INTO COLOR (COLOR, ABBREV) VALUES ('ORANGE', 'ORN')");

That same functionality using executemany( ) looks like this:

cursor.executemany("INSERT INTO COLOR ( COLOR, ABBREV ) VALUES (%s, %s )",
                   (("BLUE", "BL"), ("PURPLE", "PPL"), ("ORANGE", "ORN")));

This one line executes the same SQL three times using different values in place of the placeholders. This can be extremely useful if you are using Python in batch processing.