ksycopg2高级应用：Python与KingbaseES数据库的性能优化实战

1. 初识ksycopg2：Python与KingbaseES的桥梁

如果你正在寻找一种高效的方式来连接Python和KingbaseES数据库，ksycopg2绝对是你不可错过的选择。作为KingbaseES官方推荐的Python适配器，ksycopg2完美实现了Python DB API 2.0规范，并且是线程安全的。这意味着你可以在多线程环境中安全地使用它，而不用担心数据一致性问题。

我在实际项目中使用ksycopg2已经有三年多时间了，从最初的简单查询到后来的高并发场景，它都表现得相当稳定。与通用的PostgreSQL驱动psycopg2相比，ksycopg2针对KingbaseES进行了深度优化，特别是在数据类型映射、事务处理和高级功能支持方面表现更加出色。

安装ksycopg2非常简单，你可以通过pip直接安装：

pip install ksycopg2

或者从KingbaseES官网下载对应版本的驱动包。需要注意的是，ksycopg2驱动需要和Python大版本一致，比如Python3.8需要使用支持Python3.8.x的ksycopg2驱动。

2. 连接池管理：高并发场景的救星

在高并发应用中，频繁地创建和关闭数据库连接会带来很大的性能开销。这时候，连接池就显得尤为重要了。ksycopg2提供了内置的连接池实现，可以显著提升应用性能。

我在一个Web项目中实测过，使用连接池后，数据库操作的响应时间平均降低了40%。下面是一个简单的连接池实现示例：

from ksycopg2 import pool

class ConnectionPoolManager:
    def __init__(self, minconn=1, maxconn=10, **db_params):
        self.db_params = db_params
        try:
            self.connection_pool = pool.SimpleConnectionPool(
                minconn, maxconn, **db_params
            )
            print(" 数据库连接池创建成功")
        except Exception as e:
            print(f" 连接池创建失败: {e}")
            self.connection_pool = None

    def get_connection(self):
        if self.connection_pool:
            return self.connection_pool.getconn()
        return None

    def return_connection(self, conn):
        if self.connection_pool and conn:
            self.connection_pool.putconn(conn)

    def close_all_connections(self):
        if self.connection_pool:
            self.connection_pool.closeall()
            print(" 所有连接已关闭")

使用这个连接池管理器非常简单：

db_params = {
    "database": "TEST",
    "user": "SYSTEM",
    "password": "your_password",
    "host": "localhost",
    "port": "54321"
}

pool_mgr = ConnectionPoolManager(**db_params)

# 从连接池获取连接执行操作
conn = pool_mgr.get_connection()
if conn:
    try:
        cursor = conn.cursor()
        cursor.execute("SELECT COUNT(*) FROM employees")
        count = cursor.fetchone()[0]
        print(f"当前员工总数: {count}")
        cursor.close()
    finally:
        pool_mgr.return_connection(conn)

pool_mgr.close_all_connections()

3. 批量操作：大幅提升数据写入效率

当需要处理大量数据插入或更新时，单条执行SQL语句会非常低效。ksycopg2提供了executemany()方法来实现批量操作，可以显著提升性能。

我曾经处理过一个需要导入10万条记录的项目，使用单条插入需要近10分钟，而改用批量操作后，时间缩短到了不到30秒。下面是批量插入的示例代码：

def batch_insert_employees(employees_data):
    conn = None
    try:
        conn = ksycopg2.connect(
            dbname="TEST", 
            user="SYSTEM", 
            password="123456", 
            host="127.0.0.1", 
            port="54321"
        )
        cur = conn.cursor()
        
        # 批量插入
        cur.executemany(
            "INSERT INTO employees (name, department, salary) VALUES (%s, %s, %s)",
            employees_data
        )
        conn.commit()
        print(f"成功插入 {len(employees_data)} 条记录")
    except Exception as e:
        print(f"批量插入失败: {e}")
        if conn:
            conn.rollback()
    finally:
        if conn:
            conn.close()

# 准备测试数据
employees = [
    ('张三', '技术部', 15000.00),
    ('李四', '销售部', 12000.00),
    ('王五', '人事部', 10000.00),
    # 可以添加更多数据...
]

batch_insert_employees(employees)

对于更大量的数据，还可以考虑使用COPY命令，这是PostgreSQL系数据库(包括KingbaseES)中最快的数据导入方式。ksycopg2也提供了copy_from和copy_to方法来实现这一功能。

