kg_agent/utils/mysql/example_usage.py

"""
MySQL工具库使用示例

本文件展示如何使用MySQL工具库进行各种数据库操作
"""

from . import mysql_db, MySQLConnectionError, MySQLQueryError


def basic_crud_examples():
    """基础CRUD操作示例"""
    print("=== 基础CRUD操作示例 ===")

    try:
        # 1. 插入数据
        user_id = mysql_db.insert('users', {
            'name': 'John Doe',
            'email': 'john@example.com',
            'age': 25,
            'created_at': '2023-01-01 10:00:00'
        })
        print(f"插入用户成功，ID: {user_id}")

        # 2. 查询单条数据
        user = mysql_db.select_one('users', where='id = %s', where_params=(user_id,))
        print(f"查询用户: {user}")

        # 3. 查询多条数据
        users = mysql_db.select('users', where='age > %s', where_params=(20,), limit=10)
        print(f"查询到 {len(users)} 个用户")

        # 4. 更新数据
        affected_rows = mysql_db.update('users', {'age': 26}, 'id = %s', (user_id,))
        print(f"更新了 {affected_rows} 条记录")

        # 5. 统计记录数
        count = mysql_db.count('users', where='age > %s', where_params=(20,))
        print(f"年龄大于20的用户数: {count}")

        # 6. 检查记录是否存在
        exists = mysql_db.exists('users', 'email = %s', ('john@example.com',))
        print(f"邮箱存在: {exists}")

        # 7. 删除数据（注意：这里只是示例，实际使用时要谨慎）
        # deleted_rows = mysql_db.delete('users', 'id = %s', (user_id,))
        # print(f"删除了 {deleted_rows} 条记录")

    except (MySQLConnectionError, MySQLQueryError) as e:
        print(f"数据库操作失败: {e}")


def advanced_query_examples():
    """高级查询示例"""
    print("\n=== 高级查询示例 ===")

    try:
        # 1. 分页查询
        result = mysql_db.paginate('users', page=1, page_size=5, order_by='created_at DESC')
        print(f"分页查询结果:")
        print(f"  当前页: {result['pagination']['current_page']}")
        print(f"  总记录数: {result['pagination']['total_count']}")
        print(f"  总页数: {result['pagination']['total_pages']}")
        print(f"  数据条数: {len(result['data'])}")

        # 2. 排序查询
        users = mysql_db.select_with_sort('users', sort_field='age', sort_order='DESC', limit=5)
        print(f"按年龄降序查询到 {len(users)} 个用户")

        # 3. 多字段排序
        users = mysql_db.select_with_multiple_sort('users',
            sort_fields=[('age', 'DESC'), ('created_at', 'ASC')], limit=5)
        print(f"多字段排序查询到 {len(users)} 个用户")

        # 4. 聚合查询
        agg_result = mysql_db.aggregate('users', {
            'total_count': 'COUNT(*)',
            'avg_age': 'AVG(age)',
            'max_age': 'MAX(age)',
            'min_age': 'MIN(age)'
        })
        print(f"聚合查询结果: {agg_result[0]}")

        # 5. 分组统计
        age_groups = mysql_db.group_count('users', 'age', limit=5)
        print(f"年龄分组统计: {age_groups}")

        # 6. 模糊搜索
        search_results = mysql_db.search('users', ['name', 'email'], 'john', limit=10)
        print(f"搜索结果: {len(search_results)} 条记录")

        # 7. 单独聚合函数
        total_age = mysql_db.sum('users', 'age')
        avg_age = mysql_db.avg('users', 'age')
        max_age = mysql_db.max('users', 'age')
        min_age = mysql_db.min('users', 'age')
        print(f"年龄统计 - 总和: {total_age}, 平均: {avg_age}, 最大: {max_age}, 最小: {min_age}")

    except Exception as e:
        print(f"高级查询失败: {e}")


def transaction_examples():
    """事务操作示例"""
    print("\n=== 事务操作示例 ===")

    try:
        # 1. 使用事务上下文管理器
        with mysql_db.transaction():
            # 在事务中执行多个操作
            user_id = mysql_db.insert('users', {
                'name': 'Transaction User',
                'email': 'trans@example.com',
                'age': 30
            })

            # 更新相关数据
            mysql_db.update('users', {'age': 31}, 'id = %s', (user_id,))

            print("事务操作完成")

        # 2. 使用函数式事务
        def batch_operations(connection, user_data_list):
            results = []
            for user_data in user_data_list:
                result = mysql_db.insert('users', user_data, connection)
                results.append(result)
            return results

        user_data_list = [
            {'name': 'User1', 'email': 'user1@example.com', 'age': 25},
            {'name': 'User2', 'email': 'user2@example.com', 'age': 26},
            {'name': 'User3', 'email': 'user3@example.com', 'age': 27}
        ]

        result_ids = mysql_db.execute_in_transaction(batch_operations, user_data_list)
        print(f"批量插入结果: {result_ids}")

        # 3. 批量操作
        operations = [
            ('insert', ('users', {'name': 'Batch User 1', 'email': 'batch1@example.com', 'age': 28}), {}),
            ('insert', ('users', {'name': 'Batch User 2', 'email': 'batch2@example.com', 'age': 29}), {}),
        ]

        batch_results = mysql_db.batch_operations(operations)
        print(f"批量操作结果: {batch_results}")

    except Exception as e:
        print(f"事务操作失败: {e}")


def error_handling_examples():
    """错误处理示例"""
    print("\n=== 错误处理示例 ===")

    # 1. 处理连接错误
    try:
        # 尝试查询不存在的表
        mysql_db.select('non_existent_table')
    except MySQLQueryError as e:
        print(f"查询错误: {e.message}")
        print(f"原始错误: {e.original_error}")

    # 2. 处理数据验证错误
    try:
        # 尝试插入空数据
        mysql_db.insert('users', {})
    except ValueError as e:
        print(f"数据验证错误: {e}")


def batch_operations_examples():
    """批量操作示例"""
    print("\n=== 批量操作示例 ===")

    try:
        # 批量插入
        users_data = [
            {'name': 'Batch User A', 'email': 'a@example.com', 'age': 20},
            {'name': 'Batch User B', 'email': 'b@example.com', 'age': 21},
            {'name': 'Batch User C', 'email': 'c@example.com', 'age': 22},
        ]

        affected_rows = mysql_db.insert_many('users', users_data)
        print(f"批量插入了 {affected_rows} 条记录")

        # 批量执行自定义SQL
        sql = "UPDATE users SET age = age + 1 WHERE name LIKE %s"
        params_list = [('Batch User%',), ('Transaction%',)]

        total_affected = mysql_db.execute_many(sql, params_list)
        print(f"批量更新影响了 {total_affected} 条记录")

    except Exception as e:
        print(f"批量操作失败: {e}")


def connection_pool_examples():
    """连接池示例"""
    print("\n=== 连接池状态 ===")

    # 获取连接池状态
    status = mysql_db.pool.get_pool_status()
    print(f"连接池状态: {status}")


def main():
    """运行所有示例"""
    print("MySQL工具库使用示例")
    print("=" * 50)

    try:
        basic_crud_examples()
        advanced_query_examples()
        transaction_examples()
        batch_operations_examples()
        connection_pool_examples()
        error_handling_examples()

        print("\n" + "=" * 50)
        print("示例运行完成！")

    except Exception as e:
        print(f"示例运行出错: {e}")


if __name__ == '__main__':
    main()