Traitement de JSON avec SQLite

Introduction

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.

Créer une base de données et une table

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_json

Ces 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.db

Cette 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.

Insérer des données JSON

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.

Extraire des champs JSON avec une fonction personnalisée

É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.

.exit

Créez maintenant un fichier Python nommé json_extractor.py.

nano json_extractor.py

Collez 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'].
La boucle itère sur les composants du chemin, accédant aux valeurs imbriquées dans les données JSON.

Enregistrez le fichier et quittez nano.

Exécutez maintenant le script Python.

python3 json_extractor.py

Sortie attendue :

Dell
Intel i7

Ce 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.

Filtrer les données à l'aide de requêtes JSON

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.py

Modifiez 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.py

Sortie 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.

Mettre à jour les valeurs JSON

Dans cette étape, vous apprendrez comment mettre à jour les valeurs dans les champs JSON.

Ouvrez le fichier json_extractor.py.

nano json_extractor.py

Modifiez 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.py

Sortie 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.

Résumé

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.