Search code examples
pythonpostgresqlpsycopg2

Passing JSON file in Postgres with Python

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!

Solution

  • 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')]