Integrating Python with MySQL Databases: An Introduction and Practical Guide

Table of Contents

• Connecting MySQL with Python
  • Installing MySQL
  • Installing MySQL Connector/Python
  • Establishing an Actual Connection with MySQL Database
• Executing SQL Statements (Working with Cursors)
  • Creating a Cursor Object
  • Basic cursor Usage
  • Fetching Data From the Result of SQL Query
  • Inserting Records in Tables Using cursor.execute()
• Closing Connection
  • What Happens When Connection Fails
• Conclusion

MySQL is a very popular relational database management system. Interaction with data is critical for software application and so programming languages need a way to connect to databases and perform various operations on databases (like to store, update, query or delete data in a database).

We'll be focusing on how to integrate a Python program with MySQL database.

As you go through this article, you'll practically see and learn how you can connect and interact with MySQL from your Python code.

To fully understand the tutorials in this article, it is recommended that you have a good understanding of Python concepts like control flows, loops, functions, classes and objects, exception handling, and packages, modules, and pip (the package installer for Python).

Also, if you are new to databases, I wrote an article that provides a simple but comprehensive overview of SQL databases (specifically MySQL). You can check it out here. It is recommended that you spend sometime understanding SQL databases and SQL basic commands before going further in this article.

---

Connecting MySQL with Python

Installing MySQL

You can skip this if you already have MySQL installed on your system. However if you do not, then the first requirement here is to actually have the MySQL Server installed on the operating system you are currently working from. The MySQL official documentation provides a guide on how to do this on various operating systems (Windows, macOS, Linux, Solaris).

Installing MySQL Connector/Python

To interact with MySQL database from your Python code, you need a database driver. A database driver is a software that allows applications to connect and interact with a specific DBMS (Database Management System). The driver translates application requests into a language that the database understands.

Typically, a database driver is usually obtained as a third-party library/package. The most common and recommended driver for connecting Python to MySQL is mysql-connector-python. The mysql-connector-python is a third-party Python library that acts as a bridge between Python and MySQL databases, through which Python can interact with MySQL servers, allowing for the execution of queries, manipulation of data and managing of database objects (like tables).

The recommended way to install MySQL Connector/Python is with pip package manager. Run the command below in your terminal or command line.

pip install mysql-connector-python

Using the above command, pip downloads and installs mysql-connector-python library in your environment — global or virtual. When installed, it provides a top-level package called mysql

Note: When working with Python, setting up a virtual environment is recommended, to isolate your work/project from the system's global Python installation and other virtual environments.

Once you have the mysql-connector-python library installed, you are now ready to use its packages and modules in your Python program.

Establishing an Actual Connection with MySQL Database

The first step to take when you want to interact with a MySQL server is to establish a connection with it. To connect, you need to import the mysql.connector package.

import mysql.connector

The package contains modules like connection, errors, errorcode, etc. The connection module houses the connect() method that is used to establish a connection to a MySQL database server.

The connect() method (function) needs three important parameters to establish a connection. It takes these parameters and returns a MySQLConnection object. These three parameters are:

• host — host represents the server name or ip on which MySQL is running.
• user — user represents the database user we want to use to connect connect and interact with the database.

Note: By default the username for MySQL database is root. However, in practice, you must avoid using the root user for interactions. This is because using the root user violates the principle of least privilege which is a security risk. It is recommended that you create a custom-user and grant only the permissions required. Then use that custom-user as user in your Python program. This restricts what your program can do.

• password — password is the password of your database (the one you created when installing MySQL server).

The connect() method also accepts other parameters but they are optional — like database which represents the database name you want to connect to.

Important: The errors and errorcode modules in mysql-connector-python provide structured ways to handle exceptions that may occur during connection or database operations. It is recommended to wrap your database connection code in a try-except block to gracefully handle any errors.

Now, let's import the errors and errorcode module

from mysql.connector import errors, errorcode

Now, let's establish a connection to the MySQL server.

# Connect to MySQL server
try:
    # Create a MySQLConnection object
    connection = mysql.connector.connect(
        host="localhost",
        user="root",
        password="your_password",
        database="your_db"
    )
    print(connection)

# Handle specific programming-related MySQL errors
except errors.ProgrammingError as err:
    if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
        print("Access denied: incorrect username or password")
    elif err.errno == errorcode.ER_BAD_DB_ERROR:
        print("Database does not exist")
    else:
        print(err)

You have to replace the connection details with your actual login credentials and database name. The code uses these credentials to try to establish a connection with your MySQL server. If successful, it returns a MySQLConnection object, which is stored in the connection variable. If not successful, the creation of the object fails and an error message is returned. The except block runs and tries to gracefully handle this failure.

Note: The database parameter although optional is recommended in most cases as it tells MySQL to immediately switch to that specific database after establishing connection (just like the USE command in SQL).

