代码之家 › 专栏 › 技术社区 › T.M

在PostgreSQL中使用executemany()在另一个表中插入FOREIGN KEY

executemany foreign-keys insert postgresql python

T.M · 技术社区 · 9 年前

我试图将语句表中代码列的行值作为公司表中的外键插入。我采取了以下步骤:

创建表格

cur.execute("CREATE TABLE IF NOT EXISTS companies (code INT NOT NULL PRIMARY KEY, short_name VARCHAR(255) NOT NULL, long_name VARCHAR(255) NOT NULL)")

cur.execute("CREATE TABLE IF NOT EXISTS statements (statement_id SERIAL NOT NULL PRIMARY KEY, statement_name VARCHAR(255) NOT NULL, code INT NOT NULL, FOREIGN KEY (code) REFERENCES companies_list (code))")

公司表中包含哪些代码列(即)

 code |
-----------
  113
  221
  344

下一步是将所需数据插入语句表,如下所示:

statement_name = ["balance_sheet", "income_statement", "cash_flow"]

code = "SELECT code FROM companies_list WHERE code IS NOT NULL"

statements = [tuple((t,)) for t in zip(statement_name, code)]

query = "INSERT INTO statements (statement_name, code) VALUES %s"
cur.executemany(query, statements)

我得到了以下错误:

psycopg2.DataError: invalid input syntax for integer: "S"
LINE 1: ...ents (statement_name, code) VALUES ('balance_sheet', 'S')

我想得到的最终结果如下:

statement_id |   statement_name    |   code
---------------------------------------------
     1           balance_sheet         113
     2           income_statement      113
     3           cash_flow             113
     4           balance_sheet         221
     5          income_statement       221
     6           cash_flow             221

1 回复 | 直到 9 年前

mhawke 9 年前

错误来自以下行:

code = "SELECT code FROM companies_list WHERE code IS NOT NULL"

这不会执行实际查询,而是将SQL select语句字符串分配给 code 变量下一行将语句名称压缩为 密码 这是因为 密码 是一个字符串(可迭代的),结果是 密码 与来自 statement_name 结果是:

[(('balance_sheet', 'S'),), (('income_statement', 'E'),), (('cash_flow', 'L'),)]

所以这就是 'S' 来自-它是中“SELECT”的第一个字符 密码 一串 “S” 是字符串,而不是架构中定义的整数 statements 表,因此出现错误。

您可以看到使用生成的查询 cursor.mogrify() :

>>> statement_name = ["balance_sheet", "income_statement", "cash_flow"]
>>> code = "SELECT code FROM companies_list WHERE code IS NOT NULL"
>>> statements = [tuple((t,)) for t in zip(statement_name, code)]
>>> query = "INSERT INTO statements (statement_name, code) VALUES %s"
>>> for args in statements:
...     print(cur.mogrify(query, args))
... 
INSERT INTO statements (statement_name, code) VALUES ('balance_sheet', 'S')
INSERT INTO statements (statement_name, code) VALUES ('income_statement', 'E')
INSERT INTO statements (statement_name, code) VALUES ('cash_flow', 'L')

解决此问题的一种方法是执行 密码 获取公司代码列表,然后使用该列表构造 INSERT 查询:

import itertools

cur.execute("SELECT code FROM companies_list WHERE code IS NOT NULL")
codes = [row[0] for row in cur.fetchall()]
query = 'INSERT INTO statements (statement_name, code) VALUES (%s, %s)'
args = itertools.product(statement_name, codes)
cur.executemany(query, args)

在这里 itertools.product() 用于形成语句名称和公司代码的笛卡尔积。这是在模仿数据库连接功能,所以如果数据库中有语句类型,那么最好使用SQL而不是Python。