How to Use MySQL Database with Python

Francis Ndungu
March 1, 2025

Introduction

MySQL is a popular, open-source relational database management system that is widely used for web applications and various other platforms. Python provides excellent libraries to interact with MySQL, allowing you to perform database operations such as connecting to the database, executing queries, and managing data. By learning how to use MySQL with Python, you can build robust and scalable database applications.

This guide explains how to use MySQL database with Python.

Prerequisites

Before you start:

Deploy a VPS server. For instance, Ubuntu 24.04.
Create a non-root sudo user.
Install MySQL. Read our guide on How to Install MySQL on Ubuntu 24.04.
Install Python.

Create a Sample Database

To log in to the MySQL server and create a sample database, follow these steps:

Create a Sample Database.

SQL

mysql> CREATE DATABASE sample_db;
mysql> USE sample_db;

Create a Database User with Full Privileges. Replace password with a strong value.

SQL

mysql> CREATE USER 'sample_user'@'localhost' IDENTIFIED BY 'password';
mysql> GRANT ALL PRIVILEGES ON sample_db.* TO 'sample_user'@'localhost';

The above commands:

Log you in to the MySQL server as the root user.
Create a new sample_db database.
Create a new sample_user user with full privileges on the sample_db database.
Connect to the new database.

Install MySQL Driver for Python

To interact with MySQL using Python, you need to install the mysql-connector-python library, which is a popular MySQL adapter for Python.

Ensure you have the latest pip.
CONSOLE
```
$ pip install --upgrade pip
```
Install mysql-connector-python using pip:
CONSOLE
```
$ pip install mysql-connector-python
```

Connect to MySQL Database

To connect to a MySQL database, use the mysql-connector-python library's connect() function and provide the necessary connection parameters such as host, database, user, and password.

Create a new connect-to-db.py file using nano text editor:
CONSOLE
```
$ nano connect-to-db.py
```

Add the following content to connect-to-db.py:

Python

import mysql.connector

try:
    connection = mysql.connector.connect(
        host="localhost",
        database="sample_db",
        user="sample_user",
        password="password"
    )
    print("Connection to MySQL established successfully.")
except mysql.connector.Error as e:
    print(f"Error connecting to MySQL: {e}")

Save and close the file.
Run the file using Python.
CONSOLE
```
$ python3 connect-to-db.py
```

Create a Sample MySQL Table with Python

Create a new create-table.py file.
CONSOLE
```
$ nano create-table.py
```

Add the following content to create-table.py:

Python

import mysql.connector

try:
    connection = mysql.connector.connect(
        host="localhost",
        database="sample_db",
        user="sample_user",
        password="password"
    )
    cursor = connection.cursor()
    create_table_query = """
    CREATE TABLE employees (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(100),
        position VARCHAR(100),
        salary DECIMAL(10, 2)
    )
    """
    cursor.execute(create_table_query)
    connection.commit()
    print("Table created successfully.")
except mysql.connector.Error as e:
    print(f"Error creating table: {e}")
finally:
    cursor.close()
    connection.close()

Save and close the file.
Run the file using Python.
CONSOLE
```
$ python3 create-table.py
```

Insert Data into the Table

Create a new insert-data.py file.
CONSOLE
```
$ nano insert-data.py
```

Add the following content to insert-data.py:

Python

import mysql.connector

try:
    connection = mysql.connector.connect(
        host="localhost",
        database="sample_db",
        user="sample_user",
        password="password"
    )
    cursor = connection.cursor()
    insert_query = """
    INSERT INTO employees (name, position, salary)
    VALUES (%s, %s, %s)
    """
    data = ("John Doe", "Software Engineer", 75000)
    cursor.execute(insert_query, data)
    connection.commit()
    print("Data inserted successfully.")
except mysql.connector.Error as e:
    print(f"Error inserting data: {e}")
finally:
    cursor.close()
    connection.close()

Save and close the file.
Run the file.
CONSOLE
```
$ python3 insert-data.py
```

Query Data from the Table

Create a new query-data.py file.
CONSOLE
```
$ nano query-data.py
```

Add the following content to query-data.py:

Python

import mysql.connector

try:
    connection = mysql.connector.connect(
        host="localhost",
        database="sample_db",
        user="sample_user",
        password="password"
    )
    cursor = connection.cursor()
    select_query = "SELECT * FROM employees"
    cursor.execute(select_query)
    rows = cursor.fetchall()
    for row in rows:
        print(row)
except mysql.connector.Error as e:
    print(f"Error querying data: {e}")
finally:
    cursor.close()
    connection.close()

Save and close the file.
Run the file.
CONSOLE
```
$ python3 query-data.py
```

Update Data in the Table

Create a new update-data.py file.
CONSOLE
```
$ nano update-data.py
```

Add the following content to update-data.py:

Python

import mysql.connector

try:
    connection = mysql.connector.connect(
        host="localhost",
        database="sample_db",
        user="sample_user",
        password="password"
    )
    cursor = connection.cursor()
    update_query = """
    UPDATE employees
    SET salary = %s
    WHERE name = %s
    """
    data = (80000, "John Doe")
    cursor.execute(update_query, data)
    connection.commit()
    print("Data updated successfully.")
except mysql.connector.Error as e:
    print(f"Error updating data: {e}")
finally:
    cursor.close()
    connection.close()

Save and close the file.
Run the file.
CONSOLE
```
$ python3 update-data.py
```

Delete Data from the Table

Create a new delete-data.py file.
CONSOLE
```
$ nano delete-data.py
```

Add the following content to delete-data.py:

Python

import mysql.connector

try:
    connection = mysql.connector.connect(
        host="localhost",
        database="sample_db",
        user="sample_user",
        password="password"
    )
    cursor = connection.cursor()
    delete_query = """
    DELETE FROM employees
    WHERE name = %s
    """
    data = ("John Doe",)
    cursor.execute(delete_query, data)
    connection.commit()
    print("Data deleted successfully.")
except mysql.connector.Error as e:
    print(f"Error deleting data: {e}")
finally:
    cursor.close()
    connection.close()

Save and close the file.
Run the file.
CONSOLE
```
$ python3 delete-data.py
```

Implement Best Practices for Using MySQL with Python

Use parameterized queries: Prevent SQL injection by using parameterized queries.
Handle exceptions: Use try-except blocks to manage database-related errors.
Close connections: Ensure cursors and connections are closed after operations.
Use context managers: Utilize the with statement to manage database connections.
Commit transactions: Ensure changes are committed to the database after write operations.

Conclusion

Using MySQL with Python is essential for building robust and scalable database applications. In this guide, you've learned how to log in to the MySQL server, create a sample database, create a database user with full privileges, connect to the MySQL database, create tables, insert, query, update, and delete data, and implement best practices. By mastering these techniques, you can efficiently manage and interact with MySQL databases in your Python programs.