4. 事务管理与错误处理：保证数据一致性

在数据库操作中，事务管理至关重要。ksycopg2提供了完整的事务支持，可以确保数据的一致性。下面是一个转账操作的示例，展示了如何使用事务：

def transfer_salary(db_mgr, from_emp, to_emp, amount):
    """薪资转账事务示例"""
    conn = db_mgr.conn
    cursor = conn.cursor()
    try:
        # 开始事务
        conn.autocommit = False
        
        # 检查转出账户余额
        cursor.execute("SELECT salary FROM employees WHERE emp_code = %s", (from_emp,))
        from_balance = cursor.fetchone()
        if not from_balance:
            raise Exception(f"员工 {from_emp} 不存在")
        if from_balance[0] < amount:
            raise Exception(f"员工 {from_emp} 余额不足")

        # 执行转账操作
        cursor.execute(
            "UPDATE employees SET salary = salary - %s WHERE emp_code = %s", 
            (amount, from_emp)
        )
        cursor.execute(
            "UPDATE employees SET salary = salary + %s WHERE emp_code = %s", 
            (amount, to_emp)
        )

        # 记录交易日志
        cursor.execute("""
            INSERT INTO salary_history (emp_id, salary_month, base_salary, bonus, remark)
            SELECT emp_id, TO_CHAR(CURRENT_DATE, 'YYYY-MM'), -%s, 0, '转账给员工%s'
            FROM employees WHERE emp_code = %s
        """, (amount, to_emp, from_emp))
        
        cursor.execute("""
            INSERT INTO salary_history (emp_id, salary_month, base_salary, bonus, remark)
            SELECT emp_id, TO_CHAR(CURRENT_DATE, 'YYYY-MM'), %s, 0, '收到员工%s转账'
            FROM employees WHERE emp_code = %s
        """, (amount, from_emp, to_emp))

        # 提交事务
        conn.commit()
        print(f" 转账成功: 从 {from_emp} 向 {to_emp} 转账 {amount} 元")
        return True
    except Exception as e:
        # 回滚事务
        conn.rollback()
        print(f" 转账失败，已回滚: {e}")
        return False
    finally:
        cursor.close()
        conn.autocommit = True

在实际开发中，我还建议为数据库操作添加重试机制，特别是对于网络不稳定的环境：

def robust_query(db_mgr, sql, params=None):
    """健壮的查询函数"""
    max_retries = 3
    for attempt in range(max_retries):
        try:
            cursor = db_mgr.conn.cursor()
            cursor.execute(sql, params)
            results = cursor.fetchall()
            cursor.close()
            return results
        except OperationalError as e:
            if attempt == max_retries - 1:
                raise e
            print(f"数据库操作失败，重试 {attempt + 1}/{max_retries}")
            db_mgr.connect()  # 重新连接

5. 高级查询技巧：提升数据检索效率

除了基本的CRUD操作，ksycopg2还支持许多高级查询功能。比如，使用字典游标(DictCursor)可以让查询结果以字典形式返回，提高代码可读性：

from ksycopg2 import extras

def get_employee_details(emp_id):
    conn = None
    try:
        conn = ksycopg2.connect(
            dbname="TEST", 
            user="SYSTEM", 
            password="123456", 
            host="127.0.0.1", 
            port="54321"
        )
        # 使用DictCursor
        cur = conn.cursor(cursor_factory=extras.DictCursor)
        cur.execute("""
            SELECT e.*, d.dept_name 
            FROM employees e 
            JOIN departments d ON e.dept_id = d.dept_id 
            WHERE e.emp_id = %s
        """, (emp_id,))
        
        employee = cur.fetchone()
        if employee:
            # 现在可以通过列名访问数据
            print(f"员工 {employee['name']} 在 {employee['dept_name']} 部门")
            return dict(employee)  # 转换为普通字典
        return None
    except Exception as e:
        print(f"查询失败: {e}")
        return None
    finally:
        if conn:
            conn.close()

对于分页查询，可以使用LIMIT和OFFSET子句，但更高效的方式是使用游标分页：

