Of course! This is a very common and important task for any Python developer working on a Linux system. Let's break down how to connect to a MySQL database from Python on Linux using the mysql-connector-python library.

We'll cover:

1. Prerequisites: What you need on your Linux system.
2. Installation: How to install the Python library.
3. Connection: The core Python code to connect.
4. CRUD Operations: How to perform Create, Read, Update, and Delete operations.
5. Best Practices: Writing clean, secure, and robust code.
6. Alternative Libraries: A quick look at other popular options.

Prerequisites

Before you start writing Python code, you need to ensure your system is ready.

a) Install the MySQL Server on Linux

If you don't have a MySQL server running, you'll need to install one. The process varies slightly depending on your distribution.

For Debian/Ubuntu:

# Update package lists
sudo apt update
# Install the MySQL server
sudo apt install mysql-server
# Secure the installation (sets root password, removes anonymous users, etc.)
sudo mysql_secure_installation

For RHEL/CentOS/Fedora:

# Install the MySQL server
sudo dnf install mysql-server
# Start and enable the MySQL service
sudo systemctl start mysqld
sudo systemctl enable mysqld
# Run the secure installation script
sudo mysql_secure_installation

b) Create a Database and User

It's bad practice to use the root user for your application. Let's create a dedicated database and user.

1. Log in to the MySQL shell:

   sudo mysql -u root -p

   (Enter your root password when prompted)

   
   （图片来源网络，侵删）

2. Run the following SQL commands:

   -- Create a new database
   CREATE DATABASE my_app_db;
   -- Create a new user and grant privileges on the new database
   -- Replace 'your_strong_password' with a secure password
   CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'your_strong_password';
   -- Grant all privileges on the new database to the new user
   GRANT ALL PRIVILEGES ON my_app_db.* TO 'app_user'@'localhost';
   -- Apply the changes immediately
   FLUSH PRIVILEGES;
   -- Exit the MySQL shell
   EXIT;

Installation of the Python Library

The standard and most recommended library is mysql-connector-python. You can install it easily using pip.

# It's best practice to use a virtual environment
python3 -m venv my-project-env
source my-project-env/bin/activate
# Install the library
pip install mysql-connector-python

Basic Connection to MySQL

Now for the Python part. The key is to handle the connection and ensure it's always closed, even if errors occur. The with statement is perfect for this.

Create a file named db_connector.py:

import mysql.connector
from mysql.connector import Error
def create_server_connection(host_name, user_name, user_password):
    """Establishes a connection to the MySQL server."""
    connection = None
    try:
        connection = mysql.connector.connect(
            host=host_name,
            user=user_name,
            passwd=user_password
        )
        print("MySQL connection successful")
        return connection
    except Error as e:
        print(f"Error while connecting to MySQL: {e}")
        return None
# --- Example Usage ---
# Replace with your actual server credentials
# For a local installation, host is usually 'localhost'
server_connection = create_server_connection("localhost", "app_user", "your_strong_password")
if server_connection:
    # You can now create a database or use an existing one
    # For example, let's use the 'my_app_db' we created earlier
    server_connection.database = "my_app_db"
    # We'll do more with this connection in the next step
    server_connection.close()
    print("MySQL connection is closed")

Performing CRUD Operations

Once you have a connection, you can execute SQL queries. We'll use a cursor for this. Again, the with statement ensures the cursor is closed automatically.

Let's create a table, insert data, read it, update it, and delete it.

import mysql.connector
from mysql.connector import Error
# Assume the create_server_connection function from the previous step is defined here
# def create_server_connection(...): ...
def create_db_connection(host_name, user_name, user_password, db_name):
    """Establishes a connection to a specific MySQL database."""
    connection = None
    try:
        connection = mysql.connector.connect(
            host=host_name,
            user=user_name,
            passwd=user_password,
            database=db_name
        )
        print(f"Connected to database '{db_name}'")
        return connection
    except Error as e:
        print(f"Error while connecting to MySQL: {e}")
        return None
def execute_query(connection, query, data=None):
    """Executes a single query (INSERT, UPDATE, DELETE)."""
    cursor = connection.cursor()
    try:
        if data:
            cursor.execute(query, data)
        else:
            cursor.execute(query)
        connection.commit()
        print("Query successful")
    except Error as e:
        print(f"Error executing query: {e}")
