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.
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.
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
Quelltext auswählen
1
2
3
4
5
6
7
8
9
10
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()
Die Anzahl der Datensätze einer Datenbanktabelle kann folgendermaßen ermittelt werden:
Quelltext auswählen
1
2
3
4
5
6
7
8
9
10
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()
Um eine Datenbanktabelle umzubenennen, wird die SQL-Anweisung ALTER TABLE … RENAME TO verwendet.
Quelltext auswählen
1
2
3
4
5
6
7
8
9
10
11
12
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()
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.
Quelltext auswählen
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
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()
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.
Quelltext auswählen
1
2
3
4
5
6
7
8
9
10
11
12
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()
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.
Quelltext auswählen
1
2
3
4
5
6
7
8
9
10
11
12
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
Quelltext auswählen
1
2
# COPYT kopiert die Tabelle[0] nach `adressen (Kopie)`
alter_table("COPYT", 0, "adressen (Kopie)")
Hinzufügen eines Datenfeldes an beliebiger Position
Quelltext auswählen
1
2
# CREATE erzeugt das Datenfeld `geboren_in` hinter Datenfeld[8]
alter_table("CREATE", None, [8, "geboren_in", 0, False, None, False])
Feld ändern oder verschieben
Quelltext auswählen
1
2
# 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
Quelltext auswählen
1
2
# COPY kopiert Datenfeld[1] hinter Datenfeld[2] und benennt die Kopie `vorname2`
alter_table("COPY", 1, [2, "vorname2", None, None, None, None])