MySQL Stored Procedure Programming (2009)

Part III. Using MySQL Stored Programs in Applications

Chapter 16. Using MySQL Stored Programs with Python

Python is an open source, object-oriented, cross-platform language commonly used for system administration, application development, and many other purposes. Python is often used in very similar types of applications as Perl. However, Python devotees believe that Python offers many advantages over Perl in that it is natively object oriented, results in more readable and maintainable code, and enables greater programmer productivity, especially for large-scale developments. (Perl devotees have a different opinion, of course!)

The Python language includes a specification for a vendor-independent database-access API, the Python Database API Specification v2.0. You can find the specification for this API at http://www.python.org/peps/pep-0249.html. The MySQL implementation of this API is called MySQLdb , and is available at http://sourceforge.net/projects/mysql-python.

In this chapter we will review how to interact with a MySQL database using Python and the MySQLdb module, and explain how to exploit MySQL stored programs through this interface.

Installing the MySQLdb Extension

You can obtain the MySQLdb module for Python at http://sourceforge.net/projects/mysql-python. For Windows users, the MySQLdb module is packaged as a Windows executable. For Linux or Unix users, the module is packaged as a gzip tar archive; you should download the archive and, after unpacking it, run the following commands in the root directory of the archive (as the root user or using the sudo command):

    python setup.py build

    python setup.py install

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.

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 arguments—host, user, passwd, db, and port—which 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 flags—and allow stored procedures to return result sets—you add the CLIENT.MULTI_RESULT flag to your connection options. You will also need to import theCLIENT 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)

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:\tools\eclipse\workspace\Python1\MySQLexamples1.py", line 16, in ?

    port=options.port)

  File "C:\tools\python\Lib\site-packages\MySQLdb\__init_ _.py", line 66, in Connect

    return Connection(*args, **kwargs)

  File "C:\tools\python\Lib\site-packages\MySQLdb\connections.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])

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(  )

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(  )

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 method—fetchone( )—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(  )

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"

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])

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.

2–4

These are the empty lists that we will use to store column names, lengths, and divider strings (for our column underlines).

5–6

Create and execute a cursor with the SQL provided as a parameter to the function.

7–13

Loop through the elements (columns) in cursor1.description. Lines 8–9 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.

17–19

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

Using Stored Programs with MySQLdb

The techniques for calling stored programs with MySQLdb differ only slightly from those for using traditional SQL statements. That is, we create a cursor, execute the SQL to call the stored program, and iterate through result sets. The two key differences are that we must potentially deal with multiple result sets and that we may have to retrieve output parameters from the stored program call.

If you read the Python DB API specification, you might notice that the specification includes a cursor method for directly calling stored programs—the callproc cursor method. The callproc method was not implemented in MySQLdb as we went to press, although the maintainer ofMySQLdb, Andy Dustman, is working on an implementation that will likely be available by the time you read this. Check out the book's web site (see the Preface) for an update. This method is not implemented in MySQLdb (version 1.2, at least). Luckily, everything you need to call stored programs is available through other methods, so you don't need to wait for callproc to use stored programs with Python.

Calling Simple Stored Programs

The procedure for calling a simple stored program—one that returns no result sets and takes no parameters—is the same as for executing any non-SELECT statement. We create a cursor and execute the SQL text, as shown in Example 16-18.

Example 16-18. Executing a simple stored procedure

       cursor1=conn.cursor(  )

       cursor1.execute("call simple_stored_proc(  )")

       cursor1.close(  )

If the stored procedure takes input parameters, we can supply them using the second argument to the execute( ) method. In Example 16-19, we define a Python function that accepts input parameters and applies them to the sp_apply_discount procedure.

Example 16-19. Supplying input parameters to a stored procedure

def apply_discount(p1,p2):

       cursor1=conn.cursor(  )

       cursor1.execute("call sp_apply_discount(%s,%s)",(p1,p2))

       cursor1.close(  )

Retrieving a Single Stored Program Result Set

Retrieving a single result set from a stored program is exactly the same as retrieving a result set from a SELECT statement. Example 16-20 shows how to retrieve a single result set from a stored procedure.

Example 16-20. Retrieving a single result set from a stored procedure

       cursor1=conn.cursor(MySQLdb.cursors.DictCursor)

       cursor1.execute("CALL sp_emps_in_dept(%s)",(1))

       for row in cursor1:

           print "%d %s %s" % \

               (row['employee_id'],row['surname'],row['firstname'])

       cursor1.close(  )

