In this discussion, I explain why I prefer the Python MySQL driver, mysql-connector-python, for the MariaDB database over the mariadb driver. The latter appears to be recommended by the official MariaDB documentation and is also mentioned on the SQLAlchemy page for MySQL and MariaDB.

118-feature-image.png
Python & MariaDB: Which Driver? An Example of Executing a Stored Procedure That Returns Multiple Result Sets

❶ This new post could be considered as related to some previous posts listed below:

  1. Synology DS218: MariaDB 10 enabling remote connection: This Synology DS218 Linux box, which serves as the MariaDB 10 database server, is used in this new post.
  2. Python: executing MySQL stored procedures which return multiple result sets: This new post is an extension of the previous one. We will be using the same database content, the same stored procedure, and the same Python test code.
  3. Python: executing PostgreSQL stored functions which return multiple result sets.
  4. Rust & MySQL: executing MySQL stored procedures which return multiple result sets using crate sqlx.

❷ Around two years ago, when I bought my Synology DS218 box, I became aware of the MariaDB database. My research suggests that it is superior to and also compatible with the MySQL database. As demonstrated in a previously mentioned post, Synology DS218: MariaDB 10 enabling remote connection, I can use all MySQL client tools with the MariaDB database. Please refer to the following post, MariaDB vs MySQL: A Detailed Comparison, for further information.

❸ I have not been using the MariaDB database in my development, as I have both the MySQL and PostgreSQL databases available.

I have been exploring the MariaDB database recently. I tried out the recommended mariadb driver. Instead of following the official example, I experimented with my own stored procedure which returns multiple result sets, replicating the example in a previously mentioned post.

To recap:

⓵ The source database is the MySQL test data released by Oracle Corporation, downloadable from https://github.com/datacharmer/test_db. Using MySQL tools, I backed up a MySQL database and restored the backup content to the MariaDB database server on the Synology DS218 Linux box.

⓶ The stored procedure is reprinted below:

1
2
3
4
5
6
7
8
9
10

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; //

👉 I verified that this stored procedure works as expected with the MariaDB database. 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 </strong> distinct record sets. Please note, we could have more than two, but for the purpose of this post, two should suffice.

⓷ Install the mariadb driver into the active virtual environment.

⓸ The Python code is copied from the previous post as mentioned several times above. The only modification is: replacing the connection string as appropriate for the mariadb driver.

Content of mariadb-example.py:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38

from sqlalchemy import create_engine
from contextlib import closing

engine = create_engine( 'mariadb+mariadbconnector://behai:O,U#n*m:5QB3_zbQ@192.168.0.14:3306/employees', echo = False )
# engine = create_engine( 'mysql+mysqlconnector://behai:O,U#n*m:5QB3_zbQ@192.168.0.14:3306/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()

Please note, 192.168.0.14 is the IP address of my Synology DS218 Linux box.

It produces the following runtime error:

Exception. Type <class 'AttributeError'>: 'Cursor' object has no attribute 'stored_results'

Please also see the screenshot below:


This is not a database server error, but rather a driver error. However, we know the MariaDB server executes the above stored procedure correctly, so there is no problem on the server-side. The following page is the official MySQL documentation on the MySQLCursor.stored_results() method. I have not been able to locate an equivalent for the MariaDB database.

Having already installed the mysql-connector-python driver, switching the connection string to mysql+mysqlconnector:

# engine = create_engine( 'mariadb+mariadbconnector://behai:O,U#n*m:5QB3_zbQ@192.168.0.14:3306/employees', echo = False )
engine = create_engine( 'mysql+mysqlconnector://behai:O,U#n*m:5QB3_zbQ@192.168.0.14:3306/employees', echo = False )

produces the expected results. Please refer to the two screenshots below:


I am not sure if I missed something about the mariadb driver. I have looked through its documentation, and I have yet to find an equivalent of MySQLCursor.stored_results().

❹ Testing with the bh-database wrapper classes for SQLAlchemy.

For the two examples provided, if you want to access the MariaDB employees database, simply modify the connection string to match the one used in the above example:

SQLALCHEMY_DATABASE_URI = mysql+mysqlconnector://behai:O,U#n*m:5QB3_zbQ@192.168.0.14:3306/employees

All functions from the two examples will continue to operate correctly when using the MariaDB database.

❺ Given all the observations discussed above, the mysql-connector-python driver appears to be suitable for the MariaDB database. I plan to undertake some development work with MariaDB in the future. I will share any interesting findings I encounter with the MariaDB database.

Thank you for reading. I hope you find the information in this post useful. Stay safe, as always.

✿✿✿

Feature image source: