How to Connect to MySQL Using Python

Python's simplicity and extensive library ecosystem make it an ideal choice for managing MySQL databases. Connecting to a MySQL database using Python allows developers and administrators to interact with data directly from scripts or applications, enabling automated database operations, data analysis, and dynamic web and desktop applications that rely on stored information.

In this tutorial, you will learn how to connect to a MySQL database using Python, execute queries, and manage data efficiently.

Prerequisites

• Python 3.x installed (see how to install Python on Ubuntu, Windows, macOS, Rocky Linux).
• MySQL server installed (install MySQL on Ubuntu, Windows server, Rocky Linux).
• A MySQL user account with root privileges.

Connect to MySQL Database Using MySQL-Connector and Python

The official MySQL-Connector/Python library is Oracle's supported driver for connecting Python applications to MySQL. It is written entirely in Python, which makes it easy to install, configure, and use on any system without additional dependencies.

You can use it to automate database operations, run queries directly from scripts, or integrate MySQL into your applications.

Follow the steps below to connect Python to your MySQL database using the MySQL-connector.

Step 1: Install MySQL-Connector

If you are working on a specific project, it is best to install the connector inside a virtual environment so the dependency is isolated. Follow the steps below:

1. Open the terminal (Command Prompt on Windows or Terminal on Linux/macOS), and run the following command to create a virtual environment:

For Linux/macOS:

python3 -m venv env

source env/bin/activate

For Windows:

python -m venv env

env\Scripts\activate

2. Install the connector inside that environment. Run:

pip install mysql-connector-python

Step 2: Import the Library

Open your preferred text editor or IDE (for example, nano) and create a new file, such as connect_mysql.py.

At the top of your file, import the MySQL connector module to enable database operations:

import mysql.connector

If you plan to handle errors explicitly (recommended for production scripts), also import the Error class from the same module:

from mysql.connector import Error

This lets you catch and manage connection or query errors gracefully in a try/except block later in your code.

Step 3: Establish a Connection

Next, define the connection parameters and connect to your MySQL server.

1. In the file we created in the previous step, below the import code lines, paste the following:

try:
    conn = mysql.connector.connect(
        host="localhost",
        user="root",
        password="your_password",
        database="your_database"
    )
    if conn.is_connected():
        print("Connected to MySQL successfully.")
except Error as e:
    print("Error while connecting to MySQL:", e)

Replace "localhost" with your hostname, "root" with your actual MySQL username, "your_password" with your MySQL password for that user, and "your_database" with the database name you plan to use. For example:

2. Save and exit the file.

3. Run the script to check if the connection works. Run:

python connect_mysql.py

The code above attempts to connect to the MySQL server using the provided credentials. If the credentials are invalid or the server is down, the Error class catches the issue and prints an informative message instead of stopping the script.

Step 4: Create Cursor, Execute a Query, and Close the Connection

Once the connection is active, create a cursor object to run SQL commands. A cursor acts as a pointer to interact with the database. Note that a cursor is not thread-safe and should not be reused across multiple threads or connections.

1. Add the following to your existing script, below the connection code:

if conn.is_connected():
    cursor = conn.cursor()
    # Run a simple query to confirm the connection
    cursor.execute("SELECT DATABASE();")
    record = cursor.fetchone()
    print("You're connected to database:", record)
    # Run another query to fetch current server time
    cursor.execute("SELECT NOW();")
    current_time = cursor.fetchone()
    print("Current time on server:", current_time)
    # Close the cursor and connection
    cursor.close()
    conn.close()
    print("MySQL connection closed.")

The fetchone() method retrieves the first row of the result set - in this case, confirming which database you are connected to.

2. Save and exit the file.

3. Run the script:

python3 connect_mysql.py

Whenever you run the script (python connect_mysql.py), Python connects to MySQL, executes the query, prints the result, and then exits.

Connect to MySQL Database Using PyMySQL

PyMySQL is a lightweight, pure-Python MySQL client library that requires no external dependencies or compiled extensions. It is fully compatible with MySQLdb, which means you can use the same syntax and structure as other popular MySQL connectors.

It is a great choice for environments where installing native libraries is not possible or for applications that need portability across operating systems.

Follow the steps below to connect Python to MySQL using PyMySQL.

Step 1: Install PyMySQL

As with any Python project, consider installing PyMySQL inside a virtual environment for isolation.

1. Open the terminal/command prompt and create a virtual environment with:

python -m venv env