If you receive a 1312 error at this point (PROCEDURE X can't return a result set in the given context), then it is an indication that you need to specify the CLIENT.MULTI_RESULTS flag in your connection, as outlined in "Creating a Connection" earlier in this chapter.

Retrieving Multiple Stored Program Result Sets

Unlike other SQL statements, stored programs can return multiple result sets. To access more than one result set, we use the nextset( ) method of the cursor object to move to the next result set.

For instance, suppose that we have a stored procedure that returns two result sets, as shown in Example 16-21.

Example 16-21. Stored procedure that returns two result sets

CREATE PROCEDURE sp_rep_report(in_sales_rep_id int)

    READS SQL DATA

BEGIN

     SELECT employee_id,surname,firstname

       FROM employees

      WHERE employee_id=in_sales_rep_id;

     SELECT customer_id,customer_name

       FROM customers

      WHERE sales_rep_id=in_sales_rep_id;

END;

To retrieve the two result sets, we fetch the first result set, call nextset( ), then retrieve the second result set. Example 16-22 shows this technique.

Example 16-22. Retrieving two results from a stored procedure

       cursor=conn.cursor(MySQLdb.cursors.DictCursor)

       cursor.execute("CALL sp_rep_report(%s)",(rep_id))

       print "Employee details:"

       for row in cursor:

           print "%d %s %s" % (row["employee_id"],

                               row["surname"],

                               row["firstname"])

       cursor.nextset(  )

       print "Employees customers:"

       for row in cursor:

           print "%d %s" % (row["customer_id"],

                            row["customer_name"])

       cursor.close(  )

Retrieving Dynamic Result Sets

It's not at all uncommon for stored programs to return multiple result sets and for the result set structures to be unpredictable. To process the output of such a stored program, we need to combine the nextset( ) method with the cursor.description property described in the "Getting Metadata" section earlier in this chapter. The nextset( ) method returns a None object if there are no further result sets, so we can keep calling nextset( ) until all of the result sets have been processed. Example 16-23 illustrates this technique.

Example 16-23. Retrieving dynamic result sets from a stored procedure

1 def call_multi_rs(sp):

2   rs_id=0;

3   cursor = conn.cursor(  )

4   cursor.execute ("CALL "+sp)

5   while True:

6      data = cursor.fetchall(  )

7      if cursor.description:  #Make sure there is a result

8         rs_id+=1

9         print "\nResult set %3d" % (rs_id)

10         print "--------------\n"

11         names = []

12         lengths = []

13         rules = []

14         for field_description in cursor.description:

15            field_name = field_description[0]

16            names.append(field_name)

17            field_length = field_description[2] or 12

18            field_length = max(field_length, len(field_name))

19            lengths.append(field_length)

20            rules.append('-' * field_length)

21            format = " ".join(["%%-%ss" % l for l in lengths])

22            result = [ format % tuple(names), format % tuple(rules) ]

23         for row in data:

24            result.append(format % tuple(row))

25         print "\n".join(result)

26      if cursor.nextset(  )==None:

27         break

28   print "All rowsets returned"

29   cursor.close(  )

Example 16-23 implements a Python function that will accept a stored procedure name (together with any arguments to the stored procedure), execute the stored procedure, and retrieve any result sets that might be returned by the stored procedure.

Let's step through this code:

Line(s)

Explanation

2

rs_id is a numeric variable that will keep track of our result set sequence.

3–4

Create a cursor and execute the stored procedure call. The sp variable contains the stored procedure text and is passed in as an argument to the Python function.

5

Commence the loop that will be used to loop over all of the result sets that the stored procedure call might return.

6

Fetch the result set from the cursor.

7

Ensure that there is a result set from the stored procedure call by checking the value of cursor.description. This is a workaround to a minor bug in the MySQLdb implementation (version 1.2) in which nextset( ) returns True even if there is no next result set, and only returns False once an attempt has been made to retrieve that null result. This bug is expected to be resolved in an upcoming version of MySQLdb.

11–22

Determine the structure of the result set and create titles and formats to nicely format the output. This is the same formatting logic we introduced in Example 16-17.

23–25

Print out the result set.

26

Check to see if there is another result set. If there is not, nextset( ) returns None and we issue a break to exit from the loop. If there is another result set, we continue the loop and repeat the process starting at line 6.

28 and 29

Acknowledge the end of all result sets and close the cursor.

Example 16-24 shows a stored procedure with "dynamic" result sets. The number and structure of the result sets to be returned by this stored procedure will vary depending on the status of the employee_id provided to the procedure.

Example 16-24. Stored procedure with dynamic result sets

CREATE PROCEDURE sp_employee_report

    (in_emp_id INTEGER,

     OUT out_customer_count INTEGER)

BEGIN

  SELECT employee_id,surname,firstname,date_of_birth

    FROM employees

   WHERE employee_id=in_emp_id;

  SELECT department_id,department_name

    FROM departments

   WHERE department_id=

         (select department_id

            FROM employees

           WHERE employee_id=in_emp_id);

  SELECT COUNT(*)

    INTO out_customer_count

    FROM customers

   WHERE sales_rep_id=in_emp_id;

  IF out_customer_count=0 THEN

    SELECT 'Employee is not a current sales rep';

  ELSE

    SELECT customer_name,customer_status

      FROM customers

     WHERE sales_rep_id=in_emp_id;

    SELECT customer_name,SUM(sale_value) as "TOTAL SALES",

           MAX(sale_value) as "MAX SALE"

      FROM sales JOIN customers USING (customer_id)

     WHERE customers.sales_rep_id=in_emp_id

     GROUP BY customer_name;

  END IF;

END

We can use the Python function shown in Example 16-23 to process the output of this stored procedure. We would invoke it with the following command:

    call_multi_rs("sp_employee_report(1,@out_customer_count)")

We pass in 1 to produce a report for employee_id=1; the @out_customer_count variable is included to receive the value of the stored procedure's output parameter (see the next section, "Obtaining Output Parameters"). Partial output from this procedure is shown in Example 16-25.

Example 16-25. Output from a dynamic stored procedure call

Result set   1

--------------

employee_id surname firstname date_of_birth

----------- ------- --------- -------------------

1           FERRIS  LUCAS     1960-06-21 00:00:00

Result set   2

--------------

department_id department_name

------------- ---------------

14            KING

Result set   3

--------------

customer_name                   customer_status

------------------------------- ---------------

GRAPHIX ZONE INC DE             None

WASHINGTON M AAAIswAABAAANSjAAS None

Obtaining Output Parameters

As you know, stored procedures can include OUT or INOUT parameters, which can pass data back to the calling program. The MySQLdb extension does not provide a method to natively retrieve output parameters , but you can access their values through a simple workaround.

Earlier, in Example 16-24, we showed a stored procedure that returned multiple result sets, but also included an output parameter. We supplied a MySQL user variable (prefixed by the @ symbol) to receive the value of the parameter. All we need to do now, in Example 16-26, is to retrieve the value of that user variable using a simple SELECT.

Example 16-26. Retrieving the value of an output parameter

       call_multi_rs("sp_employee_report(1,@out_customer_count)")

       cursor2=conn.cursor(  )

       cursor2.execute("SELECT @out_customer_count")

       row=cursor2.fetchone(  )

       print "Customer count=%s" % row[0]

       cursor2.close(  )

What about INOUT parameters? This is a little trickier, although luckily we don't think you'll use INOUT parameters very much (it's usually better practice to use separate IN and OUT parameters). Consider the stored procedure in Example 16-27.

Example 16-27. Stored procedure with an INOUT parameter

CREATE PROCEDURE randomizer(INOUT a_number FLOAT)

  NOT DETERMINISTIC NO SQL

  SET a_number=RAND(  )*a_number;

To handle an INOUT parameter, we first issue a SQL statement to place the value into a user variable, execute the stored procedure, and then retrieve the value of that user parameter. Code that wraps the stored procedure call in a Python function is shown in Example 16-28.

Example 16-28. Handling an INOUT stored procedure parameter

def randomizer(python_number):

   cursor1=conn.cursor(  )

   cursor1.execute("SET @inoutvar=%s",(python_number))

   cursor1.execute("CALL randomizer(@inoutvar)")

   cursor1.execute("SELECT @inoutvar")

   row=cursor1.fetchone(  )

   cursor1.close(  )

   return(row[0])

A Complete Example

In this section we will present a complete Python program that uses a stored procedure to report on the status and configuration of a MySQL server through a web interface.

The stored procedure we will use is shown in Example 16-29. It takes as an (optional) argument a database name, and reports on the objects within that database as well as a list of users currently connected to the server, server status variables, server configuration variables, and a list of databases contained within the server. It contains one OUT parameter that reports the server version.

Example 16-29. The stored procedure for our complete Python example

CREATE PROCEDURE sp_mysql_info

    (in_database VARCHAR(60),

     OUT server_version VARCHAR(100))

    READS SQL DATA

BEGIN

  DECLARE db_count INT;

  SELECT @@version

    INTO server_version;

  SELECT 'Current processes active in server' as table_header;

  SHOW full processlist;

  SELECT 'Databases in server' as table_header;

  SHOW databases;

  SELECT 'Configuration variables set in server' as table_header;

  SHOW global variables;

  SELECT 'Status variables in server' as table_header;

  SHOW global status;

  SELECT COUNT(*)

    INTO db_count

    FROM information_schema.schemata s

   WHERE schema_name=in_database;

  IF (db_count=1) THEN

    SELECT CONCAT('Tables in database ',in_database) as table_header;

    SELECT table_name

      FROM information_schema.tables

     WHERE table_schema=in_database;

  END IF;

END$$

The number and type of result sets is unpredictable, since a list of database objects is generated only if a database matching the stored procedure's first parameter is found on the server.

Prior to every major result set, the stored procedure generates a one-row "title" as a result set. This result set is identified by the column title table_header.

In this example we are going to use the Apache mod_python module to run Python code from within an Apache web page. mod_python allows the Apache web server to run Python code without having to execute an external Python program. You can find out more about downloading and configuring mod_python at http://www.modpython.org.

The HTML part of our web page is shown in Example 16-30. It displays an HTML form that asks for MySQL server connection details, including a database name.

Example 16-30. HTML form for mod_python example

<html>

<head>

<title>MySQL Server Statistics</title>

</head>

<h1>Enter MySQL Server Details</h1><b>

    Enter your database connection details below:

  <p>

  <form action="form.py/call_sp" method="POST">

    <table>

      <tr><td>Host:</td>

          <td> <input type="text" name="mhost" value="localhost"></td></tr>

      <tr><td>Port: </td>

          <td><input type="text" name="mport" value="3306"></td></tr>

      <tr><td>Username: </td>

          <td> <input type="text" name="musername" value="root"></td></tr>

      <tr><td>Password: </td>

          <td> <input type="password"  name="mpassword"></td></tr

      <tr><td>Database: </td>

          <td> <input type="test" name="mdatabase" value="prod"></td></tr>

    </table>

     <input type="submit">

  </form>

</html>

The most important part of this HTML is the action="form.py/call_sp" portion of the FORM tag. This tells Apache that when the form is submitted, the Python program form.py should be executed with the function call_sp() as the entry point. All of the input values of the form are passed to the Python function as arguments.

Figure 16-1 shows the data entry form created by this HTML.

Example 16-31 shows the Python code that is invoked when the user clicks the Submit Query button.

mod_python input form

Figure 16-1. mod_python input form

Example 16-31. Python code for our mod_python example

1   import MySQLdb

2

3   def call_sp(mhost,musername,mpassword,mdatabase,mport):

4       html_tables=[]

5       html_out=[]

6

7       try:

8           conn = MySQLdb.connect (host = mhost,

9                               user = musername,

10                              passwd =mpassword,

11                              db = mdatabase,

12                              port=int(mport))

13

14          csr1=conn.cursor(  );

15          csr1.execute("call sp_mysql_info(%s,@server_version)",(mdatabase))

16          while True:

17              rows=csr1.fetchall(  )

18              col_desc=csr1.description

19              if col_desc<>None:  #Make sure there is a result

20                 if (col_desc[0][0] == "table_header"):

21                     #This is a special result set that contains a header only

22                    html="<h2>%s</h2>" % rows[0][0]

23                else:

24                    html=html_table(col_desc,rows)

25                html_tables.append(html)

26             if csr1.nextset(  )==None:

27                break

28         #Get stored procedure output parameter

29         csr1.execute("SELECT @server_version")

30         row=csr1.fetchone(  )

31         mysql_version=row[0]

32         csr1.close(  )

33

34         #Build up the html output

35         html_out.append("<html><head><title>"+

36                         "MySQL Server status and statistics"+

37                         "</title></head>"+

38                         "<h1>MySQL Server status and statistics</h1>")

39         html_out.append("<b>Host:</b> %s<br>" % mhost)

40         html_out.append("<b>Port:</b> %s<br>" % mport)

41         html_out.append("<b>Version:</b> %s<br>" % mysql_version)

42         html_out.append("".join(html_tables))

43

44         html_out.append("</html>")

45         return "".join(html_out)

46

47     except MySQLdb.Error, e:

48         return "MySQL Error %d: %s" % (e.args[0], e.args[1])

49

50   def html_table(col_desc,rows):

51       # Create HTML table out of cursor.description and cursor.fetchall

52       html_out=[]

53       html_out.append('<table border=1><tr>')

54       for col in col_desc:

55           html_out.append('<td><b>%s</b></td>' % col[0])

56       html_out.append('</tr>')

57       for row in rows:

58           html_out.append('<tr>')

59           for col in row:

60               html_out.append('<td>%s</td>' % col)

61           html_out.append('</tr>')

62       html_out.append('</table>')

63       s='\n'.join(html_out)

64       return s

There are two main functions in this Python code:

call_sp()

Invokes the stored procedure to generate the MySQL server status report. This is the routine referred to in the action clause of the <form> tag within the calling HTML.

html_table()

A utility function that creates an HTML table from a MySQLdb cursor result set.

Let's start with the call_sp() routine:

Line(s)

Explanation

8–12

Call the MySQLdb.connect( ) method to create the MySQL connection using the parameters specified on the HTML form.

14–15

Create and execute a cursor that invokes the stored procedure.

16

The WHILE loop that commences on this line will iterate through all of the result sets in the stored procedure. The loop ends on line 27.

17–18

On line 17 we use fetchall( ) to retrieve all the rows in the current result set. On line 18 we retrieve the column details for that result set.

20–22

If the title for the first column in the result set is table_header, then this result set contains a heading for a subsequent result set. In that case, we generate an HTML header consisting of the single row and column returned within the result set.

23–24

Otherwise, create an HTML table to represent the result set. This is done using the other function in the file—html_table ( )—which we will discuss shortly.

26–27

Request the next result set. If there are no further result sets, we issue break to terminate the loop that commenced on line 16. Otherwise, the loop repeats and we process the next result set.

29–32

Retrieve the value of the OUT parameter.

On line 29 we issue a SELECT to retrieve the user variable that contains the stored procedure OUT variable. When we called the stored procedure on line 15, we specified @server_version for the second (OUT) parameter. Now we issue a SELECT to retrieve the value of that parameter.

35–45

So far, we have stored HTML that we want to generate into an array called html_tables. Now we construct the final HTML to return to the calling form.

Lines 35–41 add the initial HTML output into an array html_out. In line 42 we add the HTML generated from the result sets to that HTML. Finally, we return all of the HTML to the calling form on line 45.

The second function—html_table( )—generates an HTML table when passed the results of the cursor.description and cursor.fetchall output. We call this in our main program on line 24 when we encounter a result set that we need to format as a HTML table:

Line(s)

Explanation

50

The routine takes two arguments. The first (col_desc) is a columns.description structure as returned by the description() method of the cursor object. The second is a results structure as would be returned by the fetchall() method.

54–55

Loop through the rows in the col_desc parameter—each row representing a column in the result set—and generate HTML to create a title row for our HTML table.

57–60

Generate the bulk of the HTML table. The loop on line 57 iterates through the rows in the result set. The loop on line 59 iterates through the columns in a specific row. On line 60 we generate the HTML for a specific value (for a particular column in a particular row).

63–64

Consolidate all of the HTML fragments—stored in the html_out array— into a single string, which is returned to the calling function.

Figure 16-2 shows the output from our mod_python example.

Output from our mod_python example

Figure 16-2. Output from our mod_python example

Conclusion

The Python MySQLdb extension contains all of the tools you need to interface with MySQL and MySQL stored procedures. Python is a pleasure to program, and it is a very viable alternative to other dynamic scripting languages such as PHP and Perl. Using mod_python (or CGI) allows us to easily implement dynamic web content in Python using MySQL as the backend.