# test_usage.py - 测试SQLite通用模块的使用
from sqliteModule.sqlite_db import g_dbInst  # 导入封装好的全局实例
'''
def test_all_operations():
    # 1. 创建数据表（带IF NOT EXISTS，重复执行不报错）
    create_sql = """
    CREATE TABLE IF NOT EXISTS user (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        username TEXT NOT NULL UNIQUE,
        age INTEGER DEFAULT 0,
        create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )
    """
    db.create_table(create_sql)

    # 2. 单条插入数据（参数化，防注入）
    insert_sql = 'INSERT INTO user (username, age) VALUES (?, ?)'
    db.execute_sql(insert_sql, ("zhangsan", 20))
    db.execute_sql(insert_sql, ("lisi", 25))

    # 3. 批量插入数据（效率更高，适合大量数据）
    batch_params = [("wangwu", 18), ("zhaoliu", 30), ("sunqi", 22)]
    db.batch_execute(insert_sql, batch_params)

    # 4. 更新数据（按条件更新，避免全表修改）
    update_sql = "UPDATE user SET age = ? WHERE username = ?"
    db.execute_sql(update_sql, (26, "lisi"))

    # 5. 删除数据（按条件删除）
    delete_sql = "DELETE FROM user WHERE username = ?"
    db.execute_sql(delete_sql, ("zhaoliu",))

    # 6. 查询单条数据（支持按列名取值，因设置了row_factory）
    one_user = db.fetch_one("SELECT * FROM user WHERE username = ?", ("zhangsan",))
    if one_user:
        print("【单条查询】ID：{}，用户名：{}，年龄：{}".format(
            one_user["id"], one_user["username"], one_user["age"]
            # 也可按索引：one_user[0], one_user[1], one_user[2]
        ))

    # 7. 查询所有数据（遍历结果）
    all_users = db.fetch_all("SELECT * FROM user ORDER BY id")
    print("\n【所有数据查询】共{}条记录：".format(len(all_users)))
    for user in all_users:
        print(f"ID：{user['id']}，用户名：{user['username']}，年龄：{user['age']}")

    # 8. 查询单个值（如统计总记录数、最大年龄）
    total = db.fetch_scalar("SELECT COUNT(*) FROM user")
    max_age = db.fetch_scalar("SELECT MAX(age) FROM user")
    print(f"\n【单个值查询】总记录数：{total}，最大年龄：{max_age}")

    # 9. 条件查询（带参数）
    young_users = db.fetch_all("SELECT * FROM user WHERE age < ?", (25,))
    print("\n【条件查询】年龄小于25的用户：")
    for user in young_users:
        print(f"用户名：{user['username']}，年龄：{user['age']}")
'''

if __name__ == "__main__":
    # 6. 查询单条数据（支持按列名取值，因设置了row_factory）
    one_user = g_dbInst.fetch_one("SELECT * FROM t_user WHERE f_user_id = ?", ("adm_001",))
    if one_user:
        print("【单条查询】ID：{}，用户type：{}，date：{}".format(
            one_user[0], one_user[1], one_user[3]
            # 也可按索引：one_user[0], one_user[1], one_user[3]
        ))