MySQLdb Basics

In this section we'll review the basic methods provided in the Python MySQLdb extension for establishing a connection to a MySQL server and processing simple SQL statements. These methods provide a foundation that we can use when working with stored programs. If you are already familiar with the MySQLdb extension, then you might like to skip forward to "Using Stored Programs with MySQLdb," later in the chapter.

16.2.1. Creating a Connection

Before we can use MySQLdb, we need to import the module. We can then use the connect() method of the base MySQLdb class to create a connection object. The connect() method takes five argumentshost, user, passwd, db, and portwhich identify the MySQL server, account, and database to which we intend to connect. Each of the arguments is optional, with sensible default values (localhost for the hostname, for instance).

Example 16-1 illustrates the basic technique.

Example 16-1. Connecting to MySQL from Python

Usually we will want to retrieve connection details from the command line. Python includes a powerful and useful command-line option parser that allows us to do this. Example 16-2 shows how to retrieve MySQL connection details from the command line and set up a connection.

Example 16-2. Getting connection details from the command line

Another option is to use a defaults file to store your connection details. In Example 16-3 we read our connection details from the file ./mysqldb, which contains name-value pairs including the host, user, and password options.

Example 16-3. Getting connection details from a defaults file

Older versions of the MySQLdb extension did not enable stored procedure result sets by default. To override the connection flagsand allow stored procedures to return result setsyou add the CLIENT.MULTI_RESULT flag to your connection options. You will also need to import the CLIENT identifer from the MySQLdb.constants module. Example 16-4 illustrates this procedure.

Example 16-4. Enabling procedure result sets in older versions of MySQLdb

16.2.2. Handling Exceptions

Python employs an exception-handling paradigm for error handling, and this paradigm is fully supported within the MySQLdb module.

Without exception handling , any errors result in program termination and a traceback message being generated. For instance, if our connection details were invalid, we could expect a message such as that shown in Example 16-5.

Example 16-5. Traceback error stack for invalid connection

We can handle the connection failure, or any other MySQL error, by enclosing the commands in a TRy/except block and catching any MySQLdb.Error that might be raised. If an error is raised by any statement within the try block, control will pass to the except block, which can interrogate the MySQLdb.Error structure to determine the error code (args[0]) and error message (args[1]). Example 16-6 shows this technique.

Example 16-6. Using an exception handler to catch MySQL errors

16.2.3. Executing a Simple Statement

To execute a SQL statement with MySQLdb, we create a cursor object using the cursor( ) method of the connection object. We can then use the execute( ) method of the cursor object to execute a statement. The rowcount property of the cursor object will reveal the number of rows affected by the SQL statement. Example 16-7 shows how to execute an UPDATE statement in this manner.

Example 16-7. Executing a simple SQL statement

16.2.4. Passing Parameters to a Statement

The execute() method allows for parameters to a statement to be passed as the second parameter to the execute() method. This parameter argument consists of a Python list containing the parameter values. These are substituted into the SQL statement contained in the execute clause. The standard Python string formats (%s) indicate the position of the parameters within the SQL.

In Example 16-8 we submit a SQL statement in a for loop which iterates through a few values of the old_manager parameter. For each employee formally reporting to these managers, we update the employees to report to a new manager.

Example 16-8. Using parameters when executing a SQL statement

16.2.5. Retrieving Rows from a Query

The Python DB API gives us a couple of methods for retrieving result sets from a cursor that executes a SELECT statement or another MySQL statement that might return a result set.

The simplest methodfetchone( )retrieves a single row from the cursor and returns that row as a Python list. To retrieve all rows, we create a loop that calls fetchone() until we encounter a None object. Columns in the row can be accessed by retrieving individual elements in the list. Example 16-9 shows this technique.

Example 16-9. Using fetchone( ) to retrieve rows from a cursor

The fetchall( ) method retrieves all rows in a single operation and returns them as a sequence of sequences (rows of columns).

In Example 16-10 we use fetchall( ) to retrieve all rows into the allrows object, which is a sequence of sequences. We iterate through the allrows sequence, creating row objects, each of which comprises a sequence of values for that row. We then print out each row value.

Example 16-10. Using fetchall( ) to retrieve rows

