PostgreSQL:如何根据cas_number列表批量查询对应ID值
PostgreSQL根据CAS号列表查询对应ID的解决方案
你使用WHERE IN报错的核心原因:cas_number为字符串类型字段,IN子句内的每个CAS号必须用单引号包裹,否则数据库会将77-58-7这类值识别为数值运算表达式,触发语法错误。
基础查询方案(无需保留输入列表重复项、无需匹配输入顺序)
如果不需要保留输入列表中的重复CAS号、不要求返回顺序和输入顺序一致,可以使用修正后的WHERE IN写法:
-- 请将chemicals替换为你实际的表名 SELECT id, cas_number FROM chemicals WHERE cas_number IN ('77-58-7', '77-58-7', '12578-12-0');
该写法的执行结果会自动去重,输入列表中重复的77-58-7仅会返回1条匹配结果。
进阶查询方案(保留输入顺序、保留重复项、支持识别未匹配CAS号)
如果需要严格匹配输入列表的顺序、重复CAS号对应返回重复ID、同时识别出输入列表中不存在于表内的CAS号,推荐使用PostgreSQL的unnest函数搭配数组实现:
-- 请将chemicals替换为你实际的表名 SELECT t.id, input_cas FROM unnest(ARRAY['77-58-7', '77-58-7', '12578-12-0']::varchar[]) AS input_cas LEFT JOIN chemicals t ON t.cas_number = input_cas;
基于你提供的示例数据,该查询的返回结果如下:
| id | input_cas |
|---|---|
| 515 | 77-58-7 |
| 515 | 77-58-7 |
| 15 | 12578-12-0 |
脚本参数化查询示例(Python版)
如果在Python脚本中调用该查询,不要手动拼接SQL字符串,推荐使用参数化查询避免SQL注入风险,示例如下:
import psycopg2 # 待查询的CAS号列表 cas_list = ["77-58-7", "77-58-7", "12578-12-0"] # 数据库连接,替换为你的实际连接参数 conn = psycopg2.connect( dbname="你的库名", user="用户名", password="密码", host="地址", port="端口" ) cur = conn.cursor() # 执行参数化查询 cur.execute(""" SELECT t.id, input_cas FROM unnest(%s::varchar[]) AS input_cas LEFT JOIN chemicals t ON t.cas_number = input_cas; """, (cas_list,)) # 获取查询结果 results = cur.fetchall() print(results) # 输出:[(515, '77-58-7'), (515, '77-58-7'), (15, '12578-12-0')] cur.close() conn.close()
内容的提问来源于stack exchange,提问作者user16402418
相关产品推荐
相关产品推荐

