How to create a JSON file from SQL query?

I am creating a JSON file from a SQL query. But I could not create truly. The problem is there is a “items” object and it has products. But mine create products directly not in “items” objects.

The Code.

import json
import collections
import sqlite3

conn = sqlite3.connect('database.db')
cursor = conn.cursor()
cursor.execute("SELECT barcode,listPrice,salePrice FROM productstable")
rows = cursor.fetchall()

objects_list = []
for row in rows:
    d = collections.OrderedDict()
    d["barcode"] = row[0]
    d["listPrice"] = row[1]
    d["salePrice"] = row[2]
    objects_list.append(d)
j = json.dumps(objects_list,indent=4)
with open("products.json", "w") as f:
    f.write(j)
conn.close()

yields the following JSON object as a result

[
    {
        "barcode": "1952084972279",
        "listPrice": 100.5,
        "salePrice": 99
    },
    {
        "barcode": "1952084972280",
        "listPrice": 115.3,
        "salePrice": 100
    }
]

while the desired one should be as follows

{
    "items": [
        {
            "barcode": "1952084972279",
            "salePrice": 100.5,
            "listPrice": 99
        },
        {
            "barcode": "1952084972280",
            "salePrice": 115.3,
            "listPrice": 100
        }
    ]
}

Answer

define objects_list as a dictionary instead of a list.

objects_list = {}
objects_list["items"] = []

And append to the objects_list["items"].

objects_list["items"].append(d)

Advertisement

Answer