4. Using and extending

In general, it is probably wise to not directly interact with the DB API except for small applicatons. Databases, even SQL databases, vary widely in capabilities and may have non-standard features. The DB API does a good job of providing a reasonably portable interface but some methods are non-portable. Specifically, the parameters accepted by connect() are completely implementation-dependent.

If you believe your application may need to run on several different databases, the author recommends the following approach, based on personal experience: Write a simplified API for your application which implements the specific queries and operations your application needs to perform. Implement this API as a base class which should be have few database dependencies, and then derive a subclass from this which implements the necessary dependencies. In this way, porting your application to a new database should be a relatively simple matter of creating a new subclass, assuming the new database is reasonably standard.

For an example of this, see the author's SQLDict module, which allows standard queries to be defined and accessed using an object which looks like a dictionary, and reads/writes user-defined objects.

Because MySQLdb's Connection and Cursor objects are written in Python, you can easily derive your own subclasses. There are several Cursor classes in MySQLdb.cursors:

BaseCursor

The base class for Cursor objects. This does not raise Warnings.

CursorWarningMixIn

Causes the Warning exception to be raised on queries which produce warnings.

CursorStoreResultMixIn

Causes the Cursor to use the mysql_store_result() function to get the query result. The entire result set is stored on the client side.

CursorUseResultMixIn

Causes the cursor to use the mysql_use_result() function to get the query result. The result set is stored on the server side and is transferred row by row using fetch operations.

CursorTupleRowsMixIn

Causes the cursor to return rows as a tuple of the column values.

CursorDictRowsMixIn

Causes the cursor to return rows as a dictionary, where the keys are column names and the values are column values. Note that if the column names are not unique, i.e., you are selecting from two tables that share column names, some of them will be rewritten as table.column. This can be avoided by using the SQL AS keyword. (This is yet-another reason not to use * in SQL queries, particularly where JOIN is involved.

Cursor

The default cursor class. This class is composed of CursorWarningMixIn, CursorStoreResultMixIn, CursorTupleRowsMixIn, and BaseCursor, i.e. it raises Warning, uses mysql_store_result(), and returns rows as tuples.

DictCursor

Like Cursor except it returns rows as dictionaries.

SSCursor

A "server-side" cursor. Like Cursor but uses CursorUseResultMixIn. Use only if you are dealing with potentially large result sets.

SSDictCursor

Like SSCursor except it returns rows as dictionaries.

XXXCursorNW

Cursors with the "NW" suffix do not raise Warnings.

For an example of how to use these classes, read the code. If you need something more exotic than this, you will have to roll your own.