SQLite-Tutorial: Installieren, Einstellen und Arbeiten

8 min read

Kennen Sie sich mit SQLite aus? Können Sie mit diesem Tool problemlos arbeiten? Wenn nein, dann haben wir für Sie ein Tutorial zum Installieren, Einstellen und Arbeiten mit dieser Bibliothek erstellt.

SQLite-Tutorial

SQLite ist eine C-Bibliothek, die eine eingebettete Datenbankserver-Software enthält. Programme, die mit der SQLite verlinkt sind, können Datenbanken ohne einen separaten Server nutzen. Sie wurde von Richard Hipp entwickelt und erschien zum ersten Mal am 17. August 2000. Heutzutage ist sie die zweitbeliebteste Software der Welt. Sie wird in vielen wichtigen Systemen wie beispielsweise dem Airbus A350 verwendet. Darüber hinaus ist SQLite zusammen mit allen ihren Bibliotheken nur ein paar Megabyte groß.

Installieren des SQLite 3-Clients

Um SQLite 3 zu installieren, muss man folgendes in die Befehlszeile eingeben:

$ sudo apt install sqlite3

Client-Einstellung

Wenn Sie die CLI SQLite 3-Standardeinstellungen ändern möchten, sollten Sie die ~ / .sqliterc-Dateien in einem Katalog redigieren. Dies ist bequem für das Speichern von Einstellungen (Rezepten), die Sie oft verwenden. Folgendes Beispiel hilft Ihnen:

$ vi ~/.sqliterc
.headers on
.mode column
.nullvalue 
.prompt ">"
.timer on

Importieren der CSV-Dateien

Es gibt zwei Befehle, mit Hilfe derer Sie CSV-Daten in SQLite 3 importieren können. Der erste Befehl überträgt den Client in eine CSV-Datei und der zweite importiert die Daten aus einer CSV-Datei. Der Befehl .separator kann das angenommene Trennzeichen ändern.

Je nachdem ob eine Zieltabelle schon erstellt ist oder nicht, werden die Zeilen behandelt. Wenn sie noch nicht bereit ist, wird in der ersten Zeile der CSV-Dateien die Benennung jeder Spalte stehen. Wenn die Zieltabelle schon existiert, dann fügen Sie alle Datenzeilen Ihrer fertigen Tabelle hinzu.

Nachfolgend ist ein Beispiel, in dem Informationen zu mehreren Flughäfen in Wales in eine CSV-Datei mit verschiedenen Kodierungen zusammengestellt werden.

$ vi airports.csv
都市,IATA,ICAO,空港
Aberporth,,EGFA,Aberporth 空港
Anglesey,,EGOQ,RAF Mona
Anglesey,,EGOV,RAF Valley
カナーボン,,EGCK,カナーボン空港
カーディフ,CWL,EGFF,カーディフ国際空港
カーディフ,,EGFC,Tremorfa ヘリポート
チェスター,CEG,EGNR,Hawarden 空港
Haverfordwest,HAW,EGFE,Haverfordwest 小型飛行場
Llanbedr,,EGOD,Llanbedr 空港
Pembrey,,EGFP,Pembrey 空港
St Athan,DGX,EGDX,RAF Saint Athan
スウォンジ,SWS,EGFH,スウォンジ空港
ウェルシュプール,,EGCW,ウェルシュプール空港

So wurde eine neue Datenbank, die airport.db benannt wurde, im SQLite 3-Client gestartet. Da es diese Datenbankdatei noch nicht gab, erstellt SQLite 3 sie automatisch.

$ sqlite3 airports.db

Ihre nächsten Schritte sind wie folgt: Sie schalten den Client in den CSV-Modus um, setzen das Komma mit Hilfe der Anführungszeichen und importieren die Datei airport.csv.

.mode csv
.separator ","
.import airports.csv airports

Jetzt bekommen Sie die Möglichkeit, den Befehl schema in der Tabelle der neuen Flughäfen zu starten. Sie sehen zwei Spalten mit japanischen Namen und zwei weitere Spalten, in denen ASCII-Zeichen verwendet werden.

.schema airports
CREATE TABLE airports(
  "都市" TEXT,
  "IATA" TEXT,
  "ICAO" TEXT,
  "空港" TEXT
);

Ohne Probleme kann man Befehle geben, indem man Kodierungen mischt.

$ echo "SELECT ICAO, 空港 FROM airports;" \
    | sqlite3 airports.db
