mysql 表级读写分离

import mysql.connector
from mysql.connector import pooling

# 创建连接池
dbconfig = {
    "host": "master_host",
    "user": "user",
    "password": "password",
    "database": "database"
}
master_pool = mysql.connector.pooling.MySQLConnectionPool(pool_name="master_pool", pool_size=5, **dbconfig)

dbconfig_slave = {
    "host": "slave_host",
    "user": "user",
    "password": "password",
    "database": "database"
}
slave_pool = mysql.connector.pooling.MySQLConnectionPool(pool_name="slave_pool", pool_size=5, **dbconfig_slave)

def execute_query(query, params=None, read_only=False):
    try:
        if read_only:
            conn = slave_pool.get_connection()
        else:
            conn = master_pool.get_connection()
        cursor = conn.cursor()
        cursor.execute(query, params)
        result = cursor.fetchall()
        cursor.close()
        conn.commit()
        return result
    except mysql.connector.Error as err:
        print(f"Error: {err}")
    finally:
        if conn.is_connected():
            conn.close()

# 示例查询
read_only_query = "SELECT * FROM users WHERE id = %s"
write_query = "UPDATE users SET name = %s WHERE id = %s"

# 读取操作
result = execute_query(read_only_query, (1,), read_only=True)
print(result)

# 写入操作
execute_query(write_query, ("new_name", 1))