Python: Datenbanktabellen verwalten
Datenbanktabelle erzeugen
► SQLite-Dokumentation: CREATE TABLE
Um eine neue Datenbanktabelle zu erzeugen, wird die SQL-Anweisung CREATE TABLE verwendet. Um einen SQL-Fehler zu vermeiden, falls die Tabelle bereits existiert, sollte man die Anweisung um die Angabe IF NOT EXISTS erweitern. Die Namen der Tabelle sowie der einzelnen Felder sollte man in einfache Anführungszeichen setzen, um sicherzustellen, dass jeder beliebige String als Name verwendet werden kann, auch wenn er ein geschützes SQL-Schlüsselwort ist, da ansonsten ein Fehler erzeugt wird.
Es stehen folgende Datentypen zur Verfügung:
TEXT - Zeichenketten
INT/INTEGER - Ganzzahlen
REAL - Fließkommazahlen
BLOB - Binary Large Object
NULL - Nullwert
Mit PRIMARY KEY wird das Feld mit der ID gekennzeichnet. Dies führt dazu, dass dieser Wert bei einem neuen Datensatz automatisch generiert wird (die bislang höchste ID der Datenbanktabelle + 1). In diesem Fall muss der Datentyp des Feldes INTEGER und nicht INT lauten!
Ein Vorgabewert, der automatisch in einem neuen Datensatz gespeichert wird, auch wenn kein Wert für dieses Feld explizit angegeben wurde, kann mit DEFAULT festgelegt werden.
Falls ein Feld niemals einen Nullwert enthalten darf, gibt man NOT NULL an. In diesem Fall muss ein Wert für DEFAULT angegeben werden.
import sqlite3
con = sqlite3.connect("Python/mydatabase.db")
dbcursor = con.cursor()
sql = "CREATE TABLE IF NOT EXISTS `adressen` (" \
"`id` INTEGER PRIMARY KEY, " \
"`vorname` TEXT DEFAULT 'N.N.', " \
"`nachname` TEXT, " \
"`geburtsdatum` TEXT, " \
"`plz` INT, " \
"`ort` TEXT, " \
"`strasse` TEXT, " \
"`hausnummer` TEXT)"
dbcursor.execute(sql)
con.close()
Struktur einer Datenbanktabelle ermitteln
► SQLite-Dokumentation: PRAGMA table_info()
Die Struktur einer Datenbanktabelle kann folgendermaßen ermittelt werden. Die Funktion table_info() gibt für jedes Feld ein Tupel zurück, das folgende Elemente enthält:
[0] Index des Feldes
[1] Name des Feldes
[2] Datentyp
[3] Flag für NOT NULL
[4] Standardwert DEFAULT
[5] Flag für PRIMARY KEY
import sqlite3
con = sqlite3.connect("Python/mydatabase.db")
dbcursor = con.cursor()
dbcursor.execute("PRAGMA table_info(`adressen`)")
for column in dbcursor:
print(column)
con.close()
Anzahl der Datensätze einer Tabelle ermitteln
► SQLite-Dokumentation: SELECT
Die Anzahl der Datensätze einer Datenbanktabelle kann folgendermaßen ermittelt werden:
import sqlite3
con = sqlite3.connect("Python/mydatabase.db")
dbcursor = con.cursor()
dbcursor.execute("SELECT Count(*) FROM `adressen`")
for res in dbcursor:
print(res[0])
con.close()
Datenbanktabelle umbenennen
► SQLite-Dokumentation: ALTER TABLE
Um eine Datenbanktabelle umzubenennen, wird die SQL-Anweisung ALTER TABLE … RENAME TO verwendet.
import sqlite3
con = sqlite3.connect("Python/mydatabase.db")
dbcursor = con.cursor()
dbcursor.execute("ALTER TABLE `adressen` RENAME TO `adressen2`")
dbcursor.execute("SELECT `name` FROM `sqlite_master` WHERE `type` = 'table'")
for tables in dbcursor:
print(tables[0])
con.close()
Datenbanktabelle kopieren
► SQLite-Dokumentation: CREATE TABLE
Um eine Datenbanktabelle zu kopieren, wird die SQL-Anweisung CREATE TABLE … AS verwendet. Hierbei ist zu beachten, dass Feldeigenschaften wie DEFAULT oder PRIMARY KEY beim Kopieren verloren gehen. Mit der unten beschriebenen Funktion alter_table() lässt sich dies aber vermeiden.
import sqlite3
con = sqlite3.connect("Python/mydatabase.db")
dbcursor = con.cursor()
dbcursor.execute("CREATE TABLE IF NOT EXISTS `adressen` AS SELECT * FROM `adressen2`")
dbcursor.execute("SELECT * FROM `adressen`")
for datensatz in dbcursor:
print(datensatz)
print()
dbcursor.execute("SELECT * FROM `adressen2`")
for datensatz in dbcursor:
print(datensatz)
con.close()
Datenbanktabelle löschen
► SQLite-Dokumentation: DROP TABLE
Um eine Datenbanktabelle einschließlich aller enthaltenen Datensätze zu löschen, wird die SQL-Anweisung DROP TABLE verwendet. Um eine Fehlermeldung zu vermeiden, falls die ausgewählte Tabelle nicht existiert, kann man die Anweisung um IF EXISTS erweitern.
import sqlite3
con = sqlite3.connect("Python/mydatabase.db")
dbcursor = con.cursor()
dbcursor.execute("DROP TABLE IF EXISTS `adressen2`")
dbcursor.execute("SELECT `name` FROM `sqlite_master` WHERE `type` = 'table'")
for tables in dbcursor:
print(tables[0])
con.close()
Feld hinzufügen
► SQLite-Dokumentation: ALTER TABLE
Um ein neues Feld zu einer Datenbanktabelle hinzuzufügen, wird die SQL-Anweisung ALTER TABLE … ADD COLUMN verwendet. Das neue Feld wird nach dem letzten Feld eingefügt. Eine andere Position lässt sich auf diese Weise nicht wählen. Mit der unten beschriebenen Funktion alter_table() lässt sich dies aber erreichen.
import sqlite3
con = sqlite3.connect("Python/mydatabase.db")
dbcursor = con.cursor()
dbcursor.execute("ALTER TABLE `adressen` ADD COLUMN `geburtsort` TEXT")
dbcursor.execute("PRAGMA table_info(`adressen`)")
for column in dbcursor:
print(column)
con.close()
Erweiterte Aktionen
Die hier vorgestellte Funktion alter_table() ist als experimentell zu betrachten, da sie nur die elementaren Eigenschaften von SQLite berücksichtigt. Um eventuellen Datenverlust zu vermeiden ist der Code an die eigenen Notwendigkeiten anzupassen!
Im Vergleich zu anderen SQL-Implementierungen ist SQLite im Funktionsumfang relativ beschränkt. Verschiedene nützliche Aktionen lassen sich nicht über übliche SQL-Befehle erreichen, sondern müssen programmatisch gelöst werden. Dazu gehören:
• Kopieren einer Tabelle bei Erhalt der Feldeigenschaften
• Hinzufügen eines Datenfeldes an beliebiger Position
• Ändern oder Verschieben eines Datenfeldes
• Kopieren eines Datenfeldes
• Löschen eines Datenfeldes
All diese Aktionen werden mit der folgenden Funktion alter_table() implementiert, indem eine Kopie der ursprünglichen Tabelle einschließlich der Datensätze angelegt wird, wobei die gewünschten Änderungen beim Kopieren umgesetzt werden. Außer bei der Aktion ›Kopieren einer Tabelle‹ (COPYT) wird dann die ursprüngliche Tabelle gelöscht. Daraufhin erhält die Kopie den Namen der ursprünglichen Tabelle.
Die Funktion wird mit drei Parametern aufgerufen:
- der Name der Aktion (siehe Beispiele)
- der Index (die Position) des betroffenen Datenfeldes (außer bei CREATE); bei COPYT der Index der betroffenen Tabelle
- bei COPYT der Name der Kopie, sonst eine Liste mit den ggf. geänderten Eigenschaften des Datenfeldes, die der Ausgabe von PRAGMA table_info() entspricht:
[0] Position des Feldes
[1] Name des Feldes
[2] Datentyp
[3] Flag für NOT NULL
[4] Standardwert DEFAULT
[5] Flag für PRIMARY KEY
import sqlite3
def is_unique(dbf):
cur1 = con.cursor()
cur1.execute("SELECT `" + dbf + "` FROM `" + cdbt + "`")
for row in cur1:
cur2 = con.cursor()
cur2.execute("SELECT Count(`" + dbf + "`) FROM `" + cdbt + "` WHERE `" + dbf + "` = '" + str(row[0]) + "'")
for count in cur2:
if (count[0]) > 1:
return False
return True
def alter_table(action, fid, props): # create, edit, copy or delete field / copy dbt
types = ["TEXT", "INTEGER", "REAL", "BLOB", "NULL"]
dbts = []
cur.execute("SELECT `name` FROM `sqlite_master` WHERE `type` = 'table'")
for table in cur:
dbts.append(table[0])
dbfs = []
cur.execute("PRAGMA table_info(`" + cdbt + "`)")
for column in cur:
dbfs.append(column)
if action == "COPYT":
m = "Tabelle `" + dbts[fid] + "` wurde nach `" + props + "` kopiert."
if action == "CREATE" or action == "EDIT":
target = props[1]
if props[3] == True:
notnull = 1
else:
notnull = 0
if props[4] != None:
default = "'" + props[4].replace("'", "''") + "'"
else:
default = None
if props[5] == True:
primary = 1
else:
primary = 0
pos = props[0]
mod = [None, target, types[props[2]], notnull, default, primary]
if action == "CREATE":
m = "Feld `" + target + "` wurde angelegt."
else:
m = "Feld `" + dbfs[fid][1] + "` wurde aktualisiert."
elif action == "COPY":
target = props[1]
pos = props[0]
mod = list(dbfs[fid][:])
mod[1] = target
m = "Feld `" + dbfs[fid][1] + "` wurde nach `" + target + "` kopiert."
elif action == "DELETE":
m = "Feld `" + dbfs[fid][1] + "` wurde gelöscht."
# check if target exists
fns = []
for i in range(len(dbfs)):
fns.append(dbfs[i][1])
if ((action == "CREATE" or action == "COPY") and target in fns):
print("Das Feld `" + target + "` existiert bereits!")
else:
if action == "EDIT" and primary == 1 and is_unique(target) == False:
print("Das Feld `" + target + "` ist nicht UNIQUE!")
else:
# make temp table name
if action == "COPYT":
temp = props
else:
temp = cdbt
while temp in dbts:
temp += "_"
# copy cdbt with modified fields in new order
fields = []
for i in range(len(dbfs)):
if (action != "DELETE" and action != "COPYT" and i == 0 and pos == 0):
fields.append(mod)
if ((action == "CREATE") or
(action == "COPYT") or
(action == "EDIT" and i != fid) or
(action == "COPY") or
(action == "DELETE" and i != fid)):
fields.append(dbfs[i])
if (action != "DELETE" and action != "COPYT" and pos == i + 1):
fields.append(mod)
sql = "CREATE TABLE IF NOT EXISTS `" + temp + "` ("
columns1 = ""
columns2 = ""
for f in fields:
sql += "`" + f[1] + "` " + f[2]
if f[3] == 1:
sql += " NOT NULL"
if f[4] == None:
f[4] = ""
if f[4] != None:
sql += " DEFAULT '" + f[4][1:-1] + "'"
if f[5] != 0:
sql += " PRIMARY KEY"
sql += ", "
# fields of temp dbt
if ((action == "CREATE" and f[1] != target) or action != "CREATE"):
columns1 += "`" + f[1] + "`, "
# fields of source dbt
if (action == "DELETE" or action == "COPYT" or (action != "DELETE" and f[1] != target)):
columns2 += "`" + f[1] + "`, "
elif (f[1] == target and (action == "EDIT" or action == "COPY")):
columns2 += "`" + dbfs[fid][1] + "`, "
sql = sql[:-2] + ")"
columns1 = columns1[:-2]
columns2 = columns2[:-2]
cur.execute(sql)
# copy rows
if action == "EDIT" and notnull == 1:
sql = "UPDATE `" + cdbt + "` SET `" + dbfs[fid][1] + "` = '' WHERE `" + dbfs[fid][1] + "` IS NULL"
cur.execute(sql)
con.commit()
sql = "INSERT INTO `" + temp + "` (" + columns1 + ") SELECT " + columns2 + " FROM `" + cdbt + "`"
cur.execute(sql)
con.commit()
if action != "COPYT":
cur.execute("DROP TABLE IF EXISTS `" + cdbt + "`")
cur.execute("ALTER TABLE `" + temp + "` RENAME TO `" + cdbt + "`")
print(m)
con = sqlite3.connect("Python/mydatabase.db")
cur = con.cursor()
cdbt = "adressen" # current database table name
# COPYT kopiert die Tabelle[0] nach `adressen (Kopie)`
alter_table("COPYT", 0, "adressen (Kopie)")
con.close()
Kopieren einer Tabelle bei Erhalt der Feldeigenschaften
# COPYT kopiert die Tabelle[0] nach `adressen (Kopie)`
alter_table("COPYT", 0, "adressen (Kopie)")
Hinzufügen eines Datenfeldes an beliebiger Position
# CREATE erzeugt das Datenfeld `geboren_in` hinter Datenfeld[8]
alter_table("CREATE", None, [8, "geboren_in", 0, False, None, False])
Feld ändern oder verschieben
# EDIT verschiebt Datenfeld[8] (`geboren_in`) hinter Datenfeld[4] und benennt es um in `geburtsort`
alter_table("EDIT", 8, [4, "geburtsort", 0, False, "unbekannt", False])
Feld kopieren
# COPY kopiert Datenfeld[1] hinter Datenfeld[2] und benennt die Kopie `vorname2`
alter_table("COPY", 1, [2, "vorname2", None, None, None, None])
Feld löschen
# DELETE löscht Datenfeld[2]
alter_table("DELETE", 2, None)