1 回答

TA贡献1804条经验 获得超7个赞
你的问题让我很好奇。下面这个对我有用。我怀疑是否可以解决转义到 CSV 或从 CSV 转义的问题。
我的表:
=# \d jbarray
Table "public.jbarray"
Column | Type | Collation | Nullable | Default
---------+---------+-----------+----------+-------------------------------------
id | integer | | not null | nextval('jbarray_id_seq'::regclass)
symbols | jsonb[] | | |
Indexes:
"jbarray_pkey" PRIMARY KEY, btree (id)
完全独立的 Python 代码:
mport json
import psycopg2
con = psycopg2.connect('dbname=<my database>')
some_objects = [{'id': x, 'array': [x, x+1, x+2, {'inside': x+3}]} for x in range(5)]
insert_array = [json.dumps(x) for x in some_objects]
print(insert_array)
c = con.cursor()
c.execute("insert into jbarray (symbols) values (%s::jsonb[])", (insert_array,))
con.commit()
结果:
=# select * from jbarray;
-[ RECORD 1 ]-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
id | 1
symbols | {"{\"id\": 0, \"array\": [0, 1, 2, {\"inside\": 3}]}","{\"id\": 1, \"array\": [1, 2, 3, {\"inside\": 4}]}","{\"id\": 2, \"array\": [2, 3, 4, {\"inside\": 5}]}","{\"id\": 3, \"array\": [3, 4, 5, {\"inside\": 6}]}","{\"id\": 4, \"array\": [4, 5, 6, {\"inside\": 7}]}"}
=# select id, unnest(symbols) from jbarray;
-[ RECORD 1 ]----------------------------------------
id | 1
unnest | {"id": 0, "array": [0, 1, 2, {"inside": 3}]}
-[ RECORD 2 ]----------------------------------------
id | 1
unnest | {"id": 1, "array": [1, 2, 3, {"inside": 4}]}
-[ RECORD 3 ]----------------------------------------
id | 1
unnest | {"id": 2, "array": [2, 3, 4, {"inside": 5}]}
-[ RECORD 4 ]----------------------------------------
id | 1
unnest | {"id": 3, "array": [3, 4, 5, {"inside": 6}]}
-[ RECORD 5 ]----------------------------------------
id | 1
unnest | {"id": 4, "array": [4, 5, 6, {"inside": 7}]}
如果插入性能对您来说太慢,那么您可以按照此处的说明prepared statement使用with 。我用过那个组合,速度非常快。execute_batch()
添加回答
举报