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.
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_jsonDiese 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.dbDieser 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.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.
Da SQLite keine integrierten JSON-Funktionen hat, erstellen Sie eine benutzerdefinierte Python-Funktion, um Daten aus den JSON-Strings zu extrahieren.
Beenden Sie zuerst die SQLite-Shell.
.exitErstellen Sie nun eine Python-Datei namens json_extractor.py.
nano json_extractor.pyFügen Sie den folgenden Python-Code in die Datei json_extractor.py ein:
## Importieren der notwendigen Bibliotheken
import sqlite3
import json
## Definieren einer Funktion zum Extrahieren eines Werts aus einem JSON-String anhand eines Pfads
def json_extract(json_str, path):
try:
## Parsen des JSON-Strings in ein Python-Dictionary
json_data = json.loads(json_str)
## Aufteilen des Pfads in Komponenten (z. B. 'specs.cpu' wird zu ['specs', 'cpu'])
path_components = path.split('.')
## Beginnen mit dem vollständigen JSON-Objekt
value = json_data
## Durchlaufen des JSON-Objekts anhand der Pfadkomponenten
for component in path_components:
## Abrufen des Werts für die aktuelle Komponente
value = value.get(component)
## Zurückgeben des endgültigen Werts
return value
## Behandeln von Fehlern, wenn JSON ungültig ist oder der Pfad nicht existiert
except (json.JSONDecodeError, AttributeError, TypeError):
return None
## Definieren einer Funktion zum Verbinden mit der Datenbank und Registrieren der benutzerdefinierten Funktion
def connect_db(db_path):
## Verbinden mit der SQLite-Datenbank am angegebenen Pfad
conn = sqlite3.connect(db_path)
## Registrieren der Python-Funktion 'json_extract' als benutzerdefinierte SQL-Funktion
## "json_extract" ist der Name in SQL, 2 ist die Anzahl der Argumente, json_extract ist die Python-Funktion
conn.create_function("json_extract", 2, json_extract)
## Zurückgeben der Datenbankverbindung
return conn
## Dieser Block wird ausgeführt, wenn das Skript direkt ausgeführt wird
if __name__ == '__main__':
## Verbinden mit der Datenbank und Registrieren der Funktion
conn = connect_db('mydatabase.db')
## Erstellen eines Cursor-Objekts zum Ausführen von SQL-Abfragen
cursor = conn.cursor()
## Verwenden der benutzerdefinierten SQL-Funktion zum Extrahieren des 'brand' aus der Spalte 'details'
cursor.execute("SELECT json_extract(details, 'brand') FROM products WHERE name = 'Laptop'")
## Abrufen des Ergebnisses und Drucken
print(cursor.fetchone()[0])
## Verwenden der benutzerdefinierten SQL-Funktion zum Extrahieren der 'cpu' aus dem verschachtelten 'specs'-Objekt
cursor.execute("SELECT json_extract(details, 'specs.cpu') FROM products WHERE name = 'Laptop'")
## Abrufen des Ergebnisses und Drucken
print(cursor.fetchone()[0])
## Schließen der Datenbankverbindung
conn.close()Dieser Python-Code definiert eine Funktion json_extract, die einen JSON-String und einen Pfad als Eingabe nimmt und den Wert an diesem Pfad zurückgibt. Er enthält auch eine Funktion connect_db, um eine Verbindung zur SQLite-Datenbank herzustellen und die Funktion json_extract zu registrieren.
json.loads(json_str): Diese Zeile parst den JSON-String in ein Python-Dictionary.path.split('.'): Dies teilt den Pfad in eine Liste von Komponenten auf. Zum Beispiel wird aus 'specs.cpu' ['specs', 'cpu'].Speichern Sie die Datei und beenden Sie nano.
Führen Sie nun das Python-Skript aus.
python3 json_extractor.pyErwartete Ausgabe:
Dell
Intel i7Dieses Skript stellt eine Verbindung zur Datenbank her, registriert die Funktion json_extract und verwendet sie dann, um die Marke und die CPU des Laptops zu extrahieren.
In diesem Schritt verwenden Sie die benutzerdefinierte Funktion json_extract, um Daten basierend auf Werten in JSON-Feldern zu filtern.
Öffnen Sie erneut die Datei json_extractor.py.
nano json_extractor.pyÄndern Sie die Datei json_extractor.py, um eine Funktion zum Abfragen der Datenbank hinzuzufügen:
## Importieren der notwendigen Bibliotheken
import sqlite3
import json
## Definieren einer Funktion zum Extrahieren eines Wertes aus einem JSON-String mithilfe eines Pfades
def json_extract(json_str, path):
try:
## Parsen des JSON-Strings in ein Python-Dictionary
json_data = json.loads(json_str)
## Aufteilen des Pfades in Komponenten (z. B. 'specs.cpu' wird zu ['specs', 'cpu'])
path_components = path.split('.')
## Beginnen mit dem vollständigen JSON-Objekt
value = json_data
## Durchlaufen des JSON-Objekts mithilfe der Pfadkomponenten
for component in path_components:
## Abrufen des Wertes für die aktuelle Komponente
value = value.get(component)
## Zurückgeben des endgültigen Wertes
return value
## Behandeln von Fehlern, wenn JSON ungültig ist oder der Pfad nicht existiert
except (json.JSONDecodeError, AttributeError, TypeError):
return None
## Definieren einer Funktion zum Verbinden mit der Datenbank und Registrieren der benutzerdefinierten Funktion
def connect_db(db_path):
## Verbinden mit der SQLite-Datenbank am angegebenen Pfad
conn = sqlite3.connect(db_path)
## Registrieren der Python-Funktion 'json_extract' als benutzerdefinierte SQL-Funktion
conn.create_function("json_extract", 2, json_extract)
## Zurückgeben der Datenbankverbindung
return conn
## Definieren einer Funktion zum Filtern von Produkten basierend auf einem JSON-Feld
def filter_products(db_path, json_path, value):
## Verbinden mit der Datenbank
conn = connect_db(db_path)
## Erstellen eines Cursor-Objekts
cursor = conn.cursor()
## Erstellen der SQL-Abfrage mithilfe eines f-Strings zum Filtern nach einem JSON-Wert
query = f"SELECT * FROM products WHERE json_extract(details, '{json_path}') = '{value}'"
## Ausführen der Abfrage
cursor.execute(query)
## Abrufen aller übereinstimmenden Ergebnisse
results = cursor.fetchall()
## Schließen der Datenbankverbindung
conn.close()
## Zurückgeben der Ergebnisse
return results
## Dieser Block wird ausgeführt, wenn das Skript direkt ausgeführt wird
if __name__ == '__main__':
## Beispielverwendung:
## Filtern nach Produkten, bei denen die Marke 'Dell' ist
dell_products = filter_products('mydatabase.db', 'brand', 'Dell')
print("Produkte mit Marke 'Dell':", dell_products)
## Filtern nach Produkten, bei denen die CPU 'Intel i7' ist
intel_products = filter_products('mydatabase.db', 'specs.cpu', 'Intel i7')
print("Produkte mit CPU 'Intel i7':", intel_products)Dieser Code fügt eine Funktion filter_products hinzu, die einen Datenbankpfad, einen JSON-Pfad und einen Wert als Eingabe nimmt. Anschließend verbindet er 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 gegebenen Wert übereinstimmt.
Speichern Sie die Datei und beenden Sie nano.
Führen Sie nun das Python-Skript aus.
python3 json_extractor.pyErwartete Ausgabe:
Produkte mit Marke 'Dell': [(1, 'Laptop', '{"brand": "Dell", "model": "XPS 13", "specs": {"cpu": "Intel i7", "memory": "16GB", "storage": "512GB SSD"}}')]
Produkte mit 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.
In diesem Schritt lernen Sie, wie Sie Werte innerhalb von JSON-Feldern aktualisieren.
Öffnen Sie die Datei json_extractor.py.
nano json_extractor.pyModifizieren Sie die Datei json_extractor.py, um Funktionen für die Aktualisierung des JSON und der Datenbank hinzuzufügen:
## Importieren der notwendigen Bibliotheken
import sqlite3
import json
## Definieren einer Funktion zum Extrahieren eines Wertes aus einem JSON-String über einen Pfad
def json_extract(json_str, path):
try:
## Parsen des JSON-Strings in ein Python-Dictionary
json_data = json.loads(json_str)
## Aufteilen des Pfades in Komponenten
path_components = path.split('.')
## Starten mit dem vollständigen JSON-Objekt
value = json_data
## Durchlaufen des JSON-Objekts
for component in path_components:
value = value.get(component)
return value
except (json.JSONDecodeError, AttributeError, TypeError):
return None
## Definieren einer Funktion zum Verbinden mit der Datenbank und Registrieren der benutzerdefinierten Funktion
def connect_db(db_path):
## Verbinden mit der Datenbank
conn = sqlite3.connect(db_path)
## Registrieren der benutzerdefinierten SQL-Funktion
conn.create_function("json_extract", 2, json_extract)
return conn
## Definieren einer Funktion zum Filtern von Produkten basierend auf einem JSON-Feld
def filter_products(db_path, json_path, value):
## Verbinden mit der Datenbank
conn = connect_db(db_path)
## Erstellen eines Cursor-Objekts
cursor = conn.cursor()
## Erstellen der SQL-Abfrage zum Filtern nach einem JSON-Wert
query = f"SELECT * FROM products WHERE json_extract(details, '{json_path}') = '{value}'"
## Ausführen der Abfrage
cursor.execute(query)
## Abrufen aller übereinstimmenden Ergebnisse
results = cursor.fetchall()
## Schließen der Verbindung
conn.close()
return results
## Definieren einer Funktion zum Aktualisieren eines Wertes innerhalb eines JSON-Strings
def update_json_value(json_str, path, new_value):
try:
## Parsen des JSON-Strings in ein Python-Dictionary
json_data = json.loads(json_str)
## Aufteilen des Pfades in Komponenten
path_components = path.split('.')
## Navigieren zum übergeordneten Element des Zielwertes
target = json_data
for i in range(len(path_components) - 1):
target = target.get(path_components[i])
## Aktualisieren des Zielwertes
target[path_components[-1]] = new_value
## Konvertieren des Python-Dictionaries zurück in einen JSON-String
return json.dumps(json_data)
except (json.JSONDecodeError, AttributeError, TypeError):
## Bei einem Fehler wird der ursprüngliche JSON-String zurückgegeben
return json_str
## Definieren einer Funktion zum Aktualisieren der Details eines Produkts in der Datenbank
def update_product_details(db_path, product_name, json_path, new_value):
## Verbinden mit der Datenbank
conn = sqlite3.connect(db_path)
## Erstellen eines Cursor-Objekts
cursor = conn.cursor()
## Abrufen der aktuellen Details für das angegebene Produkt
## Das '?' ist ein Platzhalter, um SQL-Injection zu verhindern
cursor.execute("SELECT details FROM products WHERE name = ?", (product_name,))
result = cursor.fetchone()
## Wenn das Produkt nicht existiert, wird die Verbindung geschlossen und False zurückgegeben
if not result:
conn.close()
return False
## Abrufen des aktuellen JSON-Details-Strings
current_details = result[0]
## Aktualisieren des JSON-Strings mit dem neuen Wert
updated_details = update_json_value(current_details, json_path, new_value)
## Aktualisieren der Datenbank mit dem neuen JSON-String
cursor.execute("UPDATE products SET details = ? WHERE name = ?", (updated_details, product_name))
## Übernehmen der Änderungen in der Datenbank
conn.commit()
## Schließen der Verbindung
conn.close()
return True
## Dieser Block wird ausgeführt, wenn das Skript direkt ausgeführt wird
if __name__ == '__main__':
## Beispielverwendung: Aktualisieren des Laptop-Speichers auf 32GB
update_product_details('mydatabase.db', 'Laptop', 'specs.memory', '32GB')
print("Laptop-Speicher auf 32GB aktualisiert")
## Überprüfen der Aktualisierung durch erneutes Abrufen der Daten
conn = sqlite3.connect('mydatabase.db')
cursor = conn.cursor()
cursor.execute("SELECT details FROM products WHERE name = 'Laptop'")
## Abrufen der aktualisierten Details
updated_details = cursor.fetchone()[0]
print("Aktualisierte Laptop-Details:", updated_details)
## Schließen der Verbindung
conn.close()Dieser Code fügt zwei Funktionen hinzu:
update_json_value: Diese Funktion nimmt einen JSON-String, einen Pfad und einen neuen Wert als Eingabe. Sie parst den JSON-String, aktualisiert den Wert am angegebenen Pfad und gibt den aktualisierten JSON-String zurück.update_product_details: Diese Funktion nimmt einen Datenbankpfad, einen Produktnamen, einen JSON-Pfad und einen neuen Wert als Eingabe. Sie verbindet sich mit der Datenbank, ruft die aktuellen JSON-Daten für das Produkt ab, aktualisiert den Wert am angegebenen Pfad mithilfe von 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.pyErwartete Ausgabe:
Laptop-Speicher auf 32GB aktualisiert
Aktualisierte Laptop-Details: {"brand": "Dell", "model": "XPS 13", "specs": {"cpu": "Intel i7", "memory": "32GB", "storage": "512GB SSD"}}Diese Ausgabe bestätigt, dass der Speicher des Laptops in der Datenbank auf 32GB aktualisiert wurde.
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.