def get_employees_paginated(page=1, page_size=10):
    conn = None
    try:
        conn = ksycopg2.connect(
            dbname="TEST", 
            user="SYSTEM", 
            password="123456", 
            host="127.0.0.1", 
            port="54321"
        )
        cur = conn.cursor()
        
        offset = (page - 1) * page_size
        cur.execute("""
            SELECT e.emp_id, e.name, e.position, e.salary, d.dept_name
            FROM employees e
            JOIN departments d ON e.dept_id = d.dept_id
            ORDER BY e.emp_id
            LIMIT %s OFFSET %s
        """, (page_size, offset))
        
        employees = cur.fetchall()
        return employees
    except Exception as e:
        print(f"分页查询失败: {e}")
        return []
    finally:
        if conn:
            conn.close()

6. 性能监控与调优：让数据库飞起来

要优化ksycopg2与KingbaseES的性能，首先需要知道瓶颈在哪里。KingbaseES提供了丰富的性能视图，可以帮助我们分析查询性能：

def analyze_query_performance():
    conn = None
    try:
        conn = ksycopg2.connect(
            dbname="TEST", 
            user="SYSTEM", 
            password="123456", 
            host="127.0.0.1", 
            port="54321"
        )
        cur = conn.cursor()
        
        # 获取慢查询
        cur.execute("""
            SELECT query, total_time, calls, mean_time
            FROM pg_stat_statements
            ORDER BY total_time DESC
            LIMIT 10
        """)
        
        print("最耗时的10个查询:")
        for query, total_time, calls, mean_time in cur.fetchall():
            print(f"查询: {query[:100]}...")
            print(f"总耗时: {total_time:.2f}ms, 调用次数: {calls}, 平均耗时: {mean_time:.2f}ms")
            print("-" * 50)
            
        # 获取锁等待信息
        cur.execute("""
            SELECT blocked_locks.pid AS blocked_pid,
                   blocking_locks.pid AS blocking_pid,
                   blocked_activity.query AS blocked_query,
                   blocking_activity.query AS blocking_query
            FROM pg_catalog.pg_locks blocked_locks
            JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
            JOIN pg_catalog.pg_locks blocking_locks 
                ON blocking_locks.locktype = blocked_locks.locktype
                AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE
                AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
                AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
                AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
                AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
                AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
                AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
                AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
                AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
                AND blocking_locks.pid != blocked_locks.pid
            JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
            WHERE NOT blocked_locks.GRANTED
        """)
        
        print("\n锁等待情况:")
        for blocked_pid, blocking_pid, blocked_query, blocking_query in cur.fetchall():
            print(f"被阻塞的PID: {blocked_pid}, 阻塞的PID: {blocking_pid}")
            print(f"被阻塞的查询: {blocked_query[:100]}...")
            print(f"阻塞的查询: {blocking_query[:100]}...")
            print("-" * 50)
            
    except Exception as e:
        print(f"性能分析失败: {e}")
    finally:
        if conn:
            conn.close()

除了监控，我们还可以通过一些技巧来优化查询性能：

1. 使用预编译语句：ksycopg2支持预编译语句，可以避免重复解析SQL的开销。
2. 合理设置fetch大小：对于大量数据查询，可以通过cursor.itersize设置每次从服务器获取的行数。
3. 使用服务端游标：对于非常大的结果集，可以使用服务端游标来减少内存使用。

7. 数据类型处理：避免常见的坑

KingbaseES和Python之间的数据类型映射是一个需要注意的地方。不正确的类型处理可能导致性能问题甚至错误。下面是一些常见的数据类型处理技巧：

