How to Convert JSON to SQLite Database in Python

Python / By

To Convert Json data into SQLite database use the JSON module and sqlite3 module. JSON and sqlite3 modules are built-in python modules that help us work JSON data and SQLite databases.

JSON Module in Python

Json module in Python is used to read and write JSON files. To insert JSON data into SQLite database we will first need to read the data from the JSON file and then use the sqlite3 python module to insert it into the SQLite database table.

Sqlite3 Module in Python

The sqlite3 module is a python built-in module used to read from and write data into databases using python. sqlite3 module in python provides a utility function to deal with Sqlite databases in Python.

Convert Json to Sqlit Database in Python

To Convert Json to Sqlite database, first, read the JSON data using json.load() function and then use the sqlite3 module to insert it into sqlite database.Steps to insert JSON data into SQLite database

  1.  Import JSON and sqlite3 modulesJSON module is used to read the JSON file and the sqlite3 module in python is used to insert data into SQLite database
  2.  use the json.load() to read the JSON datajson.load() function is used to read the content in a JSON file. It returns a JSON object. which is an iterable and we can get the data by iterating on it.
  3.  Create SQLite table with The required fieldsThe SQL CREATE query will help us create a table in the SQLite database. The field should be specified at the time of creating the table. The fields of the table represent columns of the table.
  4.  use for loop to get each row of the JSON and convert it into a tuple or listyou can either change the row to a list or a tuple. In order to use the insert statement in SQL. converting each row into a tuple will be a Good Idea
  5.  Using the SQL insert Statement to insert each row to the SQLite Databaseyou can create a formated string to build the insert query. The insert query will help us enter the data into SQLite database table. We have to specify the table name and the value that we are entering. In this case, we have provided a tuple.
  6.  JSON data is successfully inserted into SQLite database tableCommit the changes and close the connection, as we have successfully entered the JSON data into SQLite database.


import json
import sqlite3

connection = sqlite3.connect('db.sqlite')
cursor = connection.cursor()
cursor.execute('Create Table if not exists Student (name Text, course Text, roll Integer)')

traffic = json.load(open('json_file.json'))
columns = ['name','course','roll']
for row in traffic:
    keys= tuple(row[c] for c in columns)
    cursor.execute('insert into Student values(?,?,?)',keys)
    print(f'{row["name"]} data inserted Succefully')

connection.commit()
connection.close()

Why we use the sqlite3.connect() function

To make a connection to the database and make it active we use the sqlite3.connect() function. It takes one parameter. the parameter it takes is the path to the SQLite database. The path should be provided as a parameter to this function to make a connection. If the database file is not available in that path, It will automatically create a new database file.Example of sqlite3.connect() function.

import sqlite3
connection = sqlite3.connect('database.db')

After the execution of this line, a file with the name of database.db is created in the current working directory.

What is the difference between json.load() and json.loads() functions

The only diffence between the json.load() and json.loads() funciton is that json.load() funtion convert the json file data into a string and json.loads() funciotn convert the json file data into python dictionay object.

Leave a Comment

Scroll to Top