import sqlite3
conn = sqlite3.connect('database.db')
cu = conn.cursor()
cu.execute("select id, username, email from user")
result = cu.fetchall()
print(result)
# 輸出: [(1, 'alice', 'alice@example.com'), (2, 'bob', 'bob@example.com')]
# 只有值，沒有字段名

import sqlite3
from flask import jsonify
def userSearch():
    # 連接數(shù)據(jù)庫
    conn = handleDbConnection()
    # 關(guān)鍵步驟1: 設(shè)置 row_factory
    conn.row_factory = sqlite3.Row
    # 創(chuàng)建游標(biāo)并執(zhí)行查詢
    cu = conn.cursor()
    cu.execute("select * from user")
    # 獲取查詢結(jié)果
    listAll = cu.fetchall()
    # 關(guān)鍵步驟2: 將 Row 對(duì)象轉(zhuǎn)換為字典
    listAll = [dict(row) for row in listAll]
    # 現(xiàn)在 listAll 是字典列表
    # [{'id': 1, 'username': 'alice', 'email': 'alice@example.com'}, ...]
    return jsonify({
        "code": 200,
        "data": {
            "list": listAll,
            "total": len(listAll)
        }
    })

row = cu.fetchone()
# 可以通過索引訪問
print(row[0])  # 第一列的值
# 可以通過列名訪問
print(row['username'])  # username 字段的值
# 可以轉(zhuǎn)換為字典
print(dict(row))  # {'id': 1, 'username': 'alice', ...}

[dict(row) for row in listAll]

[表達(dá)式 for 變量 in 可迭代對(duì)象]

result = []
for row in listAll:
    result.append(dict(row))
listAll = result

# 轉(zhuǎn)換前（Row 對(duì)象列表）
listAll = [
    <sqlite3.Row object at 0x...>,
    <sqlite3.Row object at 0x...>
]
# 轉(zhuǎn)換后（字典列表）
listAll = [
    {'id': 1, 'username': 'alice', 'email': 'alice@example.com'},
    {'id': 2, 'username': 'bob', 'email': 'bob@example.com'}
]

# 示例1: 生成平方數(shù)列表
numbers = [1, 2, 3, 4, 5]
squares = [x**2 for x in numbers]
print(squares)  # [1, 4, 9, 16, 25]
# 示例2: 轉(zhuǎn)換為大寫
names = ['alice', 'bob', 'charlie']
upper_names = [name.upper() for name in names]
print(upper_names)  # ['ALICE', 'BOB', 'CHARLIE']
# 示例3: 提取字典的某個(gè)字段
users = [
    {'name': 'alice', 'age': 25},
    {'name': 'bob', 'age': 30}
]
names = [user['name'] for user in users]
print(names)  # ['alice', 'bob']

# 語法：[表達(dá)式 for 變量 in 可迭代對(duì)象 if 條件]
# 示例: 只轉(zhuǎn)換偶數(shù)
numbers = [1, 2, 3, 4, 5, 6]
even_squares = [x**2 for x in numbers if x % 2 == 0]
print(even_squares)  # [4, 16, 36]
# 在 SQLite 場景中的應(yīng)用
listAll = [dict(row) for row in listAll if row['age'] > 18]
# 只轉(zhuǎn)換年齡大于18的記錄

# 示例: 扁平化二維列表
matrix = [[1, 2], [3, 4], [5, 6]]
flat = [num for row in matrix for num in row]
print(flat)  # [1, 2, 3, 4, 5, 6]

# 錯(cuò)誤示例
listAll = cu.fetchall()  # Row 對(duì)象列表
return jsonify({"data": listAll})
# TypeError: Object of type Row is not JSON serializable
# 正確示例
listAll = [dict(row) for row in cu.fetchall()]
return jsonify({"data": listAll})
# 成功序列化為 JSON

# 使用字典
user = {'id': 1, 'username': 'alice', 'email': 'alice@example.com'}
print(user['username'])  # 清晰明了
# 使用 tuple（默認(rèn)）
user = (1, 'alice', 'alice@example.com')
print(user[1])  # 需要記住索引位置，容易出錯(cuò)

