Python MySQL数据库操作：增删改查与事务处理

使用Python操作MySQL数据库：从入门到精通

本教程将引导你使用Python语言和pymysql库来操作MySQL数据库。我们将涵盖以下方面：

• 创建数据表
• 添加数据
• 删除数据
• 修改数据
• 查询数据
• 事务处理

1. 数据库准备

首先，确保你已经安装了MySQL数据库服务器，并且创建了一个名为testdb的数据库。

2. 安装pymysql库

使用pip安装pymysql库：

pip install pymysql

3. Python代码示例

以下是一个使用Python操作MySQL数据库的示例代码，实现了对trade数据表的增加、删除、修改、查询功能和事务处理：

import pymysql

# 数据库连接信息
db_config = {
    'host': 'localhost',
    'port': 3306,
    'user': 'root',
    'password': 'password',
    'db': 'testdb'
}

# 连接数据库
conn = pymysql.connect(**db_config)

# 创建数据表
def create_table():
    with conn.cursor() as cursor:
        sql = '''
        CREATE TABLE IF NOT EXISTS trade (
            id INT PRIMARY KEY AUTO_INCREMENT,
            name VARCHAR(20),
            price FLOAT,
            quantity INT
        )
        '''
        cursor.execute(sql)
    conn.commit()

# 添加数据
def add_data(name, price, quantity):
    with conn.cursor() as cursor:
        sql = '''
        INSERT INTO trade (name, price, quantity) VALUES (%s, %s, %s)
        '''
        cursor.execute(sql, (name, price, quantity))
    conn.commit()

# 删除数据
def delete_data(id):
    with conn.cursor() as cursor:
        sql = '''
        DELETE FROM trade WHERE id = %s
        '''
        cursor.execute(sql, id)
    conn.commit()

# 修改数据
def update_data(id, name, price, quantity):
    with conn.cursor() as cursor:
        sql = '''
        UPDATE trade SET name = %s, price = %s, quantity = %s WHERE id = %s
        '''
        cursor.execute(sql, (name, price, quantity, id))
    conn.commit()

# 查询数据
def query_data():
    with conn.cursor() as cursor:
        sql = '''
        SELECT * FROM trade
        '''
        cursor.execute(sql)
        results = cursor.fetchall()
        for row in results:
            print(f'id: {row[0]}, name: {row[1]}, price: {row[2]:.2f}, quantity: {row[3]}')

# 事务处理
def transaction():
    with conn.cursor() as cursor:
        try:
            # 开启事务
            conn.begin()
            
            # 执行多个SQL语句
            sql1 = '''
            INSERT INTO trade (name, price, quantity) VALUES ('Apple', 10.5, 100)
            '''
            cursor.execute(sql1)
            
            sql2 = '''
            UPDATE trade SET price = 9.5 WHERE name = 'Apple'
            '''
            cursor.execute(sql2)
            
            # 提交事务
            conn.commit()
            print('Transaction successful.')
        except Exception as e:
            # 回滚事务
            conn.rollback()
            print(f'Transaction failed: {e}')

# 示例调用
if __name__ == '__main__':
    # 创建数据表 (如果不存在)
    create_table()

    # 添加数据
    add_data('Apple', 10.5, 100)
    add_data('Banana', 6.5, 200)

    # 查询数据
    print('--- Initial data ---')
    query_data()

    # 修改数据
    update_data(1, 'Apple', 9.9, 150)

    # 查询数据
    print('--- Data after update ---')
    query_data()

    # 删除数据
    delete_data(2)

    # 查询数据
    print('--- Data after delete ---')
    query_data()

    # 事务处理
    transaction()

    # 查询数据
    print('--- Data after transaction ---')
    query_data()

    # 关闭数据库连接
    conn.close()

4. 代码说明

• 数据库连接信息: 将db_config字典中的值替换为你的实际数据库连接信息。
• with语句: 使用with语句来自动管理数据库连接和游标，确保资源在使用后得到释放。
• 参数化查询: 使用参数化查询可以防止SQL注入攻击，提高代码的安全性。
• 事务处理: transaction()函数演示了如何在Python中执行事务处理，确保多个SQL语句要么全部执行成功，要么全部回滚。

5. 总结

本教程介绍了使用Python和pymysql库操作MySQL数据库的基本方法，包括创建数据表、增删改查数据以及事务处理。你可以根据自己的需要修改和扩展代码，实现更复杂的功能。