Dans ce laboratoire, vous apprendrez à traiter les données JSON dans SQLite. Vous explorerez comment stocker, extraire, filtrer et mettre à jour des données JSON dans une base de données SQLite. Ce laboratoire offre une introduction pratique à l'utilisation des données JSON dans SQLite, une compétence de plus en plus précieuse dans la gestion moderne des données.
Dans cette étape, vous allez créer une base de données SQLite et une table pour stocker des données JSON. SQLite est une base de données légère qui stocke les données dans un seul fichier, ce qui facilite sa gestion.
Tout d'abord, ouvrez votre terminal. Le chemin d'accès par défaut est /home/labex/project.
Maintenant, créons un répertoire pour stocker notre base de données.
mkdir sqlite_json
cd sqlite_jsonCes commandes créent un répertoire nommé sqlite_json, puis modifient le répertoire courant pour y accéder. Cela permettra de garder vos fichiers de projet organisés.
Ensuite, créez une base de données SQLite nommée mydatabase.db.
sqlite3 mydatabase.dbCette commande ouvre l'interpréteur (shell) SQLite, en se connectant à la base de données mydatabase.db. Si le fichier de base de données n'existe pas, SQLite le créera.
Maintenant, créez une table nommée products avec des colonnes pour id, name et details. La colonne details stockera les données JSON sous forme de texte.
CREATE TABLE products (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT,
details TEXT
);Cette commande SQL crée la table products :
id : Un entier unique qui s'incrémente automatiquement pour chaque nouveau produit.name : Le nom du produit (par exemple, Laptop, Smartphone).details : Un champ texte pour stocker les données JSON du produit.Dans cette étape, vous allez insérer des données JSON dans la table products.
Insérons deux exemples d'enregistrements dans la table 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"}}'
);Ces instructions INSERT ajoutent deux lignes à la table products. La colonne details contient des données JSON sous forme de chaîne de texte.
Pour vérifier que les données ont été insérées correctement, exécutez la requête suivante :
SELECT * FROM products;Résultat attendu :
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"}}Ce résultat confirme que les données JSON ont été stockées avec succès dans la table products.
Étant donné que SQLite ne dispose pas de fonctions JSON intégrées, vous allez créer une fonction Python personnalisée pour extraire des données des chaînes JSON.
Tout d'abord, quittez le shell SQLite.
.exitCréez maintenant un fichier Python nommé json_extractor.py.
nano json_extractor.pyCollez le code Python suivant dans le fichier json_extractor.py :
## Importer les bibliothèques nécessaires
import sqlite3
import json
## Définir une fonction pour extraire une valeur d'une chaîne JSON à l'aide d'un chemin
def json_extract(json_str, path):
try:
## Analyser la chaîne JSON en un dictionnaire Python
json_data = json.loads(json_str)
## Diviser le chemin en composants (par exemple, 'specs.cpu' devient ['specs', 'cpu'])
path_components = path.split('.')
## Commencer avec l'objet JSON complet
value = json_data
## Parcourir l'objet JSON en utilisant les composants du chemin
for component in path_components:
## Obtenir la valeur pour le composant actuel
value = value.get(component)
## Retourner la valeur finale
return value
## Gérer les erreurs si le JSON est invalide ou si le chemin n'existe pas
except (json.JSONDecodeError, AttributeError, TypeError):
return None
## Définir une fonction pour se connecter à la base de données et enregistrer la fonction personnalisée
def connect_db(db_path):
## Se connecter à la base de données SQLite au chemin donné
conn = sqlite3.connect(db_path)
## Enregistrer la fonction Python 'json_extract' comme fonction SQL personnalisée
## "json_extract" est le nom en SQL, 2 est le nombre d'arguments, json_extract est la fonction Python
conn.create_function("json_extract", 2, json_extract)
## Retourner la connexion à la base de données
return conn
## Ce bloc s'exécute lorsque le script est exécuté directement
if __name__ == '__main__':
## Se connecter à la base de données et enregistrer la fonction
conn = connect_db('mydatabase.db')
## Créer un objet curseur pour exécuter des requêtes SQL
cursor = conn.cursor()
## Utiliser la fonction SQL personnalisée pour extraire la 'brand' de la colonne 'details'
cursor.execute("SELECT json_extract(details, 'brand') FROM products WHERE name = 'Laptop'")
## Récupérer le résultat et l'afficher
print(cursor.fetchone()[0])
## Utiliser la fonction SQL personnalisée pour extraire le 'cpu' de l'objet imbriqué 'specs'
cursor.execute("SELECT json_extract(details, 'specs.cpu') FROM products WHERE name = 'Laptop'")
## Récupérer le résultat et l'afficher
print(cursor.fetchone()[0])
## Fermer la connexion à la base de données
conn.close()Ce code Python définit une fonction json_extract qui prend une chaîne JSON et un chemin en entrée et renvoie la valeur à ce chemin. Il inclut également une fonction connect_db pour se connecter à la base de données SQLite et enregistrer la fonction json_extract.
json.loads(json_str) : Cette ligne analyse la chaîne JSON en un dictionnaire Python.path.split('.') : Ceci divise le chemin en une liste de composants. Par exemple, 'specs.cpu' devient ['specs', 'cpu'].Enregistrez le fichier et quittez nano.
Exécutez maintenant le script Python.
python3 json_extractor.pySortie attendue :
Dell
Intel i7Ce script se connecte à la base de données, enregistre la fonction json_extract, puis l'utilise pour extraire la marque et le processeur de l'ordinateur portable.
Dans cette étape, vous utiliserez la fonction personnalisée json_extract pour filtrer les données en fonction des valeurs contenues dans les champs JSON.
Ouvrez à nouveau le fichier json_extractor.py.
nano json_extractor.pyModifiez le fichier json_extractor.py pour inclure une fonction permettant d'interroger la base de données :
## Importer les bibliothèques nécessaires
import sqlite3
import json
## Définir une fonction pour extraire une valeur d'une chaîne JSON à l'aide d'un chemin
def json_extract(json_str, path):
try:
## Analyser la chaîne JSON en un dictionnaire Python
json_data = json.loads(json_str)
## Diviser le chemin en composants (par exemple, 'specs.cpu' devient ['specs', 'cpu'])
path_components = path.split('.')
## Commencer avec l'objet JSON complet
value = json_data
## Parcourir l'objet JSON en utilisant les composants du chemin
for component in path_components:
## Obtenir la valeur pour le composant actuel
value = value.get(component)
## Retourner la valeur finale
return value
## Gérer les erreurs si le JSON est invalide ou si le chemin n'existe pas
except (json.JSONDecodeError, AttributeError, TypeError):
return None
## Définir une fonction pour se connecter à la base de données et enregistrer la fonction personnalisée
def connect_db(db_path):
## Se connecter à la base de données SQLite au chemin donné
conn = sqlite3.connect(db_path)
## Enregistrer la fonction Python 'json_extract' comme fonction SQL personnalisée
conn.create_function("json_extract", 2, json_extract)
## Retourner la connexion à la base de données
return conn
## Définir une fonction pour filtrer les produits en fonction d'un champ JSON
def filter_products(db_path, json_path, value):
## Se connecter à la base de données
conn = connect_db(db_path)
## Créer un objet curseur
cursor = conn.cursor()
## Créer la requête SQL à l'aide d'une f-string pour filtrer par une valeur JSON
query = f"SELECT * FROM products WHERE json_extract(details, '{json_path}') = '{value}'"
## Exécuter la requête
cursor.execute(query)
## Récupérer tous les résultats correspondants
results = cursor.fetchall()
## Fermer la connexion à la base de données
conn.close()
## Retourner les résultats
return results
## Ce bloc s'exécute lorsque le script est exécuté directement
if __name__ == '__main__':
## Exemple d'utilisation :
## Filtrer les produits dont la marque est 'Dell'
dell_products = filter_products('mydatabase.db', 'brand', 'Dell')
print("Produits avec la marque 'Dell':", dell_products)
## Filtrer les produits dont le CPU est 'Intel i7'
intel_products = filter_products('mydatabase.db', 'specs.cpu', 'Intel i7')
print("Produits avec le CPU 'Intel i7':", intel_products)Ce code ajoute une fonction filter_products qui prend en entrée un chemin de base de données, un chemin JSON et une valeur. Il se connecte ensuite à la base de données, enregistre la fonction json_extract et exécute une requête pour trouver tous les produits où la valeur au chemin JSON spécifié correspond à la valeur donnée.
Enregistrez le fichier et quittez nano.
Exécutez maintenant le script Python.
python3 json_extractor.pySortie attendue :
Produits avec la marque 'Dell': [(1, 'Laptop', '{"brand": "Dell", "model": "XPS 13", "specs": {"cpu": "Intel i7", "memory": "16GB", "storage": "512GB SSD"}}')]
Produits avec le CPU 'Intel i7': [(1, 'Laptop', '{"brand": "Dell", "model": "XPS 13", "specs": {"cpu": "Intel i7", "memory": "16GB", "storage": "512GB SSD"}}')]Cette sortie montre les produits qui correspondent aux critères spécifiés.
Dans cette étape, vous apprendrez comment mettre à jour les valeurs dans les champs JSON.
Ouvrez le fichier json_extractor.py.
nano json_extractor.pyModifiez le fichier json_extractor.py pour inclure des fonctions de mise à jour du JSON et de la base de données :
## Importation des bibliothèques nécessaires
import sqlite3
import json
## Définition d'une fonction pour extraire une valeur d'une chaîne JSON à l'aide d'un chemin
def json_extract(json_str, path):
try:
## Analyse de la chaîne JSON en un dictionnaire Python
json_data = json.loads(json_str)
## Séparation du chemin en composants
path_components = path.split('.')
## Commence avec l'objet JSON complet
value = json_data
## Parcours de l'objet JSON
for component in path_components:
value = value.get(component)
return value
except (json.JSONDecodeError, AttributeError, TypeError):
return None
## Définition d'une fonction pour se connecter à la base de données et enregistrer la fonction personnalisée
def connect_db(db_path):
## Connexion à la base de données
conn = sqlite3.connect(db_path)
## Enregistrement de la fonction SQL personnalisée
conn.create_function("json_extract", 2, json_extract)
return conn
## Définition d'une fonction pour filtrer les produits en fonction d'un champ JSON
def filter_products(db_path, json_path, value):
## Connexion à la base de données
conn = connect_db(db_path)
## Création d'un objet curseur
cursor = conn.cursor()
## Création de la requête SQL pour filtrer par une valeur JSON
query = f"SELECT * FROM products WHERE json_extract(details, '{json_path}') = '{value}'"
## Exécution de la requête
cursor.execute(query)
## Récupération de tous les résultats correspondants
results = cursor.fetchall()
## Fermeture de la connexion
conn.close()
return results
## Définition d'une fonction pour mettre à jour une valeur dans une chaîne JSON
def update_json_value(json_str, path, new_value):
try:
## Analyse de la chaîne JSON en un dictionnaire Python
json_data = json.loads(json_str)
## Séparation du chemin en composants
path_components = path.split('.')
## Navigation vers le parent de la valeur cible
target = json_data
for i in range(len(path_components) - 1):
target = target.get(path_components[i])
## Mise à jour de la valeur cible
target[path_components[-1]] = new_value
## Conversion du dictionnaire Python en chaîne JSON
return json.dumps(json_data)
except (json.JSONDecodeError, AttributeError, TypeError):
## En cas d'erreur, retour de la chaîne JSON d'origine
return json_str
## Définition d'une fonction pour mettre à jour les détails d'un produit dans la base de données
def update_product_details(db_path, product_name, json_path, new_value):
## Connexion à la base de données
conn = sqlite3.connect(db_path)
## Création d'un objet curseur
cursor = conn.cursor()
## Récupération des détails actuels pour le produit spécifié
## Le '?' est un espace réservé pour empêcher l'injection SQL
cursor.execute("SELECT details FROM products WHERE name = ?", (product_name,))
result = cursor.fetchone()
## Si le produit n'existe pas, fermeture de la connexion et retour de False
if not result:
conn.close()
return False
## Récupération de la chaîne de détails JSON actuelle
current_details = result[0]
## Mise à jour de la chaîne JSON avec la nouvelle valeur
updated_details = update_json_value(current_details, json_path, new_value)
## Mise à jour de la base de données avec la nouvelle chaîne JSON
cursor.execute("UPDATE products SET details = ? WHERE name = ?", (updated_details, product_name))
## Validation des modifications dans la base de données
conn.commit()
## Fermeture de la connexion
conn.close()
return True
## Ce bloc s'exécute lorsque le script est exécuté directement
if __name__ == '__main__':
## Exemple d'utilisation : Mise à jour de la mémoire de l'ordinateur portable à 32 Go
update_product_details('mydatabase.db', 'Laptop', 'specs.memory', '32GB')
print("La mémoire de l'ordinateur portable a été mise à jour à 32 Go")
## Vérification de la mise à jour en récupérant à nouveau les données
conn = sqlite3.connect('mydatabase.db')
cursor = conn.cursor()
cursor.execute("SELECT details FROM products WHERE name = 'Laptop'")
## Récupération des détails mis à jour
updated_details = cursor.fetchone()[0]
print("Détails mis à jour de l'ordinateur portable :", updated_details)
## Fermeture de la connexion
conn.close()Ce code ajoute deux fonctions :
update_json_value : Cette fonction prend une chaîne JSON, un chemin et une nouvelle valeur en entrée. Elle analyse la chaîne JSON, met à jour la valeur au chemin spécifié et retourne la chaîne JSON mise à jour.update_product_details : Cette fonction prend un chemin de base de données, un nom de produit, un chemin JSON et une nouvelle valeur en entrée. Elle se connecte à la base de données, récupère les données JSON actuelles pour le produit, met à jour la valeur au chemin spécifié à l'aide de update_json_value, puis met à jour la base de données avec les données JSON modifiées.Enregistrez le fichier et quittez nano.
Exécutez maintenant le script Python.
python3 json_extractor.pySortie attendue :
La mémoire de l'ordinateur portable a été mise à jour à 32 Go
Détails mis à jour de l'ordinateur portable : {"brand": "Dell", "model": "XPS 13", "specs": {"cpu": "Intel i7", "memory": "32GB", "storage": "512GB SSD"}}Cette sortie confirme que la mémoire de l'ordinateur portable a été mise à jour à 32 Go dans la base de données.
Dans ce labo, vous avez appris à traiter des données JSON dans SQLite. Vous avez commencé par créer une base de données et une table pour stocker des données JSON. Ensuite, vous avez appris à insérer des données JSON dans la table. Vous avez créé une fonction Python personnalisée pour extraire des champs spécifiques des données JSON et vous avez utilisé cette fonction pour filtrer les données en fonction des valeurs contenues dans les champs JSON. Enfin, vous avez appris à mettre à jour les valeurs dans les champs JSON à l'aide d'une fonction Python personnalisée. Ces compétences fournissent une base pour gérer efficacement les données JSON dans les bases de données SQLite.
