SQLite JSON Verarbeitung

Einführung

In diesem Lab lernen Sie, wie Sie JSON-Daten innerhalb von SQLite verarbeiten. Sie werden untersuchen, wie Sie JSON-Daten in einer SQLite-Datenbank speichern, extrahieren, filtern und aktualisieren können. Dieses Lab bietet eine praktische Einführung in die Arbeit mit JSON-Daten in SQLite, eine Fähigkeit, die in der modernen Datenverwaltung zunehmend an Bedeutung gewinnt.

Skills Graph

Erstellen einer Datenbank und Tabelle

In diesem Schritt erstellen Sie eine SQLite-Datenbank und eine Tabelle zum Speichern von JSON-Daten. SQLite ist eine schlanke (lightweight) Datenbank, die Daten in einer einzigen Datei speichert, was die Verwaltung vereinfacht.

Öffnen Sie zunächst Ihr Terminal. Der Standardpfad ist /home/labex/project.

Lassen Sie uns nun ein Verzeichnis erstellen, in dem wir unsere Datenbank speichern.

mkdir sqlite_json
cd sqlite_json

Diese Befehle erstellen ein Verzeichnis namens sqlite_json und ändern dann das aktuelle Verzeichnis in dieses. Dies sorgt für eine übersichtliche Organisation Ihrer Projektdateien.

Erstellen Sie als Nächstes eine SQLite-Datenbank namens mydatabase.db.

sqlite3 mydatabase.db

Dieser Befehl öffnet die SQLite-Shell und verbindet sich mit der Datenbank mydatabase.db. Wenn die Datenbankdatei nicht existiert, wird sie von SQLite erstellt.

Erstellen Sie nun eine Tabelle namens products mit Spalten für id, name und details. Die Spalte details speichert JSON-Daten als Text.

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

Dieser SQL-Befehl erstellt die Tabelle products:

• id: Eine eindeutige Ganzzahl (Integer), die für jedes neue Produkt automatisch inkrementiert wird.
• name: Der Name des Produkts (z. B. Laptop, Smartphone).
• details: Ein Textfeld zum Speichern der JSON-Daten für das Produkt.

JSON-Daten einfügen

In diesem Schritt fügen Sie JSON-Daten in die Tabelle products ein.

Lassen Sie uns zwei Beispiel-Datensätze in die Tabelle products einfügen.

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

Diese INSERT-Anweisungen fügen der Tabelle products zwei Zeilen hinzu. Die Spalte details enthält JSON-Daten als Textzeichenfolge (text string).

Um zu überprüfen, ob die Daten korrekt eingefügt wurden, führen Sie die folgende Abfrage (query) aus:

SELECT * FROM products;

Erwartete Ausgabe (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"}}

Diese Ausgabe bestätigt, dass die JSON-Daten erfolgreich in der Tabelle products gespeichert wurden.

JSON-Felder mit einer benutzerdefinierten Funktion extrahieren

Da SQLite keine integrierten JSON-Funktionen hat, erstellen Sie eine benutzerdefinierte Python-Funktion, um Daten aus den JSON-Zeichenketten (JSON strings) zu extrahieren.

Verlassen Sie zunächst die SQLite-Shell.

.exit

Erstellen Sie nun eine Python-Datei namens json_extractor.py.

nano json_extractor.py

Fügen Sie den folgenden Python-Code in die Datei json_extractor.py ein:

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()

Dieser Python-Code definiert eine Funktion json_extract, die eine JSON-Zeichenkette und einen Pfad (path) als Eingabe entgegennimmt und den Wert an diesem Pfad zurückgibt. Er enthält auch eine connect_db-Funktion, um sich mit der SQLite-Datenbank zu verbinden und die Funktion json_extract zu registrieren.

• json.loads(json_str): Diese Zeile parst die JSON-Zeichenkette in ein Python-Dictionary.
• path.split('.'): Dies teilt den Pfad in eine Liste von Komponenten auf. Zum Beispiel wird 'specs.cpu' zu ['specs', 'cpu'].
• Die Schleife iteriert durch die Pfadkomponenten und greift auf verschachtelte Werte (nested values) in den JSON-Daten zu.

Speichern Sie die Datei und beenden Sie nano.

Führen Sie nun das Python-Skript aus.

python3 json_extractor.py

Erwartete Ausgabe (Expected Output):

Dell
Intel i7

Dieses Skript verbindet sich mit der Datenbank, registriert die Funktion json_extract und verwendet sie dann, um die Marke (brand) und die CPU des Laptops zu extrahieren.

Daten mit JSON-Abfragen filtern

In diesem Schritt verwenden Sie die benutzerdefinierte Funktion json_extract, um Daten basierend auf Werten innerhalb der JSON-Felder zu filtern.

Öffnen Sie die Datei json_extractor.py erneut.

nano json_extractor.py

Ändern Sie die Datei json_extractor.py, um eine Funktion zum Abfragen (querying) der Datenbank einzufügen:

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)

