我需要将数据从json文件复制到Postgress

Question

我需要将数据从json文件复制到Postgresql数据库。我有一个json文件，其中有9000个用户及其信息，如下所示:

"name": "Kathryn", "time_created": 1665335716, "gender": "female", "age": 38, "last_name": "Smith", "ip": "192.168.0.110", "city": "NY", "premium": null,  "birth_day": "01.01", "balance": 55.43255500944704, "user_id": 8676}

我需要从这个文件复制数据到Postgresql。我如何通过sql或python做到这一点。Postgresql数据库在本地docker组合容器

Answer 1

@George Rybojchuk

check complete SQL:

drop table if exists sample_json;
drop table if exists target;
create table sample_json(record json not null);
create table target(name varchar(20),time_created varchar(20));
insert into sample_json(record) values('{"name": "Kathryn", "time_created": 1665335716, "gender": "female", "age": 38, "last_name": "Smith", "ip": "192.168.0.110", "city": "NY", "premium": null,  "birth_day": "01.01", "balance": 55.43255500944704, "user_id": 8676}');
select * from sample_json;
insert into target(name,time_created)
select record->'name',record->'time_created'
from sample_json;

python方法:

import json
if __name__ == '__main__':
records = [
'{"name": "Kathryn", "time_created": 1665335716, "gender": "female", "age": 38, "last_name": "Smith", "ip": "192.168.0.110", "city": "NY", "premium": null,  "birth_day": "01.01", "balance": 55.43255500944704, "user_id": 8676}',
'{"name": "Kathryn1", "time_created": 1665335716, "gender": "female", "age": 38, "last_name": "Smith", "ip": "192.168.0.110", "city": "NY", "premium": null,  "birth_day": "01.01", "balance": 55.43255500944704, "user_id": 8676}',
'{"name": "Kathryn2", "time_created": 1665335716, "gender": "male", "age": 38, "last_name": "Smith", "ip": "192.168.0.110", "city": "NY", "premium": null,  "birth_day": "01.01", "balance": 55.43255500944704, "user_id": 8676}',
]
for record in records:
json_data = json.loads(record)
name = json_data['name']
gender = json_data['gender']
print(name, gender)
# connect postgresql
# insert into target table