Of course! This is a very common and powerful combination. Let's break down how to use MySQL with Python on a Linux system, covering everything from installation to advanced usage.

We'll structure this guide into four main parts:

1. Prerequisites: Installing the necessary software on Linux.
2. Python Connection: Writing Python code to connect to MySQL.
3. CRUD Operations: Performing Create, Read, Update, and Delete operations.
4. Best Practices: Security, error handling, and connection pooling.

Part 1: Prerequisites - Installing on Linux

Before you can write Python code, you need a MySQL server and a Python library to connect to it.

Step 1: Install the MySQL Server

Open your terminal and use your distribution's package manager.

For Debian / Ubuntu:

# Update your package list
sudo apt update
# Install the MySQL server
sudo apt install mysql-server
# Secure the installation (recommended)
# This will set a root password, remove anonymous users, etc.
sudo mysql_secure_installation

For CentOS / RHEL / Fedora:

# Install the MySQL server
sudo dnf install mysql-server
# Start the MySQL service
sudo systemctl start mysqld
# Enable the service to start on boot
sudo systemctl enable mysqld
# Secure the installation (recommended)
sudo mysql_secure_installation

Step 2: Create a Database and User

It's best practice to create a dedicated user for your application, rather than using the root user.

1. Log in to the MySQL command-line client as root:

   sudo mysql -u root -p

   (Enter the root password you set during mysql_secure_installation)

2. Run the following SQL commands:

   -- Create a new database
   CREATE DATABASE myapp_db;
   -- Create a new user with a password
   CREATE USER 'myapp_user'@'localhost' IDENTIFIED BY 'a_strong_password';
   -- Grant all privileges on the new database to the new user
   GRANT ALL PRIVILEGES ON myapp_db.* TO 'myapp_user'@'localhost';
   -- Apply the changes
   FLUSH PRIVILEGES;
   -- Exit the MySQL client
   EXIT;

Step 3: Install the Python MySQL Library

The most popular and recommended library is mysql-connector-python. It's maintained by Oracle and is very reliable.

# It's best to use a virtual environment to manage project dependencies
# Create a virtual environment
python3 -m venv myproject_env
# Activate the virtual environment
source myproject_env/bin/activate
# Install the library
pip install mysql-connector-python

Part 2: Connecting to MySQL from Python

Now for the fun part. Let's write a simple Python script to connect to the database we just created.

Create a file named connect.py:

import mysql.connector
from mysql.connector import Error
def connect_to_mysql():
    """Connects to a MySQL database and returns the connection object."""
    try:
        # Establish the connection
        connection = mysql.connector.connect(
            host='localhost',          # or '127.0.0.1'
            database='myapp_db',       # The database you created
            user='myapp_user',         # The user you created
            password='a_strong_password' # The password for the user
        )
        if connection.is_connected():
            db_info = connection.get_server_info()
            print(f"Successfully connected to MySQL Server version {db_info}")
            cursor = connection.cursor()
            cursor.execute("SELECT database();")
            record = cursor.fetchone()
            print(f"You're connected to database: {record[0]}")
            return connection
    except Error as e:
        print(f"Error while connecting to MySQL: {e}")
        return None
# --- Main execution ---
if __name__ == "__main__":
    connection = connect_to_mysql()
    if connection and connection.is_connected():
        connection.close()
        print("MySQL connection is closed.")

To run this script:

python3 connect.py

If everything is set up correctly, you should see output like this:

Successfully connected to MySQL Server version 8.0.33
You're connected to database: myapp_db
MySQL connection is closed.

Part 3: Performing CRUD Operations

CRUD stands for Create, Read, Update, and Delete. These are the four basic operations for persistent storage.

Let's create a new file crud_operations.py.

import mysql.connector
from mysql.connector import Error
# --- Use the same connection function from before ---
def get_db_connection():
    try:
        connection = mysql.connector.connect(
            host='localhost',
            database='myapp_db',
            user='myapp_user',
            password='a_strong_password'
        )
        return connection
    except Error as e:
        print(f"Error connecting to MySQL: {e}")
        return None
# --- C: CREATE ---
def create_product(name, price):
    """Inserts a new product into the products table."""
    connection = get_db_connection()
    if not connection:
        return
    try:
        cursor = connection.cursor()
        sql_query = "INSERT INTO products (name, price) VALUES (%s, %s)"
        data_tuple = (name, price)
        cursor.execute(sql_query, data_tuple)
        connection.commit() # IMPORTANT: Commit the transaction
        print(f"Successfully created product: {name}")
    except Error as e:
        print(f"Failed to insert record into table: {e}")
    finally:
        if connection.is_connected():
            cursor.close()
            connection.close()
