JSON Data with Python in SQLite Database

In this article will discuss JSON data with Python in SQLite database, like to read, insert and create tables.  JSON and SQLite libraries can be used from the Python Standard library by import statement. sqlite3.connect is used to create a SQLite database.

Connect to SQLite database

# Connect to SQLite database (or create it)
conn = sqlite3.connect('sampledb.db')
cursor = conn.cursor()
 
# Create a table to store JSON data
cursor.execute('''
    CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY,
        data TEXT
    )
''')

 Code Example

import sqlite3
import json

# Sample JSON data
json_data = {
    "employee_name": "John Doe",
    "age": 30,
    "address": "New York",
    "salary":"30000",
    "Phone Number":"1234543210"
}

conn = sqlite3.connect('sampledb.db')
cursor = conn.cursor()

cursor.execute('''
    CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY,
        data TEXT
    )
''')

cursor.execute('''
    INSERT INTO users (data) VALUES (?)
''', (json.dumps(json_data),))

conn.commit()

cursor.execute('SELECT data FROM users')
rows = cursor.fetchall()

for row in rows:
    data = json.loads(row[0])
    print(data)

conn.close()

 Output

In Database Output