def handle_data_types():
    conn = None
    try:
        conn = ksycopg2.connect(
            dbname="TEST", 
            user="SYSTEM", 
            password="123456", 
            host="127.0.0.1", 
            port="54321"
        )
        cur = conn.cursor()
        
        # 创建测试表
        cur.execute('''
            CREATE TABLE IF NOT EXISTS data_type_test (
                id SERIAL PRIMARY KEY,
                name VARCHAR(100),
                salary DECIMAL(10,2),
                hire_date DATE,
                is_active BOOLEAN,
                metadata JSONB,
                binary_data BYTEA,
                created_at TIMESTAMP
            )
        ''')
        conn.commit()
        
        # 插入各种类型的数据
        test_data = {
            'name': '张三',
            'salary': 15000.50,
            'hire_date': '2023-01-15',
            'is_active': True,
            'metadata': {'department': 'IT', 'level': 'Senior'},
            'binary_data': b'\x00\x01\x02\x03',
            'created_at': '2023-01-15 10:30:00'
        }
        
        cur.execute('''
            INSERT INTO data_type_test 
            (name, salary, hire_date, is_active, metadata, binary_data, created_at)
            VALUES (%(name)s, %(salary)s, %(hire_date)s, %(is_active)s, 
                    %(metadata)s, %(binary_data)s, %(created_at)s)
        ''', test_data)
        conn.commit()
        
        # 查询并处理各种类型数据
        cur.execute('SELECT * FROM data_type_test')
        row = cur.fetchone()
        
        print(f"ID: {row[0]}, 类型: {type(row[0])}")  # int
        print(f"姓名: {row[1]}, 类型: {type(row[1])}")  # str
        print(f"薪资: {row[2]}, 类型: {type(row[2])}")  # Decimal
        print(f"入职日期: {row[3]}, 类型: {type(row[3])}")  # date
        print(f"是否活跃: {row[4]}, 类型: {type(row[4])}")  # bool
        print(f"元数据: {row[5]}, 类型: {type(row[5])}")  # dict (JSONB)
        print(f"二进制数据: {row[6]}, 类型: {type(row[6])}")  # memoryview/bytes
        print(f"创建时间: {row[7]}, 类型: {type(row[7])}")  # datetime
        
    except Exception as e:
        print(f"数据类型处理失败: {e}")
        if conn:
            conn.rollback()
    finally:
        if conn:
            conn.close()

在处理二进制数据时，ksycopg2会将BYTEA类型的数据转换为memoryview对象（Python 3）或buffer对象（Python 2）。对于大型二进制对象（BLOB），最好使用流式处理：

def handle_large_binary():
    conn = None
    try:
        conn = ksycopg2.connect(
            dbname="TEST", 
            user="SYSTEM", 
            password="123456", 
            host="127.0.0.1", 
            port="54321"
        )
        cur = conn.cursor()
        
        # 创建大对象
        large_obj_id = conn.lobject().oid
        print(f"创建的大对象ID: {large_obj_id}")
        
        # 写入大对象
        large_obj = conn.lobject(large_obj_id, 'w')
        with open('/path/to/large/file', 'rb') as f:
            while True:
                data = f.read(8192)  # 8KB chunks
                if not data:
                    break
                large_obj.write(data)
        large_obj.close()
        
        # 读取大对象
        large_obj = conn.lobject(large_obj_id, 'r')
        with open('/path/to/output/file', 'wb') as f:
            while True:
                data = large_obj.read(8192)
                if not data:
                    break
                f.write(data)
        large_obj.close()
        
        # 删除大对象
        conn.lobject(large_obj_id).unlink()
        
    except Exception as e:
        print(f"大对象处理失败: {e}")
    finally:
        if conn:
            conn.close()

8. 实战案例：构建高性能员工管理系统

让我们把这些知识点综合起来，构建一个完整的员工管理系统。这个系统将展示如何使用ksycopg2的高级特性来优化性能。

首先，我们创建数据库表结构：

def create_employee_schema():
    conn = None
    try:
        conn = ksycopg2.connect(
            dbname="TEST", 
            user="SYSTEM", 
            password="123456", 
            host="127.0.0.1", 
            port="54321"
        )
        cur = conn.cursor()
        
        # 创建部门表
        cur.execute('''
            CREATE TABLE IF NOT EXISTS departments (
                dept_id SERIAL PRIMARY KEY,
                dept_name VARCHAR(100) NOT NULL UNIQUE,
                manager VARCHAR(50),
                budget DECIMAL(15,2),
                create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        ''')
        
        # 创建员工表
        cur.execute('''
            CREATE TABLE IF NOT EXISTS employees (
                emp_id SERIAL PRIMARY KEY,
                emp_code VARCHAR(20) UNIQUE NOT NULL,
                name VARCHAR(50) NOT NULL,
                gender CHAR(1) CHECK (gender IN ('M', 'F')),
                age INT CHECK (age >= 18 AND age <= 65),
                position VARCHAR(50),
                salary DECIMAL(10,2),
                dept_id INT REFERENCES departments(dept_id),
                hire_date DATE,
                active BOOLEAN DEFAULT TRUE,
                skills TEXT[],
                created_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        ''')
        
        # 创建薪资历史表
        cur.execute('''
            CREATE TABLE IF NOT EXISTS salary_history (
                record_id SERIAL PRIMARY KEY,
                emp_id INT NOT NULL REFERENCES employees(emp_id),
                salary_month VARCHAR(7) NOT NULL,  -- 格式: YYYY-MM
                base_salary DECIMAL(10,2),
                bonus DECIMAL(10,2) DEFAULT 0,
                deduction DECIMAL(10,2) DEFAULT 0,
                total_salary DECIMAL(10,2) GENERATED ALWAYS AS (base_salary + bonus - deduction) STORED,
                pay_date DATE,
                remark TEXT,
                created_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                CONSTRAINT unique_emp_month UNIQUE(emp_id, salary_month)
            )
        ''')
        
        # 创建索引提升查询性能
        cur.execute('CREATE INDEX IF NOT EXISTS idx_employees_dept ON employees(dept_id)')
        cur.execute('CREATE INDEX IF NOT EXISTS idx_employees_active ON employees(active)')
        cur.execute('CREATE INDEX IF NOT EXISTS idx_salary_history_emp ON salary_history(emp_id)')
        cur.execute('CREATE INDEX IF NOT EXISTS idx_salary_history_month ON salary_history(salary_month)')
        
        conn.commit()
        print("数据库架构创建成功")
        
    except Exception as e:
        print(f"创建数据库架构失败: {e}")
        if conn:
            conn.rollback()
    finally:
        if conn:
            conn.close()

