4 Ways You Can Connect Python to MySQL

MySQL is one of the most popular relational databases. It allows you to store data in tables and create relationships between those tables. To use MySQL, which runs as a database server, you will need to write code to connect to it.

Most programming languages like Python provide support for this. In fact, in Python, there are several approaches you can take, each with its own advantages.

Prepare Your MySQL Configuration

To connect to the database, you need the following values:

Host: the location of the MySQL server, localhost if you’re running it on the same computer.
User: the MySQL username.
Password: the MySQL password.
Database name: the name of the database you want to connect to.

Before connecting to the MySQL database, create a new directory:

        mkdir python-mysql

Set Up a Python Virtual Environment

A Python virtual environment allows you to install packages and run scripts in an isolated environment. When you create a virtual environment, you can then install versions of Python and Python dependencies within it. This way, you isolate different versions and avoid compatibility issues.

Connect to MySQL Using mysqlclient

The mysqlclient driver is an interface to the MySQL database server that provides the Python database server API. It is written in C.

Run the following command in the virtual environment to install mysqlclient:

        pip install mysqlclient

If you are on a Linux machine, install the Python 3 and MySQL development headers and libraries first.

        # Debian / Ubuntu
sudo apt-get install python3-dev default-libmysqlclient-dev build-essential
 
# Red Hat / CentOS
sudo yum install python3-devel mysql-devel

On Windows, you can install mysqlclient using a binary wheel file. Download the mysqlclient file that’s compatible with your platform from Christoph Gohlke’s unofficial collection. You can then use the downloaded wheel file with pip to install mysqlclient like this:

        pip install c:\\mysqlclient‑1.3.13‑cp36‑cp36m‑win_amd64.whl

Use the following connection code to connect to the MySQL database once the installation is complete:

        import MySQLdb
 
connection = MySQLdb.connect(
    host="localhost",
    user="<mysql_user>",
    passwd="<mysql_password>",
    db="<database_name>"
)
 
cursor = connection.cursor()
cursor.execute("select database();")
db = cursor.fetchone()
 
if db:
    print("You're connected to database: ", db)
else:
    print('Not connected.')

In this program, you have:

Imported mysqlclient.
Created a connection object using MySQLdb.connect().
Passed the database configuration details to MySQLdb.connect().
Created a cursor object to interact with MySQL.
Used the cursor object to fetch the version of the connected MySQL database.

Remember to switch the database details with your own.

Connect to MySQL Using mysql-connector-python

mysql-connector-python is the official connection driver supported by Oracle. It is also written in pure Python.

Install it via pip to start using it.

        pip install mysql-connector-python

Connect to MySQL using the following connection code.

        import mysql.connector
from mysql.connector import Error
 
connection = mysql.connector.connect(host="localhost",
    user="<mysql_user>",
    passwd="<mysql_password>",
    db="<database_name>")
 
try:
    if connection.is_connected():
        cursor = connection.cursor()
        cursor.execute("select database();")
        db = cursor.fetchone()
        print("You're connected to dtabase: ", db)
except Error as e:
    print("Error while connecting to MySQL", e)
finally:
    if connection.is_connected():
        cursor.close()
        connection.close()
        print("MySQL connection is closed")

The above connection code does the same thing that the mysqclient connection code does.

Once you create the connection object, you can create a cursor, which you can then use to execute queries on the database.

This connection program also uses the try…catch block. The Error class, from mysql.connector, lets you catch exceptions raised when connecting to the database. This should simplify debugging and troubleshooting.

Connect to MySQL Using PyMySQL

The PyMySQL connection driver is a replacement for MySQLdb. To use it, you need to be running Python 3.7 or newer and your MySQL server should be version 5. 7, or newer. If you use MariaDB it should be version 10.2 or higher. You can find these requirements on the PyMySQL Github page.

To install PyMySQL, run the following command.

        pip install PyMySQL

Connect to MySQL using PyMySQL using this code.

        import pymysql
 
connection = pymysql.connect(host="localhost",
    user="<mysql_user>",
    password="<mysql_password>",
    database="<database_name>")
 
try:
    cursor = connection.cursor()
    cursor.execute("select database();")
    db = cursor.fetchone()
    print("You're connected to database: ", db)
except pymysql.Error as e:
    print("Error while connecting to MySQL", e)
finally:
    cursor.close()
    connection.close()
    print("MySQL connection is closed")

Once you’ve made the connection, and created the cursor object, you can start making SQL queries.

Connect to MySQL Using aiomysql

The aiomysql connection driver is like the asynchronous version of PyMySQL. It provides access to a MySQL database from the asyncio framework.

To use aiomysql, you need Python 3.7+ and PyMySQL installed in your development environment.

Run the following command to install asyncio and aiomysql.

        pip install asyncio
pip install aiomysql

With aiomysql, you can connect Python to MySQL using a basic connection object and using a connection pool.

Here is an example showing how to connect to the MySQL database using a connection object.

        import asyncio
import aiomysql
loop = asyncio.get_event_loop()
 
async def python_mysql():
    connection = await aiomysql.connect(host="localhost",
    user="<mysql_user>",
    password="<mysql_password>",
    database="<database_name>")
 
    cur = await connection.cursor()
    await cur.execute("select database();")
    db = await cur.fetchone()
    print("You're connected to database: ", db)
    await cur.close()
    connection.close()
 
loop.run_until_complete(python_mysql())

Unlike a database connection object, a connection pool allows you to reuse database connections. It does this by maintaining a pool of open connections and assigning them upon request. When a client requests a connection, they are assigned one from the pool. Once the client closes the connection, the connection goes back to the pool.

The basic code for connecting through a pool is like below:

        import asyncio
import aiomysql
loop = asyncio.get_event_loop()

async def python_mysql():
    pool = await aiomysql.create_pool(host="localhost",
    user="<mysql_user>",
    password="<mysql_password>",
    database="<database_name>", loop=loop, autocommit=False)
 
    async with pool.acquire() as connection:
        cursor = await connection.cursor()
        await cur.execute("select database();")
		    db = await cur.fetchone()
		    print("You're connected to database: ", db)
 
    pool.close()
    await pool.wait_closed()
 
loop.run_until_complete(python_mysql())

This program should print the version of MySQL you have connected to when you run it.

Managing Your PostgreSQL Database

This article showed you several ways you can connect a Python application to MySQL. Each of these methods allows you to interact with and execute queries on the database.

Once connected to a MySQL database, you can execute data queries and perform database transactions. You can create a Python application, hook it up to MySQL, and begin storing data.