import sqlite3
from typing import List, Dict, Any
def query_to_dict(conn: sqlite3.Connection, sql: str, params: tuple = ()) -> List[Dict[str, Any]]:
    """
    執(zhí)行 SQL 查詢并返回字典列表
    Args:
        conn: 數(shù)據(jù)庫連接對(duì)象
        sql: SQL 查詢語句
        params: 查詢參數(shù)（可選）
    Returns:
        字典列表，每個(gè)字典代表一行數(shù)據(jù)
    """
    # 設(shè)置 row_factory
    conn.row_factory = sqlite3.Row
    cu = conn.cursor()
    # 執(zhí)行查詢
    cu.execute(sql, params)
    # 轉(zhuǎn)換為字典列表
    result = [dict(row) for row in cu.fetchall()]
    # 關(guān)閉游標(biāo)
    cu.close()
    return result
# 使用示例
conn = sqlite3.connect('database.db')
users = query_to_dict(conn, "SELECT * FROM user WHERE age > ?", (18,))
print(users)
# [{'id': 1, 'username': 'alice', 'age': 25}, ...]

def userSearch(pageNum: int = 1, pageSize: int = 10) -> dict:
    """
    用戶查詢（帶分頁）
    Args:
        pageNum: 頁碼（從1開始）
        pageSize: 每頁數(shù)量
    Returns:
        包含列表和分頁信息的字典
    """
    conn = handleDbConnection()
    conn.row_factory = sqlite3.Row
    cu = conn.cursor()
    # 查詢總數(shù)
    cu.execute("SELECT COUNT(*) as total FROM user")
    total = cu.fetchone()['total']
    # 計(jì)算偏移量
    offset = (pageNum - 1) * pageSize
    # 查詢分頁數(shù)據(jù)
    cu.execute("SELECT * FROM user LIMIT ? OFFSET ?", (pageSize, offset))
    listAll = [dict(row) for row in cu.fetchall()]
    cu.close()
    conn.close()
    return {
        "code": 200,
        "data": {
            "list": listAll,
            "total": total,
            "pageNum": pageNum,
            "pageSize": pageSize,
            "totalPages": (total + pageSize - 1) // pageSize
        },
        "message": "成功"
    }

import time
# 方法1: 使用列表推導(dǎo)式（推薦）
start = time.time()
result1 = [dict(row) for row in listAll]
print(f"列表推導(dǎo)式耗時(shí): {time.time() - start:.4f}秒")
# 方法2: 使用 map 函數(shù)
start = time.time()
result2 = list(map(dict, listAll))
print(f"map 函數(shù)耗時(shí): {time.time() - start:.4f}秒")
# 方法3: 使用傳統(tǒng)循環(huán)
start = time.time()
result3 = []
for row in listAll:
    result3.append(dict(row))
print(f"傳統(tǒng)循環(huán)耗時(shí): {time.time() - start:.4f}秒")

# 轉(zhuǎn)換時(shí)修改字段名
listAll = [
    {
        'userId': row['id'],
        'userName': row['username'],
        'userEmail': row['email']
    }
    for row in cu.fetchall()
]

# 添加額外的計(jì)算字段
listAll = [
    {
        **dict(row),  # 展開原始字段
        'fullName': f"{row['first_name']} {row['last_name']}",
        'isAdmin': row['role'] == 'admin'
    }
    for row in cu.fetchall()
]

# 只保留非空字段
listAll = [
    {k: v for k, v in dict(row).items() if v is not None}
    for row in cu.fetchall()
]

# 錯(cuò)誤
conn = sqlite3.connect('db.sqlite')
cu = conn.cursor()
cu.execute("SELECT * FROM user")
listAll = [dict(row) for row in cu.fetchall()]
# TypeError: cannot convert dictionary update sequence element #0 to a sequence

# 正確
conn.row_factory = sqlite3.Row  # 必須添加這一行

# 錯(cuò)誤：在 cursor 之后設(shè)置
cu = conn.cursor()
conn.row_factory = sqlite3.Row  # 太晚了！

# 正確：在 cursor 之前設(shè)置
conn.row_factory = sqlite3.Row
cu = conn.cursor()

# 錯(cuò)誤
return jsonify({"data": cu.fetchall()})
# TypeError: Object of type Row is not JSON serializable

# 正確
return jsonify({"data": [dict(row) for row in cu.fetchall()]})