O'Reilly logo

Managing & Using MySQL, 2nd Edition by Hugh E. Williams, Randy Yarger, George Reese, Tim King

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required