How to insert JSONB into Postgresql with Python?

user_5910050 0
Viewed 14369

I am new to python and postgresql

I've been battling just hardcoding each json line with python and I don't think this the scalable method. If someone can point me to the literature or documentation that can handle json insertion from python without hardcoding.

I've looked into COPY.

jsonb psycopg2 postgresql python json
2 Answers
import json

data = [1, [2,3], {'a': [4,5]}]
my_json = json.dumps(data)
insert_query = "insert into t (j) values (%s) returning j"
cursor.execute(insert_query, (my_json,))
print (cursor.fetchone()[0])

Output:

[1, [2, 3], {'a': [4, 5]}]
user_1318740

With dataset + psycopg2

import dataset

def construct_pg_url(postgres_user='postgres', postgres_password='', postgres_host='localhost', postgres_port='5432', postgres_database='postgres'):
    PG_URL = "postgresql://" + postgres_user + ":" + postgres_password + '@' + postgres_host + ':' + postgres_port + '/' + postgres_database
    return PG_URL

pgconn = dataset.Database(
             url=construct_pg_url(),
             schema='my_schema'
)
table = pgconn['my_table']
rows = [dict(foo='bar', baz='foo')] * 10000
table.insert_many(rows)
user_86589610
Related