EGFA|Aberporth 空港
EGOQ|RAF Mona
EGOV|RAF Valley
EGCK|カナーボン空港
EGFF|カーディフ国際空港
EGFC|Tremorfa ヘリポート
EGNR|Hawarden 空港
EGFE|Haverfordwest 小型飛行場
EGOD|Llanbedr 空港
EGFP|Pembrey 空港
EGDX|RAF Saint Athan
EGFH|スウォンジ空港
EGCW|ウェルシュプール空港

Außerdem gibt es die Möglichkeit, die Datenbank im SQL mit Hilfe nur eines Befehls zurückzusetzen.

PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE airports(
  "都市" TEXT,
  "IATA" TEXT,
  "ICAO" TEXT,
  "空港" TEXT
);
INSERT INTO "airports" VALUES('Aberporth','','EGFA','Aberporth 空港');
INSERT INTO "airports" VALUES('Anglesey','','EGOQ','RAF Mona');
INSERT INTO "airports" VALUES('Anglesey','','EGOV','RAF Valley');
INSERT INTO "airports" VALUES('カナーボン','','EGCK','カナーボン空港');
INSERT INTO "airports" VALUES('カーディフ','CWL','EGFF','カーディフ国際空港');
INSERT INTO "airports" VALUES('カーディフ','','EGFC','Tremorfa ヘリポート');
INSERT INTO "airports" VALUES('チェスター','CEG','EGNR','Hawarden 空港');
INSERT INTO "airports" VALUES('Haverfordwest','HAW','EGFE','Haverfordwest 小型飛行場');
INSERT INTO "airports" VALUES('Llanbedr','','EGOD','Llanbedr 空港');
INSERT INTO "airports" VALUES('Pembrey','','EGFP','Pembrey 空港');
INSERT INTO "airports" VALUES('St Athan','DGX','EGDX','RAF Saint Athan');
INSERT INTO "airports" VALUES('スウォンジ','SWS','EGFH','スウォンジ空港');
INSERT INTO "airports" VALUES('ウェルシュプール','','EGCW','ウェルシュプール空港');
COMMIT;

Beachten Sie noch, dass die erstellten .db-Dateien ziemlich groß sein können. Während des Schreibens dieses Artikels wurde eine CSV-Datei erstellt, die aus einer Million Zeilen und 12 Spalten bestand. Sie enthielt hauptsächlich Zahlen und ein Textfeld. Die CSV-Datei, die mit GZIP komprimiert wurde, war 41 MB groß, die entpackte CSV-Datei 142 MB und beim Import in SQLite 3 (.db-Datei) war sie 165 MB groß. Es ist uns gelungen, eine .db-Datei mit Hilfe von GZIP auf 48 MB zu komprimieren. Leider ist SQLite 3 unfähig, die mit GZIP komprimierten Datenbanken zu öffnen.

Erstellen einer Datenbank im Speicher

Sie können die Datenlokalisierung wesentlich verbessern, indem Sie die SQLite 3-Datenbank nicht auf einer Festplatte, sondern im Speicher speichern. In folgendem Beispiel werden 10 Fibonacci-Werte berechnet und in der SQLite 3-Datenbank gespeichert, die sich im Speicher befindet, indem Python 3 verwendet wird.

$ sudo apt install python3
$ python3
import sqlite3

def fib(n):
    a, b = 0, 1
 
    for _ in range(n):
        yield a
        a, b = b, a + b
 
connection = sqlite3.connect(':memory:')
cursor = connection.cursor()
 
with connection:
    cursor.execute('''CREATE TABLE IF NOT EXISTS fib (
                            calculated_value INTEGER)''')
    cursor.executemany('INSERT INTO fib VALUES (?)',
                       [(str(x),) for x in fib(10)])
 
cursor.execute('SELECT * FROM fib')
print(cursor.fetchall())
 
connection.close()

Benutzerfunktionen

Sie haben die Möglichkeit, in Python Benutzerfunktionen zu erstellen, die unter Verwendung der Daten, die sich in der SQLite 3-Datenbank befinden, ausgeführt werden. Schauen Sie sich eine kleine SQLite 3-Datenbank an:

$ sqlite3 urls.db
CREATE TABLE urls (url STRING);
INSERT INTO urls VALUES
    ('https://packages.debian.org/stretch/sqlite3'),
    ('https://docs.python.org/3/library/sqlite3.html'),
    ('https://sqlite.org/about.html');

Dann wurde eine Funktion in Python erstellt, die den Hostnamen aus der URL-Adresse zieht und die Aktionen ausführt, indem sie sich auf die Tabelle konzentrieren.

$ python3
import sqlite3
from urllib.parse import urlsplit

def hostname(url):
    return urlsplit(url).netloc

connection = sqlite3.connect('urls.db')
connection.create_function('hostname', 1, hostname)
 
