What Is The Proper Formatting For A Jsonb Array In Python For Postgres?

I have a schema that looks like Column | Type | ------------------------------------------------------- message_id | integer

Solution 1:

Your question made me curious. This below works for me. I have doubts whether the escaping going to/from CSV can be resolved.

My table:

=# \d jbarray
                             Table "public.jbarray"
 Column|  Type   |Collation| Nullable |Default---------+---------+-----------+----------+-------------------------------------
 id      |integer||notnull| nextval('jbarray_id_seq'::regclass)
 symbols | jsonb[] |||
Indexes:
    "jbarray_pkey" PRIMARY KEY, btree (id)

Completely self-contained Python code:

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()

Result:

=# select * from jbarray;
-[ RECORD1 ]-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
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;
-[ RECORD1 ]----------------------------------------
id     |1
unnest | {"id": 0, "array": [0, 1, 2, {"inside": 3}]}
-[ RECORD2 ]----------------------------------------
id     |1
unnest | {"id": 1, "array": [1, 2, 3, {"inside": 4}]}
-[ RECORD3 ]----------------------------------------
id     |1
unnest | {"id": 2, "array": [2, 3, 4, {"inside": 5}]}
-[ RECORD4 ]----------------------------------------
id     |1
unnest | {"id": 3, "array": [3, 4, 5, {"inside": 6}]}
-[ RECORD5 ]----------------------------------------
id     |1
unnest | {"id": 4, "array": [4, 5, 6, {"inside": 7}]}

If the insert performance is too slow for you, then you can use a prepared statement with execute_batch()as documented here. I have used that combination, and it was very fast.