Tabellen mit SQL anlegen#
The problem with SQL is that it pretends to implement the relational model, but doesn’t.
— David McGoveran
Folien/PDF#
Tabellen mit SQL implementieren#
SQL erlaubt es uns nicht nur Daten aus bestehenden Tabellen abzufragen, sondern auch neue Tabellen zu erzeugen und diese zu verändern. Wir wollen diese Funktionalität im folgenden nutzen, um unseren Logischen Entwurf für die Geometrieobjekte zu implementieren.
Hierfür nutzen wir wieder SQLite und erzeugen uns eine neue Datenbank geometry.sqlite. Wir setzten uns auch direkt einen Kursor cur, um SQL Befehle auf der Datenbank auszuführen.
import sqlite3
# Create a SQL connection to our SQLite database
con = sqlite3.connect("geometry.sqlite")
cur = con.cursor()
Als erstes müssen wir die generischen Datentypen des logischen Entwurfs auf die konkreten Datentypen von SQLite abbilden. Grundsätzlich unterscheiden die Datentypen sich für jedes DBMS etwas und man muss immer schauen, welche Datentypen am besten geeignet ist. SQLite beschränkt sich auf relativ wenige Datentypen, welche gut mit den primitiven Datentypen von Python überein stimmen. Die folgende Tabelle vergleicht die Datentypen von SQLite mit Python.
SQLite |
Python |
|
|---|---|---|
Boolean |
(INTEGER) |
bool |
Ganzzahl |
INTEGER |
int |
Gleitkommazahl |
REAL |
float |
Interpretiert beliebige Dateneingaben als Zahl |
NUMERIC |
- |
Text-String, gespeichert in UTF-8 oder UTF-16 |
TEXT |
str |
Beliebiger Block an Binärdaten |
BLOB |
bytes |
Keine Daten |
NULL |
None |
Besonderheit hier ist der NUMERIC Datentyp, welcher dazu da ist, dass Zahlen aus Texteingaben automatisch in die passende Zahlenrepräsentation in der Datenbank umgewandelt wird. Der NULL Wert ist in der Tabelle kein Datentyp an sich sondern wird benutzt beim Laden von Daten in Tabellen fehlende Werte zu übertragen.
Mit diesen Datentypen und dem ER-Diagramm des Logischen Entwurfsmodells oben können wir jetzt unsere Tabellen erzeugen. Dies geschieht in SQL mit dem CREATE TABLE Befehl auf welchen der Tabellenname (Entitätsname) folgt und dann in Klammern, die einzelnen Attribute. Für die fünf Entitäten ergeben sich die folgenden SQL Befehle.
sql='''
CREATE TABLE Points (
point_id INTEGER,
x REAL,
y REAL
);
CREATE TABLE Lines (
lines_id INTEGER,
start INTEGER,
end INTEGER
);
CREATE TABLE PolygonTypes (
polygontype_id INTEGER,
name TEXT
);
CREATE TABLE Polygons (
polygon_id INTEGER,
polygontype INTEGER
);
CREATE TABLE PolygonPoints (
polypoint_id INTEGER,
polygon_id INTEGER,
point_id INTEGER
);
'''
Prinzipiell könnten wir diese Befehle bereits an die SQLite DB senden und die Tabellen anlegen. Allerdings fehlen hier noch die Deklarierung der Primär- und Fremdschlüssel sowie die Not Null Constraints.
Hierfür ergänzen wir die Zeilen der Attribute welche als Primärschlüssel dienen mit der Anmerkung PRIMARY KEY. In unserm Beispiel definieren wir den Schlüssel als AUTOINCREMENT, das heißt, dass neuen Daten die ohne Schlüssel hinzugefügt werden, automatisch ein Schlüssel zugewiesen wird.
Die Fremdschlüssel definieren wir als zusätzlichen Constraint (Bedingung) indem wir angeben welche Attribute Fremdschlüssel sind und auf welche Tabelle und Schlüsselattribute diese sich beziehen. Der Fremdschlüssel polytype in der Tabelle Polygons verweißt zum Beispiel auf den Primärschlüssel polytype_id in der Tabelle PolygonTypes. Mehrfachnennungen sind hier möglich wie das Beispiel für den start und end Point in der Tabelle Lines zeigt.
Wir annotieren weiterhin alle Attribute und Fremdschlüssel die nicht Null sein dürfen mit NOT NULL. Primärschlüssel dürfen nie null sein, weshalb wir diese Annotation nicht brauchen.
Damit ergibt sich unser vervollständigtes SQL-Skript zum Erzeugen aller Tabellen zu:
sql='''
CREATE TABLE Points (
point_id INTEGER PRIMARY KEY AUTOINCREMENT,
x REAL NOT NULL,
y REAL NOT NULL
);
CREATE TABLE Lines (
lines_id INTEGER PRIMARY KEY AUTOINCREMENT,
start INTEGER NOT NULL,
end INTEGER NOT NULL,
FOREIGN KEY(start,end) REFERENCES Points(point_id,point_id)
);
CREATE TABLE PolygonTypes (
polytype_id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT
);
CREATE TABLE Polygons (
polygon_id INTEGER PRIMARY KEY AUTOINCREMENT,
polytype INTEGER,
FOREIGN KEY(polytype) REFERENCES PolygonTypes(polytype_id)
);
CREATE TABLE PolygonPoints (
polypoint_id INTEGER PRIMARY KEY AUTOINCREMENT,
polygon_id INTEGER,
point_id INTEGER,
FOREIGN KEY(polygon_id) REFERENCES Polygons(polygon_id),
FOREIGN KEY(point_id) REFERENCES Points(point_id)
);
'''
Da dieses SQL-Script mehrere Befehle enthält, können wir es nicht mit cur.execute(sql) an die Datenbank senden, sondern benutzen
cur.executescript(sql)
Um zu prüfen ob unsere Tabellen angelegt worden sind, können wir alle Tabellen in der SQLite Datebank abfragen mit:
sql="SELECT name FROM sqlite_schema WHERE type='table' ORDER BY name;"
rows=cur.execute(sql)
for row in rows:
print(row)
('Lines',)
('Points',)
('PolygonPoints',)
('PolygonTypes',)
('Polygons',)
('sqlite_sequence',)
Daten hinzufügen#
Im nächsten Schritt wollen wir einige Werte in die Datenbank laden. Dies geschieht mit dem SQL Befehl INSERT INTO mit dem Tabellennamen und den einzutragenden Werten, in genau der Reihenfolge der vorher deklarierten Spalten (also point_id, x, y).
Zum Beispiel wollen wir zwei Punkte mit den Koordinaten (54.083336, 12.108811) und (12.094167, 54.075211). Dabei geben wir auch den Wert des Primärindex als 1 und 2 mit an:
sql="""
INSERT INTO Points VALUES(1, 54.083336, 12.108811);
INSERT INTO Points VALUES(2, 12.094167, 54.075211);
"""
cur.executescript(sql)
Dieser Primärindex muss einmalig sein. Wollen wir zum Beispiel die gleichen Punkte noch einmal erzeugen, so gibt uns die Datenbank eine Fehlermeldung zurück, dass die Punkte schon existieren.
cur.executescript(sql)
---------------------------------------------------------------------------
IntegrityError Traceback (most recent call last)
Cell In[8], line 1
----> 1 cur.executescript(sql)
IntegrityError: UNIQUE constraint failed: Points.point_id
Wollen wir von der Spaltenreihenfolge abweichen oder Werte weglassen. So können wir die zu schreibenden Spaltennamen am INSERT Befehl auch mit angeben. Lassen wir eine Spalte weg, so wird sie mit NULL beschrieben, sofern die Spalte Nullwerte erlaubt. Da wir bei der Tabellendeklaration den Primärschlüssel point_id als AUTOINCREMENT deklariert haben, müssen wir diesen nicht unbedingt mit angeben. Ihm wird dann automatisch ein Wert zugewiesen.
sql="INSERT INTO Points(x, y) VALUES(54.083336, 12.108811);"
cur.execute(sql)
Hier haben wir allerdings das Problem, dass wir den Wert nicht kennen und somit auch später als Fremdschlüssel nicht weiter verwenden können. In Python wird dieser im Cursor gespeichert unter
cur.lastrowid
3
Als nächstes wollen wir eine Linie zwischen dem Punkt (54.083336, 12.108811) und (12.094167, 54.075211) erzeugen. Dies können wir nicht direkt mit den Koordinaten, sondern müssen die Fremdschlüssel der Punkte nutzen, welche wir beim erzeugen zugewiesen haben. Also 1 und 2.
sql="INSERT INTO Lines(start, end) VALUES(1, 2);"
cur.execute(sql)
cur.lastrowid
1
Bevor wir ein Polygon erzeugen können müssen wir erst den Polygontyp in der Tabelle PolygonTypes deklarieren. Zum Beispiel sagen wir, dass es den Polygontyp “Dreieck” gibt mit dem Primärschlüsselwert 0.
sql='INSERT INTO PolygonTypes VALUES(0, "Dreieck");'
cur.execute(sql)
cur.lastrowid
0
Jetzt erzeugen wir den Eintrag für das Polygon in der Tabelle Polygon. Hier gibt es nur den Primärschlüssel, z.B. 33 und den Fremdschlüssel 0 für den Polygontyp.
sql='INSERT INTO Polygons VALUES(33, 0);'
cur.execute(sql)
cur.lastrowid
33
Wollen wir zum Beispiel ein Polygon für einen nicht existenten Polygontypen mit dem Fremdschlüssel 6 erzeugen, so gibt uns die Datenbank eine Fehlermeldung zurück.
sql='INSERT INTO Polygons VALUES(66, 6);'
cur.execute(sql)
cur.lastrowid
66
Hier sieht man die Datenbank die Integrität der Daten absichert und uns vor der Eingabe falscher Daten abhält.
Allerdings ist unser Polygon noch nicht fertig definiert und wir müssen noch die dem Polygon zugehörigen Punkte verknüpfen. Dies machen wir durch drei Eintragungen in der Tabelle PolygonPoints unter Angabe der neuen Primärschlüssel (331, 332, 333), des Fremdschlüssel für das Polygon 33 und der Fremdschlüssel für die drei Punkte (1,2,3).
sql="""INSERT INTO PolygonPoints VALUES(331, 33, 1);
INSERT INTO PolygonPoints VALUES(332, 33, 2);
INSERT INTO PolygonPoints VALUES(333, 33, 3);
"""
cur.executescript(sql)
cur.lastrowid
66
Daten löschen#
Um Einträge aus einer Tabelle zu löschen verwendet man in SQL den DELETE FROM Befehl mit der Tabelle und einer WHERE-Bedingung, welche angibt welche Einträge zu löschen sind. Wollen wir zum Beispiel die eben verknüpften Punkte aus der Tabelle PolygonPoints wieder löschen so können wir schreiben, dass wir alle Zeilen löschen, wo die refernzierte Polygon die polygon_id 33 hat.
sql="DELETE FROM PolygonPoints WHERE polygon_id=33;"
cur.execute(sql)
cur.lastrowid # gibt uns die zuletzt gelöschte Zeile zurück
333
Tabellen umbenennen und verändern#
Machmal ist es notwendig Tabellen zu sichern oder SQL Abfragen mit SQL Select zu “materialisieren”, also als neue Tabelle zu speichern. Dafür können wir den CREATE TABLE ... AS mit einem SELECT benutzen. Dies ist sinnvoll um zum Beispiel eine Kopie einer Tabelle zu erzeugen, bevor man z.B. Änderungen vornimmt. Man beachte
sql="CREATE TABLE PointsCopy AS SELECT * FROM Points;"
cur.execute(sql)
Möchte man eine Tabelle umbennenen so nutzt man dafür den ALTER TABLE ... RENAME Befehl in SQL. Um die Tabelle Points nach Punkte umzubenennen, schreiben wir
sql="ALTER TABLE Points RENAME TO Punkte;"
cur.execute(sql)
Auf ähnliche Weise können wir auch Spalten in Tabellen umbenennen oder neue Spalten hinzufügen. Wollen wir zum Beispiel beim Polygontypen eine neue Spalte hinzufügen, welche die maximale Anzahl an Punkten enthält so geschieht dies wie folgt:
sql="ALTER TABLE PolygonTypes ADD COLUMN maxPoints INTEGER DEFAULT 0;"
cur.execute(sql)
Es ist zu beachten, dass dies die Spalte zwar anlegt aber keine Werte schreibt. Deshalb weisen wir hier der Spalten den Standartwert 0 mit DEFAULT 0 zu.
Tabellen löschen#
SQL bietet natürlich auch einen Befehl um Tabellen vollständig zu löschen. Möchte man nur alle Daten aus einer Tabelle löschen aber die Definition der Tabelle behalten (wenn man zum Beispiel die Daten austauscht), so kann man den DELETE FROM ohne WHERE-Bedingung nutzen. Dies nennt man auch Truncating einer Tabelle.
sql="DELETE FROM PointsCopy;"
cur.execute(sql)
<sqlite3.Cursor at 0x106a5e0c0>
Möchte man eine Tabelle vollständig löschen so nutzt man den DROP TABLE Befehl. Hier nutzen wir den optionalen Zusatz IF EXISTS um sicherzustellen, das die Tabelle nur gelöscht wird, wenn sie existiert.
sql='''
DROP TABLE IF EXISTS PointsCopy;
DROP TABLE IF EXISTS Punkte;
DROP TABLE IF EXISTS Lines;
DROP TABLE IF EXISTS PolygonTypes;
DROP TABLE IF EXISTS Polygons;
DROP TABLE IF EXISTS PolygonPoints;
'''
cur.executescript(sql)
Damit haben wir die Exkursion in SQL zum Umgang mit Datenbanken abgeschlossen. Zuletzt müssen wir die Verbindung zur Datenbank noch mit close() schließen.
cur.close()
con.close()
Anmerkung zu Tabellen in SQLite speichern mit Pandas#
Note
In der letzten Übung haben wir Tabellen direkt mit Pandas in SQLite erzeugt. Das ist durchaus sinnvoll, wenn man größere Datensätze mal schnell sicher in einer Datenbank speichern möchte. Es ist aber darauf hinzuweisen, dass die von Pandas erzeugten Tabellen keine Relationen mit Fremdschlüssel, Constraints (z.B. NotNull) oder Suchindizies definieren. Die resultierenden Tabellen sind also keinesfalls 3NF-Konform und optimiert für die Abfrage und das speichern großer Datenmengen in praktischen Anwendungen. Hier ist immer zu empfehlen einen richtigen ER-Entwurf zu machen. Die Tabellen mit CREATE mit allen Relationen, Constraints und Indizies zu erzeugen und dann erst mit Pandas Daten in die bestehenden Tabellen zu laden.