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())
columns = []
column = []
for data in json_data:
column = list(data.keys())
for col in column:
if col not in columns:
columns.append(col)
value = []
values = []
for data in json_data:
for i in columns:
value.append(str(dict(data).get(i)))
values.append(list(value))
value.clear()
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
- 17th May, 2019: Initial version