Python Convert JSON to SQLite

Kamran Ahmed Khan — Fri, 27 Jan 2023 21:12:37 +0000

Here are the detailed steps to convert JSON to SQLite using Python:

Install the required libraries:

pip install sqlite3
pip install json

Import the required libraries:

import sqlite3
import json

Open a connection to the SQLite database:

conn = sqlite3.connect('example.db')

Open the JSON file:

with open('example.json', 'r') as json_file:
    data = json.load(json_file)

Iterate through the JSON data and insert it into the SQLite table:

for item in data:
    conn.execute("INSERT INTO example_table (field1, field2, field3) VALUES (?, ?, ?)", 
                 (item["field1"], item["field2"], item["field3"]))

Commit the changes and close the connection:

conn.commit()
conn.close()

Note:

Replace 'example.db' and 'example.json' with the appropriate file name, and 'example_table' with the appropriate table name
Also, you may need to create the table before inserting the data, you can check this by using the below command

conn.execute("CREATE TABLE example_table (field1 text, field2 text, field3 text);")

Example:

import sqlite3
import json

conn = sqlite3.connect('example.db')
conn.execute("CREATE TABLE example_table (field1 text, field2 text, field3 text);")
with open('example.json', 'r') as json_file:
    data = json.load(json_file)
    for item in data:
        conn.execute("INSERT INTO example_table (field1, field2, field3) VALUES (?, ?, ?)", (item["field1"], item["field2"], item["field3"]))
conn.commit()
conn.close()

This will create a SQLite database file named 'example.db' in the current directory and insert the data from 'example.json' into a table named 'example_table'.

DEV Community: Kamran Ahmed Khan

Python Convert JSON to SQLite