接下来，我们实现员工管理类，使用连接池和批量操作优化性能：

from ksycopg2 import pool, extras

class EmployeeManager:
    def __init__(self, pool_manager):
        self.pool = pool_manager
    
    def add_department(self, dept_name, manager=None, budget=None):
        """添加部门"""
        conn = self.pool.get_connection()
        if not conn:
            return None
            
        try:
            cur = conn.cursor()
            cur.execute('''
                INSERT INTO departments (dept_name, manager, budget)
                VALUES (%s, %s, %s)
                RETURNING dept_id
            ''', (dept_name, manager, budget))
            
            dept_id = cur.fetchone()[0]
            conn.commit()
            print(f"部门 '{dept_name}' 添加成功，ID: {dept_id}")
            return dept_id
        except Exception as e:
            print(f"添加部门失败: {e}")
            conn.rollback()
            return None
        finally:
            self.pool.return_connection(conn)
    
    def batch_add_employees(self, employees):
        """批量添加员工"""
        if not employees:
            return 0
            
        conn = self.pool.get_connection()
        if not conn:
            return 0
            
        try:
            cur = conn.cursor()
            
            # 使用executemany批量插入
            cur.executemany('''
                INSERT INTO employees 
                (emp_code, name, gender, age, position, salary, dept_id, hire_date, skills)
                VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)
            ''', employees)
            
            count = cur.rowcount
            conn.commit()
            print(f"成功添加 {count} 名员工")
            return count
        except Exception as e:
            print(f"批量添加员工失败: {e}")
            conn.rollback()
            return 0
        finally:
            self.pool.return_connection(conn)
    
    def get_employees_by_department(self, dept_id, active_only=True):
        """按部门查询员工"""
        conn = self.pool.get_connection()
        if not conn:
            return []
            
        try:
            # 使用DictCursor方便处理结果
            cur = conn.cursor(cursor_factory=extras.DictCursor)
            
            if active_only:
                cur.execute('''
                    SELECT e.*, d.dept_name
                    FROM employees e
                    JOIN departments d ON e.dept_id = d.dept_id
                    WHERE e.dept_id = %s AND e.active = TRUE
                    ORDER BY e.salary DESC
                ''', (dept_id,))
            else:
                cur.execute('''
                    SELECT e.*, d.dept_name
                    FROM employees e
                    JOIN departments d ON e.dept_id = d.dept_id
                    WHERE e.dept_id = %s
                    ORDER BY e.salary DESC
                ''', (dept_id,))
                
            employees = cur.fetchall()
            
            # 将结果转换为字典列表
            result = []
            for emp in employees:
                result.append(dict(emp))
                
            return result
        except Exception as e:
            print(f"查询员工失败: {e}")
            return []
        finally:
            self.pool.return_connection(conn)
    
    def update_salary(self, emp_code, new_salary, remark=None):
        """更新员工薪资并记录历史"""
        conn = self.pool.get_connection()
        if not conn:
            return False
            
        try:
            conn.autocommit = False
            cur = conn.cursor()
            
            # 更新当前薪资
            cur.execute('''
                UPDATE employees 
                SET salary = %s 
                WHERE emp_code = %s
                RETURNING emp_id
            ''', (new_salary, emp_code))
            
            result = cur.fetchone()
            if not result:
                raise Exception(f"员工 {emp_code} 不存在")
                
            emp_id = result[0]
            
            # 记录薪资变更历史
            current_month = datetime.datetime.now().strftime('%Y-%m')
            cur.execute('''
                INSERT INTO salary_history 
                (emp_id, salary_month, base_salary, remark)
                VALUES (%s, %s, %s, %s)
                ON CONFLICT (emp_id, salary_month) 
                DO UPDATE SET base_salary = EXCLUDED.base_salary,
                              remark = EXCLUDED.remark
            ''', (emp_id, current_month, new_salary, remark))
            
            conn.commit()
            print(f"员工 {emp_code} 薪资更新为 {new_salary}")
            return True
        except Exception as e:
            print(f"更新薪资失败: {e}")
            conn.rollback()
            return False
        finally:
            conn.autocommit = True
            self.pool.return_connection(conn)
    
    def get_department_stats(self):
        """获取部门统计信息"""
        conn = self.pool.get_connection()
        if not conn:
            return []
            
        try:
            cur = conn.cursor()
            
            cur.execute('''
                SELECT 
                    d.dept_id,
                    d.dept_name,
                    COUNT(e.emp_id) AS employee_count,
                    AVG(e.salary) AS avg_salary,
                    SUM(e.salary) AS total_salary
                FROM departments d
                LEFT JOIN employees e ON d.dept_id = e.dept_id AND e.active = TRUE
                GROUP BY d.dept_id, d.dept_name
                ORDER BY total_salary DESC
            ''')
            
            stats = cur.fetchall()
            
            # 格式化结果
            result = []
            for stat in stats:
                result.append({
                    'dept_id': stat[0],
                    'dept_name': stat[1],
                    'employee_count': stat[2] or 0,
                    'avg_salary': float(stat[3]) if stat[3] else 0,
                    'total_salary': float(stat[4]) if stat[4] else 0
                })
                
            return result
        except Exception as e:
            print(f"获取部门统计失败: {e}")
            return []
        finally:
            self.pool.return_connection(conn)