Make sure to avoid hard-coding your login credentials (username, password, database) directly in your Python script. This is a serious security risk. A more secure way to store sensitive data, is by using environment variables. In Python, the python-dotenv package lets you store environment variables outside your code and load them into your program. This keeps sensitive data out of your script or source code.

To do this, you must have the python-dotenv package installed in your development environment. Then create a .env file in the root directory of your project, and save all your secrets as key-value pairs in it. See a more elaborate explanation of how to this here

Here's what your code would look when you use dotenv

import mysql.connector
from mysql.connector import errors, errorcode
import os
from dotenv import load_dotenv

# Load environment variables from a .env file
load_dotenv()

DB_HOST = os.getenv("DB_HOST")
DB_USER = os.getenv("DB_USER")
DB_PASSWORD = os.getenv("DB_PASSWORD")
DB_NAME = os.getenv("DB_NAME")

# Connect to MySQL server
try:
    # Create a MySQLConnection object
    connection = mysql.connector.connect(
        host=DB_HOST,
        user=DB_USER,
        password=DB_PASSWORD,
        database=DB_NAME
    )
    print(connection)

# Handle specific programming-related MySQL errors
except errors.ProgrammingError as err:
    if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
        print("Access denied: incorrect username or password")
    elif err.errno == errorcode.ER_BAD_DB_ERROR:
        print("Database does not exist")
    else:
        print(err)

The above method is more secure. If all your details are correct and the try block above runs successfully, it'll return the output below

If any of your login credential (username or password) is incorrect, the except block runs and you'll get the output below

However, if your credentials are correct but your database name is incorrect, you'll get a different output like the one below.

Executing SQL Statements (Working with Cursors)

To execute SQL query/queries in Python, you'll need to use a cursor, which is an instance of MySQLCursor class A cursor is an object used to execute SQL statements and retrieve data from a database. It is created after establishing a connection to the MySQL database, like we've done above. To be more exact, the cursor is created from your database connection object.

Creating a Cursor Object

cursor = connection.cursor()

Basic cursor Usage

The execute() method is used to execute queries. You have to provide the query as a string, either passed directly as an argument or stored in a variable first before passing to cursor.execute(). Once a query is passed to cursor.execute(), it is sent to the database engine for execution. See example of passing the query directly below:

# Create Books table
cursor.execute("""
CREATE TABLE IF NOT EXISTS Books (
    book_id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(130) NOT NULL,
    price DOUBLE,
    publication_date DATE
)
""")

The more cleaner and readable way to do this is to first store the query as a string in a named variable. Then pass this variable to cursor.execute(). See how to do this below:

# Create Books table
create_book_table = """
CREATE TABLE IF NOT EXISTS Books (
    book_id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(130) NOT NULL,
    price DOUBLE,
    publication_date DATE
)
"""
cursor.execute(create_book_table)

In the code above, the query is stored in the create_book_table variable. This variable is then passed to cursor.execute() for execution.

Fetching Data From the Result of SQL Query

There are various methods provided by MySQLCursor class, three (3) of which are used to retrieve the result set of a query.

• fetchall() — This method retrieves/fetches all rows in the result set of the last executed statement, and returns them as a list of tuples.

• fetchone() — This returns the next row of the result set as a tuple.

• fetchmany(size) — this returns the next size number of rows. Size here represents the number of rows you want to retrieve. For instance, cursor.fetchmany(4) will return the next 4 rows from a query result.

Alright, as an example let's retrieve the schema information of the Books table we just created.

cursor.execute("DESCRIBE Books")
result = cursor.fetchall()
for row in result:
    print(row)

Once the code above is executed, you would receive an output like the one below.

Note: The cursor stores the result set for your query temporarily. The fetch methods is how we access the rows from memory.

Inserting Records in Tables Using cursor.execute()

Let's insert data (a book) into our Books table.

But before we get into it, remember the Books table we created has 4 columns, namely:

• book_id
• title
• price
• publication_date

Also, remember that the book_id column was defined with the AUTO_INCREMENT attribute when the table was created. Since the book_id column has the AUTO_INCREMENT attribute, you do not need to manually provide a value for it when inserting a new record. This is because, the database will automatically generate and assign the next available integer value for book_id each time a new row is inserted.

With these in mind, let's get into it practically.

from datetime import date

# Insert a new book into Books table
sql = """
    INSERT INTO Books (title, price, publication_date)
    VALUES (%s, %s, %s)
"""
data = ("Integrating Python with MySQL", 100.00, date(2025, 7, 19))

cursor.execute(sql, data)

# commit the changes
connection.commit()

print("Data committed successfully")

We imported the date class from the datetime module because the publication_date column in the Books table is of type DATE

Also, you may notice a connection.commit() statement at the end of the code. This is used to explicitly commit (save) any changes made to the database during the current session. In mysql-connector-python, changes made by operations such as INSERT, UPDATE, or DELETE are not automatically committed. The connector uses transactional behavior by default, meaning:

• Your changes remain pending (in memory only)
• They are not permanently saved to the database until you call commit()
• If an error occurs before committing, you can use rollback() to undo the changes

