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.
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_jsonEstos 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.dbEste 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.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.
Dado que SQLite no tiene funciones JSON integradas, crearás una función personalizada de Python para extraer datos de las cadenas JSON.
Primero, sal del shell de SQLite.
.exitAhora, crea un archivo Python llamado json_extractor.py.
nano json_extractor.pyPega el siguiente código Python en el archivo json_extractor.py:
## Importar las bibliotecas necesarias
import sqlite3
import json
## Definir una función para extraer un valor de una cadena JSON usando una ruta
def json_extract(json_str, path):
try:
## Analizar la cadena JSON en un diccionario de Python
json_data = json.loads(json_str)
## Dividir la ruta en componentes (por ejemplo, 'specs.cpu' se convierte en ['specs', 'cpu'])
path_components = path.split('.')
## Empezar con el objeto JSON completo
value = json_data
## Recorrer el objeto JSON usando los componentes de la ruta
for component in path_components:
## Obtener el valor para el componente actual
value = value.get(component)
## Devolver el valor final
return value
## Manejar errores si el JSON no es válido o la ruta no existe
except (json.JSONDecodeError, AttributeError, TypeError):
return None
## Definir una función para conectarse a la base de datos y registrar la función personalizada
def connect_db(db_path):
## Conectarse a la base de datos SQLite en la ruta dada
conn = sqlite3.connect(db_path)
## Registrar la función de Python 'json_extract' como una función SQL personalizada
## "json_extract" es el nombre en SQL, 2 es el número de argumentos, json_extract es la función de Python
conn.create_function("json_extract", 2, json_extract)
## Devolver la conexión a la base de datos
return conn
## Este bloque se ejecuta cuando el script se ejecuta directamente
if __name__ == '__main__':
## Conectarse a la base de datos y registrar la función
conn = connect_db('mydatabase.db')
## Crear un objeto cursor para ejecutar consultas SQL
cursor = conn.cursor()
## Usar la función SQL personalizada para extraer la 'marca' de la columna 'details'
cursor.execute("SELECT json_extract(details, 'brand') FROM products WHERE name = 'Laptop'")
## Obtener el resultado e imprimirlo
print(cursor.fetchone()[0])
## Usar la función SQL personalizada para extraer la 'cpu' del objeto anidado 'specs'
cursor.execute("SELECT json_extract(details, 'specs.cpu') FROM products WHERE name = 'Laptop'")
## Obtener el resultado e imprimirlo
print(cursor.fetchone()[0])
## Cerrar la conexión a la base de datos
conn.close()Este código Python define una función json_extract que toma una cadena JSON y una ruta 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 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'].Guarda el archivo y sal de nano.
Ahora, ejecuta el script de Python.
python3 json_extractor.pySalida esperada:
Dell
Intel i7Este script se conecta a la base de datos, registra la función json_extract y luego la utiliza para extraer la marca y la CPU del Laptop.
En este paso, utilizará la función personalizada json_extract para filtrar datos basándose en valores dentro de los campos JSON.
Abra de nuevo el archivo json_extractor.py.
nano json_extractor.pyModifique el archivo json_extractor.py para incluir una función para consultar la base de datos:
## Importar las bibliotecas necesarias
import sqlite3
import json
## Definir una función para extraer un valor de una cadena JSON usando una ruta
def json_extract(json_str, path):
try:
## Analizar la cadena JSON en un diccionario de Python
json_data = json.loads(json_str)
## Dividir la ruta en componentes (por ejemplo, 'specs.cpu' se convierte en ['specs', 'cpu'])
path_components = path.split('.')
## Empezar con el objeto JSON completo
value = json_data
## Recorrer el objeto JSON usando los componentes de la ruta
for component in path_components:
## Obtener el valor para el componente actual
value = value.get(component)
## Devolver el valor final
return value
## Manejar errores si el JSON no es válido o la ruta no existe
except (json.JSONDecodeError, AttributeError, TypeError):
return None
## Definir una función para conectarse a la base de datos y registrar la función personalizada
def connect_db(db_path):
## Conectarse a la base de datos SQLite en la ruta dada
conn = sqlite3.connect(db_path)
## Registrar la función de Python 'json_extract' como una función SQL personalizada
conn.create_function("json_extract", 2, json_extract)
## Devolver la conexión a la base de datos
return conn
## Definir una función para filtrar productos basándose en un campo JSON
def filter_products(db_path, json_path, value):
## Conectarse a la base de datos
conn = connect_db(db_path)
## Crear un objeto cursor
cursor = conn.cursor()
## Crear la consulta SQL usando una f-string para filtrar por un valor JSON
query = f"SELECT * FROM products WHERE json_extract(details, '{json_path}') = '{value}'"
## Ejecutar la consulta
cursor.execute(query)
## Obtener todos los resultados coincidentes
results = cursor.fetchall()
## Cerrar la conexión a la base de datos
conn.close()
## Devolver los resultados
return results
## Este bloque se ejecuta cuando el script se ejecuta directamente
if __name__ == '__main__':
## Ejemplo de uso:
## Filtrar productos donde la marca es 'Dell'
dell_products = filter_products('mydatabase.db', 'brand', 'Dell')
print("Products with brand 'Dell':", dell_products)
## Filtrar productos donde la CPU es 'Intel i7'
intel_products = filter_products('mydatabase.db', 'specs.cpu', 'Intel i7')
print("Products with CPU 'Intel i7':", intel_products)Este código añade una función filter_products que toma como entrada una ruta de base de datos, una ruta JSON y un valor. Luego se conecta a la base de datos, registra la función json_extract y ejecuta una consulta 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.pySalida Esperada:
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.
En este paso, aprenderá cómo actualizar valores dentro de los campos JSON.
Abra el archivo json_extractor.py.
nano json_extractor.pyModifique el archivo json_extractor.py para incluir funciones para actualizar el JSON y la base de datos:
## Importar las bibliotecas necesarias
import sqlite3
import json
## Definir una función para extraer un valor de una cadena JSON usando una ruta
def json_extract(json_str, path):
try:
## Analizar la cadena JSON en un diccionario de Python
json_data = json.loads(json_str)
## Dividir la ruta en componentes
path_components = path.split('.')
## Comenzar con el objeto JSON completo
value = json_data
## Recorrer el objeto JSON
for component in path_components:
value = value.get(component)
return value
except (json.JSONDecodeError, AttributeError, TypeError):
return None
## Definir una función para conectarse a la base de datos y registrar la función personalizada
def connect_db(db_path):
## Conectarse a la base de datos
conn = sqlite3.connect(db_path)
## Registrar la función SQL personalizada
conn.create_function("json_extract", 2, json_extract)
return conn
## Definir una función para filtrar productos basándose en un campo JSON
def filter_products(db_path, json_path, value):
## Conectarse a la base de datos
conn = connect_db(db_path)
## Crear un objeto cursor
cursor = conn.cursor()
## Crear la consulta SQL para filtrar por un valor JSON
query = f"SELECT * FROM products WHERE json_extract(details, '{json_path}') = '{value}'"
## Ejecutar la consulta
cursor.execute(query)
## Obtener todos los resultados coincidentes
results = cursor.fetchall()
## Cerrar la conexión
conn.close()
return results
## Definir una función para actualizar un valor dentro de una cadena JSON
def update_json_value(json_str, path, new_value):
try:
## Analizar la cadena JSON en un diccionario de Python
json_data = json.loads(json_str)
## Dividir la ruta en componentes
path_components = path.split('.')
## Navegar hasta el padre del valor objetivo
target = json_data
for i in range(len(path_components) - 1):
target = target.get(path_components[i])
## Actualizar el valor objetivo
target[path_components[-1]] = new_value
## Convertir el diccionario de Python de nuevo a una cadena JSON
return json.dumps(json_data)
except (json.JSONDecodeError, AttributeError, TypeError):
## Si ocurre un error, devolver la cadena JSON original
return json_str
## Definir una función para actualizar los detalles de un producto en la base de datos
def update_product_details(db_path, product_name, json_path, new_value):
## Conectarse a la base de datos
conn = sqlite3.connect(db_path)
## Crear un objeto cursor
cursor = conn.cursor()
## Obtener los detalles actuales para el producto especificado
## El '?' es un marcador de posición para prevenir la inyección de SQL
cursor.execute("SELECT details FROM products WHERE name = ?", (product_name,))
result = cursor.fetchone()
## Si el producto no existe, cerrar la conexión y devolver False
if not result:
conn.close()
return False
## Obtener la cadena de detalles JSON actual
current_details = result[0]
## Actualizar la cadena JSON con el nuevo valor
updated_details = update_json_value(current_details, json_path, new_value)
## Actualizar la base de datos con la nueva cadena JSON
cursor.execute("UPDATE products SET details = ? WHERE name = ?", (updated_details, product_name))
## Confirmar los cambios en la base de datos
conn.commit()
## Cerrar la conexión
conn.close()
return True
## Este bloque se ejecuta cuando el script se ejecuta directamente
if __name__ == '__main__':
## Ejemplo de uso: Actualizar la memoria del portátil a 32GB
update_product_details('mydatabase.db', 'Laptop', 'specs.memory', '32GB')
print("Memoria del portátil actualizada a 32GB")
## Verificar la actualización obteniendo los datos de nuevo
conn = sqlite3.connect('mydatabase.db')
cursor = conn.cursor()
cursor.execute("SELECT details FROM products WHERE name = 'Laptop'")
## Obtener los detalles actualizados
updated_details = cursor.fetchone()[0]
print("Detalles actualizados del portátil:", updated_details)
## Cerrar la conexión
conn.close()Este código añade dos funciones:
update_json_value: Esta función toma una cadena JSON, una ruta 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, un nombre de producto, una ruta JSON 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 usando 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.pySalida Esperada:
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 del portátil se ha actualizado a 32GB en la base de datos.
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.
