Of course! Connecting Python to a MySQL database on Windows is a very common task. This guide will walk you through the entire process, from installation to writing and executing your first Python script.

We will cover two primary methods:

1. The Modern & Recommended Method: Using mysql-connector-python, which is the official driver from Oracle.
2. The High-Performance Method: Using PyMySQL, a popular pure-Python alternative.

Prerequisites

Before you start, make sure you have:

1. Python Installed: You should have Python 3 installed on your Windows machine. You can download it from python.org. During installation, make sure to check the box that says "Add Python to PATH".
2. MySQL Server Installed: You need a running MySQL server. You can download the free MySQL Community Server from the Oracle website. The MySQL Installer for Windows makes this very easy. You'll need to know the server's hostname (usually localhost), port (default is 3306), username (default is root), and password you set during installation.

Method 1: Using mysql-connector-python (Official Driver)

This is the most straightforward method as it's maintained by the creators of MySQL.

Step 1: Install the MySQL Connector Library

Open your Command Prompt or PowerShell and run the following command to install the library using pip (Python's package installer):

pip install mysql-connector-python

If you have both Python 2 and 3, you might need to use pip3:

pip3 install mysql-connector-python

Step 2: Write the Python Script

Create a new file named connect.py and paste the following code into it. Remember to update the connection details with your own MySQL server information.

import mysql.connector
from mysql.connector import Error
def connect_to_mysql():
    """Connects to a MySQL database and prints the server version."""
    connection = None  # Initialize connection to None
    cursor = None      # Initialize cursor to None
    try:
        # --- IMPORTANT: Replace with your database connection details ---
        connection = mysql.connector.connect(
            host='localhost',          # Or your server's IP address
            database='your_database',  # The name of the database you want to connect to
            user='your_username',      # Your MySQL username (e.g., 'root')
            password='your_password'   # Your MySQL password
        )
        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]}")
    except Error as e:
        # Handle potential errors (e.g., wrong credentials, server not running)
        print(f"Error while connecting to MySQL: {e}")
    finally:
        # Ensure the connection and cursor are closed
        if connection is not None and connection.is_connected():
            if cursor is not None:
                cursor.close()
            connection.close()
            print("MySQL connection is closed.")
# Run the function
if __name__ == "__main__":
    connect_to_mysql()

Step 3: Run the Script

1. Open a Command Prompt or PowerShell.

2. Navigate to the directory where you saved connect.py.

   
   （图片来源网络，侵删）

3. Run the script using Python:

   python connect.py

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

Successfully connected to MySQL Server version 8.0.32
You're connected to database: your_database
MySQL connection is closed.

Method 2: Using PyMySQL (Popular Alternative)

PyMySQL is another excellent, lightweight choice. It's pure Python, which can make it easier to install in some environments.

Step 1: Install the PyMySQL Library

Open your Command Prompt or PowerShell and run:

pip install PyMySQL

Step 2: Write the Python Script

The code is very similar, but the connection syntax is slightly different. Create a file named connect_pymysql.py and use this code:

import pymysql
import sys
def connect_to_mysql_pymysql():
    """Connects to a MySQL database using PyMySQL."""
    connection = None
    cursor = None
    try:
        # --- IMPORTANT: Replace with your database connection details ---
        connection = pymysql.connect(
            host='localhost',
            database='your_database',
            user='your_username',
            password='your_password',
            cursorclass=pymysql.cursors.DictCursor  # Optional: returns results as dictionaries
        )
        with connection.cursor() as cursor:
            # Read a single record
            sql = "SELECT `id`, `name` FROM `your_table` LIMIT 1"
            cursor.execute(sql)
            result = cursor.fetchone()
            print(f"Connected successfully! Found a record: {result}")
    except Exception as e:
        print(f"Error while connecting to MySQL using PyMySQL: {e}")
        sys.exit(1)
    finally:
        if connection:
            connection.close()
            print("PyMySQL connection is closed.")
# Run the function
if __name__ == "__main__":
    connect_to_mysql_pymysql()

Step 3: Run the Script

1. Open a Command Prompt or PowerShell.

2. Navigate to the directory where you saved connect_pymysql.py.

3. Run the script:

   python connect_pymysql.py

Executing INSERT, UPDATE, and DELETE Queries

For queries that modify data (INSERT, UPDATE, DELETE), you must commit the transaction for the changes to be saved to the database.

Here's a complete example using mysql-connector-python that shows how to insert a new record and commit it.

import mysql.connector
from mysql.connector import Error
def insert_data():
    """Inserts a new record into a table."""
    connection = None
    cursor = None
    try:
        connection = mysql.connector.connect(
            host='localhost',
            database='your_database',
            user='your_username',
            password='your_password'
        )
        cursor = connection.cursor()
        # SQL query with placeholders (%s)
        sql_insert_query = "INSERT INTO employees (name, position) VALUES (%s, %s)"
        # Data to be inserted
        insert_tuple = ('John Doe', 'Software Engineer')
        # Execute the query
        result = cursor.execute(sql_insert_query, insert_tuple)
        print(f"{cursor.rowcount} record inserted.")
        # IMPORTANT: Commit the transaction to save the changes
        connection.commit()
        print("Transaction committed.")
    except Error as e:
        print(f"Failed to insert record into table: {e}")
        # Rollback in case of error
        if connection:
            connection.rollback()
    finally:
        if connection and connection.is_connected():
            if cursor:
                cursor.close()
            connection.close()
            print("MySQL connection is closed.")
# To run this, you need a table named 'employees' with columns (id INT AUTO_INCREMENT, name VARCHAR(255), position VARCHAR(255))
# You can create it with: CREATE TABLE employees (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), position VARCHAR(255));
if __name__ == "__main__":
    insert_data()

Common Errors and Troubleshooting

• mysql.connector.errors.InterfaceError: 2003: Can't connect to MySQL server on 'localhost:3306'

  • Cause: The most common error. It means Python cannot find the MySQL server.
  • Solutions:
    1. Make sure your MySQL Server is running. You can check this via the "MySQL 8.0 Service" in Windows Services (services.msc).
    2. Double-check your host. If MySQL is not on your local machine, use the correct IP address.
    3. Check the port. The default is 3306, but if you changed it during installation, you must specify it: port=3307.
    4. Firewall: Your Windows Firewall might be blocking the connection. Try temporarily disabling it to test.

• mysql.connector.errors.ProgrammingError: 1045 (28000): Access denied for user 'your_username'@'localhost' (using password: YES)

  • Cause: Incorrect username or password.
  • Solution: Verify your MySQL credentials. You can reset the root password if you've forgotten it.

• ModuleNotFoundError: No module named 'mysql' or 'pymysql'

  • Cause: The library is not installed in the Python environment you are using.
  • Solution: Make sure you installed it using pip and that you are running the script from the same Python environment. Check which Python you are using with where python in the command prompt. If you have multiple versions, you may need to be specific (e.g., py -3 connect.py).