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

import MySQLdb

conn = MySQLdb.connect (host = "localhost",
 user = "root",
 passwd = "secret",
 db = "mysql",
 port=3306)

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

import MySQLdb
from optparse import OptionParser

parser = OptionParser( )
parser.add_option("-u","--username", dest="username",default="root")
parser.add_option("-H","--hostname",default="localhost")
parser.add_option("-p","--password",dest="password",default="")
parser.add_option("-d","--database",dest="database",default="mysql")
parser.add_option("-P","--port",dest="port",type="int",default=3306)
(options, args) = parser.parse_args( )

conn = MySQLdb.connect (host = options.hostname,
 user = options.username,
 passwd = options.password,
 db = options.database,
 port=options.port)

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

try:
 option_file = ".mysqldb"
 conn = MySQLdb.connect(read_default_file = "./.mysqldb")
 print "Connected"
except MySQLdb.Error, e:
 print "Top level Error %d: %s" % (e.args[0], e.args[1])
 sys.exit (1)

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

import MySQLdb
from MySQLdb.constants import CLIENT

conn = MySQLdb.connect( other connection_options ,
 client_flag=CLIENT.MULTI_RESULTS)

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

Traceback (most recent call last):
 File "C:	oolseclipseworkspacePython1MySQLexamples1.py", line 16, in ?
 port=options.port)
 File "C:	oolspythonLibsite-packagesMySQLdb\__init_ _.py", line 66, in Connect
 return Connection(*args, **kwargs)
 File "C:	oolspythonLibsite-packagesMySQLdbconnections.py", line 134, in _ _init_ _
 super(Connection, self).__init_ _(*args, **kwargs2)
_mysql_exceptions.OperationalError: (1045, "Access denied for user 'root'@'localhost'
(using password: NO)")

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

try:
 conn = MySQLdb.connect (host = options.hostname,
 user = options.username,
 passwd = options.password,
 db = options.database,
 port=options.port)

except MySQLdb.Error, e:
 print "Error connecting %d: %s" % (e.args[0], e.args[1])

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

 cursor1=conn.cursor( )
 cursor1.execute("UPDATE employees "+
 " SET manager_id=28"+
 " WHERE manager_id=24")
 print "%d rows updated" % cursor1.rowcount
 cursor1.execute("COMMIT")
 cursor1.close( )

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

 new_manager=24
 cursor1=conn.cursor( )
 for old_manager in [28,87,60]:
 cursor1.execute("UPDATE employees "+
 " SET manager_id=%s"+
 " WHERE manager_id=%s",
 [new_manager,old_manager])
 print "%d employees updated from manager %d to %d" % 
 (cursor1.rowcount,old_manager,new_manager)
 cursor1.execute("COMMIT")
 cursor1.close( )

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

 cursor1=conn.cursor( );
 cursor1.execute("SELECT department_id,department_name "+
 " FROM departments")
 while True:
 row = cursor1.fetchone ( )
 if not row:
 break
 print "%6d %-20s" % (row[0], row[1])
 cursor1.close( )

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

 cursor1=conn.cursor( );
 cursor1.execute("SELECT department_id,department_name "+
 " FROM departments")
 allrows=cursor1.fetchall( )
 for row in allrows:
 print "%6d %-20s" % (row[0],row[1])
 cursor1.close( )

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

1 cursor1=conn.cursor( )
2 cursor1.execute("SELECT department_id,department_name "+ 
3 " FROM departments ORDER BY department_id")
4 while True:
5 somerows=cursor1.fetchmany(10)
6 if not somerows :
7 break
8 for row in somerows:
9 print "%6d %-20s" % (row[0],row[1])
10 cursor1.close( )

Let's look at this code line by line:

Line(s)	Explanation
4	This is the outer loop in which we loop over batches returned by fetchmany( ). The loop will continue indefinitely, so we need to end it explicitly with a break statement.
5	Call fetchmany(10) to fetch a batch of 10 rows.
6 and 7	If fetchmany() returns an empty sequence, we break out of the loop we constructed on line 4, having retrieved all of the rows from the result set.
8 and 9	Iterate through each row in the batch of rows returned by fetchmany( ) and return the row value.

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

 cursor1 = conn.cursor (MySQLdb.cursors.DictCursor)
 cursor1.execute ("SELECT department_id,department_name "+
 " FROM departments")
 result_set = cursor1.fetchall ( )
 for row in result_set:
 print "%s, %s" % (row["department_id"], row["department_name"])

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

 cursor1=conn.cursor( );
 cursor1.execute("SELECT department_id,department_name "+
 " FROM departments")
 for row in cursor1:
 print "%6d %-20s" % (row[0], row[1])
 cursor1.close( )

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

 cursor1=conn.cursor( );
 cursor1.execute("SELECT department_id,department_name "+
 " FROM departments")
 for department_id, department_name in cursor1:
 print "%6d %-20s" % (department_id, department_name)
 cursor1.close( )

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

 try:
 conn.autocommit(False)
 csr1.execute("UPDATE account_balance "+
 " SET balance=balance-%s "+
 "WHERE account_id=%s",
 [tfer_amount,from_account])
 csr1.execute("UPDATE account_balance "+
 " SET balance=balance+%s "+
 "WHERE account_id=%s",
 [tfer_amount,to_account])

 except MySQLdb.Error, e:
 conn.rollback( )
 print "Transaction aborted: %d: %s" % (e.args[0], e.args[1])
 else:
 conn.commit( )
 print "Transaction succeeded"

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

 cursor1=conn.cursor( )
 cursor1.execute("SELECT *"+
 " FROM employees")
 print "%-20s %8s" % ("Name","Length")
 print "-----------------------------"
 for col_desc in cursor1.description:
 print "%-20s %8d " % 
 (col_desc[0],col_desc[3])

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

1 def dynamic_sql(sql):
2 names=[]
3 lengths=[]
4 dividers=[]
5 cursor1=conn.cursor( )
6 cursor1.execute(sql)
7 for col_desc in cursor1.description:
8 col_name=col_desc[0]
9 col_length=col_desc[2]
10 col_length=max(col_length,len(col_name))
11 names.append(col_name)
12 lengths.append(col_length)
13 dividers.append('-' * col_length)
14 format = " ".join(["%%-%ss" % col_len for col_len in lengths])
15 print format % tuple(names)
16 print format % tuple(dividers)
17 rows=cursor1.fetchall( )
18 for row in rows:
19 print format % tuple(row)
20 cursor1.close( )

Let us step through this example:

Line(s)	Explanation
1	Define the function and its input parameter: a string containing the SQL to be executed.
24	These are the empty lists that we will use to store column names, lengths, and divider strings (for our column underlines).
56	Create and execute a cursor with the SQL provided as a parameter to the function.
713	Loop through the elements (columns) in cursor1.description. Lines 89 retrieve the column name and display length.
10	Set the column length to be equal either to the display length or to the length of the column name (so that we have room for our titles if the column name is longer than the column data).
11 and 12	Store the column names and lengths in the appropriate list.
13	Append a series of dashes equal to the column length. These will form the column dividers for our output.
14	Create a format string that will be used to format column names, dividers, and column data. The format strings are simply string formats of the appropriate lengths for each column as determined in line 10.
15 and 16	Print the column headings for our formatted output.
1719	Issue a fetchall( ) to retrieve all rows from the query and then print each row according to the format we constructed in line 14.
20	All done! So we close the cursor.

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