Passing JSON file in Postgres with Python

Question

connection = psycopg2.connect("dbname=db1 user=postgres password=postgres")
cursor = connection.cursor()
cursor.execute("set search_path to public")

with open('json_template') as file:
    data = file.read()

query_sql = """
insert into table1 select * from
json_populate_recordset(NULL::table1, %s);
"""

# change .execute(query_sql) to .execute(query_sql, (data,))
cursor.execute(query_sql, (data,))
connection.commit()

So I am trying to pass the contents of "json_template.json" to a Postgres table, but when I do I get the this error: psycopg2.errors.InvalidParameterValue: cannot call json_populate_recordset on an object

I've created the table through psql cmd. Also, here are the contents of my json_template file:

{"key": "A123", "value": "15.6", "ts":"2020-10-07 13:28:43.399620+02:00"} Attaching screenshot: json_template.json.

Did some research on the error, however nothing came out. Also tried to rewrite the code in other fashion several times - still the same error.

Thank you in advance!

Answer 1

You got a JSON object, so you need to use json_populate_record instead of json_populate_recordset, which works for an array of objects.

import psycopg2

con = psycopg2.connect(...)
cursor = con.cursor()

with open('json_template') as file:
    data = file.read()

query_sql = """
insert into table1 select * from
json_populate_record(NULL::table1, %s);
"""

cursor.execute(query_sql, (data,))
con.commit()
cursor.execute('select * from table1')
print(cursor.fetchall())

Out:

[('A123', '15.6', '2020-10-07 13:28:43.399620+02:00')]

Answer 2

I just changed the JSON file from:

{"key": "A123", "value": "15.6", "ts":"2020-10-07 13:28:43.399620+02:00"}

To:

[
    {
        "key": "A128",
        "value": "15.6",
        "ts": "2020-10-07 13:28:43.399620+02:00"
    }
]

And it worked. Thank you all for the help!