# --- R: READ ---
def get_all_products():
    """Retrieves and prints all products from the products table."""
    connection = get_db_connection()
    if not connection:
        return
    try:
        cursor = connection.cursor(dictionary=True) # dictionary=True is very useful!
        cursor.execute("SELECT * FROM products")
        records = cursor.fetchall()
        print("\n--- All Products ---")
        if not records:
            print("No products found.")
        else:
            for row in records:
                print(f"ID: {row['id']}, Name: {row['name']}, Price: ${row['price']:.2f}")
    except Error as e:
        print(f"Error reading data from MySQL table: {e}")
    finally:
        if connection.is_connected():
            cursor.close()
            connection.close()
# --- U: UPDATE ---
def update_product_price(product_id, new_price):
    """Updates the price of a specific product."""
    connection = get_db_connection()
    if not connection:
        return
    try:
        cursor = connection.cursor()
        sql_query = "UPDATE products SET price = %s WHERE id = %s"
        data_tuple = (new_price, product_id)
        cursor.execute(sql_query, data_tuple)
        connection.commit()
        print(f"Successfully updated product ID {product_id} to price ${new_price:.2f}")
    except Error as e:
        print(f"Failed to update record: {e}")
    finally:
        if connection.is_connected():
            cursor.close()
            connection.close()
# --- D: DELETE ---
def delete_product(product_id):
    """Deletes a product from the table."""
    connection = get_db_connection()
    if not connection:
        return
    try:
        cursor = connection.cursor()
        sql_query = "DELETE FROM products WHERE id = %s"
        cursor.execute(sql_query, (product_id,))
        connection.commit()
        print(f"Successfully deleted product with ID {product_id}")
    except Error as e:
        print(f"Failed to delete record: {e}")
    finally:
        if connection.is_connected():
            cursor.close()
            connection.close()
# --- Main execution ---
if __name__ == "__main__":
    # First, let's create the table if it doesn't exist
    conn = get_db_connection()
    if conn:
        cursor = conn.cursor()
        try:
            cursor.execute("""
                CREATE TABLE IF NOT EXISTS products (
                    id INT AUTO_INCREMENT PRIMARY KEY,
                    name VARCHAR(255) NOT NULL,
                    price DECIMAL(10, 2) NOT NULL
                )
            """)
            conn.commit()
            print("Table 'products' is ready.")
        except Error as e:
            print(f"Error creating table: {e}")
        finally:
            cursor.close()
            conn.close()
    # Now, let's perform CRUD operations
    print("\n--- Performing CRUD Operations ---")
    create_product("Laptop", 1200.50)
    create_product("Mouse", 25.00)
    create_product("Keyboard", 75.75)
    get_all_products()
    update_product_price(2, 29.99) # Update the price of the Mouse (ID 2)
    get_all_products()
    delete_product(3) # Delete the Keyboard (ID 3)
    get_all_products()

To run this:

python3 crud_operations.py

Part 4: Best Practices

Security: Never Hardcode Credentials

Putting your database password directly in the code is a major security risk. Use environment variables.

Install the python-dotenv library:

pip install python-dotenv

Create a file named .env in your project directory:

# .env file
DB_HOST=localhost
DB_DATABASE=myapp_db
DB_USER=myapp_user
DB_PASSWORD=a_strong_password

Create a config.py file:

# config.py
import os
from dotenv import load_dotenv
load_dotenv() # Load variables from .env file
db_config = {
    'host': os.getenv('DB_HOST'),
    'database': os.getenv('DB_DATABASE'),
    'user': os.getenv('DB_USER'),
    'password': os.getenv('DB_PASSWORD')
}

Now, update your connect.py to use db_config from config.py. This keeps your secrets out of your source code.

Error Handling and try...finally

Always use try...except...finally blocks. The finally block ensures that your database connections and cursors are always closed, even if an error occurs. This prevents resource leaks.

Using Connection Pooling (For High-Traffic Applications)

Opening and closing a database connection for every request is inefficient. A connection pool maintains a set of open connections that your application can reuse.

mysql-connector-python has a built-in connection pool.

import mysql.connector
from mysql.connector import pooling
# Create a connection pool
db_pool = pooling.MySQLConnectionPool(
    pool_name="mypool",
    pool_size=5, # Number of connections to keep in the pool
    host='localhost',
    database='myapp_db',
    user='myapp_user',
    password='a_strong_password'
)
# Get a connection from the pool
connection = db_pool.get_connection()
try:
    # Use the connection as usual
    cursor = connection.cursor()
    cursor.execute("SELECT * FROM products;")
    # ... do work ...
finally:
    # IMPORTANT: Return the connection to the pool, don't close it!
    connection.close() # This actually returns it to the pool

Using an ORM (Advanced)

For larger applications, writing raw SQL can become cumbersome. An Object-Relational Mapper (ORM) like SQLAlchemy allows you to interact with your database using Python objects and methods instead of SQL queries.

pip install SQLAlchemy

SQLAlchemy is a more advanced topic but is extremely powerful for managing database schemas, relationships, and complex queries in a Pythonic way.