使用Psycopg2的sql模块构造查询执行时出现语法错误如何解决？

错误原因 #

• 最核心的错误是调用了Python原生字符串的format()方法，而非psycopg2.sql.SQL对象自带的format()方法。原生字符串格式化会直接将sql.Identifier实例转为Identifier('xxx')格式的普通字符串，而非合法的SQL标识符（表名、列名），最终生成的SQL语法完全不符合PostgreSQL规范，数据库无法识别。
• 次要问题是IN子句的参数传递逻辑有误：直接传列表作为单个参数时，Psycopg2会将其转为PostgreSQL数组格式，而IN子句不支持直接接数组参数，才会出现ARRAY相关的语法错误。

解决方法 #

第一步：修正SQL构造逻辑 #

调整format方法的调用顺序，使用sql.SQL实例的format方法处理标识符占位符，才会正确渲染表名和列名：

# 推荐使用 = ANY(%s) 写法，对单值/多值参数兼容性更强
query = sql.SQL("SELECT * FROM {t_name} WHERE {col} = ANY(%s);").format(
    t_name=sql.Identifier(table_name),
    col=sql.Identifier(fips_col)
)

此时构造出的SQL对象会被正确渲染为类似SELECT * FROM "cb_tracts" WHERE "st_cnty_fips" = ANY(%s);的合法格式。

第二步：调整参数传递方式 #

使用= ANY(%s)写法时，不管是单值还是多值，都直接把参数包装为列表/元组作为第二个参数传入即可：

# 单值查询
cursor.execute(query, (['06037'],))
# 多值查询
cursor.execute(query, (['06037', '06073'],))

如果坚持使用IN子句，需要把参数包装为两层元组：

query = sql.SQL("SELECT * FROM {t_name} WHERE {col} IN %s;").format(
    t_name=sql.Identifier(table_name),
    col=sql.Identifier(fips_col)
)
# 单值查询
cursor.execute(query, (('06037',),))
# 多值查询
cursor.execute(query, (('06037', '06073'),))

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