cursor = connection.cursor()
 
cursor.execute('SELECT hostname(url) FROM urls')
print(cursor.fetchall())

Das Abfrageergebnis einer Fetchall-Funktion sieht so aus:

[(u'packages.debian.org',), (u'docs.python.org',), (u'sqlite.org',)]

Arbeiten mit mehreren Datenbanken

Die Besonderheit des SQLite 3-Clients besteht darin, dass er mit mehreren Datenbanken gleichzeitig arbeiten kann. Starten wir einen Client und wählen zwei Datenbanken.

$ sqlite3
ATTACH 'airports.db' AS airport;
ATTACH 'urls.db' AS urls;

Dann führen wir den Befehl .databases aus, der dem Erzeugen der Namen und Orte der Datenbanken dient.

.databases
seq  name             file
---  ---------------  -----------------------
0    main
2    airport          /home/mark/airports.db
3    urls             /home/mark/urls.db

In der Rolle des Präfixes kann man Tabellennamen in Abfragen mit dem Namen verwenden, der der Datenbank zugewiesen wurde.

SELECT COUNT(*) FROM urls.urls;
3

Visualisieren mit Hilfe von Jupyter Notebooks

Jupyter Notebooks ist ein Programm, das die Daten visualisiert. Sehen wir uns den Einstellungsprozess und ein paar Visualisierungsbeispiele an.

Als Erstes wurde eine Reihe von Systemabhängigkeiten bestimmt.

$ sudo apt update
$ sudo apt install \
      libgeos-dev \
      python3-dev \
      python3-pip \
      python3-tk \
      python3-venv

Dann wurde eine virtuelle Python-Umgebung erstellt, um die Abhängigkeit des Pythons von anderen Projekten zu trennen. Sie wurde .taxis genannt.

$ pyvenv .taxis
$ source .taxis/bin/activate

Um in dieser virtuellen Umgebung mit dem Python Pip-Paketmanager weiterarbeiten zu können, soll er auf Version 9.0.1 aktualisiert werden.

$ pip install --upgrade pip

Der nächste Schritt war das Installieren einiger beliebten Python-Bibliotheken.

$ pip install \
      https://github.com/matplotlib/basemap/archive/v1.1.0.tar.gz \
      'bokeh<0.12.4' \
      gmaps \
      'holoviews[extras]' \
      jupyter \
      pandas \
      Pillow

Dann muss man einen Sonderarbeitsordner erstellen, damit Jupyter Notebooks ihn im Linux-Computer über HTTP öffnen kann.

$ mkdir -p ~/jupyter-working
$ cd ~/jupyter-working

Danach wurde die gmaps-Erweiterung deaktiviert und Jupyter die Erlaubnis erteilt, Widgets zu benutzen.

$ jupyter nbextension enable --py --sys-prefix gmaps
$ jupyter nbextension enable --py widgetsnbextension

Jetzt starten Sie einen Notebook-Server. Sehen Sie eine URL-Adresse mit einem Token-Parameter? Dann folgen Sie dem Link in einem Webbrowser und starten Notebook.

$ jupyter notebook \
      --ip=0.0.0.0 \
      --NotebookApp.iopub_data_rate_limit=100000000
...
Copy/paste this URL into your browser when you connect for the first time,
to login with a token:
    http://0.0.0.0:8888/?token=123...

Bevor eine URL-Adresse geöffnet wird, ist eine SQLite 3-Datenbank aus einer CSV-Datei zu erstellen. In unserem Fall ist dies eine Datenbank, die aus fast einer Million zufälliger Daten zu Taxifahrten besteht. Folgende Schritte sind notwendig, um diese Daten aus Hive zu exportieren:

$ hive -e 'SET hive.cli.print.header=true;
           SELECT trip_id,
                  cab_type,
                  passenger_count,
                  trip_distance,
                  fare_amount,
                  tip_amount,
                  pickup_datetime,
                  dropoff_datetime,
                  pickup_longitude,
                  pickup_latitude,
                  dropoff_longitude,
                  dropoff_latitude
           FROM trips
           WHERE RAND() <= 0.001
           DISTRIBUTE BY RAND()
           SORT BY RAND()
           LIMIT 1000000' \
    | sed 's/[\t]/,/g' \
    | gzip \
    > trips.csv.gz

Beim Importieren der Datensätze in Hive werden die ersten drei Zeilen dieser CSV-Datei so aussehen, wobei in der ersten Zeile Spaltennamen stehen.

