使用python用外键插入Postgres表

Question

我正试图用python(psycopg2(将json中的数据插入到我的postgres表中。这个json是巨大的，现在我想知道插入所有这些数据的正确方法是什么。

让我感到困难的是，该表引用了另一个表。我想根据json中的一个值引用该表的id。

我的问题细节：

CREATE TABLE market.entities (
id serial PRIMARY KEY,
code TEXT NOT null,
name TEXT NOT NULL,
country TEXT NOT null,
exchange TEXT,
currency_code TEXT NOT null,
"type" TEXT,
isin TEXT,
api_source int REFERENCES market.source_api(id) NOT null,
imported_at date NOT null
);

其中该表实体已经填充了这样的数据：

id|code|name|country|exchange|currency_code|type|isin|api_source|imported_at
----------------------------------------------------------------------------
1|A|Agilent Technologies, Inc|USA|NYSE|USD|Common Stock|US00846U1016|1|2021-07-17
----------------------------------------------------------------------------

我想用json:中的数据填充的表

CREATE TABLE IF NOT EXISTS market.end_of_days(
id serial PRIMARY KEY,
entity_id int REFERENCES market.entities(id),
import_type int REFERENCES market.import_types(id),
source_api int REFERENCES market.source_api(id),
date date,
open int,
high int,
low int,
close int,
adjusted_close int,
volume int,
imported_at date,
UNIQUE (date, source_api, entity_id)
);

json:的一部分

[
{
"code": "ONTRF",
"exchange_short_name": "US",
"date": "2021-07-08",
"open": 0.1393,
"high": 0.1393,
"low": 0.1393,
"close": 0.1393,
"adjusted_close": 0.1393,
"volume": 0
},
{
"code": "ONTX",
"exchange_short_name": "US",
"date": "2021-07-08",
"open": 5.72,
"high": 6.19,
"low": 5.7,
"close": 6.07,
"adjusted_close": 6.07,
"volume": 324700
}
]

"；代码"；json中的键已经位于entities表中，因此在end_of_days.entity_id中，我想引用基于此"的entities表的id；代码"；价值

psycopg2有很好的解决方案吗？通常我只会使用psycopg2.extras.execute_values((，但我认为这在这种特定情况下不起作用。

(附言：这是我第一次在stackoverflow上发帖，所以如果我需要具体说明什么，或者用不同的方式构建我的问题，请告诉我。(

Answer 1

这不是一个真正的答案，而是一个可能的处理方法的建议。在弄清楚这一点的同时，为了测试的目的，将JSON数据分解成更小的块。想法：

创建一个临时表，直接将文件中的JSONINSERTjsonb字段。使用psycopg2的JSON功能。然后，您可以使用"表9.47中的JSON函数"中的JSON处理函数。JSON处理函数"修改数据并将数据移动到market.end_of_days

更新

我发现这样做更容易：

CREATE TABLE symbol_import
(code varchar, 
exchange_short_name varchar, 
"date" date, 
open numeric, 
high numeric, 
low numeric, 
close numeric, 
adjusted_close numeric, 
volume numeric);

import json
import psycopg2
from psycopg2.extras import execute_batch
json_val = """[
{
"code": "ONTRF",
"exchange_short_name": "US",
"date": "2021-07-08",
"open": 0.1393,
"high": 0.1393,
"low": 0.1393,
"close": 0.1393,
"adjusted_close": 0.1393,
"volume": 0
},
{
"code": "ONTX",
"exchange_short_name": "US",
"date": "2021-07-08",
"open": 5.72,
"high": 6.19,
"low": 5.7,
"close": 6.07,
"adjusted_close": 6.07,
"volume": 324700
}
]"""
data_dict = json.loads(json_val)
con = psycopg2.connect("...")
cur = con.cursor()
data_insert = "insert into symbol_import values(%(code)s, %(exchange_short_name)s, %(date)s, %(open)s, %(high)s, %(low)s, %(close)s, %(adjusted_close)s, %(volume)s)"
execute_batch(cur, data_insert, data_dict)
con.commit()