TigerJython xx
für Gymnasien

Datenbank Twitter


  Datenbankapplikationen wie Twitter wären ohne Datenbanksysteme kaum realisierbar. In einem stark vereinfachten Beispiel zeigen wir, wie bei Twitter die Informationen über User, Follower und Tweets in einer Datenbank gespeichert werden können. Unsere Datenbank twitter besteht aus den Tabellen person, tweets und follower, die in einer Beziehung zueinander stehen.

In der Tabelle person sind Name, Vorname, Wohnort, Geschlecht und Jahrgang der Twitter-Benutzer gespeichert. Am einfachsten verwendet man die Tabelle person aus der Datenbank demo.db, die bereits 6 Datensätze enthält. Das Feld id enthält den Primärschlüssel dieser Tabelle und identifiziert eindeutig jeden Datensatz.

Es wäre sicher keine gute Idee, sämtliche Tweets für jede Person in der Tabelle person abzuspeichern. Mit den vielen langen Textfeldern wäre die Tabelle person sehr gross und könnte nicht mehr effizient verwaltet werden. Es ist vorteilhaft die Tweets als Textfelder mit 140 Zeichen in einer zweiten Tabelle tweets zu speichern. Diese können über den Schlüssel userId abgerufen werden.

Es wäre auch nicht sinnvoll, sämtliche Follower einer Twitter-Users mit ihren Personaldaten in der Tabelle person abzuspeichern. Da die User gleichzeitig Follower anderer Users sind, wird nur jede User-Follower-Beziehung gemäss der nebenstehenden Grafik in der Tabelle follower gespeichert. Die Grafik mit Pfeilen, die die Relation "ist ein Follower von" darstellen, wurde übrigens mit einem Python-Programm erstellt (ArrowDiagram.py [►]).

Tabelle Person
Falls Ihre Tabelle Person nicht mehr vorhanden oder unvollständig ist, können Sie diese mit dem folgenden Programm erstellen. (Eine unvollständige Tabelle kann durch die Aktivierung der Zeile cursor.execute("DROP TABLE person") gelöscht werden.)

# CreateTableUsers.py

from sqlite3 import *
from prettytable import printTable

con = connect("demo.db")
with con:
    cursor = con.cursor()
    cursor.execute("DROP TABLE person")
    cursor.execute("""CREATE TABLE person
                  (id INTEGER PRIMARY KEY, 
                  name VARCHAR(30), 
                  vorname VARCHAR(30), 
                  wohnort VARCHAR(30),
                  geschlecht CHAR(1),  
                  jahrgang INTEGER(4))""")
    cursor.execute("""INSERT INTO person VALUES
                  (1,'Huber','Lia','Bern','w', 2002),
                  (2,'Meier','Luca','Basel','m', 2003),
                  (3,'Frech','Marc','Bern','m', 2000),
                  (4,'Bauer','Paul','Luzern','m', 2003),
                  (5,'Zwahlen','Noe','Thun','m', 2002),
                  (6,'Meier','Nina','Biel','w', 2001)""")      
    cursor.execute("SELECT * FROM person")
    printTable(cursor)
Programmcode markieren (Ctrl+C kopieren, Ctrl+V einfügen)

Tabelle Tweets
# CreateTableTweets.py

from sqlite3 import *
from prettytable import printTable

with connect("demo.db") as con:
    cursor = con.cursor()
    #cursor.execute("DROP TABLE tweets")
    cursor.execute("""CREATE TABLE tweets
                  (userId NOT NULL, tweet TEXT)""")      
    cursor.execute("""INSERT INTO tweets VALUES
                  (4, 'Nina hat heute coole Jacke an'),
                  (2,'Heute gehe ich skifahren'),
                  (5,'Party bei Marc war mega cool'),
                  (3,'Ich lerne jetzt programmieren'),
                  (1,'Nina hat heute coole Jacke an'),
                  (6,'Wer kommt heute ins Kino?'),
                  (1,'Mein neues Smartphone ist mega gut.')""")          
    cursor.execute("SELECT * FROM tweets")
    printTable(cursor)      
Programmcode markieren (Ctrl+C kopieren, Ctrl+V einfügen)

Tabelle Follower
# CreateTableFollower.py

from sqlite3 import *
from prettytable import printTable

with connect("demo.db") as con:
    cursor = con.cursor()
    #cursor.execute("DROP TABLE follower")
    cursor.execute("""CREATE TABLE follower 
                  (userId INTEGER NOT NULL, 
                  followerId INTEGER NOT NULL)""")  
    cursor.execute("""INSERT INTO follower VALUES 
                  (1, 2), (1, 4), (1, 5),(2, 4), (2, 6),(3, 1),
                  (4, 3), (4, 1), (4, 2), (5, 6), (6, 2)""")       
    cursor.execute("SELECT * FROM follower")
    printTable(cursor) 
Programmcode markieren (Ctrl+C kopieren, Ctrl+V einfügen)


  Abfragen in verknüpften Tabellen


Beispiel 1: Man will alle Tweets von Lia Huber lesen

Man muss zuerst Lia Huber in der Tabelle person suchen und den Eintrag im Feld id finden. Dann wählt man aus der Tabelle tweets alle Datensätze, bei welchen der Wert im Feld userId gleich id ist.


# TwitterJoinEx1.py

from sqlite3 import *
from prettytable import printTable

with connect("demo.db") as con:
    cursor = con.cursor()
    sql = """SELECT name, vorname, tweet 
             FROM person, tweets 
             WHERE name = 'Huber' AND vorname = 'Lia' AND person.id = tweets.userId"""
    cursor.execute(sql)
    printTable(cursor)
Programmcode markieren (Ctrl+C kopieren, Ctrl+V einfügen)


Beispiel 2: Man möchte alle Follower von Lia kennen

Dazu sucht man in der Tabelle Follower alle Zeilen mit userId = 1 (Lia) und hollt für die gefundenen followerId's die gewünschten Informationen aus der Tabelle person.

# TwitterJoinEx2.py

from sqlite3 import *
from prettytable import printTable

print("Follower of Lia")
with connect("demo.db") as con:
    cursor = con.cursor()
    sql = """SELECT name, vorname, wohnort FROM person, follower 
             WHERE follower.userId = 1 and person.id = follower.followerId"""
    cursor.execute(sql)
    printTable(cursor)
Programmcode markieren (Ctrl+C kopieren, Ctrl+V einfügen)

 

Beispiel 3 :Der User mit id = 4 interessiert sich für seine männliche Follower und möchte auch den Wohnort und ihr Jahrgang wissen.

# TwitterJoinEx3.py

from sqlite3 import *
from prettytable import printTable

con = connect("demo.db")
with con:
    cursor = con.cursor()
    sql = """SELECT name, vorname, wohnort, geschlecht, jahrgang
             FROM person, follower
             WHERE userId = 4 and person.id = follower.followerId AND geschlecht = 'm'"""
    cursor.execute(sql) 
    printTable(cursor, align =["l", "l", "l", "c", "r"])
Programmcode markieren (Ctrl+C kopieren, Ctrl+V einfügen)