How to create a JSON file from SQL query?

Question

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

Thank you, dear Barbaros Hocam. You made the question understandable. — HASAN HÜSEYİN YÜCEL
– HASAN HÜSEYİN YÜCEL, Commented Mar 12, 2021 at 14:37

forpas · Accepted Answer · 2021-03-12 14:35:09Z

5

You can do it with SQLite code:

SELECT json_object("items",
         json_group_array(
           json_object(
             'barcode', barcode, 
             'listPrice', listPrice, 
             'salePrice', salePrice
           )
         )
       ) result
FROM productstable

See the demo.
Result:

{"items":
  [
    {"barcode":"1952084972279","listPrice":100.5,"salePrice":99.0}, 
    {"barcode":"1952084972280","listPrice":115.3,"salePrice":100.0}
  ]
}

edited Mar 12, 2021 at 14:35

answered Mar 12, 2021 at 14:30

forpas

165k10 gold badges51 silver badges85 bronze badges

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

7 Comments

HASAN HÜSEYİN YÜCEL Over a year ago

This looks nice. Can I use it for Postgre?

forpas Over a year ago

@HASANHÜSEYİNYÜCEL I'm sure that Postgresql has similar functions. I can check and if I find anything I will post it.

forpas Over a year ago

@HASANHÜSEYİNYÜCEL Do you mean the link: dbfiddle.uk/… It works for me. Try this: db-fiddle.com/f/4iheyW7aHsuL1vnSJJbA3k/0 also

forpas Over a year ago

@HASANHÜSEYİNYÜCEL For Postgresql it's simpler: dbfiddle.uk/… or: db-fiddle.com/f/oJFXFUknvSrwRo9X31M3kd/0

HASAN HÜSEYİN YÜCEL Over a year ago

Very helpful :) Thanks

|

Vishal Singh · Accepted Answer · 2021-03-12 14:01:27Z

3

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)

answered Mar 12, 2021 at 14:01

Vishal Singh

6,2522 gold badges19 silver badges34 bronze badges

1 Comment

HASAN HÜSEYİN YÜCEL Over a year ago

Thank you for your help. I have to work on dictionaries :)

Collectives™ on Stack Overflow

How to create a JSON file from SQL query?

2 Answers 2

7 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related