Kurs
Datenbearbeitung in SQL
4 Std.
335K
Was ist SQLite?
Wenn du relationale Datenbanksysteme kennst, hast du wahrscheinlich schon von Schwergewichten wie MySQL, SQL Server oder PostgreSQL gehört. SQLite ist jedoch ein weiteres nützliches RDBMS, das sehr leicht einzurichten und zu bedienen ist und einige Besonderheiten gegenüber anderen relationalen Datenbanken bietet. Dazu gehören:
- Kein Server nötig: Es gibt keine Serverprozesse, die gestartet, gestoppt oder konfiguriert werden müssen. Es braucht keine Datenbankadministration für Instanzen oder Zugriffsrechte.
- Einfache Datenbankdateien: Eine SQLite-Datenbank ist eine einzelne, ganz normale Datei auf der Festplatte und kann in jedem Verzeichnis liegen. Dadurch ist das Teilen unkompliziert, denn die Datei lässt sich einfach auf einen USB-Stick kopieren oder per E-Mail versenden.
- Manifest Typing: Die meisten SQL-Datenbank-Engines setzen auf statische Typisierung. Das heißt, in einer Spalte dürfen nur Werte des zugeordneten Datentyps gespeichert werden. SQLite verwendet hingegen Manifest Typing und erlaubt es, in jede Spalte beliebige Datentypen und beliebige Mengen an Daten zu schreiben, unabhängig vom deklarierten Spaltentyp. Beachte, dass es Ausnahmen gibt, etwa Spalten mit Integer Primary Key: Diese dürfen nur ganze Zahlen enthalten.
Wie so oft gibt es im Vergleich zu anderen Datenbanken auch Einschränkungen. So unterstützt SQLite zum Beispiel keine RIGHT oder FULL OUTER JOINS, und die Serverlosigkeit kann in puncto Sicherheit und Schutz vor Datenmissbrauch nachteilig sein. Trotzdem ist SQLite ein sehr leistungsfähiges Werkzeug, das in bestimmten Situationen extrem hilfreich ist. Ich habe zum Beispiel in einem Social-Media-Analyseprojekt eine SQLite-Datenbank verwendet, um eine $edge \space list^{1}$ mit Millionen von Twitter-Nutzern einfach und effizient zu speichern.
In diesem Einsteiger-Tutorial zeige ich dir daher, wie du mit einer SQLite-Datenbank arbeitest. Wir gehen die Einrichtung von SQLite, das Erstellen und Bearbeiten von Tabellen, SQLite-Dot-Befehle und einfache SQL-Abfragen durch, um die Daten in einer SQLite-Datenbank zu manipulieren.
$^{1}$ Eine Edge- oder Adjazenzliste ist eine Sammlung ungeordneter Listen zur Darstellung eines Graphen in der Netzwerkanalyse. Jede Liste beschreibt die Nachbarn eines Knotens bzw. einer Ecke im Graphen.
SQLite installieren und einrichten
Als Erstes schauen wir uns an, wie du SQLite auf deinen Rechner bekommst. Das ist denkbar einfach. Im SQLite mit Python Tutorial von Sayak Paul siehst du, dass es mit DB Browser for SQLite ein GUI-Tool gibt, das dir den Einstieg erleichtert. Der Abwechslung halber zeige ich dir hier jedoch die Nutzung des Kommandozeilentools, das ich oft sogar bevorzuge.
Wenn du Windows nutzt, gehe auf diese Seite und lade sqlite-tools-win32-x86-3270200.zip herunter. Nach dem Entpacken solltest du diese drei Dateien sehen:
Danach startest du die SQLite-Kommandozeile einfach durch einen Klick auf die Anwendung sqlite3. Es öffnet sich ein Fenster wie dieses:
Standardmäßig verwendet SQLite eine flüchtige In-Memory-Datenbank. Das willst du in den meisten Fällen nicht. Stattdessen erstellen wir mit dem Dot-Befehl .open und dem gewünschten Namen eine neue Datenbankdatei (siehe unten):
Schon ist die neue Datenbank angelegt. Wichtig: Hänge an den gewünschten Namen .db an, damit eine Datenbankdatei entsteht. Wir nennen die Datenbank Tweet_Data.db, da ich Tweets nutze, um die Tabellen zu befüllen.
Tabellen erstellen
Mit der erstellten Datenbank können wir unsere Tweet-Tabelle anlegen. Dafür gibt es zwei Wege. Erstens kannst du einfach eine .csv-Datei importieren und daraus eine Tabelle machen. Dazu setzt du SQLite mit dem Dot-Befehl .mode auf csv und nutzt anschließend .import, das eine Datei (hier eine csv im gleichen Ordner, es kann aber jede csv auf deinem Rechner sein, wenn du den vollständigen Pfad angibst) und einen Tabellennamen deiner Wahl erwartet. Mit .headers gefolgt von on blenden wir außerdem die Spaltenüberschriften ein. (Siehe unten)
Danach kannst du mit .tables alle Tabellen der Datenbankdatei anzeigen lassen und mit .schema gefolgt vom Tabellennamen die Struktur der Tabelle. Außerdem kannst du mit einer einfachen Abfrage wie SELECT $$ FROM Table_Name* prüfen, ob die Daten vorhanden sind.
Neben dem Erstellen über den Import von Dateien wie CSVs kannst du Tabellen auch mit CREATE TABLE anlegen und anschließend mit INSERT INTO Zeilen einfügen. Im folgenden Beispiel siehst du, wie du eine Tabelle von Grund auf erstellst und zwei Zeilen hinzufügst.
Hinweis: In der SQLite-Kommandozeile kannst du wie im Beispiel eine Abfrage über mehrere Zeilen schreiben, indem du Enter drückst. Ausgeführt wird sie erst, wenn am Ende ein Semikolon steht und du Enter drückst.
Aus SQLite exportieren
Wenn du die Ergebnisse einer Abfrage in eine externe Datei wie eine csv exportieren willst, geht das in SQLite ebenfalls problemlos. Für einen sauberen CSV-Export gehst du ähnlich vor wie beim Import: Zuerst .header on und .mode csv setzen. Dann mit dem Dot-Befehl .output den gewünschten CSV-Dateinamen angeben, anschließend die gewünschte SQL-Abfrage ausführen und SQLite mit .exit verlassen. Exportieren wir nun die zuvor erstellte Tabelle als CSV.
Hier siehst du die neu erstellte CSV-Datei mit den Daten aus Sample_Tweets_2.
Tabellen aktualisieren
Neben dem Erstellen und Exportieren von Tabellen kannst du mit den SQL-Anweisungen UPDATE/SET sowie DROP TABLE Zeilen aktualisieren oder eine Tabelle komplett löschen. Im folgenden Beispiel aktualisieren wir den zweiten Tweet in der Beispiel-Tabelle Sample_Tweets_2.
Und nun verabschieden wir uns von der Beispiel-Tabelle Sample_Tweets_2 und löschen sie mit DROP TABLE aus der Datenbankdatei:
Twitter-Daten mit einer SQLite-Datenbank verarbeiten
Jetzt kennst du die Essentials, um mit einer SQLite-Datenbank zu arbeiten und Tabellen zu manipulieren. Der nächste Schritt ist, mit SQL-Abfragen nützliche Informationen zu gewinnen. Im Folgenden zeige ich ein paar relativ einfache Abfragen aus meinem Projekt zur Twitter-Social-Media-Analyse. Du kannst es als kleine Fallstudie sehen, die eine praktische Anwendung von SQLite zeigt.
Starten wir einfach: Finde alle eindeutigen Screen Names der Twitter-Nutzenden in der Tabelle Sample_Tweets. In meinem Projekt war es wichtig, alle eindeutigen Nutzer zu identifizieren, von denen Tweets gesammelt wurden, um anschließend ihre Follower abzurufen und damit die eingangs erwähnte Edge-Liste zu erstellen.
26.860 eindeutige Nutzende in der Datenbank. Das ist eine hervorragende Stichprobe, zumal sich die beobachteten Themen auf Website-Lokalisierung und Übersetzung bezogen. Schauen wir uns mit einer etwas komplexeren SQL-Abfrage die Top 20 Nutzenden mit den meisten Tweets an (Retweets ausgeschlossen):
Sieh dir das an: Die meisten Tweets stammen von einem Bot! Wie sich herausstellte, ist das letzte Mitglied dieser Liste, UweMuegge, laut verschiedenen Netzwerkmesszahlen durchaus ein Influencer. Der wichtigste war er jedoch definitiv nicht.
Zum Schluss werfen wir mit einer weiteren SQL-Abfrage einen Blick auf einige Tweets von UweMuegge:
Zwei Hauptthemen stechen heraus: Stellenanzeigen in der Übersetzung und Konferenzen zur Übersetzung. Kein Wunder, dass ihm Fachleute aus dem Bereich auf Twitter folgen.
Außerdem fällt im Screenshot auf: Bei großen Textfeldern stößt die SQLite-Kommandozeile an ihre Grenzen. Die Ausgabe ist schwer lesbar. In solchen Fällen ist DB Browser for SQLite eine gute Wahl.
Zusammenfassung
In diesem Tutorial hast du gelernt, wie du SQLite-Datenbanken erstellst und Tabellen mit Dot-Befehlen und SQL-Anweisungen bearbeitest. Außerdem hast du eine kleine Fallstudie aus der Praxis gesehen, in der ich eine SQLite-Datenbank für Twitter-Daten verwendet habe. Eine ganze Menge—Glückwunsch!
Besonders nützlich ist SQLite in Kombination mit R und Python. In Python kannst du SQLite-Datenbanken über das Modul sqlite3 ansprechen (mehr dazu im Tutorial SQLite mit Python) oder in R mit RSQLite. Die eingangs erwähnte Edge-Liste der Follower von Twitter-Nutzenden liegt zum Beispiel in einer SQLite-Datenbank, die ich mit RSQLite erstellt habe, indem ich die Followernetzwerke der einzelnen Nutzer sukzessive an eine Tabelle angehängt habe. In diesem Projektschritt war SQLite entscheidend, denn Stromausfälle oder automatische Windows-Updates können den Rechner hart ausschalten und dich dazu zwingen, die Sammlung der Followernetzwerke von vorn zu beginnen. Wegen der Rate Limits der Twitter-API kann das rund vier Wochen dauern—ein Neustart bei Null wäre also äußerst ungünstig. Wenn du die Followernetzwerke jedoch fortlaufend in einer SQLite-Datenbank speicherst, kannst du nach einem erzwungenen Shutdown genau bei der Nutzer-ID weitermachen, die zuletzt in der Datenbank gespeichert wurde. Das spart enorm Zeit.
Das ist nur ein Beispiel dafür, wie SQLite-Datenbanken ein starkes Werkzeug im Repertoire von Data Scientists sein können. Ich empfehle dir, dich über SQLite und SQL über dieses Tutorial hinaus weiter zu informieren. Bleib dran—deiner Lernkurve sind keine Grenzen gesetzt!
Wenn du mehr über SQL lernen möchtest, schau dir den DataCamp-Kurs Introduction to Relational Databases in SQL an.