SQLite3如何向存储JSON数据的client_ids_list列追加客户ID值

现有代码问题 #

• 直接用新客户ID覆盖client_ids_list字段，没有保留历史客户ID数据
• 直接拼接SQL语句，存在SQL注入风险，同时容易触发语法异常

修正后的完整代码 #

import json

# 此处假设你已提前创建数据库连接对象conn、操作游标cursor
def create_client(company_id, username, password, bankaccount, address):
    # 插入Users表原有逻辑不变
    cursor.execute(''' 
        INSERT INTO Users(username,password,bankaccount,address)
        VALUES(?,?,?,?)''', (username, password, bankaccount, address))
    print("Customer account successfully created")
    client_id = int(cursor.lastrowid)
    print(client_id)
    # 插入Clients表原有逻辑不变
    cursor.execute('''
        INSERT INTO Clients(client_id)
        VALUES(?)''', (client_id,))
    print("Client id added to the Clients table")

    # 读取对应公司现有客户ID列表
    cursor.execute('SELECT client_ids_list FROM Companies WHERE company_id = ?', (company_id,))
    res = cursor.fetchone()
    client_list = []
    if res and res[0]:
        # 字段非空时解析原有JSON数组
        client_list = json.loads(res[0])
    # 追加新客户ID
    client_list.append(client_id)
    # 序列化后使用参数化查询更新字段，避免SQL注入
    cursor.execute(
        'UPDATE Companies SET client_ids_list = ? WHERE company_id = ?',
        (json.dumps(client_list), company_id)
    )
    # *注意：若使用SQLite、MySQLdb等需手动提交事务的数据库驱动，请取消注释下行，conn为你的数据库连接对象
    # conn.commit()
    print("Client added to the clients ids list")

关键说明 #

• 针对公司首次添加客户的场景，client_ids_list为NULL或空字符串时，代码会自动初始化空列表存储数据
• 全程使用参数化查询传递参数，完全规避SQL注入风险，也不会因特殊字符触发SQL语法错误
• 若你使用的数据库驱动需要手动提交事务，必须在更新操作后执行连接对象的commit()方法，否则修改不会持久化到数据库

内容的提问来源于stack exchange，提问作者Volen