使用psycopg执行PostgreSQL建表语句时遇语法错误求助

尝试用Psycopg3通过参数字典执行PostgreSQL的CREATE TABLE语句时，遇到两种语法错误，代码如下：

#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
Created on Fri Nov  1 09:32:32 2024

@author: me
"""

import psycopg

pg_uri = "postgres://postgres:******@localhost:5432/mydatabase"
conn_dict = psycopg.conninfo.conninfo_to_dict(pg_uri)
conn = psycopg.connect(**conn_dict)
curs = conn.cursor()

d = {"CRS": 4326}
raw = "CREATE TABLE foo (id INT, geom geometry('Point',%(CRS)s));"

query0 = psycopg.sql.SQL(raw).format(**d)
query1 = psycopg.sql.SQL(raw.format(**d))
query2 = psycopg.sql.SQL(raw)

curs.execute(query0) # SyntaxError: syntax error at or near "%"
curs.execute(query1) # SyntaxError: syntax error at or near "%"
curs.execute(query2,d) # SyntaxError: type modifiers must be simple constants or identifiers

触发的错误 #

• 执行query0和query1时：SyntaxError: syntax error at or near "%"
• 执行query2时：SyntaxError: type modifiers must be simple constants or identifiers

版本信息 #

• Python: 3.10
• Psycopg: 3.2.3
• PostgreSQL: 16

解决方案 #

PostgreSQL不允许用参数绑定替换DDL语句中的类型修饰符（这类属于SQL语法结构，不是查询参数），必须用Psycopg的sql模块安全拼接SQL语句，避免直接字符串格式化带来的注入风险。

修改后的代码：

import psycopg
from psycopg import sql

pg_uri = "postgres://postgres:******@localhost:5432/mydatabase"
conn_dict = psycopg.conninfo.conninfo_to_dict(pg_uri)
conn = psycopg.connect(**conn_dict)
curs = conn.cursor()

d = {"CRS": 4326}
# 使用sql.SQL定义模板，用sql.Literal包装CRS值安全注入
create_query = sql.SQL("CREATE TABLE foo (id INT, geom geometry('Point', {}));").format(sql.Literal(d["CRS"]))

curs.execute(create_query)
conn.commit()  # DDL语句需要提交生效
conn.close()

错误原因说明 #

1. query0/query1报错：psycopg.sql.SQL.format()要求传入sql模块的对象（如sql.Literal/sql.Identifier），直接传入普通字典值无法解析%(CRS)s占位符，导致SQL语句保留了%符号，触发语法错误。
2. query2报错：cursor.execute()的参数绑定仅适用于查询中的数据值（如SELECT/INSERT中的条件或值），而geometry的SRID属于类型修饰符，属于SQL语法结构部分，PostgreSQL不允许用参数替换这类常量，因此报错。

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