home | O'Reilly's CD bookshelfs | FreeBSD | Linux | Cisco | Cisco Exam  

Book HomeManaging and Using MySQLSearch this book

Chapter 10. Python

If you do a lot of Perl programming but are not familiar with Python, you should definitely take a look at it. Python is an object-oriented scripting language that combines the strengths of languages such as Perl and Tcl with a clear syntax that lends itself to applications that are easy to maintain and extend. Learning Python,by Mark Lutz and David Asher (O'Reilly), provides an excellent introduction to Python programming. This chapter assumes you have a working understanding of the Python language.

To follow the content of this chapter, you will need to download and install MySQLdb, the MySQL version of DB-API. You can find the module at http://dustman.net/andy/python/MySQLdb. Chapter 20 includes directions on how to install MySQLdb.

10.1. DB-API

Like Java and Perl, Python has developed a unified API for database access: DB-API. This database API was developed by a Python Special Interest Group (SIG) called the Database SIG. The Database SIG is a group of influential Python developers interested in implementing Python access to various databases. On the positive side, DB-API is a very small, simple API. On the negative side, it has design flaws. Part of its problem is that, being small, it does not support many of the more complex features database programmers expect in a database API. It also fails to achieve true database independence. As a comprehensive database access API, it still leaves a lot to be desired and does not compete with more mature APIs such as Perl DBI and Java JDBC. You should therefore expect significant changes in this API over time.

10.1.2. Cursors

Cursors represent SQL statements and their results. The connection object provides your application with a cursor via the cursor( ) method:

cursor = conn.cursor( );

This cursor is the center of your Python database access. Through the execute( ) method, you send SQL to the database and process any results. The simplest form of database access is, of course, a simple insert:

conn = MySQLdb.connect(host='carthage', user='test', passwd='test', db='test');
cursor = conn.cursor( );
cursor.execute("INSERT INTO test (test_id, test_char) VALUES (1, 'test')");
print "Affected rows: ", cursor.rowcount;

In this example, the application inserts a new row into the database using the cursor generated by the MySQL connection. It then verifies the insert by printing out the number of rows affected. For inserts, this value should always be 1.

Query processing is a little more complex. Again, you use the execute( ) method to send SQL to the database. Instead of checking the affected rows, however, you grab the results from the cursor using one of many fetch methods. Example 10-1 shows a Python program processing a simple query.

Example 10-1. A simple query

import MySQLdb;

connection = None;
    connection = MySQLdb.connect(host="carthage", user="user", 
                                 passwd="pass", db="test");
    cursor = connection.cursor( );
    cursor.execute("SELECT test_id, test_val FROM test ORDER BY test_id");
    for row in cursor.fetchall( ):
        print "Key: ", row[0];
        print "Value: ", row[1];
     connection.close( );
    if connection:
        connection.close( );

The cursor object actually provides several fetch methods: fetchone( ), fetchmany( ), and fetchall( ). For each of these methods, a row is represented by a Python tuple. In Example 10-1, the fetchall( ) method fetches all the results from the query into a list of Python tuples. This method, like all the fetch methods, will throw an exception if the SQL was not a query.

Of course, fetching all the rows at once can be inefficient for large result sets. You can instead fetch each row one by one by using the fetchone( ) method. The fetchone( ) method returns a single row as a tuple where each element represents a column in the returned row. If you have already fetched all the rows of the result set, fetchone( ) will return None.

The final fetch method, fetchmany( ), is middle ground between fetchone( ) and fetchall( ). It enables an application to fetch a predefined number of rows at once. You can either pass in the number of rows you wish to see or rely on the value of cursor.arraysize to provide a default value.

10.1.3. 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 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( ):


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.

Library Navigation Links

Copyright © 2003 O'Reilly & Associates. All rights reserved.