2. Activate the virtual environment with the following command:

For Linux/macOS:

source env/bin/activate

For Windows:

env\Scripts\activate

3. Install PyMySQL inside that environment:

pip install PyMySQL

Step 2: Import the Library

Create a new file, for example, connect_pymysql.py, and open it in your text editor.

At the top of the file, import the PyMySQL module and its error class:

import pymysql
from pymysql import MySQLError

You will use pymysql to establish a connection and execute queries against the MySQL database, and MySQLError to handle any connection or query errors gracefully.

Step 3: Establish a Connection

Use the connect() method to create a connection object. This function authenticates you with the MySQL server and sets up communication between Python and MySQL.

1. Add the following code to your file:

try:
    conn = pymysql.connect(
        host="localhost",      # or IP address / remote host
        user="root",           # MySQL username
        password="your_password",
        database="your_database"
    )
    print("Connection established successfully.")
except MySQLError as e:
    print("Error while connecting to MySQL:", e)

Replace "localhost" with your hostname, "root" with your actual MySQL username, "your_password" with your MySQL password for that user, and "your_database" with the database name you plan to use. For example:

2. Save and exit the file.

3. Run the file from your terminal using:

python connect_pymysql.py

If the connection is successful, you will see a confirmation message.

Step 4: Create a Cursor, Execute Queries, and Close the Connection

Once connected, create a cursor to interact with the database. Cursors let you send SQL statements and retrieve results.

1. Below your connection code, add:

if conn.open:
    cursor = conn.cursor()
    # Fetch the current server time
    cursor.execute("SELECT NOW();")
    time_now = cursor.fetchone()
    print("Current time:", time_now)
    # Close cursor and connection
    cursor.close()
    conn.close()
    print("MySQL connection closed.")

2. Save and exit the file.

3. Run the script:

python3 connect_pymysql.py

The output shows that the connection is established, executes your queries, and then closes the session properly.

Connect to MySQL Database Using mysqlclient

mysqlclient is a Python wrapper around the native MySQL C client library. It is the most efficient option performance-wise, making it ideal for production systems or data-intensive applications.

Unlike pure-Python connectors, mysqlclient requires system dependencies, but in exchange it provides faster query execution and lower latency.

The steps below show how to connect to MySQL using mysqlclient.

Step 1: Install mysqlclient

Before installing the Python package, make sure the required system libraries and header files are present.

On Debian-based Linux systems, run:

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

On macOS, you can install MySQL client tools using Homebrew:

brew install mysql-client

2. Next, install the Python module:

pip install mysqlclient

Step 2: Import the Library

Create a new file called connect_mysqlclient.py and open it in your editor.

Import the library as follows:

import MySQLdb

MySQLdb is the module name you will use, even though the package installed is mysqlclient.

Step 3: Establish a Connection

Use the connect() function to create a connection to your MySQL database.

1. Add the following code below the import statement:

try:
    conn = MySQLdb.connect(
        host="localhost",
        user="root",
        passwd="your_password",
        db="your_database"
    )
    print("Connected to MySQL successfully.")
except MySQLdb.Error as e:
    print("Error while connecting to MySQL:", e)

Replace "localhost" with your hostname, "root" with your actual MySQL username, "your_password" with your MySQL password for that user, and "your_database" with the database name you plan to use. For example:

2. Save and exit the file.

3. Run the script in the terminal:

python3 connect_mysqlclient.py

If successful, you will see a confirmation message in the output:

Step 4: Create a Cursor, Execute a Query, and Close Connection

Once connected, you can create a cursor and execute SQL statements to interact with the database.

1. Add the following code to your script:

if conn:
    cursor = conn.cursor()
    # Example query: fetch current server time
    cursor.execute("SELECT NOW();")
    time_now = cursor.fetchone()
    print("Current time:", time_now)
    # Always close cursor and connection
    cursor.close()
    conn.close()
    print("MySQL connection closed.")

2. Save and exit the file.

3. Execute the script:

python3 connect_mysqlclient.py

The script connects to MySQL, prints the current server time, and closes the connection.

Conclusion

This tutorial showed how to connect to MySQL via Python using three different methods - MySQL-Connector, PyMySQL, and mysqlclient. Each option offers distinct advantages depending on your needs - from official support and ease of use to lightweight portability or performance. With the right connector, you can seamlessly integrate MySQL into your Python scripts or applications for efficient data management and automation.

Next, check out our Python Poetry guide for beginners or see how to read a file line by line in Python.