decocode decocode deco    

Datenbanktabellen verwalten #

Übersicht
Datenbanktabelle erzeugen
Struktur einer Datenbanktabelle ermitteln
Anzahl der Datensätze einer Tabelle ermitteln
Datenbanktabelle umbenennen
Datenbanktabelle kopieren
Datenbanktabelle löschen

Feld hinzufügen
Erweiterte Aktionen
Kopieren einer Tabelle
Hinzufügen eines Datenfeldes
Feld ändern oder verschieben
Feld kopieren
Feld löschen

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.

Quelltext auswählen
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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

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()

Anzahl der Datensätze einer Tabelle ermitteln #

► SQLite-Dokumentation: SELECT

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()

Datenbanktabelle umbenennen #

► SQLite-Dokumentation: ALTER TABLE

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()

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.

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()

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.

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()

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.

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:

  1. der Name der Aktion (siehe Beispiele)
  2. der Index (die Position) des betroffenen Datenfeldes (außer bei CREATE); bei COPYT der Index der betroffenen Tabelle
  3. 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

Quelltext auswählen
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
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])

Feld löschen #

Quelltext auswählen
1
2
# DELETE löscht Datenfeld[2]
alter_table("DELETE", 2, None)