Python (MySQL & mSQL)

If you are not familiar with Python and you do a lot of Perl programming, you definitely want to take a look at it. Python is an object-oriented scripting language that combines the strengths of languages like Perl and Tcl with a clear syntax that lends itself to applications that are easy to maintain and extend. The O'Reilly & Associates, Inc. book Learning Python by Mark Lutz and David Asher provides an excellent introduction into Python programming. This chapter assumes a working understanding of the Python language, including the ability to add new modules into a Python installation.

Both APIs are virtually the same. We will, therefore, approach both modules together and note where they differ.

11.1. Basic Connectivity

The Python APIs are likely the simplest database APIs of any in this book. As with the other APIs, we need to start with database connectivity -- making the connection. Because Python has an interactive interface, the simplest way to demonstrate a connection is by using the command line interpreter. The following two Python sessions demonstrate simple database connections to MySQL and mSQL, respectively. The first example shows MySQL connectivity:

[4:30pm] athens> python

Python 1.5.1 (#1, Jun 13 1998, 22:38:15)  [GCC 2.7.2] on sunos5
Copyright 1991-1995 Stichting Mathematisch Centrum, Amsterdam
>>> import MySQL;
>>> db = MySQL.connect('athens.imaginary.com');
>>> db.selectdb('db_test');
>>> result = db.do('select test_val from test where test_id = 1');
>>> print result;
[['This is a MySQL test.']]
>>>

The mSQL code that does the same thing looks nearly identical:

[4:30pm] athens> python

Python 1.5.1 (#1, Jun 13 1998, 22:38:15)  [GCC 2.7.2] on sunos5
Copyright 1991-1995 Stichting Mathematisch Centrum, Amsterdam
>>> import mSQL;
>>> db = mSQL.connect('athens.imaginary.com');
>>> db.selectdb('db_test');
>>> result = db.query('select test_val from test where test_id = 1');
>>> print result;
[('This is a mSQL test.',)]
>>>

In both cases, your first task is to import the appropriate Python module. You should not use the from mSQL import * syntax since this import will pollute the namespace of your application. Instead, you should get a database handle instance via the connect() method in each module and perform your database access through that database handle.

The connect() call for both APIs is similar, though not identical. In the previous MySQL session, we are connecting to a database that allows global access. Because no user name or password is required, the connect() call for the MySQL session looks similar to the call for the mSQL session. You can, however, specify user name and password arguments when required by your MySQL database. For example, db = MySQL.connect('athens.imaginary.com','myuid','password'); will connect you to the MySQL server at athens.imaginary.com as the user "myuid" using the password "password." Neither API even requires a host name if you are connecting to the local machine. In such situations, they are smart enough to use a Unix domain socket (on Unix systems) for quicker connectivity.

The C API connection process is a two step process that requires you first to connect to the server, and then select which database you want to use. The Python APIs follow the same steps. In fact, under both MySQL and mSQL, the APIs for selecting a database are practically identical: selectdb(). For most uses, you will only ever pass this method a single parameter -- the database name. MySQL does support an optional second parameter that enables you to direct result set data storage to stay on the server until each row is requested. You would only want to use this version of the API when you are on a client where you know memory is limited or are retrieving unusually large result sets.

11.1.1. Queries

The two APIs differ slightly in the way you send statements to the database and how you deal with whatever you get back. The mSQL API is very simple with no support for cursor management. The MySQL API, on the other hand, supports the simple mSQL API along with a more complex set of APIs that more accurately mirror the C API and provide cursor support. In the Python world, cursor support is of dubious value since neither database allows in-place edits and the simpler API shown in the interactive sessions above allows you to navigate back and forth through a result set as easily as a cursor. We will, however, find a use for the cursor API later in the chapter because the same API that provides cursor support also gives us support for dynamic database access.

mSQL and the simple form of the MySQL API enable an application to query a database and get results in the form of a list. Unfortunately, the two APIs have two trivial, but annoying, differences in how you do this. First of all, the mSQL query method is called query() and the MySQL one is called do(). Each method accepts any SQL string as an argument. If the statement produces a result set, that result set is returned in the form of a list: a list of tuples for mSQL and a list of lists for MySQL.

For most uses, the difference in the return types is meaningless -- tuples are immutable. The code will almost always appear the same. You should nevertheless be aware that MySQL rows are lists and mSQL rows are tuples in the event you encounter a situation where the difference is relevant. Example 11-1 is a simple Python program that accesses MySQL and mSQL databases and prints out the results.

Example 11-1. Query Processing in Python for mSQL and MySQL

#!/usr/local/bin/python

# Import the modules
import mSQL, MySQL;

# Initialize database and query values
database = 'db_test';
query = 'SELECT test_id, test_val FROM test';

# Connect to the servers
msql = mSQL.connect();
mysql = MySQL.connect();

# Select the test databases
msql.selectdb(database);
mysql.selectdb(database);

# Run the query
m_result = msql.query(query);
my_result = mysql.do(query);

# Process the results from mSQL
for row in m_result:
    # Here, row is a tuple
    print "mSQL- test_id: ",row[0]," | test_val: ",row[1];

# Process the results from MySQL
for row in my_result:
    # Here, row is a list
    print "MySQL- test_id: ",row[0]," | test_val: ",row[1];

# Close the connections (mSQL only)
msql.close();

For both the MySQL and mSQL databases, the application loops through each row from the result set and prints out its data. Under mSQL, the first element in the tuple represents the first column from the query and the second element the second column. Similarly, the first element in the MySQL list represents the first column from the query and the second element the second column.

11.1.2. Updates

Issuing an update, insert, or delete to the database uses the same API as queries -- you just don't need any result set processing. In other words, call query() or do() and do nothing else. MySQL does have the added functionality of returning the AUTO_INCREMENT value of the table in question has an AUTO_INCREMENT field.

Example 11-1. Query Processing in Python for mSQL and MySQL

Chapter 11. Python

Contents:

11.1. Basic Connectivity

11.1.1. Queries

11.1.2. Updates