from dbutils.pooled_db import PooledDB
import pymysql

# 創(chuàng)建連接池
pool = PooledDB(
    creator=pymysql,
    host='localhost',
    user='root',
    password='password',
    database='test_db',
    charset='utf8mb4',
    mincached=5,  # 初始連接數(shù)
    maxcached=20, # 最大空閑連接數(shù)
    maxconnections=50,  # 最大連接數(shù)
    blocking=True  # 連接不足時(shí)是否阻塞等待
)

# 從連接池獲取連接
conn = pool.connection()
try:
    with conn.cursor() as cursor:
        cursor.execute("UPDATE users SET balance = balance - 100 WHERE id = 1")
        conn.commit()
finally:
    conn.close()  # 歸還連接到池中

import pymysql

conn = pymysql.connect(...)
try:
    with conn.cursor() as cursor:
        # 準(zhǔn)備批量數(shù)據(jù)（列表的列表）
        data = [
            (100, 'Alice'),
            (200, 'Bob'),
            (300, 'Charlie')
        ]
        # 使用executemany批量更新
        cursor.executemany(
            "UPDATE accounts SET balance = %s WHERE username = %s",
            data
        )
        conn.commit()
        print(f"Updated {cursor.rowcount} records")
finally:
    conn.close()

def batch_update_with_case(user_ids, new_balances):
    conn = pymysql.connect(...)
    try:
        with conn.cursor() as cursor:
            # 構(gòu)建動(dòng)態(tài)SQL
            sql = """
            UPDATE users 
            SET balance = CASE id
            """
            for user_id, balance in zip(user_ids, new_balances):
                sql += f"WHEN {user_id} THEN {balance} "
            sql += "END WHERE id IN (" + ",".join(map(str, user_ids)) + ")"
            
            cursor.execute(sql)
            conn.commit()
    finally:
        conn.close()

# 步驟1：創(chuàng)建臨時(shí)表并導(dǎo)入數(shù)據(jù)
cursor.execute("""
    CREATE TEMPORARY TABLE temp_updates (
        id INT PRIMARY KEY,
        new_balance DECIMAL(10,2)
    )
""")
# 使用executemany插入臨時(shí)數(shù)據(jù)（此處省略具體代碼）

# 步驟2：執(zhí)行JOIN更新
cursor.execute("""
    UPDATE users u
    JOIN temp_updates t ON u.id = t.id
    SET u.balance = t.new_balance
""")

def transfer_money(from_id, to_id, amount):
    conn = pymysql.connect(autocommit=False)  # 顯式關(guān)閉自動(dòng)提交
    try:
        with conn.cursor() as cursor:
            # 開始事務(wù)（MySQL中可省略，DML語句會(huì)自動(dòng)開啟）
            cursor.execute("START TRANSACTION")
            
            # 執(zhí)行扣款
            cursor.execute(
                "UPDATE accounts SET balance = balance - %s WHERE id = %s AND balance >= %s",
                (amount, from_id, amount)
            )
            if cursor.rowcount == 0:
                raise ValueError("Insufficient balance or user not found")
            
            # 執(zhí)行加款
            cursor.execute(
                "UPDATE accounts SET balance = balance + %s WHERE id = %s",
                (amount, to_id)
            )
            
            conn.commit()  # 提交事務(wù)
            print("Transaction completed successfully")
    except Exception as e:
        conn.rollback()  # 回滾事務(wù)
        print(f"Transaction failed: {e}")
    finally:
        conn.close()

# 錯(cuò)誤示例：對(duì)索引字段使用函數(shù)導(dǎo)致索引失效
cursor.execute("""
    SELECT * FROM users 
    WHERE DATE(create_time) = '2026-01-01'  # 索引失效
""")

# 正確寫法：使用范圍查詢
cursor.execute("""
    SELECT * FROM users 
    WHERE create_time BETWEEN '2026-01-01 00:00:00' AND '2026-01-01 23:59:59'
""")

from celery import Celery
import pymysql

app = Celery('tasks', broker='redis://localhost:6379/0')

@app.task
def async_update_user_score(user_id, new_score):
    conn = pymysql.connect(...)
    try:
        with conn.cursor() as cursor:
            cursor.execute(
                "UPDATE users SET score = %s WHERE id = %s",
                (new_score, user_id)
            )
        conn.commit()
    finally:
        conn.close()

# 調(diào)用異步任務(wù)
async_update_user_score.delay(123, 95)

# 原始代碼（存在問題）
for item_id in item_ids:
    cursor.execute(
        "UPDATE inventory SET stock = stock - 1 WHERE id = %s AND stock > 0",
        (item_id,)
    )
    conn.commit()  # 每次更新都提交，性能極差

def update_inventory_batch(item_updates):
    """
    item_updates: List[Tuple[item_id, quantity]]
    """
    conn = pymysql.connect(autocommit=False)
    try:
        with conn.cursor() as cursor:
            # 批量更新主邏輯
            for item_id, quantity in item_updates:
                cursor.execute("""
                    UPDATE inventory 
                    SET stock = stock - %s 
                    WHERE id = %s AND stock >= %s
                """, (quantity, item_id, quantity))
                if cursor.rowcount == 0:
                    raise ValueError(f"Inventory shortage for item {item_id}")
            
            # 提交事務(wù)（所有更新成功或全部回滾）
            conn.commit()
            
            # 可選：記錄更新日志到異步隊(duì)列
            # async_log_inventory_changes(item_updates)
    except Exception as e:
        conn.rollback()
        raise e
    finally:
        conn.close()