最后，我们可以这样使用这个员工管理系统：

# 初始化连接池
db_params = {
    "database": "TEST",
    "user": "SYSTEM",
    "password": "123456",
    "host": "127.0.0.1",
    "port": "54321"
}

pool_mgr = ConnectionPoolManager(minconn=2, maxconn=10, **db_params)
emp_manager = EmployeeManager(pool_mgr)

# 添加部门
tech_dept = emp_manager.add_department("技术部", "张经理", 1000000)
sales_dept = emp_manager.add_department("销售部", "李经理", 800000)
hr_dept = emp_manager.add_department("人事部", "王经理", 500000)

# 批量添加员工
employees = [
    ("EMP2023001", "张三", "M", 28, "高级工程师", 15000.00, tech_dept, "2023-01-15", ["Python", "SQL", "Linux"]),
    ("EMP2023002", "李四", "F", 25, "前端工程师", 12000.00, tech_dept, "2023-02-20", ["JavaScript", "HTML", "CSS"]),
    ("EMP2023003", "王五", "M", 30, "销售经理", 13000.00, sales_dept, "2023-01-10", ["沟通", "谈判", "市场分析"]),
    ("EMP2023004", "赵六", "F", 26, "招聘专员", 9000.00, hr_dept, "2023-03-01", ["面试", "招聘", "员工关系"]),
    ("EMP2023005", "钱七", "M", 32, "架构师", 20000.00, tech_dept, "2023-02-15", ["架构设计", "系统优化", "Python"]),
]

emp_manager.batch_add_employees(employees)

# 查询技术部员工
tech_employees = emp_manager.get_employees_by_department(tech_dept)
print("\n技术部员工:")
for emp in tech_employees:
    print(f"{emp['emp_code']}: {emp['name']} - {emp['position']} (薪资: {emp['salary']})")

# 更新薪资
emp_manager.update_salary("EMP2023001", 18000.00, "年度调薪")

# 获取部门统计
dept_stats = emp_manager.get_department_stats()
print("\n部门统计:")
for stat in dept_stats:
    print(f"{stat['dept_name']}: {stat['employee_count']}人, 平均薪资: {stat['avg_salary']:.2f}, 总薪资: {stat['total_salary']:.2f}")

# 关闭连接池
pool_mgr.close_all_connections()