Send nested JSON data to Postgres with Python - can´t find a way to insert null values on a table with psycopg2

Question

I'm new to Python, I'm still learning the concepts and a question has come up. I have the following piece of JSON file:

{
    "Id": 1,
    "SubGroup": [
        {
            "Id": 1234,
            "SubGroup": [],
            "Name": "Out of Service"
        },
        {
            "Id": 5678,
            "SubGroup": [],
            "Name": "Some Name"
        },
        {
            "Id": 91011,
            "SubGroup": [
                {
                    "Id": 9101101,
                    "SubGroup": [],
                    "Name": "Other Name_1"
                },
                {
                    "Id": 9101102,
                    "SubGroup": [],
                    "Name": "Other Name_2"
                }
            ],
            "Name": "Company Subsidiary"
        }
    ],
    "Name": "Company Name"
}

I´m using the following python code to try to plot all values (including null values) in a table:

    import psycopg2
    import json

    with open("./subgroups.json") as arq_api:
        read_data = json.load(arq_api)

    data_groupid = []
    def get_data():
        list_1 = read_data['SubGroup']
        for dic in list_1:
            group_id = dic.get('Id')
            group_name = dic.get('Name')

        for a in dic['SubGroup']:
            if dic['SubGroup'] == []:
                subgrop_id = None
                subgrop_name = None
            else:   
                subgrop_id = a['Id']
                subgrop_name = a['Name']

            data_groupid.append([
                group_id,
                group_name,
                subgrop_id,
                subgrop_name
                ])
    get_data()

    conn = psycopg2.connect(
        "host=myhost dbname=mydb user=myuser password=mypass")
    cur = conn.cursor()
    cur.execute('TRUNCATE TABLE subgroups')


    def post_gre():
        for item in data_groupid:
            my_data = tuple(item)
            cur.execute(
                'INSERT INTO subgroups VALUES (%s,%s,%s,%s)', my_data)
    post_gre()

    conn.commit()
    conn.close()

With this code, I am generating the following table:

Table from python code - without null

But I need the table to be like this image below:

Table with null

Thank you for any help!

Hi, passing None as the value of the NULL column should work. — IronMan
– IronMan, Commented Aug 2, 2019 at 0:19
Hi!I tried with subgrop_id = None and subgrop_name = None but it didn't work! — PhillyCool
– PhillyCool, Commented Aug 2, 2019 at 0:23
Focus in on this line if dic['SubGroup'] == [] Comparison in Python is not what you expect it to be here. This answer already covers it. — Chase
– Chase, Commented Aug 2, 2019 at 0:30
Do not edit "SOLVED" into the title here; that's a common practice on some forums, but Stack Overflow is not a forum. Instead, click the check box by the answer that solved your question. If you solved it a different way, click the "Add an Answer" button, add your own answer, and after a delay you'll be able to click the checkbox for that to mark it as a correct solution. — Charles Duffy
– Charles Duffy, Commented Aug 4, 2019 at 21:38

pnadolny · Accepted Answer · 2019-08-02 00:52:09Z

2

The issue is not with the way that you're passing data to psycopg but rather with your logic. You miss the first two entries in the top level subgroup.

Try this:

def get_data():
list_1 = read_data['SubGroup']
for dic in list_1:
    group_id = dic.get('Id')
    group_name = dic.get('Name')
    subgroup = dic.get('SubGroup')
    if len(subgroup) == 0:
        data_groupid.append([
            group_id,
            group_name,
            None,
            None
            ])
    else:
        for sub_dict in subgroup:
            subgrop_id = sub_dict['Id']
            subgrop_name = sub_dict['Name']
            data_groupid.append([
                    group_id,
                    group_name,
                    subgrop_id,
                    subgrop_name
                    ])

That should give you the correct output based on your images.

answered Aug 2, 2019 at 0:52

pnadolny

1231 silver badge9 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

PhillyCool Over a year ago

Thank you so much @pnadolny, it worked! Thank you for writing the answer clearly and for taking the time to help me.

Collectives™ on Stack Overflow

Send nested JSON data to Postgres with Python - can´t find a way to insert null values on a table with psycopg2

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related