Python原生DB-API执行聚合SQL时，须白名单校验表/字段名、值参数严格用params占位；GROUP BY字段必须完整覆盖非聚合列；CTE或窗口函数可省内存；落地用ON CONFLICT需确保目标列有唯一约束。
用 sqlite3 或 psycopg2 执行原生 SQL 聚合时，别直接拼字符串
Python 原生 DB-API（如 sqlite3、psycopg2）支持执行任意 SQL，但跨表 JOIN + GROUP BY + HAVING + 子查询一多，手拼字符串极易出错或被注入。尤其当表名、字段名来自配置或用户输入时，str.format() 或 % 拼接是危险的。
实操建议：
- 表名/字段名若需动态，先白名单校验（例如只允许匹配
r'^[a-zA-Z_][a-zA-Z0-9_]*$'），再用str.format()插入； - 值参数一律走
cursor.execute(sql, params)的params元组/字典，让驱动处理转义； - 避免在 SQL 中写
WHERE name = '{user_input}'这类硬拼——哪怕输入看似“可控”，也迟早翻车。
GROUP BY 后选字段必须在聚合函数里或出现在 GROUP BY 列表中
这是 SQL 标准行为，但 Python 里报错信息常不直观（比如 sqlite3.OperationalError: no such column: t2.status），实际是因没把 t2.status 放进 GROUP BY 或套上 MAX()/MIN()。
典型清洗场景：合并订单主表与明细表，按客户统计总金额、最大单笔、订单数、最近下单时间：
SELECT t1.customer_id, COUNT(*) AS order_count, SUM(t2.amount) AS total_amount, MAX(t2.amount) AS max_item, MAX(t1.created_at) AS last_order_at FROM orders t1 JOIN order_items t2 ON t1.id = t2.order_id GROUP BY t1.customer_id
注意：t1.customer_id 必须出现在 GROUP BY，而 t1.created_at 用了 MAX() 才合法。漏掉 GROUP BY t1.customer_id 会直接报错。
子查询或 CTE 在清洗链路中比 Pandas 更省内存
当数据量超 100 万行、且中间结果不适合全量加载到 Python 内存时，把清洗逻辑压进 SQL 比用 pandas.read_sql() 拿原始数据再 groupby 更可靠。
例如：剔除每个客户中金额最低的那条订单明细（保留其余），用窗口函数比在 Python 里分组排序再切片更稳：
WITH ranked AS (
SELECT *,
ROW_NUMBER() OVER (
PARTITION BY order_id
ORDER BY amount ASC
) AS rn
FROM order_items
)
SELECT * FROM ranked WHERE rn > 1
使用要点：
-
psycopg2和较新版本sqlite3（3.8.3+）都支持 CTE； - CTE 不是视图，不会物化，但可读性远高于嵌套子查询；
- 避免在 CTE 里写
SELECT *后续又只用几列——数据库仍要读全字段，IO 浪费明显。
ON CONFLICT（SQLite）或 ON CONFLICT DO UPDATE（PostgreSQL）是清洗后落地的关键
清洗完的数据常要回写目标表，但目标表可能已有部分数据。用 INSERT OR REPLACE 或 INSERT ... ON CONFLICT 比先 DELETE 再 INSERT 更安全，尤其并发写入时。
示例（PostgreSQL）：将清洗后的客户汇总写入 customer_summary，按 customer_id 冲突则更新：
INSERT INTO customer_summary (customer_id, total_amount, order_count) SELECT customer_id, SUM(amount), COUNT(*) FROM orders o JOIN order_items i ON o.id = i.order_id GROUP BY customer_id ON CONFLICT (customer_id) DO UPDATE SET total_amount = EXCLUDED.total_amount, order_count = EXCLUDED.order_count
注意点：
- SQLite 的
ON CONFLICT REPLACE是整行替换，不支持部分字段更新； -
EXCLUDED是 PostgreSQL 关键字，指代本次 INSERT 中冲突的那行值； - 目标表必须对
customer_id有唯一约束（UNIQUE或PRIMARY KEY），否则ON CONFLICT不触发。
跨表聚合清洗真正卡住人的，往往不是语法，而是 GROUP BY 字段一致性、CTE 的执行边界、以及落地时没设唯一约束导致 ON CONFLICT 失效——这三处多查一次 schema 就能避开大半问题。
Python免费学习笔记（深入）：立即使用
在学习笔记中，你将探索 Python 的核心概念和高级技巧！
