Tabellen verknüpfen


Der Umgang mit mehreren Tabellen wird am Beispiel aus der Schulverwaltung demonstriert. Bei einem Sporttag sollen sportliche Leistungen von Schülerinnen und Schülern in verschiedenen Disziplinen gespeichert und Ranglisten erstellt werden. Für die Datenspeicherung verwendet man zwei Tabellen: die bereits vorhandene Tabelle person und zusätzlich eine zweite Tabelle sport mit den Sportergebnissen. Mit Hilfe der Schlüsselfelder werden die beiden Tabellen miteinander verknüpft.

Die Verteilung der Daten auf zwei Tabellen bringt folgende Vorteile:

  • Die Tabelle person wird nicht durch zusätzliche Tabellenspalten belastet und kann daher effizienter verwaltet werden
  • In der Tabelle sport müssen keine Personaldaten der Schülerinnen und Schülern eingegeben werden. Es genügt nur eine einzige Zahl im Feld id, die beim Sporttag als Startnummer verwendet werden kann
  • Bei allfälliger Aktualisierung der Personaldaten, wie z.B. Wohnortwechsel, wird die Aktualisierung nur in einer Tabelle durchgeführt

Die Tabellenverknüpfungen werden nicht beim Erzeugen der Tabellen, sondern mit den SELECT-Abfragen definiert. Grundsätzlich können solche Abfragen mit JOIN...ON - Befehl oder mit WHERE-Befehl realisiert werden:

SELECT [felder] FROM tabelle1 JOIN tabelle2 ON bedingung

SELECT [felder] FROM tabelle1, tabelle2 WHERE bedingung

Wir erzeugen eine Tabelle sport mit den Feldern id, lauf, weitsprung und hochsprung und fügen einige Datensätze ein.

# CreateTableSport.py

from sqlite3 import *
from prettytable import printTable

con = connect("demo.db")
with con:
    cursor = con.cursor()
    cursor.execute("""CREATE TABLE sport
                  (id INTEGER, 
                  lauf FLOAT, 
                  weitsprung FLOAT, 
                  hochsprung FLOAT)""")  
    cursor.execute("""INSERT INTO sport VALUES 
                  (5, 12.2, 3.8, 1.15),
                  (4, 11.5, 4.2, 1.20),
                  (1, 13.1, 3.7, NULL),
                  (6, 12.8, 4.5, 1.30),
                  (3, 11.9, NULL , 1.25),
                  (2, 12.5, 4.1, 1.30)""")
    cursor.execute("SELECT * FROM sport")
    printTable(cursor)
Programmcode markieren (Ctrl+C kopieren, Ctrl+V einfügen)

Ergebnis:

Erklärungen zum Programmcode:

FLOAT: Die Einträge in dieser Spalte sind Dezimalzahlen. Deswegen sind diese Felder vom Datentyp FLOAT
NULL: Wenn ein Eintrag fehlt, wird im SQL-Befehl NULL eingegeben. In der Python-Tabelle erscheint der Eintrag None

 

  Tabellen person und sport mit JOIN...ON verknüpfen

Beispiel 1: Virtuelle Tabelle erzeugen

Mit einer SELECT-Abfrage kann eine virtuelle Tabelle mit Spalten aus beiden Tabellen erzeugt werden:

SELECT name, vorname, lauf, weitsprung, hochsprung FROM person JOIN sport ON person.id = sport.id 


# JoinEx1.py

from sqlite3 import *
from prettytable import printTable

with connect("demo.db") as con:
    cursor = con.cursor()
    sql = """SELECT name,vorname,lauf, weitsprung, hochsprung FROM person 
             JOIN sport ON person.id = sport.id ORDER BY name, vorname"""
    cursor.execute(sql)
    printTable(cursor)
Programmcode markieren (Ctrl+C kopieren, Ctrl+V einfügen)

Ergebnis:

Erklärungen zum Programmcode:

ON person.id = sport.id : Verbindet die beiden Tabellen über die Felder person.id und sport.id. Häufig werden in der JOIN-Anweisung auch die Feldbezeichnungen mit dem Vorspann person. bzw. sport. versehen:
SELECT person.name, person.vorname, sport.lauf, sport.weitsprung, sport.hochsprung FROM person ....

Beispiel 2: JOIN-Abfrage mit formatierter Ausgabe

Mit printTable() können die Tabellenspalten linksbündig, rechtsbündig oder zentriert angezeigt werden. Die Ausrichtung wird in einer Liste mit "r", "l" bzw. "c" entwprechend der gewünschten Formatierung angegeben 

# JoinEx2.py

from sqlite3 import *
from prettytable import printTable

with connect("demo.db") as con:
    cursor = con.cursor()
    sql = """SELECT name, vorname, lauf, weitsprung, hochsprung
             FROM person INNER JOIN sport ON person.id = sport.id"""
    cursor.execute(sql) 
    printTable(cursor, align =["l", "l", "r", "r", "r"], sortby = "name")
Programmcode markieren (Ctrl+C kopieren, Ctrl+V einfügen)

Ergebnis:


Beispiel 3: Abfragen in verknüpften Tabellen

Es ist oft von Interesse, bei einem Sporttag die Schülerinnen und Schüler herauszufiltern, die in allen Disziplinen gute Leistungen erbracht haben. Dazu muss eine Join-Abfrage mit einer WHERE-Bedingung ergänzt werden. 

# JoinEx3.py

from sqlite3 import *
from prettytable import printTable

with connect("demo.db") as con:
    cursor = con.cursor()
    sql = """SELECT name, vorname, lauf, weitsprung, hochsprung
             FROM person JOIN sport ON person.id = sport.id 
             WHERE lauf <= 13.0 AND weitsprung >= 4.0 AND hochsprung >= 1.2
             ORDER BY name"""   
    cursor.execute(sql) 
    printTable(cursor, align =["l", "l", "r", "r", "r"])
Programmcode markieren (Ctrl+C kopieren, Ctrl+V einfügen)

Ergebnis:


  Tabellen mit WHERE verknüpfen

Beispiel 4: Tabellen person und sport mit WHERE verknüpfen

Auch mit einer WHERE-Bedingung können Informationen aus mehreren Tabellen angezeigt werden. Im folgenden Beispiel werden für jede Disziplin nach Leistungen sortierte Daten herausfiltriert.

# JoinEx3a.py

from sqlite3 import *
from prettytable import printTable

with connect("demo.db") as con:
    cursor = con.cursor()
    sql = """SELECT name, vorname,lauf FROM person, sport
          WHERE person.id = sport.id ORDER BY lauf"""   
    cursor.execute(sql) 
    printTable(cursor, align =["l", "l", "r"])
Programmcode markieren (Ctrl+C kopieren, Ctrl+V einfügen)

Ergebnis:

Erklärungen zum Programmcode:

Um die Weitsprungergebnisse nach Bestleistungen sortiert anzuzeigen, wird beim Sortieren DESC angefügt.

SELECT name, vorname, weitsprung FROM person, sport
WHERE person.id = sport.id ORDER BY weitsprung DESC

Ergebnis: