Connect to a MySQL database in Python

Connecting to a MySQL database with Python can be confusing. There are several different connection drivers, and not all of them play nicely with different operating systems. On top of that, enabling SSL can be a challenge.

This article covers four methods you can use to connect your Python application to MySQL. These examples will use PlanetScale, a serverless database platform for MySQL, but the actual database you connect to is arbitrary.

The following connection methods will be covered:

• mysqlclient
• mysql-connector-python
• PyMySQL
• aiomysql

Set up PlanetScale

If you need a free database to test your connection, PlanetScale is an excellent option. PlanetScale is built on top of Vitess and provides you a familiar branching workflow to test and deploy schema changes.

Vitess was built in 2010 to solve scaling issues at YouTube. Since then, the open source project has continued to grow and now helps several companies like Slack and Square handle their massive data scaling needs.

Sign up for an account to get a free 5GB database.

Create a database

Once you're in the PlanetScale dashboard:

1. Click "Create new database" on your organization overview dashboard.
2. Give your database a name.
3. Choose a region from the dropdown.
4. Click Create database.

Your database will be created with a main development branch. You can make schema changes here and promote it to production once you're ready.

Get the connection credentials

To generate the connection credentials:

1. Click "Connect" on your database overview page.
2. Select "Python" from the "Connect With" dropdown.
3. Copy the credentials into a .env file in your Python application folder.

To ensure a secure connection, PlanetScale uses certificates from a Certificate Authority (CA) that is part of the system root available on almost all platforms. You must specify the path to the CA certificates you trust when connecting to the database. Note that this path depends on your platform. Here’s a list of common paths across various operating systems and distributions.

If you copied the credentials from the PlanetScale dashboard, this should already be done for you, as PlanetScale auto-detects your operating system.

If you’re using a Windows system, you need to download the root certificates, and then point to their location with the correct configuration options for the driver you’re using.

Connect to MySQL with mysqlclient

The mysqlclient package is one of the most popular Python packages for MySQL. It contains the MySQLdb module, an interface to MySQL that provides the Python database API.

Begin by installing mysqlclient. You can do this in two ways:

1. Use an installer from the official MySQL site that matches the OS and SQL version you’re running.
2. Use pip to install it:

pip install mysqlclient

On Linux, you might need to install the Python3 and MySQL development headers and libraries before installing mysqlclient:

sudo apt-get install python3-dev default-libmysqlclient-dev build-essential

Once you have mysqlclient installed, you can connect to the database using the following code:

import os
import MySQLdb # import the MySQLdb module
from dotenv import load_dotenv
load_dotenv()

# Create the connection object
connection = MySQLdb.connect(
    host=os.getenv("HOST"),
    user=os.getenv("USERNAME"),
    passwd=os.getenv("PASSWORD"),
    db=os.getenv("DATABASE"),
    ssl_mode="VERIFY_IDENTITY",
    ssl={
        'ca': os.getenv("SSL_CERT")
    }
)

# Create cursor and use it to execute SQL command
cursor = connection.cursor()
cursor.execute("select @@version")
version = cursor.fetchone()

if version:
    print('Running version: ', version)
else:
    print('Not connected.')

Note, you must install python-dotenv and import the os module to access the database credentials in the .env file.

Note for Windows users: The mysqlclient installation from a wheel file doesn’t support ssl-mode. Since you want to ensure the database connection is secure, we recommend using another driver, such as mysql-connector-python.

This example uses the connection to fetch the SQL version of the database and then closes the connection.

The rest of the connection methods have nearly the same syntax as this example. This similar syntax stems from the Python database API specification (PEP 249) that encourages consistency among the Python modules used to access databases.

Connect to MySQL with the MySQL connector

The MySQL Connector/Python module is the official Oracle-supported driver to connect MySQL through Python. The connector is entirely Python, while mysqlclient is written in C. It’s also self-contained, meaning it doesn’t require the MySQL client library or any Python modules outside the standard library.

Note that MySQL Connector/Python doesn’t support the old MySQL server authentication methods, which means that MySQL versions before 4.1 don’t work.

Start by installing the mysql-connector-python module. It’s recommended to install it using pip:

pip install mysql-connector-python

Once installed, use the following code to connect to MySQL:

import os
from dotenv import load_dotenv
from mysql.connector import Error
import mysql.connector

load_dotenv()

connection = mysql.connector.connect(
host=os.getenv("HOST"),
database=os.getenv("DATABASE"),
user=os.getenv("USERNAME"),
password=os.getenv("PASSWORD"),
ssl_ca=os.getenv("SSL_CERT")
)

try:
    if connection.is_connected():
        cursor = connection.cursor()
    cursor.execute("select @@version ")
    version = cursor.fetchone()
    if version:
        print('Running version: ', version)
    else:
        print('Not connected.')
except Error as e:
    print("Error while connecting to MySQL", e)
finally:
    connection.close()

Connect to MySQL with PyMySQL

The PyMySQL package is another connector you can use to connect Python to MySQL. It’s a good option if you’re looking for speed, as it’s faster than mysql-connector-python.

You can instal it using pip with:

pip install PyMySQL

Then, use the following connection code:

from dotenv import load_dotenv
import pymysql
import os
load_dotenv()
connection = pymysql.connect(
    host=os.getenv("HOST"),
    database=os.getenv("DATABASE"),
    user=os.getenv("USERNAME"),
    password=os.getenv("PASSWORD"),
    ssl_ca=os.getenv("SSL_CERT")
)
cursor = connection.cursor()
cursor.execute("select @@version ")
version = cursor.fetchone()
if version:
    print('Running version: ', version)
else:
    print('Not connected.')
connection.close()

Connect to MySQL with aiomysql

The aiomysql library is used for accessing a MySQL database from the asyncio framework. Apart from being asynchronous, the connection code is similar to PyMySQL. Note that using aiomysql requires Python 3.7+ and PyMySQL.

To use aiomysql, install the asynchronous module first with:

pip install asyncio

Then, install aiomysql using:

pip install aiomysql

You can then connect to MySQL using the following code:

import os
import asyncio
import aiomysql
import ssl

from dotenv import load_dotenv
load_dotenv()

ctx = ssl.SSLContext(ssl.PROTOCOL_TLS_CLIENT)
ctx.load_verify_locations(cafile=os.getenv("SSL_CERT"))

loop = asyncio.get_event_loop()

async def connect_db():
   connection = await aiomysql.connect(
       host=os.getenv("HOST"),
       port=3306,
       user=os.getenv("USERNAME"),
       password=os.getenv("PASSWORD"),
       db=os.getenv("DATABASE"),
       loop=loop,
       ssl=ctx
   )
   cursor = await connection.cursor()
   await cursor.execute("select @@version")
   version = await cursor.fetchall()
   print('Running version: ', version)
   await cursor.close()
   connection.close()
loop.run_until_complete(connect_db())

Conclusion

Persisting data in a database is one of the common features of software applications. Like Python, most programming languages support connection and interaction with different databases.

This tutorial explored connecting Python to MySQL. We shared four different common connectors you can use and tested them on a PlanetScale database using SSL for security — a crucial step, as it prevents man-in-the-middle attacks. We reviewed how the connector worked for each connection method and showed you the code necessary to run it. Now that you’ve worked through this hands-on overview, you’re all set to connect a MySQL database to a Python app.