Using Python to insert JSON into PostgreSQL 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 &lt;module&gt;
    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"
    }
]
There's no need to have python parse the json; just pass through the file contents, retrieved with .read(), to cursor.execute() as a parameter (&amp; make sure to wrap in in a tuple, as shown below). And don't forget connection.commit() or you'll never actually see the inserted data as it'll be rolled back when the connection closes after python finishes running.

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


with open('data.json') as file:
    # change json.load(file) to file.read()
    data = file.read()

# Just put a placeholder %s instead of using {} and .format().
query_sql = """
insert into json_table select * from
json_populate_recordset(NULL::json_table, %s);
"""

# change .execute(query_sql) to .execute(query_sql, (data,))
cursor.execute(query_sql, (data,))
# Add a commit on the connection.
connection.commit()