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
1 |
|
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
1 |
|
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) isFalse
as of MySQL 8 and defaults to True in earlier versions. If the C extension is not available on the system thenuse_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) oruse_pure=True
(to use the Python implementation) as an argument tomysql.connector.connect()
.- For Connector/Python installations that do not include the C Extension, passing
use_pure=False
tomysql.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
tomysql.connector.connect()
raises an exception regardless of its value.