如何使用Python连接和操作MySQL数据库？请提供示例代码。

如何使用Python连接和操作MySQL数据库？请提供示例代码。

要使用Python连接和操作MySQL数据库，通常使用的库是 mysql-connector-python 或 PyMySQL。下面是使用 mysql-connector-python 库的步骤和示例代码。

1. 安装库

首先，安装 mysql-connector-python 库：

pip install mysql-connector-python

2. 连接到MySQL数据库

import mysql.connector
from mysql.connector import Error

def create_connection():
    try:
        # 建立连接
        connection = mysql.connector.connect(
            host='localhost',  # MySQL服务器地址
            database='your_database',  # 数据库名称
            user='your_username',  # 用户名
            password='your_password'  # 密码
        )
        
        if connection.is_connected():
            print("Successfully connected to the database")
            return connection

    except Error as e:
        print(f"Error: {e}")
        return None

# 使用完连接后记得关闭
def close_connection(connection):
    if connection.is_connected():
        connection.close()
        print("MySQL connection is closed")

# 示例使用
connection = create_connection()
if connection:
    close_connection(connection)

3. 创建表格、插入数据、查询数据

创建表格

def create_table(connection):
    try:
        cursor = connection.cursor()
        create_table_query = '''
        CREATE TABLE IF NOT EXISTS students (
            id INT AUTO_INCREMENT PRIMARY KEY,
            name VARCHAR(100),
            age INT,
            gender VARCHAR(10),
            grade VARCHAR(10)
        );
        '''
        cursor.execute(create_table_query)
        connection.commit()
        print("Table is created successfully")
    
    except Error as e:
        print(f"Error: {e}")

# 示例使用
connection = create_connection()
if connection:
    create_table(connection)
    close_connection(connection)

插入数据

def insert_data(connection, name, age, gender, grade):
    try:
        cursor = connection.cursor()
        insert_query = '''
        INSERT INTO students (name, age, gender, grade)
        VALUES (%s, %s, %s, %s);
        '''
        cursor.execute(insert_query, (name, age, gender, grade))
        connection.commit()
        print("Record inserted successfully")
    
    except Error as e:
        print(f"Error: {e}")

# 示例使用
connection = create_connection()
if connection:
    insert_data(connection, "Alice", 22, "Female", "A")
    insert_data(connection, "Bob", 24, "Male", "B")
    close_connection(connection)

查询数据

def fetch_data(connection):
    try:
        cursor = connection.cursor()
        fetch_query = "SELECT * FROM students;"
        cursor.execute(fetch_query)
        result = cursor.fetchall()
        
        for row in result:
            print(row)
    
    except Error as e:
        print(f"Error: {e}")

# 示例使用
connection = create_connection()
if connection:
    fetch_data(connection)
    close_connection(connection)

4. 更新和删除数据

更新数据

def update_data(connection, student_id, new_name):
    try:
        cursor = connection.cursor()
        update_query = '''
        UPDATE students
        SET name = %s
        WHERE id = %s;
        '''
        cursor.execute(update_query, (new_name, student_id))
        connection.commit()
        print("Record updated successfully")
    
    except Error as e:
        print(f"Error: {e}")

# 示例使用
connection = create_connection()
if connection:
    update_data(connection, 1, "Alice Johnson")
    close_connection(connection)

删除数据

def delete_data(connection, student_id):
    try:
        cursor = connection.cursor()
        delete_query = '''
        DELETE FROM students WHERE id = %s;
        '''
        cursor.execute(delete_query, (student_id,))
        connection.commit()
        print("Record deleted successfully")
    
    except Error as e:
        print(f"Error: {e}")

# 示例使用
connection = create_connection()
if connection:
    delete_data(connection, 2)
    close_connection(connection)

总结

通过上述示例代码，你可以在Python中连接MySQL数据库，进行表的创建、数据的插入、查询、更新和删除操作。