Output JSON file from Python. Data in JSON file messed up

Question

I wrote some code that outputs a JSON file from a SQL server but the JSON file is very messed up. It repeats some of the data and is very confusing:

JSON Output File:

[["Name: ", "Trenton", "Age: ", "16", "Losses: ", "0"]][["Name: ", "Trenton", "Age: ", "16", "Losses: ", "0"], ["Name: ", "Max", "Age: ", "15", "Losses: ", "0"]][["Name: ", "Trenton", "Age: ", "16", "Losses: ", "0"], ["Name: ", "Max", "Age: ", "15", "Losses: ", "0"], ["Name: ", "Corbin", "Age: ", "16", "Losses: ", "0"]][["Name: ", "Trenton", "Age: ", "16", "Losses: ", "0"], ["Name: ", "Max", "Age: ", "15", "Losses: ", "0"], ["Name: ", "Corbin", "Age: ", "16", "Losses: ", "0"], ["Name: ", "TJ", "Age: ", "17", "Losses: ", "0"]][["Name: ", "Trenton", "Age: ", "16", "Losses: ", "0"], ["Name: ", "Max", "Age: ", "15", "Losses: ", "0"], ["Name: ", "Corbin", "Age: ", "16", "Losses: ", "0"], ["Name: ", "TJ", "Age: ", "17", "Losses: ", "0"], ["Name: ", "Xander", "Age: ", "16", "Losses: ", "0"]]

Code:

import psycopg2
import json

try:
    connection = psycopg2.connect(user="postgres", password="password", host="192.168.1.88", port="5432", database="losers")
    cursor = connection.cursor()
    postgreSQL_select_Query = "select * from people"

    cursor.execute(postgreSQL_select_Query)
    loss_records = cursor.fetchall()

    lossarray_list = []

    for row in loss_records:
        t = (row[0], row[1], row.[2])
        lossarray_list.append(t)
        with open('data.json', 'a') as out:
            json.dump(lossarray_list, out)


except (Exception, psycopg2.Error) as error:
    print("Error while fetching data from database", error)

finally:
    if(connection):
        cursor.close()
        connection.close()

The output should be like something like the link below

[{"Name: ", "Name1", "Age: ", "16", "Losses: ", "0"}],[{"Name: ", "Name2", "Age: ", "16", "Losses: ", "0"}],[{"Name: ", "Name3", "Age: ", "16", "Losses: ", "0"}],[{"Name: ", "Name4", "Age: ", "16", "Losses: ", "0"}],[{"Name: ", "Name5", "Age: ", "16", "Losses: ", "0"}]

What is going on and how do I fix it?

Don't post resources as external links. It just makes it harder for us to help. — John Gordon, Mar 10 '20 at 21:51
Don't call `json.dump()` in the loop. Put everything into a single list, and call `json.dump()` on the whole thing at the end. — Barmar, Mar 10 '20 at 21:52
Each time through the loop you're appending a new row to `lossarray_list` and then dumping the entire thing to the json file. So you end up with the first row duplicated N times, the second row duplicated N-1 times, etc. — John Gordon, Mar 10 '20 at 21:55
Be careful about using `except` like you are here. See, for example: https://stackoverflow.com/questions/54948548/what-is-wrong-with-using-a-bare-except. — AMC, Mar 10 '20 at 22:13

score 0 · Accepted Answer · answered Mar 10 '20 at 21:54

0

Dump the entire lossarray_list once at the end of the loop, not every time through it.

    lossarray_list = [row[:3] for row in loss_records]
    with open('data.json', 'w') as out:
        json.dump(lossarray_list, out)

answered Mar 10 '20 at 21:54

Barmar

741,623
53
500
612

Output JSON file from Python. Data in JSON file messed up

1 Answers1