MySQL Stored Procedure Programming

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 of MySQLdb, 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( ...