1

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"
    }
]

2 Answers 2

2

Follow the psycopg docs. Plus, use json.dumps because dict conversion is not (yet) supported.

import json
from pathlib import Path

import psycopg2

if Path("data.json").exists():
    data = json.loads(Path("data.json").read_text())
else:
    data = [
        {
            "p_id": 10,
            "first_name": "Merry",
            "last_name": "Brandybuck",
            "p_attribute": {"Anything": "here"},
            "quote_content": "This, my friend, is a pint!",
        },
        {
            "p_id": 11,
            "first_name": "Pippin",
            "last_name": "Took",
            "p_attribute": {"Anything": "else"},
            "quote_content": "What about second breakfast?",
        },
    ]

with psycopg2.connect("") as conn:
    with conn.cursor() as cur:
        cur.execute(
            """ create table if not exists lotr_character(
            p_id integer, first_name text, last_name text,
            p_attribute jsonb, quote_content text) """
        )
        query_sql = """ insert into lotr_character
            select * from json_populate_recordset(NULL::lotr_character, %s) """
        cur.execute(query_sql, (json.dumps(data),))
1
  • Thank you for your answer, I eventually used json.dumps and figured it out. Commented Jun 3, 2020 at 6:15
0

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 (& 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()
2
  • I don't understand the necessity of the "," in the "cursor.execute(query_sql, (data,))" statement. Is that what you mean by wrapping it in a tuple? Commented Jun 3, 2020 at 6:19
  • Found it in the psycopg2 documentation: "For positional variables binding, the second argument must always be a sequence, even if it contains a single variable (remember that Python requires a comma to create a single element tuple)" psycopg.org/docs/usage.html#query-parameters Commented Jun 3, 2020 at 6:33

Not the answer you're looking for? Browse other questions tagged or ask your own question.