Using Python to insert JSON into PostgreSQL

by saltiest potato   Last Updated June 02, 2020 14:06 PM

I have the following table:

create table json_table (
    p_id int primary key,
    first_name varchar(20),
    last_name varchar(20),
    p_attribute json,
    quote_content text
)

Now I basically want to load a json object with the help of a python script and let the python script insert the json into the table. I have achieved inserting a JSON via psql, but its not really inserting a JSON-File, it's more of inserting a string equivalent to a JSON file and PostgreSQL just treats it as json.

What I've done with psql to achieve inserting JSON-File:

Reading the file and loading the contents into a variable

\set content type C:\PATH\data.json

Inserting the JSON using json_populate_recordset() and predefined variable 'content':

insert into json_table select * from json_populate_recordset(NULL:: json_table, :'content');

This works well but I want my python script to do the same. In the following Code, the connection is already established:

connection = psycopg2.connect(connection_string)
cursor = connection.cursor()
cursor.execute("set search_path to public")


with open('data.json') as file:
        data = json.load(file)

query_sql = """
insert into json_table select * from
json_populate_recordset(NULL::json_table, '{}');
""".format(data)

cursor.execute(query_sql)

I get the following error:

Traceback (most recent call last):
  File "C:/PATH", line 24, in <module>
    main()
  File "C:/PATH", line 20, in main
    cursor.execute(query_sql)
psycopg2.errors.SyntaxError: syntax error at or near "p_id"
LINE 3:     json_populate_recordset(NULL::json_table, '[{'p_id': 1, ...

If I paste the JSON content in pgAdmin4 and use the string inside json_populate_recordset() it works. I assume im handling the JSON file wrong...

My data.json looks like this:

[
    {
        "p_id": 1,
        "first_name": "Jane",
        "last_name": "Doe",
        "p_attribute": {
            "age": "37",
            "hair_color": "blue",
            "profession": "example",
            "favourite_quote": "I am the classic example"
        },
        "quote_content": "'am':2 'classic':4 'example':5 'i':1 'the':3"
    },
    {
        "p_id": 2,
        "first_name": "Gordon",
        "last_name": "Ramsay",
        "p_attribute": {
            "age": "53",
            "hair_color": "blonde",
            "profession": "chef",
            "favourite_quote": "Where is the lamb sauce?!"
        },
        "quote_content": "'is':2 'lamb':4 'sauce':5 'the':3 'where':1"
    }
]


Related Questions


Query against JSON array in PostgreSQL

Updated September 28, 2016 09:02 AM

Insert data from .json file - psycopg2 complaining

Updated February 02, 2018 16:06 PM



PostgreSQL/Psycopg2 upsert syntax to update columns

Updated March 20, 2017 08:06 AM