Bitte warten...

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.

Code kopieren
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

Code kopieren
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:

Code kopieren
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.

Code kopieren
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.

Code kopieren
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.

Code kopieren
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.

Code kopieren
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

Code kopieren
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

Code kopieren
# COPYT kopiert die Tabelle[0] nach `adressen (Kopie)`
alter_table("COPYT", 0, "adressen (Kopie)")

Hinzufügen eines Datenfeldes an beliebiger Position

Code kopieren
# CREATE erzeugt das Datenfeld `geboren_in` hinter Datenfeld[8]
alter_table("CREATE", None, [8, "geboren_in", 0, False, None, False])

Feld ändern oder verschieben

Code kopieren
# 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

Code 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

Code kopieren
# DELETE löscht Datenfeld[2]
alter_table("DELETE", 2, None)