$ gunzip -c trips.csv.gz | head -n3
trip_id, cab_type, passenger_count, trip_distance, fare_amount, tip_amount, pickup_datetime, dropoff_datetime, pickup_longitude,pickup_latitude, dropoff_longitude, dropoff_latitude
745713518, yellow, 1, 5.600, 20.50, 1.00, 2013-04-30 13:43:58, 2013-04-30 14:04:49, -73.94273100000000, 40.79017800000000, -74.00244499999999, 40.76083900000000
788379509, yellow, 1, 1.200, 6.00, 0.00, 2013-07-07 12:24:33, 2013-07-07 12:28:52, -73.95807200000000, 40.76124600000000, -73.94632400000000, 40.77708900000000

Dann wird die GZIP-Datei entpackt, SQLite 3 gestartet und trip.db als Parameter hinzugefügt.

$ gunzip trips.csv.gz
$ sqlite3 trips.db

Zum Schluss schaltet man in den CSV-Modus um. Um ein richtiges Ergebnis zu erzielen, muss man noch folgendes beachten: Erstens erfüllt das Komma die Rolle des Trennzeichens; Zweitens importiert die CSV-Datei in die Tabelle der Fahrten.

.mode csv
.separator ","
.import trips.csv trips

Weitere Schritte nach dem Einstellen

Nachdem Daten importiert worden sind, wird eine Notebook-URL geöffnet und Python 3 Notebook im Jupyter-Interface erstellt. Dann fügt man in die erste Zelle folgendes ein, hält shift und die Ausführungstaste gleichzeitig gedrückt.

import sqlite3
 
import pandas as pd
import holoviews as hv

hv.extension('bokeh')
 
connection = sqlite3.connect('trips.db')

Die Aufgabe des obigen Codes besteht darin, Pandas, die Python-Bibliothek für SQLite 3, Holoviews (dies ist eine Datenverarbeitungsbibliothek) und die Visualisierungsbibliothek zu importieren sowie auch eine Bokeh-Erweiterung für Holoviews zu initialisieren. Der letzte Schritt ist das Verbinden der SQLite 3-Datenbank mit Daten zu Taxifahrten.

Nachfolgend ist ein Code dargestellt, auf Grund dessen eine Heatmap erstellt wird, die das Aufteilen der Fahrten nach Parametern wie Tag und Stunde ermöglicht.

%%opts Points [tools=['hover']] (size=5) HeatMap [tools=['hover']] Histogram [tools=['hover']] Layout [shared_axes=False]
 
 
sql = """SELECT strftime('%w', pickup_datetime) as weekday,
                strftime('%H', pickup_datetime) as hour,
                COUNT(*) as cnt
         FROM trips
         GROUP BY 1, 2;"""
df = pd.read_sql_query(sql, connection)
hv.HeatMap(df)

Folgendes Lineardiagramm zeigt die Anzahl der Taxifahrten.

%matplotlib inline
 
sql = """SELECT date(pickup_datetime) as date,
                COUNT(*) as cnt
         FROM trips
         GROUP BY 1
         ORDER BY 1;"""
df = pd.read_sql_query(sql, connection)
df['date'] = df.date.astype('datetime64[ns]')
df.plot(x='date', y='cnt')

Wenn Sie ein Histogramm erstellen möchten, in dem Angaben zu verschiedenen Fahrzeugfarben verglichen werden, muss eine neue Zeile hinzugefügt werden, in die Sie dann entsprechende Informationen eingeben.

%%opts Bars [stack_index=1 xrotation=90 legend_cols=7 show_legend=False show_frame=False tools=['hover']]

hv.extension('bokeh', 'matplotlib')
sql = """SELECT strftime('%m', pickup_datetime) as month,
                cab_type,
                COUNT(*) as cnt
         FROM trips
         GROUP BY 1, 2;"""
df = pd.read_sql_query(sql, connection)
table = hv.Table(df, kdims=[('month', 'month'), ('cab_type', 'cab_type')], vdims=[('cnt', 'cnt')])
table.to.bars(['month', 'cab_type'], 'cnt', [])

Folgendes Kreisdiagramm stellt die Abhängigkeit der Reisen von der Tageszeit dar.

%matplotlib inline

sql = """SELECT round(strftime('%H', pickup_datetime)) as hour,
                count(*) as cnt
         FROM trips
         GROUP BY 1;"""
df = pd.read_sql_query(sql, connection)
df.plot(kind='pie', y='cnt', legend=False)

Wenn Sie ein Streumatrixdiagramm erstellen möchten, dann folgen Sie dem unten dargestellten Code. Dies kann ein paar Minuten dauern. Bevor Sie das Diagramm selbst sehen, wird ein Datenfeld angezeigt.

