Python: executing MySQL stored procedures which return multiple result sets.

MySQL stored procedures can return multiple result sets. In this post, we're looking at calling such stored procedure; for each returned record set, retrieving its column names and records, then store these in a structure of our own choosing. We're using SQLAlchemy, and the following three ( 3 ) connector packages: mysqlclient, PyMySQL and mysql-connector-python. mysqlclient and PyMySQL handle multiple result sets in a similar manner, while mysql-connector-python does it differently.

This SQLAlchemy page MySQL and MariaDB lists several MySQL Python connector packages, but the previously mentioned ones are on top of the list, and seem to be the most mentioned ones across forums. These're the reasons why I pick them for this study. Their web pages: mysql-connector-python, mysqlclient and PyMySQL.

The source database is the MySQL test data released by Oracle Corporation. Downloadable from https://github.com/datacharmer/test_db. It is a simple database with only a few tables, easy to setup.

Below is the stored procedure we're using in this post:

delimiter //

drop procedure if exists DemoStoredProc1; //

create procedure DemoStoredProc1( pm_dept_no varchar(4) )
reads sql data
begin
  select * from departments where dept_no = pm_dept_no;
  select * from dept_manager where dept_no = pm_dept_no;
end; //

If there is a match, the first record set will have only a single record, the second record set will have one or more records: they are two ( 2 ) distinct record sets. Please note, we could have more than two, but for the purpose of this post, two should suffice.

I'd like to extract column names and records into a multi-dimensional list as illustrated below:

[
    [
        ['dept_no', 'dept_name'], 
        ['d001', 'Marketing']
    ],
    [
        ['emp_no', 'dept_no', 'from_date', 'to_date'],
        [110022, 'd001', datetime.date(1985, 1, 1), datetime.date(1991, 10, 1)],
        [110039, 'd001', datetime.date(1991, 10, 1), datetime.date(9999, 1, 1)]
    ]
]

That is:

Each returned record set is in its own list. And in this list:
- The first element is the list of column names.
- The subsequent elements are lists of data records.
All record set lists are stored within a another list.

When there is no data, only column names are extracted:

[
    [
        ['dept_no', 'dept_name']
    ], 
    [
        ['emp_no', 'dept_no', 'from_date', 'to_date']
    ]
]

Cannot return results from stored procedure using Python cursor is the principal post which helps me figure out how mysqlclient and PyMySQL work.

mysql-connector-python

MySQLCursor.stored_results() method provides access to individual result sets:

from sqlalchemy import create_engine
from contextlib import closing

engine = create_engine( 'mysql+mysqlconnector://behai:super-secret-password@localhost/employees', echo = False )
connection = engine.raw_connection()

try:
    with closing( connection.cursor() ) as cursor:
        cursor.callproc( 'DemoStoredProc1', [ 'd001' ] )

        data = []
        for sr in cursor.stored_results():
            #-- 
            columns = [ column[0] for column in sr.description ]
            ds = sr.fetchall()

            dataset = []
            dataset.append( columns )
            for row in ds:
                dataset.append( list(row) )

            data.append( dataset )
            #--
            sr.close()

        cursor.close()

        import pprint
        print( '\n' )
        pprint.pprint( data )

except Exception as e:
    print( f'Exception. Type {type(e)}: {str(e)}' )
finally:
    if 'connection' in locals():
        connection.close()

Each iterator returned by MySQLCursor.stored_results() is a MySQLCursor class, where MySQLCursor.description property provides access to column information. The rest of the codes should be self-explanatory.

mysqlclient and PyMySQL

mysqlclient and PyMySQL cursor classes do not implement the stored_results() method. Instead, they implement the .nextset() method. Their respective documentations, mysqlclient/MySQLdb | Cursor Objects and PyMySQL Cursor Objects.

.nextset()-based looping is not as elegant as iterating with stored_results():

from sqlalchemy import create_engine
from contextlib import closing

# mysqlclient
engine = create_engine( 'mysql://behai:super-secret-password@localhost/employees', echo = False )
# PyMySQL
engine = create_engine( 'mysql+pymysql://behai:super-secret-password@localhost/employees', echo = False )

connection = engine.raw_connection()

try:
    with closing( connection.cursor() ) as cursor:
        cursor.callproc( 'DemoStoredProc1', [ 'd001' ] )

        data = []
        ns = cursor
        while ns != None:
            if ( cursor.description != None ):
                #--
                columns = [ column[0] for column in cursor.description ]
                ds = cursor.fetchall()              

                dataset = []
                dataset.append( columns )
                for row in ds:
                    dataset.append( list(row) )

                data.append( dataset )
                #--

            ns = cursor.nextset()

        cursor.close()

        import pprint
        print( '\n' )
        pprint.pprint( data )

except Exception as e:
    print( f'Exception. Type {type(e)}: {str(e)}' )
finally:
    if 'connection' in locals():
        connection.close()

According to the documentations, I should not need the condition:

if ( cursor.description != None ):

But without it, the loop will result in an exception when at the end! I have not been able to work that one out yet... and probably never will: I am not using these two packages.

Personally, I like mysql-connector-python best: rich documentation and it is written by MySQL themselves, and so is likely always up to date.

I looked into this out of curiosity. I have not taken advantage of this feature of MySQL in any of the projects yet. I hope you find this useful. Thank you for reading and stay safe as always.

Blog

Python: executing MySQL stored procedures which return multiple result sets.

Be Hai Nguyen

mysql-connector-python

mysqlclient and PyMySQL

Join Our Newsletter. No Spam, Only the good stuff.

Related