The fetchmany( ) method is a compromise between fetchone( ) and fetchall( ) in which we retrieve rows in batches. The size of each batch is defined as an argument to fetchmany( ).

In order to retrieve all rows using fetchmany( ), we need to construct two loops: one to retrieve each batch, and an inner loop to retrieve each row in the batch. We terminate the outer loop when we have retrieved an empty set from fetchmany( ).

Example 16-11 shows fetchmany( ) in action.

Example 16-11. Using fetchmany( ) to retrieve rows

Let's look at this code line by line:

In previous examples, we have retrieved rows as lists of columns. MySQLdb also supports retrieving rows as dictionaries in which each element is indexed by column name rather than by column offset. To retrieve rows as dictionaries, we specify the MySQLdb.cursors.DictCursor type as an argument to the con_cursor() method, as shown in Example 16-12.

Example 16-12. Using DictCursor to retrieve rows as Python dictionaries

It is not necessary to use one of the fetch family of methods, at least in recent versions of Python (2.2 and later). Instead, you can access the rows directly from the cursor following a successful execute(). In Example 16-13 we retrieve the column values from the cursor as a sequence.

Example 16-13. . Accessing column values directly from a cursor as a sequence

We can also retrieve the row directly into appropriately named variables, as shown in Example 16-14.

Example 16-14. Accessing column values directly from a cursor, using named variables

16.2.6. Managing Transactions

The Python DB API specifies methods to the connection class that can manipulate the autocommit setting and explicitly issue commits and rollbacks. The methods are:

autocommit({True|False})

Turns autocommit on (true) or off (False). This is equivalent to issuing a SET AUTOCOMMIT= statement.

commit( )

Commit the active transaction in the connection.

rollback( )

Roll back any active transaction in the connection.

Python exception handling is well suited to handling transaction control logic using a try /except /else structure:

try

This block contains the statements that constitute the transaction.

except

This block fires if any errors are encountered. It issues a rollback and notifies the user or calling application that the transaction has failed.

else

This block executes if no exceptions have been raised. It is responsible for committing the transaction and advising of successful completion.

Example 16-15 illustrates the use of the TRy/except/else structure and the connection transaction methods to manage transaction logic.

Example 16-15. Transaction logic in MySQLdb

16.2.7. Getting Metadata

If we need to retrieve information about the result set that will be returned by a cursor, we can use the description property of the cursor class. The description property consists of a sequence of sequences. The primary sequence consists of one sequence for each column in the result set. The sequence for each column consists of the following items:

• The name of the column
• A code representing the data type of the column
• The "display size" of the column, which can be used to allocate space in output formats
• The "internal" size of the column
• The precision (for numeric columns)
• The scale (for numeric columns)

You will most often want to access the first and third elements in the sequence so that you can format titles and display lengths for the output of a query. For instance, Example 16-16 uses cursor.description to generate titles for the output of a query.

Example 16-16. Retrieving result set metadata

16.2.8. Dynamically Processing a Result Set

Using cursor.description, we can handle the output of a query even if we don't know what the SQL will be when we are writing our Python code (such as whether the SQL was dynamically generated or provided by the user).

In Example 16-17, adapted from the Python Cookbook by David Ascher, Alex Martelli, and Anna Ravenscroft (O'Reilly, 2005), we define a function that will accept any SQL statement and "pretty print" the output.

Example 16-17. Dynamically processing a result set

Let us step through this example:

If we submit a SQL statement to this function, as shown below:

dynamic_sql("SELECT * FROM departments")

the function generates a nicely formatted result set:

DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION ------------- --------------- ---------- --------------- 1 DUPLIN 33 MORENO VALLEY 2 MADISON 19 BEAVER 3 MCHENRY 5 OKEECHOBEE 4 CHARITON 25 TULLYTOWN 5 SUMMERS 12 OLD CHURCH 6 LINCOLN 20 SWENGEL 7 CHAMPAIGN 37 AMF GREENSBORO 8 WILKES 23 CUSHING 9 CRAVEN 32 TAHOE PARADISE 10 COTTONWOOD 4 WICHITA 11 TAZEWELL 35 KLAWOCK