import mysql.connector
from mysql.connector import Error
def transfer_money(from_id, to_id, amount):
    """
    模擬轉賬的事務處理
    :param from_id: 轉出賬戶ID
    :param to_id: 轉入賬戶ID
    :param amount: 轉賬金額
    """
    connection = None
    try:
        # 1. 建立數(shù)據(jù)庫連接
        connection = mysql.connector.connect(
            host='localhost',
            user='root',
            password='123456',
            database='test_db'
        )
        # 2. 關閉自動提交，開啟手動事務控制
        connection.autocommit = False
        cursor = connection.cursor()
        # 3. 執(zhí)行事務內(nèi)的多個SQL操作
        # 步驟1：扣除轉出賬戶金額
        deduct_sql = "UPDATE account SET balance = balance - %s WHERE id = %s"
        cursor.execute(deduct_sql, (amount, from_id))
        # 步驟2：增加轉入賬戶金額
        add_sql = "UPDATE account SET balance = balance + %s WHERE id = %s"
        cursor.execute(add_sql, (amount, to_id))
        # 模擬異常（可取消注釋測試回滾效果）
        # raise Error("模擬轉賬異常，觸發(fā)回滾")
        # 4. 所有操作執(zhí)行成功，提交事務
        connection.commit()
        print("轉賬成功！事務已提交")
    except Error as e:
        # 5. 發(fā)生異常，回滾事務（撤銷所有已執(zhí)行的SQL操作）
        if connection:
            connection.rollback()
        print(f"轉賬失敗，事務已回滾！錯誤信息：{e}")
    finally:
        # 6. 釋放資源（關閉游標和連接）
        if connection and connection.is_connected():
            cursor.close()
            # 恢復自動提交（可選，不影響，但規(guī)范）
            connection.autocommit = True
            connection.close()
            print("數(shù)據(jù)庫連接已關閉")
# ==================== 測試前準備 ====================
# 先在MySQL中創(chuàng)建測試表和數(shù)據(jù)：
# CREATE DATABASE IF NOT EXISTS test_db;
# USE test_db;
# CREATE TABLE IF NOT EXISTS account (
#     id INT PRIMARY KEY,
#     name VARCHAR(50),
#     balance DECIMAL(10,2)
# );
# INSERT INTO account (id, name, balance) VALUES (1, '張三', 1000.00), (2, '李四', 500.00);
# ==================== 執(zhí)行轉賬測試 ====================
# 測試正常轉賬（張三給李四轉200元）
transfer_money(from_id=1, to_id=2, amount=200.00)

def batch_insert_users(users):
    """批量插入用戶，使用事務保證全部成功/失敗"""
    connection = None
    try:
        connection = mysql.connector.connect(
            host='localhost',
            user='root',
            password='123456',
            database='test_db'
        )
        connection.autocommit = False
        cursor = connection.cursor()
        insert_sql = "INSERT INTO user (name, age) VALUES (%s, %s)"
        # 批量執(zhí)行SQL（效率更高）
        cursor.executemany(insert_sql, users)
        connection.commit()
        print(f"批量插入 {cursor.rowcount} 條數(shù)據(jù)成功")
    except Error as e:
        if connection:
            connection.rollback()
        print(f"批量插入失敗，事務回滾：{e}")
    finally:
        if connection and connection.is_connected():
            cursor.close()
            connection.autocommit = True
            connection.close()
# 測試批量插入
user_list = [("王五", 30), ("趙六", 28), ("孫七", 35)]
batch_insert_users(user_list)