Managing & Using MySQL, 2nd Edition

Proprietary Operations

In general, you should stick to the published DB-API specification when writing database code in Python. There will be some instances, however, when you may need access to MySQL-specific functionality. MySQLdb is actually built on top of the MySQL C API and exposes that API to programs that wish to use it. This ability is particularly useful for applications that want metadata about the MySQL database.

Basically, MySQLdb exposes most C methods except those governing result set processing, because cursors are a better interface for that functionality. Example 10-2 shows a trivial application that uses the list_dbs( ) and list_tables( ) methods from the C API to loop through all the tables in all the databases on the MySQL server and print out the first row from each table. Needless to say, do not run this application against a production machine.

Example 10-2. A Python application using proprietary functionality

import MySQLdb;

conn = None;
try:
    conn = MySQLdb.connect(host="carthage", user="test", 
                           passwd="test", db="test");
    for db in conn.list_dbs(  ):
        for tbl in conn.list_tables(db[0]):
            cursor = conn.cursor(  );
            cursor.execute("SELECT * FROM " + tbl[0]);
            print cursor.fetchone(  );
            cursor.close(  );
except:
    if conn:
        conn.close(  );

Chapter 20 lists the proprietary APIs exposed by MySQLdb.