When you forget to commit(), the following happens

• Changes won't persist in the database
• Once the connection is closed or rolled back, your uncommitted changes are lost.

Closing Connection

Once you are done working with the database, you must never forget to close the connection to release the resources used for the connection. Always close your cursor and connection to avoid resource leaks.

Here's how to do it

cursor.close()
connection.close()

A cursor leak happens when your program opens a connection or cursor but never closes it. Overtime, it can cause:

• memory exhaustion or resource exhaustion on your system or the database server.
• slower application performance.

When you interact with a database using Python, you create:

• A connection object (to the database)
• A cursor object (to send SQL statements and retrieve results)

Both processes use system and server resources, so you must always make sure to close them.

What Happens When Connection Fails

Now, although you can close your cursor and connection with the code above, it is not the recommended way to do it.

When you look at the code that handles the connection to MySQL server, if mysql.connector.connect() fails, this is what happens:

• No connection object is created
• Python jumps immediately to the except block
• So cursor and connection do not exist yet

This becomes a problem because, if you try to run the:

cursor.close()
connection.close()

you'll get an error like this:

To avoid running into an error when trying to close your cursor and connection, it is recommended that you always check if the variables exist in your code and are valid before trying to close them.

The way I recommended is to use finally block in try-except-finally and have your cursor and connection variables initialized to None beforehand.

With this method, even if your try block fails before creating a connection, finally will still run. Remember that the finally block always runs irrespective of whether the prior code was successfully executed or not. And checking if the variable still has the value None or not protects against calling .close() on a variable that was never defined.

connection = None
cursor = None

finally:
    # Safely close cursor and connection if they were created
    if cursor is not None:
        cursor.close()
    if connection is not None and connection.is_connected():
        connection.close()

connection.is_connected() ensures connection is still open before closing it.

Conclusion

In this article, you learned how to integrate a Python application with a MySQL database using the mysql-connector-python library. We backed theory with practical examples on how to:

• Install and use the mysql-connector-python driver.
• Establish a connection to a MySQL database from Python.
• Use a cursor object to execute SQL statements.
• Create and insert data into a MySQL table using Python.
• Retrieve data using fetchone(), fetchmany(), and fetchall().
• Use connection.commit() to persist changes.
• Handle potential database errors using try-except with errors and errorcode.
• Properly close your cursor and connection to avoid resource leaks
• Safely clean up connections using finally and None checks

Putting it all together:

import mysql.connector                                  from mysql.connector import errors, errorcode
import os
from dotenv import load_dotenv
from datetime import date

# Load environment variables from a .env file
load_dotenv()

DB_HOST = os.getenv("DB_HOST")
DB_USER = os.getenv("DB_USER")
DB_PASSWORD = os.getenv("DB_PASSWORD")
DB_NAME = os.getenv("DB_NAME")

# Initialize connection and cursor
connection = None
cursor = None

# Connect to MySQL server and execute queries
try:
    # Create a MySQLConnection object
    connection = mysql.connector.connect(
        host=DB_HOST,
        user=DB_USER,
        password=DB_PASSWORD,
        database=DB_NAME
    )
    print(connection)

    try:
        # Create a cursor object
        cursor = connection.cursor()

        # Variable store for SQL query
        create_book_table = """
        CREATE TABLE IF NOT EXISTS Books (
            book_id INT AUTO_INCREMENT PRIMARY KEY,
            title VARCHAR(130) NOT NULL,
            price DOUBLE,
            publication_date DATE
        )
        """

        # Create the Books table
        cursor.execute(create_book_table)

        # Get Books table schema (columns define in the Books table)
        cursor.execute("DESCRIBE Books")
        result = cursor.fetchall()
        for row in result:
            print(row)

        # Insert a new book into Books table
        sql = """
            INSERT INTO Books (title, price, publication_date)
            VALUES (%s, %s, %s)
        """
        data = ("Integrating Python with MySQL", 100.00, date(2025, 7, 19))

        cursor.execute(sql, data)

        # commit the changes
        connection.commit()
        print("Data committed successfully")

        # Retrieve all records in the Books table
        cursor.execute("SELECT * FROM Books")
        result = cursor.fetchall()
        print(result)

    except mysql.connector.Error as err:
        print(err)

# Handle specific programming-related MySQL errors during connection
except errors.ProgrammingError as err:
    if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
        print("Access denied: incorrect username or password")
    elif err.errno == errorcode.ER_BAD_DB_ERROR:
        print("Database does not exist")
    else:
        print(err)

finally:
    # Safely close cursor and connection if they were created
    if cursor is not None:
        cursor.close()
    if connection is not None and connection.is_connected():
        connection.close()

Finally, you would most likely be using version control during development, so make sure to add .env to your .gitignore. This is to ensure that you do not mistakenly commit and push .env (which contains sensitive information) to version control platforms like GitHub. Pushing your secrets to version control platforms exposes them to the public.