%matplotlib inline

sql = """SELECT round(strftime('%H', pickup_datetime)) as hour,
                round(trip_distance),
                round(fare_amount),
                round(tip_amount)
         FROM trips;"""
df = pd.read_sql_query(sql, connection)
pd.plotting.scatter_matrix(df, figsize=(15, 15))

Es gibt zwei Möglichkeiten, wie geographische Punkte auf Karten angezeigt werden. Die erste Möglichkeit besteht in der Verwendung der Matplotlib und Basemap, die offline arbeiten werden, API-Schlüssel werden dabei nicht benötigt. So sehen die Sammelstellen für Taxirouten in einem Datensatz aus.

%matplotlib inline

import matplotlib.pyplot as plt
from mpl_toolkits.basemap import Basemap

sql = """SELECT ROUND(pickup_longitude, 3) as long,
                ROUND(pickup_latitude, 3) as lat,
                COUNT(*) as cnt
         FROM trips
         GROUP BY long, lat"""
 
df = pd.read_sql_query(sql, connection)
df = df[pd.to_numeric(df['long'], errors='coerce').notnull()]
df = df[pd.to_numeric(df['lat'], errors='coerce').notnull()]
df = df.dropna(thresh=1)
df.long = df.long.astype(float, errors='ignore').fillna(0.0)
df.lat = df.lat.astype(float, errors='ignore').fillna(0.0)
 
plt.figure(figsize=(20, 20))
 
map = Basemap(projection='merc',
              llcrnrlat=40,
              urcrnrlat=42,
              llcrnrlon=-75,
              urcrnrlon=-72,
              resolution='i',
              area_thresh=50,
              lat_0=40.78,
              lon_0=-73.96)
map.drawcountries()
map.drawcoastlines(linewidth=0.5)
map.drawstates()
map.bluemarble()
 
lons = df['long'].values
lats = df['lat'].values
 
x, y = map(lons, lats)
map.plot(x, y, 'ro', markersize=4)
plt.show()

Einfach, nicht wahr?

Folgender Code erstellt eine Heatmap, die über dem Google Maps-Widget aufgebaut wird. Der Nachteil besteht darin, dass ein mit Google verbundener API-Schlüssel erstellt werden muss. Sie benötigen dann einen Internetanschluss, wenn der API-Schlüssel verwendet wird.

Möglicherweise tritt das folgende Problem noch auf: Oft sind geographische Breiten- / Längenangaben ungültig. In diesem Fall überspringen Sie sie nicht, sondern erhalten eine Fehlermeldung. Da ein Datensatz oft nicht ideal ist, werden Sie wahrscheinlich viel Zeit brauchen, um falsche Werte zu filtern.

import gmaps
 
gmaps.configure(api_key="...")
 
locations = [(float(row['lat']), float(row['long']))
             for index, row in df.iterrows()
             if -80 < float(row['long']) < -70
             and 35 < float(row['lat']) < 45]
fig = gmaps.Map()
fig.add_layer(gmaps.heatmap_layer(locations))
fig

Pandas DataFrames-Dumping für SQLite

Wenn es sich um Erstellen derivativer Datensätze mit einer minimalen Anzahl des Codes handelt, ist Pandas DataFrames ideal. Außerdem schaffen Sie es problemlos, Pandas DataFrames in SQLite 3 zurückzusetzen. In folgendem Beispiel sind nächste Schritte dargestellt: Hinzufügen einiger CSV-Daten dem DataFrame, Erstellen einer neuen SQLite 3-Datenbank und Dumping des DataFrames in diese Datei.

import sqlite3
import pandas
 
connection = sqlite3.connect('trips.db')
 
df = pandas.read_csv('trips.csv', sep=',')
df.to_sql('trips', connection, if_exists='append', index=False)

Fazit

Sie haben sicherlich verstanden, dass SQLite 3 kein Spielzeug ist. Sie haben es mit einer leistungsfähigen SQL-Erweiterung zu tun. Wir können mit Sicherheit sagen, dass sich SQLite 3 rasch weiterentwickelt. Dies liegt daran, dass die Datenmenge durch die Speichergeschwindigkeit sowie auch durch die Leistungsfähigkeit eines einzelnen Prozessorkerns erhöht wird.

Ohne Zweifel ist SQLite 3 eine der bequemsten Datenbanken, mit der viele Aufgaben erfüllt werden können.

Sollten Sie Probleme beim Installieren, Einstellen und Benutzen der SQLite 3 haben, so hilft Ihnen gerne das Team von AI-United.de per Mail oder im Q&A-Bereich.

Quelle

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.