def read_query(connection, query):
    """Executes a SELECT query and returns the results."""
    cursor = connection.cursor(dictionary=True) # dictionary=True makes results easy to use
    result = None
    try:
        cursor.execute(query)
        result = cursor.fetchall()
        return result
    except Error as e:
        print(f"Error executing read query: {e}")
        return result
# --- Main Execution ---
if __name__ == "__main__":
    # --- Connection Details ---
    db = "my_app_db"
    user = "app_user"
    password = "your_strong_password"
    host = "localhost"
    # 1. Create a connection to the database
    conn = create_db_connection(host, user, password, db)
    if conn:
        # 2. CREATE: Create a table
        create_table_query = """
        CREATE TABLE IF NOT EXISTS employees (
            id INT AUTO_INCREMENT PRIMARY KEY,
            name VARCHAR(255) NOT NULL,
            department VARCHAR(255),
            salary INT
        );
        """
        execute_query(conn, create_table_query)
        # 3. CREATE: Insert data into the table
        insert_employee_query = "INSERT INTO employees (name, department, salary) VALUES (%s, %s, %s)"
        employee_data = ("Alice", "Engineering", 90000)
        execute_query(conn, insert_employee_query, employee_data)
        employee_data_2 = ("Bob", "Marketing", 75000)
        execute_query(conn, insert_employee_query, employee_data_2)
        # 4. READ: Select all employees
        select_employees_query = "SELECT * FROM employees"
        employees = read_query(conn, select_employees_query)
        print("\n--- All Employees ---")
        for employee in employees:
            print(employee)
        # 5. UPDATE: Update an employee's salary
        update_salary_query = "UPDATE employees SET salary = %s WHERE name = %s"
        new_salary = 95000
        employee_to_update = "Alice"
        execute_query(conn, update_salary_query, (new_salary, employee_to_update))
        # 6. READ again to see the change
        updated_employees = read_query(conn, select_employees_query)
        print("\n--- Employees After Update ---")
        for employee in updated_employees:
            print(employee)
        # 7. DELETE: Delete an employee
        delete_employee_query = "DELETE FROM employees WHERE name = %s"
        employee_to_delete = "Bob"
        execute_query(conn, delete_employee_query, (employee_to_delete,))
        # 8. READ one last time
        final_employees = read_query(conn, select_employees_query)
        print("\n--- Employees After Deletion ---")
        for employee in final_employees:
            print(employee)
        # 9. Close the connection
        conn.close()
        print("\nMySQL connection is closed")

Best Practices

a) Using Configuration Files

Hardcoding credentials in your script is a security risk. Use a configuration file (e.g., config.ini).

config.ini:

[mysql]
host = localhost
database = my_app_db
user = app_user
password = your_strong_password

Python code to read config.ini:

import configparser
config = configparser.ConfigParser()
config.read('config.ini')
db_config = config['mysql']
conn = create_db_connection(
    host_name=db_config['host'],
    user_name=db_config['user'],
    user_password=db_config['password'],
    db_name=db_config['database']
)

b) Using Environment Variables

For even better security, use environment variables.

# In your terminal before running the script
export DB_HOST="localhost"
export DB_USER="app_user"
export DB_PASS="your_strong_password"
export DB_NAME="my_app_db"

Python code to read environment variables:

import os
conn = create_db_connection(
    host_name=os.getenv("DB_HOST"),
    user_name=os.getenv("DB_USER"),
    user_password=os.getenv("DB_PASS"),
    db_name=os.getenv("DB_NAME")
)

c) Using an ORM (Object-Relational Mapper)

For larger applications, writing raw SQL can be tedious and error-prone. ORMs like SQLAlchemy or Django ORM (if using Django) map your Python classes to database tables, allowing you to interact with the database using Python objects instead of SQL queries.

Alternative Libraries

• PyMySQL: A pure Python MySQL client library. It's very popular and works well. The API is similar to mysql-connector-python.

  pip install PyMySQL

• Django ORM: If you are using the Django web framework, it has a powerful, built-in ORM that handles database connections, queries, and schema migrations automatically.
• SQLAlchemy: A popular, feature-rich SQL toolkit and Object-Relational Mapping (ORM) library for Python. It provides a full suite of well-known enterprise-level persistence patterns.

  pip install SQLAlchemy

This guide should give you a solid foundation for connecting to MySQL from Python on a Linux system. Happy coding