python - psycopg2 从带有占位符的元组设置主键

Question

如何在 psycopg2 中根据从元组循环的值元组设置主键？

例如我有我的元组

meetattrs = ('id', 'venue', 'date', 'rail', 'weather', 'trackcondition')

然后我想创建表并提取和插入值。从 meetattrs 中，我希望将 id 设置为主键。

conn = psycopg2.connect("")
with conn, conn.cursor() as cur:
        # First, create tables.
    cur.execute("drop table if exists meetings, races, horses")
    cur.execute("create table meetings (id integer PRIMARY KEY, " +
                ", ".join("%s varchar" % fld for fld in meetattrs)
                + ")")

但这会产生此错误，不确定如何解决。

Traceback (most recent call last):
  File "racemeeting.py", line 56, in <module>
    + ")")
psycopg2.ProgrammingError: column "id" specified more than once

Answer 1

执行join并且占位符取值后当前的sql查询是:

'create table meetings (id integer PRIMARY KEY, id varchar, venue varchar, date varchar, rail varchar, weather varchar, trackcondition varchar)'

id 指定了两次。

您可以以更简洁的方式构建查询:

query_params = " %s integer PRIMARY KEY" + ", %s varchar" * (len(meetattrs) - 1)

你的 SQL 语句变成:

"create table meetings (" + query_params % meetattrs + ")"

插入占位符值后:

'create table meetings (id integer PRIMARY KEY, venue varchar, date varchar, rail varchar, weather varchar, trackcondition varchar)'

确保您的元组始终将其第一项作为 id