Dieser Code fügt eine filter_products-Funktion hinzu, die einen Datenbankpfad (database path), einen JSON-Pfad und einen Wert als Eingabe entgegennimmt. Anschließend verbindet sie sich mit der Datenbank, registriert die Funktion json_extract und führt eine Abfrage aus, um alle Produkte zu finden, bei denen der Wert am angegebenen JSON-Pfad mit dem angegebenen Wert übereinstimmt.

Speichern Sie die Datei und beenden Sie nano.

Führen Sie nun das Python-Skript aus.

python3 json_extractor.py

Erwartete Ausgabe (Expected 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"}}')]

Diese Ausgabe zeigt die Produkte, die den angegebenen Kriterien entsprechen.

JSON-Werte aktualisieren

In diesem Schritt lernen Sie, wie Sie Werte innerhalb der JSON-Felder aktualisieren können.

Öffnen Sie die Datei json_extractor.py.

nano json_extractor.py

Ändern Sie die Datei json_extractor.py, um Funktionen zum Aktualisieren des JSON und der Datenbank einzufügen:

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()

Dieser Code fügt zwei Funktionen hinzu:

• update_json_value: Diese Funktion nimmt eine JSON-Zeichenkette, einen Pfad und einen neuen Wert als Eingabe entgegen. Sie parst die JSON-Zeichenkette, aktualisiert den Wert am angegebenen Pfad und gibt die aktualisierte JSON-Zeichenkette zurück.
• update_product_details: Diese Funktion nimmt einen Datenbankpfad, einen Produktnamen, einen JSON-Pfad und einen neuen Wert als Eingabe entgegen. Sie verbindet sich mit der Datenbank, ruft die aktuellen JSON-Daten für das Produkt ab, aktualisiert den Wert am angegebenen Pfad mit update_json_value und aktualisiert dann die Datenbank mit den geänderten JSON-Daten.

Speichern Sie die Datei und beenden Sie nano.

Führen Sie nun das Python-Skript aus.

python3 json_extractor.py

Erwartete Ausgabe (Expected Output):

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

Diese Ausgabe bestätigt, dass der Arbeitsspeicher (memory) des Laptops in der Datenbank auf 32 GB aktualisiert wurde.

Zusammenfassung

In diesem Lab haben Sie gelernt, wie man JSON-Daten innerhalb von SQLite verarbeitet. Sie begannen mit der Erstellung einer Datenbank und einer Tabelle zum Speichern von JSON-Daten. Anschließend lernten Sie, wie man JSON-Daten in die Tabelle einfügt. Sie erstellten eine benutzerdefinierte Python-Funktion, um bestimmte Felder aus den JSON-Daten zu extrahieren, und verwendeten diese Funktion, um Daten basierend auf Werten innerhalb der JSON-Felder zu filtern. Abschließend lernten Sie, wie man Werte innerhalb der JSON-Felder mithilfe einer benutzerdefinierten Python-Funktion aktualisiert. Diese Fähigkeiten bilden eine Grundlage für die effektive Verwaltung von JSON-Daten innerhalb von SQLite-Datenbanken.