Of course! Here is a comprehensive guide covering everything you need to know about using Python with MySQL on an Ubuntu system. We'll break it down into several key sections:

1. Prerequisites: Ensuring you have Python and a package manager.
2. Setting up the MySQL Server: Installing and securing the database on Ubuntu.
3. Creating a Database and User: Preparing your environment for Python to connect.
4. Installing the Python MySQL Connector: The essential library to bridge Python and MySQL.
5. Writing Python Code: A complete, step-by-step example with connection, queries, and error handling.
6. Best Practices: How to manage credentials and handle connections gracefully.

Prerequisites

Before you start, make sure you have the following:

• Ubuntu System: A fresh installation of a recent Ubuntu LTS version (e.g., 20.04, 22.04) is recommended.
• Python 3: Ubuntu comes with Python 3 pre-installed. You can verify this by opening a terminal and typing:

  python3 --version

• pip (Python's Package Installer): This is usually installed with Python. Check with:

  pip3 --version

  If it's not installed, you can add it with:

  sudo apt update
  sudo apt install python3-pip

Setting up the MySQL Server

If you don't have a MySQL server running, you'll need to install it.

Step 1: Install MySQL

Open your terminal and run the following commands:

# Update your package list
sudo apt update
# Install the MySQL server package
sudo apt install mysql-server

Step 2: Secure the Installation

The MySQL installation includes a security script you should run. It will help you set a root password, remove anonymous users, disallow remote root login, and remove test databases.

sudo mysql_secure_installation

You will be guided through a series of prompts. Here's a typical recommendation:

• Set root password? Y (Yes) and choose a strong password.
• Remove anonymous users? Y (Yes) - It's more secure.
• Disallow root login remotely? Y (Yes) - For security, you'll connect as root only from localhost.
• Remove test databases? Y (Yes).
• Reload privileges now? Y (Yes) to apply the changes.

Creating a Database and User

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

1. Log in to the MySQL prompt as the root user:

   
   （图片来源网络，侵删）

   sudo mysql -u root -p

   You'll be prompted for the root password you set in the previous step.

2. Run the following SQL commands in the MySQL prompt:

   -- Create a new database for your application
   CREATE DATABASE my_python_app;
   -- Create a new user and set a password for it
   -- Replace 'your_strong_password' with a secure password
   CREATE USER 'python_user'@'localhost' IDENTIFIED BY 'your_strong_password';
   -- Grant all privileges on the new database to the new user
   -- The *.* means all tables in all databases, but we can be more specific
   GRANT ALL PRIVILEGES ON my_python_app.* TO 'python_user'@'localhost';
   -- Apply the changes immediately
   FLUSH PRIVILEGES;
   -- Exit the MySQL prompt
   EXIT;

Installing the Python MySQL Connector

Now we need the Python library that allows our script to communicate with the MySQL server. The most common and officially recommended one is mysql-connector-python.

Open your terminal and install it using pip3:

pip3 install mysql-connector-python

Writing Python Code

Let's put it all together. We'll create a Python script that connects to our database, creates a table, inserts some data, and then queries it.

Create a new file named app.py:

nano app.py

Paste the following code into the file. Remember to replace the placeholder password with the one you set for python_user.

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")
    except Error as e:
        print(f"The error '{e}' occurred")
    return connection
def create_database(connection, query):
    """Creates a new database."""
    cursor = connection.cursor()
    try:
        cursor.execute(query)
        print("Database created successfully")
    except Error as e:
        print(f"The error '{e}' occurred")
def create_db_connection(host_name, user_name, user_password, db_name):
    """Establishes a connection to a specific database."""
    connection = None
    try:
        connection = mysql.connector.connect(
            host=host_name,
            user=user_name,
            passwd=user_password,
            database=db_name
        )
        print("MySQL Database connection successful")
    except Error as e:
        print(f"The error '{e}' occurred")
    return connection
def execute_query(connection, query):
    """Executes a single query (CREATE, INSERT, UPDATE, DELETE)."""
    cursor = connection.cursor()
    try:
        cursor.execute(query)
        connection.commit()
        print("Query executed successfully")
    except Error as e:
        print(f"The error '{e}' occurred")
def read_query(connection, query):
    """Executes a SELECT query and returns the results."""
    cursor = connection.cursor()
    result = None
    try:
        cursor.execute(query)
        result = cursor.fetchall()
        return result
    except Error as e:
        print(f"The error '{e}' occurred")
# --- Main Execution ---
# 1. Create a connection to the MySQL server (to create the database)
# Note: We are connecting to the server here, not a specific database.
db_connection = create_server_connection("localhost", "root", "YOUR_ROOT_PASSWORD")
# 2. Create the database if it doesn't exist
create_database_query = "CREATE DATABASE IF NOT EXISTS my_python_app"
if db_connection is not None:
    create_database(db_connection, create_database_query)
    db_connection.close() # Close the server connection
# 3. Connect to the specific database we just created
# Replace 'your_strong_password' with the password for 'python_user'
db_connection = create_db_connection("localhost", "python_user", "your_strong_password", "my_python_app")
# 4. Create a table
create_employees_table = """
CREATE TABLE IF NOT EXISTS employees (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  department VARCHAR(100),
  salary DECIMAL(10, 2)
)
"""
if db_connection is not None:
    execute_query(db_connection, create_employees_table)
# 5. Insert data into the table
insert_employees = """
INSERT INTO employees (name, department, salary) VALUES
('Alice', 'Engineering', 90000.00),
('Bob', 'Marketing', 75000.00),
('Charlie', 'Engineering', 110000.00)
"""
if db_connection is not None:
    execute_query(db_connection, insert_employees)
# 6. Query data from the table
select_employees = "SELECT * FROM employees"
if db_connection is not None:
    employees = read_query(db_connection, select_employees)
    for employee in employees:
        print(employee)
# 7. Close the database connection
if db_connection is not None and db_connection.is_connected():
    db_connection.close()
    print("MySQL connection is closed")

How to Run the Script

1. Save the file (Ctrl+O, then Enter, then Ctrl+X in nano).
2. Make sure you've replaced YOUR_ROOT_PASSWORD and your_strong_password.
3. Run the script from your terminal:

   python3 app.py

You should see output similar to this:

MySQL connection successful
Database created successfully
MySQL Database connection successful
Query executed successfully
Query executed successfully
(1, 'Alice', 'Engineering', Decimal('90000.00'))
(2, 'Bob', 'Marketing', Decimal('75000.00'))
(3, 'Charlie', 'Engineering', Decimal('110000.00'))
MySQL connection is closed

Best Practices

Storing Credentials Securely

Hardcoding passwords in your code is a major security risk. Use environment variables to store them.

1. Install the python-dotenv library:

   pip3 install python-dotenv

2. Create a .env file in your project directory:

   nano .env

   Add your credentials:

   DB_HOST=localhost
   DB_USER=python_user
   DB_PASSWORD=your_strong_password
   DB_NAME=my_python_app

3. Create a .gitignore file to prevent your .env file from being committed to version control:

   nano .gitignore

   Add the following line:

   .env

4. Modify your app.py to load the variables:

   import os
   from dotenv import load_dotenv
   # ... (other imports)
   # Load environment variables from .env file
   load_dotenv()
   # Use the variables
   db_host = os.getenv("DB_HOST")
   db_user = os.getenv("DB_USER")
   db_password = os.getenv("DB_PASSWORD")
   db_name = os.getenv("DB_NAME")
   # Now, pass these to your connection functions
   # db_connection = create_db_connection(db_host, db_user, db_password, db_name)

Using Connection Pooling

For applications that make frequent database connections, creating a new connection for every query is inefficient. Use a connection pool to reuse connections.

Here's a simple example of how to set up a pool:

import mysql.connector
from mysql.connector import pooling
# Configure the connection pool
dbconfig = {
    "host": "localhost",
    "user": "python_user",
    "password": "your_strong_password",
    "database": "my_python_app"
}
# Create a connection pool with 5 connections
connection_pool = pooling.MySQLConnectionPool(pool_name="mypool", pool_size=5, **dbconfig)
# Get a connection from the pool
connection = connection_pool.get_connection()
try:
    if connection.is_connected():
        cursor = connection.cursor()
        cursor.execute("SELECT * FROM employees")
        records = cursor.fetchall()
        for row in records:
            print(row)
finally:
    # IMPORTANT: Always return the connection to the pool
    connection.close()
    print("Connection returned to the pool.")