Load JSON File with Array of Objects to SQLite3 On Python

Introduction

This Python code can be used to load the data of a JSON file with array of objects without any information about the attributes into SQLite3 database.

For 20 MB of JSON file, it takes less than 1 second to load. In SQLite3 database, the size is about 10 MB.

Using the Code

Code is developed on Python 3.6.

This Python code works for the JSON files in this format:

[{"id":1,"Name":"John"}, {"id":2,"Location":"UK"}, {"id":3,"Name":"Mike", "Location": "USA"} ]

The block inside "{}" with Key/Value pair is a JSON object and "[]" is the array. Keys don't have to align for all objects. If there is no value for a key, it can be skipped in the object like in this sample. Empty columns will be loaded to the SQLite3 db as "None" which means Null.

import json
import sqlite3
from datetime import datetime
db=sqlite3.connect('C:\myDB.sqlite')
with open('C:\myJSON.json', encoding='utf-8-sig') as json_file:
    json_data = json.loads(json_file.read())
    
#Aim of this block is to get the list of the columns in the JSON file.
    columns = []
    column = []
    for data in json_data:
        column = list(data.keys())
        for col in column:
            if col not in columns:
                columns.append(col)
                                
#Here we get values of the columns in the JSON file in the right order.   
    value = []
    values = [] 
    for data in json_data:
        for i in columns:
            value.append(str(dict(data).get(i)))   
        values.append(list(value)) 
        value.clear()
        
#Time to generate the create and insert queries and apply it to the sqlite3 database       
    create_query = "create table if not exists myTable ({0})".format(" text,".join(columns))
    insert_query = "insert into myTable ({0}) 
                    values (?{1})".format(",".join(columns), ",?" * (len(columns)-1))    
    print("insert has started at " + str(datetime.now()))  
    c = db.cursor()   
    c.execute(create_query)
    c.executemany(insert_query , values)
    values.clear()
    db.commit()
    c.close()
    print("insert has completed at " + str(datetime.now())) 

History

• 17^th May, 2019: Initial version