json data insertion problem in postgresql database with python

Question

main.py

data = []
with open('data.json') as f:
    for line in f:
        data.append(json.loads(line))
f.close()

fields = [
    'id', #integer
    'name', #varchar
    'log_date', #date
    'log_time', #timestamp
    'login', #timestamp
    'logout' #timestamp
]

for item in data:
    my_data = [item[field] for field in fields]
    insert_query = "INSERT INTO employee VALUES (%d, %s, %s, %s, %s, %s)"
    cur.execute(insert_query, tuple(my_data))

data.json

[
    {
        "id": 1,
        "name": "Prosenjit Das",
        "log_date": "2019-03-02",
        "log_time": "12:10:12.247257",
        "login": null,
        "logout": null
    },
    {
        "id": 2,
        "name": "Sudipto Rahman",
        "log_date": "2019-03-02",
        "log_time": "12:10:12.247257",
        "login": "11:26:45",
        "logout": "10:49:53"
    },
    {
        "id": 3,
        "name": "Trump Khatun",
        "log_date": "2019-03-02",
        "log_time": "12:10:12.247257",
        "login": null,
        "logout": null
    }
]

postgresql column fields

My database connect is okay. In that picture line 37 when i'm using dumps instead of loads then another problem is shown in line 50 that "Typeerror: string indices must be integers". Notice that here json format type is a list. This kinds of problem but not exactly i've seen many but properly doesn't work.

Thanks.

the json is invalid because you have a trailing comma after the last object in the array, remove that comma and try reading it in again, also don't read in the json file line by line, just read it in all at once — gold_cy
– gold_cy, Commented Mar 3, 2019 at 14:20
Following on from @aws_apprentice, whenever working with json files, I like to paste the contents into jsonformatter.curiousconcept.com to check that it is syntactically correct before running the program — user5407570
– user5407570, Commented Mar 3, 2019 at 14:22
@aws_apprentice Thanks. But this json data i'm getting from an API. So in this case can i remove comma after retrieve json data? — Prosenjit
– Prosenjit, Commented Mar 3, 2019 at 14:23
there is other problems here as well, specifically you need to specify which columns you are inserting given you are not uploading all the columns present in the table — gold_cy
– gold_cy, Commented Mar 3, 2019 at 14:23
@Prosenjit you'll have to modify it to conform to json standards otherwise it's not valid json — gold_cy
– gold_cy, Commented Mar 3, 2019 at 14:25

gold_cy · Accepted Answer · 2019-03-03 14:43:18Z

1

So couple of changes I would make here

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

# no need to do f.close() since we are using a context manager

fields = [
    'id', #integer
    'name', #varchar
    'log_date', #date
    'log_time', #timestamp
    'login', #timestamp
    'logout' #timestamp
]

for item in data:
    my_data = [item[field] for field in fields]
    insert_query = "INSERT INTO employee (id, name, log_date, log_time, login, logout) VALUES (%s, %s, %s, %s, %s, %s)"

    # also ALL placeholders must be %s even if it is an integer
    cur.execute(insert_query, tuple(my_data))

Also if you are using the psycopg2 module for your DB actions you can do the following

from psycopg2.extras import execute_values

my_data = [tuple(item[field] for field in fields) for item in data]
insert_query = "INSERT INTO employee (id, name, log_date, log_time, login, logout) VALUES %s"
execute_values(cursor, insert_query, my_data)

answered Mar 3, 2019 at 14:43

gold_cy

14.2k4 gold badges27 silver badges55 bronze badges

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

4 Comments

Prosenjit Over a year ago

Sorry i haven't find yet trailing comma standard solution

gold_cy Over a year ago

you said that your json was valid and there was no trailing comma

Prosenjit Over a year ago

yes that's showing valid. You can try that i've given above pastebin link my json data in that website that you gave me.

Prosenjit Over a year ago

pastebin.com/DkVcNbmc and jsonformatter.curiousconcept.com try please

balderman · Accepted Answer · 2019-03-03 14:27:52Z

0

Load the json into a list of dicts once + removing an extra comma

import json

with open('data.json', 'r') as f:
    data = json.load(f)
# now  you can iterate and push to entries to DB

data.json

[
    {
        "id": 1,
        "name": "Prosenjit Das",
        "log_date": "2019-03-02",
        "log_time": "12:10:12.247257",
        "login": null,
        "logout": null
    },
    {
        "id": 2,
        "name": "Sudipto Rahman",
        "log_date": "2019-03-02",
        "log_time": "12:10:12.247257",
        "login": "11:26:45",
        "logout": "10:49:53"
    },
    {
        "id": 3,
        "name": "Trump Khatun",
        "log_date": "2019-03-02",
        "log_time": "12:10:12.247257",
        "login": null,
        "logout": null
    }, --> was removed
]

answered Mar 3, 2019 at 14:27

balderman

24k8 gold badges39 silver badges60 bronze badges

3 Comments

Prosenjit Over a year ago

I've used that comma in the last but getting same output. jsonformatter.curiousconcept.com in this site i've tested my json data pastebin.com/DkVcNbmc and showing valid. what happened here actually which comma i've to move i can't understand. Just you can check out my json data is valid or not.

balderman Over a year ago

Json looks fine. Do you still face any issue?

Prosenjit Over a year ago

No. only i've faced that issue.

Collectives™ on Stack Overflow

json data insertion problem in postgresql database with python

main.py

data.json

postgresql column fields

2 Answers 2

4 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

main.py

data.json

postgresql column fields

2 Answers 2

4 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related