Srikanth Technologies

Converting table rows to and from JSON

In this blog, we understand how to convert rows in a table to JSON and load data from a JSON file into table.

Database

We use simple database engine called SQLite in order to focus more on coding rather than installation and configuration of Database.

Use SQLiteStudio to create and manage SQLite database.

However, you can use any other database in place of SQLite. Here are my blogs on how to access Oracle and MySQL from Python. The code used in this blog remains the same, except the way we connect to database.

I assume we have a test.db database in SQLite.

It has a table - PRODUCTS, which was created with the following command. 

CREATE TABLE PRODUCTS (
    id    INTEGER PRIMARY KEY AUTOINCREMENT,
    name  TEXT (30),
    price FLOAT,
    qty   INTEGER
);

ID column is automatically incremented on insert.

Insert some rows into it using the INSERT command. Below is the sample data. 

1	iPad Air         60000	10
2	Google Pixel     55000	 5
3	Samsung Note 8   50000	 2
4	iPhone 14 Pro   120000	 3
5	AirPods 3rd Gen  20000	 5

Create JSON file from Table

We want to convert rows to JSON objects and write them into products.json file.

After retrieving each row, we need to convert each row to dictionary. We eventually create a list of dictionaries and convert that list to an array of JSON objects using dump() function of json module. 

# Convert PRODUCTS table to an array of JSON objects and write them to products.json

import sqlite3
import json


# Convert tuple with product details to dict
def product_row_to_dict(product):
    return {"id": product[0], "name": product[1], "price": product[2], "qty": product[3]}


products = []
con = sqlite3.connect(r"c:\dev\python\test.db")  # Connect to database - test.db
cur = con.cursor()
cur.execute("select * from products")  # retrieve all products from table

for product in cur.fetchall():
    products.append(product_row_to_dict(product))  # convert tuple to dict and add to list

cur.close()
con.close()

# Write to file
f = open("products.json", "wt")
json.dump(products, f)      # converts list of dict to array of JSON and writes it to products.json
f.close()

After the above program is run, products.json contains the following contents. 

[
  {"id": 1, "name": "iPad Air", "price": 60000.0, "qty": 10}, 
  {"id": 2, "name": "Google Pixel", "price": 55000.0, "qty": 5}, 
  {"id": 3, "name": "Samsung Note 8", "price": 50000.0, "qty": 2}, 
  {"id": 4, "name": "iPhone 14 Pro", "price": 120000.0, "qty": 3}, 
  {"id": 5, "name": "AirPods 3rd Gen", "price": 20000.0, "qty": 5}
]

Load data from JSON file to table

At times it is required to load data from JSON file to database table.

We load data from JSON file using load() function of json module, which reads data from the given file and returns a list of dict objects - one dict for each JSON object.

Then we take data from dict and insert that into PRODUCTS table.

I assume we have newproducts.json file with the following contents. 

[
  {"name": "Logi MX Master 3", "price": 15000.0, "qty": 3},
  {"name": "Seagate 2 TB External Disk", "price": 5500, "qty": 1}
]

Here is the program to load data from newproducts.json to PRODUCTS table. 

# Load data from JSON file to PRODUCTS table

import sqlite3
import json

f = open("newproducts.json", "rt")

# Convert array of JSON objects to list of dict using load()
# Each product is represented by a dict
products = json.load(f)

f.close()

con = sqlite3.connect(r"c:\dev\python\test.db")
cur = con.cursor()
count = 0

# take one dict at a time from list 
for product in products:
    try:
        # insert a row 
        cur.execute("insert into products(name,price,qty) values(?,?,?)",
                    (product['name'], product['price'], product['qty']))
        count += 1
    except Exception as ex:
        print("Error : ", ex) 

# make insertions permanent
con.commit()
cur.close()
con.close()
print(f"Inserted {count} products")   # Display how many rows were inserted

For more information about functions in json module, read its documentation.

All the best!

Srikanth Pragada