如何按SQL表列值筛选行并按internal_id分组存入列表？

问题说明 #

现有MySQL表结构（简化后）：

internal_id   start_location end_location fleet_name Cars_n     departure_time 
832            value           value      XXX          [1,73,3]   10:15:17
832            value           value      XXX          [10,43,3]  13:15:17
83             value           value      XXX          [16,43,32] 15:15:17
85             value           value      XXX          [1,43,3]   12:15:17

需求：将数据按internal_id分组，每个ID对应其所有行程记录的列表，最终生成{ "internal_id值": [对应所有行], ... }的结构，并整合为internal_ids = [所有分组列表]的形式。

你原本的思路存在两个问题：一是需要先获取所有唯一ID才能逐个查询，效率低下；二是直接拼接SQL语句存在严重的SQL注入风险，必须避免。

解决方案 #

方法1：一次性查询+Python端分组（推荐） #

只需要一次数据库查询，在Python中完成分组，效率更高且更安全：

import json
import mysql.connector

# 假设已建立数据库连接db
cursor = db.cursor(dictionary=True)  # 返回字典格式的行数据，便于处理
cursor.execute("SELECT * FROM your_table_name;")
all_rows = cursor.fetchall()

# 按internal_id分组数据
grouped_data = {}
for row in all_rows:
    id_str = str(row["internal_id"])  # 转字符串作为JSON键（JSON键仅支持字符串）
    if id_str not in grouped_data:
        grouped_data[id_str] = []
    grouped_data[id_str].append(row)

# 生成你需要的internal_ids结构
internal_ids = list(grouped_data.values())

# 如需输出JSON字符串
json_result = json.dumps(grouped_data, indent=4)
print(json_result)

方法2：先获取唯一ID再逐个查询（不推荐） #

如果必须逐个查询每个ID，务必使用参数化查询避免注入：

import mysql.connector

cursor = db.cursor(dictionary=True)

# 先获取所有唯一的internal_id
cursor.execute("SELECT DISTINCT internal_id FROM your_table_name;")
unique_ids = [str(row["internal_id"]) for row in cursor.fetchall()]

grouped_data = {}
for internal_id in unique_ids:
    # 参数化查询，用%s作为占位符，避免SQL注入
    cursor.execute("SELECT * FROM your_table_name WHERE internal_id = %s;", (internal_id,))
    grouped_data[internal_id] = cursor.fetchall()

internal_ids = list(grouped_data.values())

关键注意事项 #

• 绝对禁止字符串拼接SQL：直接拼接internal_id会导致SQL注入漏洞，必须使用参数化查询（%s占位符）。
• 使用字典游标：dictionary=True让返回的每行数据包含列名，比元组格式更易生成可读的JSON。
• JSON键类型：将internal_id转为字符串，因为JSON的键只能是字符串类型。

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