postgresql - 如何让 psycopg2 不发出引号？

Question

我想从 Pytnon 创建一个表:

import psycopg2  as pg
from psycopg2 import sql

conn = pg.connect("dbname=test user=test")
table_name = "testDB"
column_name = "mykey"
column_type = "bigint"
cu = conn.cursor()
cu.execute(sql.SQL("CREATE TABLE {t} ({c} {y})").format(
    t=sql.Identifier(table_name),
    c=sql.Identifier(column_name),
    y=sql.Literal(column_type)))

唉，这会发出 CREATE TABLE "testDB"("mykey"'bigint') 失败并显示

psycopg2.ProgrammingError: syntax error at or near "'bigint'"

当然，我可以做类似的事情

cu.execute(sql.SQL("CREATE TABLE {t} ({c} %s)" % (column_name)).format(
    t=sql.Identifier(table_name),
    c=sql.Identifier(column_name)))

但我怀疑有更优雅(和安全!)的解决方案。

附言。另见 How to make psycopg2 emit nested quotes?

Answer 1

the documentation中有一个例子如何构建带有占位符的查询文本。使用 psycopg2.extensions.AsIs(object)对于 column_type:

query = sql.SQL("CREATE TABLE {t} ({c} %s)").format(
    t=sql.Identifier(table_name),
    c=sql.Identifier(column_name)).as_string(cu)
    
cu.execute(query, [AsIs(column_type)])