Connecting to legacy database in Python

When we interact with database in production environment, it’s not possible to always use the latest version of database. Sometimes we need to connect to a legacy database that may not be supported by the latest mysql-connector-python. This post explains how to connect to it with mysql-connector-python 8.1.

Setup test environment

We can setup test environment in Docker. I’m going to use MySQL 5.1 here. docker pull vsamov/mysql-5.1.73

docker run -d -p 3309:3306 --name remote-mysql -e MYSQL_ALLOW_EMPTY_PASSWORD=true fa73519d1891

The mysql-connector-python version used here is 8.1.0

Here’s a table for versions of MySQL supported by different versions of connectors:

Connector/Python Version	MySQL Server Versions	Python Versions	Connector Status
8.x Innovation	8.1, 8.0, 5.7, 5.6	3.12 (8.2.0), 3.11, 3.10, 3.9, 3.8	General Availability
8.0	8.0, 5.7, 5.6, 5.5	3.11, 3.10, 3.9, 3.8, 3.7, (3.6 before 8.0.29), (2.7 and 3.5 before 8.0.24)	General Availability
2.2 (continues as 8.0)	5.7, 5.6, 5.5	3.5, 3.4, 2.7	Developer Milestone, No releases
2.1	5.7, 5.6, 5.5	3.5, 3.4, 2.7, 2.6	General Availability
2.0	5.7, 5.6, 5.5	3.5, 3.4, 2.7, 2.6	GA, final release on 2016-10-26
1.2	5.7, 5.6, 5.5 (5.1, 5.0, 4.1)	3.4, 3.3, 3.2, 3.1, 2.7, 2.6	GA, final release on 2014-08-22

Connecting to legacy database through MySQL client command will raise bad handshake error:

Connect the legacy database in Python

import mysql.connector


conn = mysql.connector.connect(
            host='',
            user='',
            password='',
            charset ='latin1',
            port='',
            db='',
            use_pure=True
        )
cursor = conn.cursor()
cursor.execute("SELECT * FROM `dummy`")
result = cursor.fetchall()
print(result)
conn.close()

The code above should be able to print something from dummy table.

Note

The charset and use_pure must be set in connection.

If charset is not set, default utf8mb4 will used. This character set won’t be recognized by MySQL 5.1. To check the character set used by legacy MySQL server, use command select @@character_set_database; For me, the character set is ‘latin1’.

use_pure also needs to be set. Otherwise, python will raise mysql.connector.errors.OperationalError, indicating it’s a bad handshake.

According to MySQL connector document:

• By default, use_pure (use the pure Python implementation) is False as of MySQL 8 and defaults to True in earlier versions. If the C extension is not available on the system then use_pure is True.
• On Linux, the C and Python implementations are available as different packages. You can install one or both implementations on the same system. On Windows and macOS, the packages include both implementations.
• For Connector/Python installations that include both implementations, it can optionally be toggled it by passing use_pure=False (to use C implementation) or use_pure=True (to use the Python implementation) as an argument to mysql.connector.connect().
• For Connector/Python installations that do not include the C Extension, passing use_pure=False to mysql.connector.connect() raises an exception.
• For older Connector/Python installations that know nothing of the C Extension (before version 2.1.1), passing use_pure to mysql.connector.connect() raises an exception regardless of its value.