In this lab, you will learn how to process JSON data within SQLite. You'll explore how to store, extract, filter, and update JSON data within an SQLite database. This lab provides a hands-on introduction to working with JSON data in SQLite, a skill increasingly valuable in modern data management.
In this step, you will create an SQLite database and a table to store JSON data. SQLite is a lightweight database that stores data in a single file, making it easy to manage.
First, open your terminal. The default path is /home/labex/project.
Now, let's create a directory to store our database.
mkdir sqlite_json
cd sqlite_jsonThese commands create a directory named sqlite_json and then change the current directory to it. This will keep your project files organized.
Next, create an SQLite database named mydatabase.db.
sqlite3 mydatabase.dbThis command opens the SQLite shell, connecting to the mydatabase.db database. If the database file doesn't exist, SQLite will create it.
Now, create a table named products with columns for id, name, and details. The details column will store JSON data as text.
CREATE TABLE products (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT,
details TEXT
);This SQL command creates the products table:
id: A unique integer that automatically increments for each new product.name: The name of the product (e.g., Laptop, Smartphone).details: A text field to store the JSON data for the product.In this step, you will insert JSON data into the products table.
Let's insert two sample records into the products table.
INSERT INTO products (name, details) VALUES (
'Laptop',
'{"brand": "Dell", "model": "XPS 13", "specs": {"cpu": "Intel i7", "memory": "16GB", "storage": "512GB SSD"}}'
);
INSERT INTO products (name, details) VALUES (
'Smartphone',
'{"brand": "Samsung", "model": "Galaxy S21", "specs": {"display": "6.2 inch", "camera": "12MP", "storage": "128GB"}}'
);These INSERT statements add two rows to the products table. The details column contains JSON data as a text string.
To verify the data was inserted correctly, run the following query:
SELECT * FROM products;Expected Output:
1|Laptop|{"brand": "Dell", "model": "XPS 13", "specs": {"cpu": "Intel i7", "memory": "16GB", "storage": "512GB SSD"}}
2|Smartphone|{"brand": "Samsung", "model": "Galaxy S21", "specs": {"display": "6.2 inch", "camera": "12MP", "storage": "128GB"}}This output confirms that the JSON data has been successfully stored in the products table.
Since SQLite doesn't have built-in JSON functions, you'll create a custom Python function to extract data from the JSON strings.
First, exit the SQLite shell.
.exitNow, create a Python file named json_extractor.py.
nano json_extractor.pyPaste the following Python code into the json_extractor.py file:
## Import the necessary libraries
import sqlite3
import json
## Define a function to extract a value from a JSON string using a path
def json_extract(json_str, path):
try:
## Parse the JSON string into a Python dictionary
json_data = json.loads(json_str)
## Split the path into components (e.g., 'specs.cpu' becomes ['specs', 'cpu'])
path_components = path.split('.')
## Start with the full JSON object
value = json_data
## Traverse the JSON object using the path components
for component in path_components:
## Get the value for the current component
value = value.get(component)
## Return the final value
return value
## Handle errors if JSON is invalid or path does not exist
except (json.JSONDecodeError, AttributeError, TypeError):
return None
## Define a function to connect to the database and register the custom function
def connect_db(db_path):
## Connect to the SQLite database at the given path
conn = sqlite3.connect(db_path)
## Register the 'json_extract' Python function as a custom SQL function
## "json_extract" is the name in SQL, 2 is the number of arguments, json_extract is the Python function
conn.create_function("json_extract", 2, json_extract)
## Return the database connection
return conn
## This block runs when the script is executed directly
if __name__ == '__main__':
## Connect to the database and register the function
conn = connect_db('mydatabase.db')
## Create a cursor object to execute SQL queries
cursor = conn.cursor()
## Use the custom SQL function to extract the 'brand' from the 'details' column
cursor.execute("SELECT json_extract(details, 'brand') FROM products WHERE name = 'Laptop'")
## Fetch the result and print it
print(cursor.fetchone()[0])
## Use the custom SQL function to extract the 'cpu' from the nested 'specs' object
cursor.execute("SELECT json_extract(details, 'specs.cpu') FROM products WHERE name = 'Laptop'")
## Fetch the result and print it
print(cursor.fetchone()[0])
## Close the database connection
conn.close()This Python code defines a function json_extract that takes a JSON string and a path as input and returns the value at that path. It also includes a connect_db function to connect to the SQLite database and register the json_extract function.
json.loads(json_str): This line parses the JSON string into a Python dictionary.path.split('.'): This splits the path into a list of components. For example, 'specs.cpu' becomes ['specs', 'cpu'].Save the file and exit nano.
Now, run the Python script.
python3 json_extractor.pyExpected Output:
Dell
Intel i7This script connects to the database, registers the json_extract function, and then uses it to extract the brand and CPU of the Laptop.
In this step, you'll use the custom json_extract function to filter data based on values within the JSON fields.
Open the json_extractor.py file again.
nano json_extractor.pyModify the json_extractor.py file to include a function for querying the database:
## Import the necessary libraries
import sqlite3
import json
## Define a function to extract a value from a JSON string using a path
def json_extract(json_str, path):
try:
## Parse the JSON string into a Python dictionary
json_data = json.loads(json_str)
## Split the path into components (e.g., 'specs.cpu' becomes ['specs', 'cpu'])
path_components = path.split('.')
## Start with the full JSON object
value = json_data
## Traverse the JSON object using the path components
for component in path_components:
## Get the value for the current component
value = value.get(component)
## Return the final value
return value
## Handle errors if JSON is invalid or path does not exist
except (json.JSONDecodeError, AttributeError, TypeError):
return None
## Define a function to connect to the database and register the custom function
def connect_db(db_path):
## Connect to the SQLite database at the given path
conn = sqlite3.connect(db_path)
## Register the 'json_extract' Python function as a custom SQL function
conn.create_function("json_extract", 2, json_extract)
## Return the database connection
return conn
## Define a function to filter products based on a JSON field
def filter_products(db_path, json_path, value):
## Connect to the database
conn = connect_db(db_path)
## Create a cursor object
cursor = conn.cursor()
## Create the SQL query using an f-string to filter by a JSON value
query = f"SELECT * FROM products WHERE json_extract(details, '{json_path}') = '{value}'"
## Execute the query
cursor.execute(query)
## Fetch all matching results
results = cursor.fetchall()
## Close the database connection
conn.close()
## Return the results
return results
## This block runs when the script is executed directly
if __name__ == '__main__':
## Example usage:
## Filter for products where the brand is 'Dell'
dell_products = filter_products('mydatabase.db', 'brand', 'Dell')
print("Products with brand 'Dell':", dell_products)
## Filter for products where the CPU is 'Intel i7'
intel_products = filter_products('mydatabase.db', 'specs.cpu', 'Intel i7')
print("Products with CPU 'Intel i7':", intel_products)This code adds a filter_products function that takes a database path, a JSON path, and a value as input. It then connects to the database, registers the json_extract function, and executes a query to find all products where the value at the specified JSON path matches the given value.
Save the file and exit nano.
Now, run the Python script.
python3 json_extractor.pyExpected Output:
Products with brand 'Dell': [(1, 'Laptop', '{"brand": "Dell", "model": "XPS 13", "specs": {"cpu": "Intel i7", "memory": "16GB", "storage": "512GB SSD"}}')]
Products with CPU 'Intel i7': [(1, 'Laptop', '{"brand": "Dell", "model": "XPS 13", "specs": {"cpu": "Intel i7", "memory": "16GB", "storage": "512GB SSD"}}')]This output shows the products that match the specified criteria.
In this step, you'll learn how to update values within the JSON fields.
Open the json_extractor.py file.
nano json_extractor.pyModify the json_extractor.py file to include functions for updating the JSON and the database:
## Import the necessary libraries
import sqlite3
import json
## Define a function to extract a value from a JSON string using a path
def json_extract(json_str, path):
try:
## Parse the JSON string into a Python dictionary
json_data = json.loads(json_str)
## Split the path into components
path_components = path.split('.')
## Start with the full JSON object
value = json_data
## Traverse the JSON object
for component in path_components:
value = value.get(component)
return value
except (json.JSONDecodeError, AttributeError, TypeError):
return None
## Define a function to connect to the database and register the custom function
def connect_db(db_path):
## Connect to the database
conn = sqlite3.connect(db_path)
## Register the custom SQL function
conn.create_function("json_extract", 2, json_extract)
return conn
## Define a function to filter products based on a JSON field
def filter_products(db_path, json_path, value):
## Connect to the database
conn = connect_db(db_path)
## Create a cursor object
cursor = conn.cursor()
## Create the SQL query to filter by a JSON value
query = f"SELECT * FROM products WHERE json_extract(details, '{json_path}') = '{value}'"
## Execute the query
cursor.execute(query)
## Fetch all matching results
results = cursor.fetchall()
## Close the connection
conn.close()
return results
## Define a function to update a value within a JSON string
def update_json_value(json_str, path, new_value):
try:
## Parse the JSON string into a Python dictionary
json_data = json.loads(json_str)
## Split the path into components
path_components = path.split('.')
## Navigate to the parent of the target value
target = json_data
for i in range(len(path_components) - 1):
target = target.get(path_components[i])
## Update the target value
target[path_components[-1]] = new_value
## Convert the Python dictionary back to a JSON string
return json.dumps(json_data)
except (json.JSONDecodeError, AttributeError, TypeError):
## If an error occurs, return the original JSON string
return json_str
## Define a function to update a product's details in the database
def update_product_details(db_path, product_name, json_path, new_value):
## Connect to the database
conn = sqlite3.connect(db_path)
## Create a cursor object
cursor = conn.cursor()
## Get the current details for the specified product
## The '?' is a placeholder to prevent SQL injection
cursor.execute("SELECT details FROM products WHERE name = ?", (product_name,))
result = cursor.fetchone()
## If the product doesn't exist, close connection and return False
if not result:
conn.close()
return False
## Get the current JSON details string
current_details = result[0]
## Update the JSON string with the new value
updated_details = update_json_value(current_details, json_path, new_value)
## Update the database with the new JSON string
cursor.execute("UPDATE products SET details = ? WHERE name = ?", (updated_details, product_name))
## Commit the changes to the database
conn.commit()
## Close the connection
conn.close()
return True
## This block runs when the script is executed directly
if __name__ == '__main__':
## Example usage: Update the laptop's memory to 32GB
update_product_details('mydatabase.db', 'Laptop', 'specs.memory', '32GB')
print("Laptop memory updated to 32GB")
## Verify the update by fetching the data again
conn = sqlite3.connect('mydatabase.db')
cursor = conn.cursor()
cursor.execute("SELECT details FROM products WHERE name = 'Laptop'")
## Fetch the updated details
updated_details = cursor.fetchone()[0]
print("Updated Laptop details:", updated_details)
## Close the connection
conn.close()This code adds two functions:
update_json_value: This function takes a JSON string, a path, and a new value as input. It parses the JSON string, updates the value at the specified path, and returns the updated JSON string.update_product_details: This function takes a database path, a product name, a JSON path, and a new value as input. It connects to the database, retrieves the current JSON data for the product, updates the value at the specified path using update_json_value, and then updates the database with the modified JSON data.Save the file and exit nano.
Now, run the Python script.
python3 json_extractor.pyExpected Output:
Laptop memory updated to 32GB
Updated Laptop details: {"brand": "Dell", "model": "XPS 13", "specs": {"cpu": "Intel i7", "memory": "32GB", "storage": "512GB SSD"}}This output confirms that the Laptop's memory has been updated to 32GB in the database.
In this lab, you have learned how to process JSON data within SQLite. You started by creating a database and a table to store JSON data. Then, you learned how to insert JSON data into the table. You created a custom Python function to extract specific fields from the JSON data and used this function to filter data based on values within the JSON fields. Finally, you learned how to update values within the JSON fields using a custom Python function. These skills provide a foundation for managing JSON data effectively within SQLite databases.
