Procesamiento de JSON en SQLite

Introducción

En este laboratorio, aprenderá cómo procesar datos JSON dentro de SQLite. Explorará cómo almacenar, extraer, filtrar y actualizar datos JSON dentro de una base de datos SQLite. Este laboratorio proporciona una introducción práctica al trabajo con datos JSON en SQLite, una habilidad cada vez más valiosa en la gestión de datos moderna.

Skills Graph

Crear una base de datos y una tabla

En este paso, creará una base de datos SQLite y una tabla para almacenar datos JSON. SQLite es una base de datos ligera que almacena datos en un solo archivo, lo que facilita su gestión.

Primero, abra su terminal. La ruta predeterminada es /home/labex/project.

Ahora, creemos un directorio para almacenar nuestra base de datos.

mkdir sqlite_json
cd sqlite_json

Estos comandos crean un directorio llamado sqlite_json y luego cambian el directorio actual a él. Esto mantendrá sus archivos de proyecto organizados.

A continuación, cree una base de datos SQLite llamada mydatabase.db.

sqlite3 mydatabase.db

Este comando abre el shell de SQLite, conectándose a la base de datos mydatabase.db. Si el archivo de la base de datos no existe, SQLite lo creará.

Ahora, cree una tabla llamada products con columnas para id, name y details (detalles). La columna details almacenará datos JSON como texto.

CREATE TABLE products (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT,
    details TEXT
);

Este comando SQL crea la tabla products:

• id: Un entero único que se incrementa automáticamente para cada nuevo producto.
• name: El nombre del producto (por ejemplo, Laptop, Smartphone).
• details: Un campo de texto para almacenar los datos JSON del producto.

Insertar datos JSON

En este paso, insertará datos JSON en la tabla products.

Insertemos dos registros de ejemplo en la tabla products.

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"}}'
);

Estas sentencias INSERT agregan dos filas a la tabla products. La columna details contiene datos JSON como una cadena de texto.

Para verificar que los datos se insertaron correctamente, ejecute la siguiente consulta:

SELECT * FROM products;

Resultado esperado:

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"}}

Este resultado confirma que los datos JSON se han almacenado correctamente en la tabla products.

Extraer campos JSON con una función personalizada

Dado que SQLite no tiene funciones JSON integradas, creará una función personalizada de Python para extraer datos de las cadenas JSON.

Primero, salga del shell de SQLite.

.exit

Ahora, cree un archivo Python llamado json_extractor.py.

nano json_extractor.py

Pegue el siguiente código Python en el archivo json_extractor.py:

import sqlite3
import json

def json_extract(json_str, path):
    try:
        json_data = json.loads(json_str)
        path_components = path.split('.')
        value = json_data
        for component in path_components:
            value = value.get(component)
        return value
    except (json.JSONDecodeError, AttributeError, TypeError):
        return None

def connect_db(db_path):
    conn = sqlite3.connect(db_path)
    conn.create_function("json_extract", 2, json_extract)
    return conn

if __name__ == '__main__':
    conn = connect_db('mydatabase.db')
    cursor = conn.cursor()

    cursor.execute("SELECT json_extract(details, 'brand') FROM products WHERE name = 'Laptop'")
    print(cursor.fetchone()[0])

    cursor.execute("SELECT json_extract(details, 'specs.cpu') FROM products WHERE name = 'Laptop'")
    print(cursor.fetchone()[0])

    conn.close()

Este código Python define una función json_extract que toma una cadena JSON y una ruta (path) como entrada y devuelve el valor en esa ruta. También incluye una función connect_db para conectarse a la base de datos SQLite y registrar la función json_extract.

• json.loads(json_str): Esta línea analiza (parse) la cadena JSON en un diccionario de Python.
• path.split('.'): Esto divide la ruta en una lista de componentes. Por ejemplo, 'specs.cpu' se convierte en ['specs', 'cpu'].
• El bucle (loop) itera a través de los componentes de la ruta, accediendo a los valores anidados en los datos JSON.

Guarde el archivo y salga de nano.

Ahora, ejecute el script de Python.

python3 json_extractor.py

Resultado esperado:

Dell
Intel i7

Este script se conecta a la base de datos, registra la función json_extract y luego la usa para extraer la marca (brand) y la CPU de la Laptop.

Filtrar datos usando consultas JSON

En este paso, utilizará la función personalizada json_extract para filtrar datos basándose en valores dentro de los campos JSON.

Abra el archivo json_extractor.py nuevamente.

nano json_extractor.py

Modifique el archivo json_extractor.py para incluir una función para consultar la base de datos:

import sqlite3
import json

def json_extract(json_str, path):
    try:
        json_data = json.loads(json_str)
        path_components = path.split('.')
        value = json_data
        for component in path_components:
            value = value.get(component)
        return value
    except (json.JSONDecodeError, AttributeError, TypeError):
        return None

def connect_db(db_path):
    conn = sqlite3.connect(db_path)
    conn.create_function("json_extract", 2, json_extract)
    return conn

