If you’re about to launch a project that uses JSON or you’re deep into one that already does, this article is your go-to guide. JSON is powerful, but it can also be a bit tricky. You might find yourself asking, “What type of JSON am I dealing with?” or “How do I get this JSON data into local variables or into an SQLite3 database?” This article will walk you through a Python script that automatically identifies different JSON types and dynamically creates the necessary SQLite tables to store your data.
Step-by-Step Guide: Handling JSON with Python
The Python script provided below is designed to take any JSON file, identify its structure, and store its contents in an SQLite database. The best part? It does all of this dynamically, meaning the database tables are created based on the JSON data you provide.
import json
import sqlite3
def check_json_type(data):
if isinstance(data, dict):
if all(isinstance(value, (str, int, float, bool, type(None))) for value in data.values()):
return "Key-Value Pair JSON"
else:
return "Nested JSON Object"
elif isinstance(data, list):
if all(isinstance(item, dict) for item in data):
return "JSON Array"
elif all(isinstance(item, list) for item in data):
return "JSON Array of Arrays"
else:
return "JSON Array of Values"
else:
return "Unknown JSON Type"
def create_table(c, table_name, columns):
columns_definition = ', '.join([f"{col} TEXT" for col in columns])
create_statement = f"CREATE TABLE IF NOT EXISTS {table_name} ({columns_definition})"
c.execute(create_statement)
def insert_into_db(json_type, data):
conn = sqlite3.connect('example.db')
c = conn.cursor()
if json_type == "Key-Value Pair JSON":
table_name = 'kv_pairs_dynamic'
columns = data.keys()
create_table(c, table_name, columns)
c.execute(f"INSERT OR REPLACE INTO {table_name} ({', '.join(columns)}) VALUES ({', '.join(['?'] * len(columns))})", list(data.values()))
elif json_type == "Nested JSON Object":
for key, value in data.items():
if isinstance(value, dict):
table_name = f"{key}_nested"
columns = value.keys()
create_table(c, table_name, columns)
c.execute(f"INSERT INTO {table_name} ({', '.join(columns)}) VALUES ({', '.join(['?'] * len(columns))})", list(value.values()))
else:
table_name = 'nested_data'
create_table(c, table_name, [key])
c.execute(f"INSERT INTO {table_name} ({key}) VALUES (?)", (value,))
elif json_type == "JSON Array":
table_name = 'json_array_dynamic'
columns = data[0].keys() # Assuming all dicts in the array have the same keys
create_table(c, table_name, columns)
for item in data:
c.execute(f"INSERT INTO {table_name} ({', '.join(columns)}) VALUES ({', '.join(['?'] * len(columns))})", list(item.values()))
elif json_type == "JSON Array of Arrays":
table_name = 'json_array_of_arrays_dynamic'
num_columns = max(len(sublist) for sublist in data)
columns = [f"col_{i+1}" for i in range(num_columns)]
create_table(c, table_name, columns)
for sublist in data:
# Fill the rest with None if sublist is shorter
sublist += [None] * (num_columns - len(sublist))
c.execute(f"INSERT INTO {table_name} ({', '.join(columns)}) VALUES ({', '.join(['?'] * num_columns)})", sublist)
elif json_type == "JSON Array of Values":
table_name = 'json_array_of_values_dynamic'
create_table(c, table_name, ['value'])
for value in data:
c.execute(f"INSERT INTO {table_name} (value) VALUES (?)", (value,))
conn.commit()
conn.close()
print("Data has been inserted into the database.")
# Example JSON strings to test the script
json_examples = [
'{"key1": "value1", "key2": "value2"}',
'{"person": {"name": "John", "age": 30}, "city": "New York"}',
'[{"name": "John"}, {"name": "Jane"}]',
'[["John", 30], ["Jane", 25]]',
'["John", "Jane", "Doe"]'
]
for json_string in json_examples:
data = json.loads(json_string)
json_type = check_json_type(data)
print(f"JSON Type Identified: '{json_type}'")
insert_into_db(json_type, data)Running the Script with Your JSON File
To use this script with your own JSON file, follow these steps:
- Replace Example JSON: Replace the JSON strings in the
json_exampleslist with your own JSON data. Each JSON string should be wrapped in single quotes. You can add as many different JSON examples as you like.
json_examples = [
'your_json_string_here',
'another_json_string_here'
]2.Run the Script: Execute the script. It will automatically identify the type of JSON structure, display the type to you, and then insert the data into an SQLite database, creating tables as needed based on the JSON data structure.
Example Outputs
Here’s what happens when you run the script with different types of JSON data:
1. Key-Value Pair JSON
{
"key1": "value1",
"key2": "value2"
}Output:
- Identified as: “Key-Value Pair JSON”
- Local Variables:
key1 = "value1"
key2 = "value2"- SQLite Table Created
CREATE TABLE IF NOT EXISTS kv_pairs_dynamic (key1 TEXT, key2 TEXT)2. Nested JSON Object
{
"person": {
"name": "John",
"age": 30
},
"city": "New York"
}Output:
- Identified as: “Nested JSON Object”
- Local Variables:
person = {"name": "John", "age": 30}
city = "New York"- SQLite Tables Created:
CREATE TABLE IF NOT EXISTS person_nested (name TEXT, age TEXT)
CREATE TABLE IF NOT EXISTS nested_data (city TEXT)3. JSON Array
[
{"name": "John"},
{"name": "Jane"}
]Output:
- Identified as: “JSON Array”
- Local Variables:
name_1 = "John"
name_2 = "Jane"- SQLite Table Created:
CREATE TABLE IF NOT EXISTS json_array_dynamic (name TEXT)4. JSON Array of Arrays
[
["John", 30],
["Jane", 25]
]Output:
- Identified as: “JSON Array of Arrays”
- Local Variables:
col_1 = "John", col_2 = 30
col_1 = "Jane", col_2 = 25- SQLite Table Created:
CREATE TABLE IF NOT EXISTS json_array_of_arrays_dynamic (col_1 TEXT, col_2 TEXT)5. JSON Array of Values
[
"John",
"Jane",
"Doe"
]Output:
- Identified as: “JSON Array of Values”
- Local Variables:
value_1 = "John"
value_2 = "Jane"
value_3 = "Doe"- SQLite Table Created:
CREATE TABLE IF NOT EXISTS json_array_of_values_dynamic (value TEXT)With this Python script, you can seamlessly handle any JSON data, automatically identify its structure, see the local variables and store it in an SQLite database. The script dynamically creates tables based on your JSON data, allowing you to manage and organize your data effectively. Whether you’re dealing with key-value pairs, nested objects, or arrays, this tool will make your JSON handling tasks straightforward and efficient.
Thank you for following along with this tutorial. We hope you found it helpful and informative. If you have any questions, or if you would like to suggest new Python code examples or topics for future tutorials/articles, please feel free to join and comment. Your feedback and suggestions are always welcome!
You can find the same tutorial on Medium.com.