def filter_products(db_path, json_path, value):
    conn = connect_db(db_path)
    cursor = conn.cursor()
    query = f"SELECT * FROM products WHERE json_extract(details, '{json_path}') = '{value}'"
    cursor.execute(query)
    results = cursor.fetchall()
    conn.close()
    return results

if __name__ == '__main__':
    ## Example usage:
    dell_products = filter_products('mydatabase.db', 'brand', 'Dell')
    print("Products with brand 'Dell':", dell_products)

    intel_products = filter_products('mydatabase.db', 'specs.cpu', 'Intel i7')
    print("Products with CPU 'Intel i7':", intel_products)

Este código agrega una función filter_products que toma una ruta de base de datos (db_path), una ruta JSON (json_path) y un valor (value) como entrada. Luego, se conecta a la base de datos, registra la función json_extract y ejecuta una consulta (query) para encontrar todos los productos donde el valor en la ruta JSON especificada coincide con el valor dado.

Guarde el archivo y salga de nano.

Ahora, ejecute el script de Python.

python3 json_extractor.py

Resultado esperado:

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"}}')]

Esta salida muestra los productos que coinciden con los criterios especificados.

Actualizar valores JSON

En este paso, aprenderá cómo actualizar valores dentro de los campos JSON.

Abra el archivo json_extractor.py.

nano json_extractor.py

Modifique el archivo json_extractor.py para incluir funciones para actualizar el JSON y la base de datos:

import sqlite3
import json

def json_extract(json_str, path):
    try:
        json_data = json.loads(json_str)
        path_components = path.split('.')
        value = json_data
        for component in path_components:
            value = value.get(component)
        return value
    except (json.JSONDecodeError, AttributeError, TypeError):
        return None

def connect_db(db_path):
    conn = sqlite3.connect(db_path)
    conn.create_function("json_extract", 2, json_extract)
    return conn

def filter_products(db_path, json_path, value):
    conn = connect_db(db_path)
    cursor = conn.cursor()
    query = f"SELECT * FROM products WHERE json_extract(details, '{json_path}') = '{value}'"
    cursor.execute(query)
    results = cursor.fetchall()
    conn.close()
    return results

def update_json_value(json_str, path, new_value):
    try:
        json_data = json.loads(json_str)
        path_components = path.split('.')
        target = json_data
        for i in range(len(path_components) - 1):
            target = target.get(path_components[i])

        target[path_components[-1]] = new_value
        return json.dumps(json_data)
    except (json.JSONDecodeError, AttributeError, TypeError):
        return json_str  ## Return original if update fails

def update_product_details(db_path, product_name, json_path, new_value):
    conn = sqlite3.connect(db_path)
    cursor = conn.cursor()

    ## Get the current details
    cursor.execute("SELECT details FROM products WHERE name = ?", (product_name,))
    result = cursor.fetchone()
    if not result:
        conn.close()
        return False

    current_details = result[0]

    ## Update the JSON
    updated_details = update_json_value(current_details, json_path, new_value)

    ## Update the database
    cursor.execute("UPDATE products SET details = ? WHERE name = ?", (updated_details, product_name))
    conn.commit()
    conn.close()
    return True

if __name__ == '__main__':
    ## Example usage:
    update_product_details('mydatabase.db', 'Laptop', 'specs.memory', '32GB')
    print("Laptop memory updated to 32GB")

    ## Verify the update
    conn = sqlite3.connect('mydatabase.db')
    cursor = conn.cursor()
    cursor.execute("SELECT details FROM products WHERE name = 'Laptop'")
    updated_details = cursor.fetchone()[0]
    print("Updated Laptop details:", updated_details)
    conn.close()

Este código agrega dos funciones:

• update_json_value: Esta función toma una cadena JSON, una ruta (path) y un nuevo valor como entrada. Analiza la cadena JSON, actualiza el valor en la ruta especificada y devuelve la cadena JSON actualizada.
• update_product_details: Esta función toma una ruta de base de datos (db_path), un nombre de producto (product_name), una ruta JSON (json_path) y un nuevo valor como entrada. Se conecta a la base de datos, recupera los datos JSON actuales para el producto, actualiza el valor en la ruta especificada utilizando update_json_value y luego actualiza la base de datos con los datos JSON modificados.

Guarde el archivo y salga de nano.

Ahora, ejecute el script de Python.

python3 json_extractor.py

Resultado esperado:

Laptop memory updated to 32GB
Updated Laptop details: {"brand": "Dell", "model": "XPS 13", "specs": {"cpu": "Intel i7", "memory": "32GB", "storage": "512GB SSD"}}

Esta salida confirma que la memoria de la Laptop se ha actualizado a 32GB en la base de datos.

Resumen

En este laboratorio, ha aprendido cómo procesar datos JSON dentro de SQLite. Comenzó creando una base de datos y una tabla para almacenar datos JSON. Luego, aprendió cómo insertar datos JSON en la tabla. Creó una función personalizada de Python para extraer campos específicos de los datos JSON y utilizó esta función para filtrar datos basándose en valores dentro de los campos JSON. Finalmente, aprendió cómo actualizar valores dentro de los campos JSON utilizando una función personalizada de Python. Estas habilidades proporcionan una base para administrar datos JSON de manera efectiva dentro de las bases de datos SQLite.