Python and SQL



Einführung

SQL Python Logo Die Geschichte von SQL geht zurück in die 1970er-Jahre. SQL ist eine Abkürzung, die für "Structured Query Language" steht. SQL ist eine Datenbanksprache zur Definition von Datenstrukturen in relationalen Datenbanken sowie zum Bearbeiten, - d.h. Einfügen, Verändern, Löschen, - und Abfragen von Datenbeständen. Es handelt sich um eine relationales Modell, was auf einem Artikel von Edgar F. Codds's aus dem Jahre 1970 basiert. SQL wurde zum Standard des "American National Standards Institute" (ANSI) im Jahr 1986 und des "International Organization for Standardization" (ISO) in 1987.
Da die meisten Leute, die diesen Artikel finden bzw. gesucht haben, sich bereits mehr oder weniger gut mit mSQL, PostgresSQL, MySQL oder anderen Varianten von SQL auskennen, möchten wir nicht weiter darauf eingehen.

Eine Datenbank ist eine organisierte Sammlung von Daten. Die Daten sind typischerweise so organisiert, dass sie Aspekte der Realität so modellieren, dass sie Prozesse unterstützen, die diese Information benötigen.
Der Begriff "Datenbank" kann sich sowhl auf die Daten selbst als auch auf das Datenbankmanagementsystem (DBMS) beziehen. Das DBMS dient der Interaktion zwischen den Benutzern (Usern) und der eigentlichen Datenbank. Benutzer müssen nicht notwendigerweise menschliche Nutzer sein. Auch Programme können als Benutzer eines DBMS fungieren. In diesem Artikel werden wir lernen wie Python oder besser gesagt ein Python-Programm mit einer SQL-Datenbank interagieren kann.

In diesem Zusammenhang gehen wir auf Python und die Schnittstelle zu SQLite und MySQL ein. Der Python-Standard für Datenbank-Schnittstellen ist die DB-API, die von den Python-Schnittstellen für Datenbanken genutzt wird. Die DB-API ist als allgemeines Interface konzipiert, das genutzt werden kann um relationale Datenbanken anzusprechen. In anderen Worten: Der in Python genutzte Code, um mit einer Datenbank zu kommunizieren sollte gleich sein, ganz egal welche Datenbank oder welches Datenbankmodul benutzt wird.

SQLite

SQLite ist ein einfaches relationales Datenbanksystem, welches seine Daten in regulären Datein oder sogar im RAM eines Computers abspeichert. Es wurde für eingebettete Systeme wie Mozilla-Firefox (Bookmarks), Symbian OS oder Android konzipiert. SQLITE ist "ziemlich" schnell, obwohl es nur eine einfache Datei als Speicher benutzt. SQLite kann auch für große Datenbanken verwendet werden.
Um SQLite zu benutzen, muss man das Modul sqlite3 importieren. Zuerst muss man ein Connection-Objekt erzeugen, um eine Datenbank zu nutzen. Das Connection-Objekt repräsentiert die Datenbank. Das Argument von connection - im folgenden Beispiel "companys.db" - fungiert sowohl als Name der Datei, in der die Daten abgespeichert werden, und auch als Name der Datenbank. Falls eine Datei mit diesem Namen bereits existiert, wird sie geöffnet. Dabei muss es sich natürlich um eine SQLite-Datei handeln. Im folgenden Beispiel öffnen wir die Datenbank "company". Die Datei muss nicht existieren: 
>>> import sqlite3
>>> connection = sqlite3.connect("company.db")

Wir haben nun eine Datenbank mit dem Namen "company" erzeugt. Das verhält sich so, als hätte man den Befehl "CREATE DATABASE company;" an einen SQL-Server geschickt. Ruft man "sqlite3.connect('company.db')" erneut auf, wird die zuvor geschriebe Datenbank wieder geöffnet.

Nachdem wir eine Datenbank erzeugt haben, wollen wir natürlich ein oder mehrere Tabellen einfügen. Im folgenden erzeugen wir eine Tabelle "employee" für unsere Datenbank "company": 
CREATE TABLE employee ( 
staff_number INT NOT NULL AUTO_INCREMENT, 
fname VARCHAR(20), 
lname VARCHAR(30), 
gender CHAR(1), 
joining DATE,
birth_date DATE,  
PRIMARY KEY (staff_number) );
So würde man es jedoch auf der SQL-Kommandozeile machen. Wir wollen es natürlich direkt von Python aus tun. Um ein Kommando an SQL oder SQLite schicken zu können, benötigen wir ein Cursor-Objekt. Üblicherweise dient in SQL ein Cursor dazu, die Datensätze zu durchlaufen. Er wird also benutzt, um die Ergebnisse herüberzuholen.
In SQLite (und andern DB-Interfaces) wird er allgemeiner verwendet. Er wird verwendet um alle SQL-Kommandos auszuführen.

Wir erhalten ein Cursor-Objekt, indem wir die cursor-Methode von connection aufrufen. Wir können eine beliebige Anzahl von Cursor erzeugen. Der Cursor wird auch benutzt, um die Datensätze des Ergebnisses zu durchlaufen. Ein komplettes Python-Programm zur Erzeugung der Datenbank "company" und zur Erzeugung einer Tabelle "employee" haben wir im Folgenden aufgeführt: 
sql_command = """
CREATE TABLE employee ( 
staff_number INTEGER PRIMARY KEY, 
fname VARCHAR(20), 
lname VARCHAR(30), 
gender CHAR(1), 
joining DATE,
birth_date DATE);"""
Ein Anmerkung zu SQL-Syntax: Einigen wird aufgefallen sein, dass das AUTOINCREMENT-Feld im SQLite-Code fehlt, den wir im Python-Programm verwenden. Wir haben das staff_number-Feld als "INTEGER PRIMARY KEY" definiert. Ein solcherart gekennzeichnetes Feld wird automatisch zu einem "autoincrement"-Feld in SQLite3.
In anderen Worten: Falls eine Spalte einer Tabelle als "INTEGER PRIMARY KEY" deklariert wird, dann wird jedesmal, wenn jemand NULL als Eingabe für dieses Feld nutzt eine Integer-Zahl eingefügt. Diese Zahl ist ein größer als die größte bisher benutzte Zahl in dieser Spalte. Falls die bisher größte Zahl dem Wert 9223372036854775807 entspricht, also dem größtmöglichen INT in SQLite, wird zufällig ein ungenutzer Wert ausgesucht.

Nun haben wir eine Datenbank mit einer Tabelle aber noch sind keine Daten enthalten. Um die Tabelle zu befüllen, müssen wir das "INSERT"-Kommando an SQLite schicken. Dazu benutzen wir wieder das execute-Kommando.

Das folgende Beispiel ist ein vollständiges lauffähiges Programm. Man muss jedoch entweder die Datei company.db löschen oder den Befehl "DROP TABLE" auskommentieren: 

import sqlite3
connection = sqlite3.connect("company.db")

cursor = connection.cursor()

# delete 
#cursor.execute("""DROP TABLE employee;""")

sql_command = """
CREATE TABLE employee ( 
staff_number INTEGER PRIMARY KEY, 
fname VARCHAR(20), 
lname VARCHAR(30), 
gender CHAR(1), 
joining DATE,
birth_date DATE);"""

cursor.execute(sql_command)

sql_command = """INSERT INTO employee (staff_number, fname, lname, gender, birth_date)
    VALUES (NULL, "William", "Shakespeare", "m", "1961-10-25");"""
cursor.execute(sql_command)


sql_command = """INSERT INTO employee (staff_number, fname, lname, gender, birth_date)
    VALUES (NULL, "Frank", "Schiller", "m", "1955-08-17");"""
cursor.execute(sql_command)

# never forget this, if you want the changes to be saved:
connection.commit()

connection.close()
Natürlich wird man in den meisten Fällen die Daten nicht "wörtlich" in die SQL-Tabelle einfügen. Meistens wird man eine Menge Daten in einer Datenstruktur, z.B. eine Liste oder ein Dictionary haben, und man möchte diese Daten als Eingabe für insert benutzen.

Im folgenden Beispiel nehmen wir an, dass die Datenbank company.db und eine Tabelle "employee" bereits existiert. Wir benutzen die Daten der Liste staff_data bei unseren INSERT-Anweisungen: 
import sqlite3
connection = sqlite3.connect("company.db")

cursor = connection.cursor()

staff_data = [ ("William", "Shakespeare", "m", "1961-10-25"),
               ("Frank", "Schiller", "m", "1955-08-17"),
               ("Jane", "Wall", "f", "1989-03-14") ]
               
for p in staff_data:
    format_str = """INSERT INTO employee (staff_number, fname, lname, gender, birth_date)
    VALUES (NULL, "{first}", "{last}", "{gender}", "{birthdate}");"""

    sql_command = format_str.format(first=p[0], last=p[1], gender=p[2], birthdate = p[3])
    cursor.execute(sql_command)

Nun möchten wir unsere employee-Tabelle abfragen: 

import sqlite3
connection = sqlite3.connect("company.db")

cursor = connection.cursor()

cursor.execute("SELECT * FROM employee") 
print("fetchall:")
result = cursor.fetchall() 
for r in result:
    print(r)
cursor.execute("SELECT * FROM employee") 
print("\nfetch one:")
res = cursor.fetchone() 
print(res)
Falls wir dieses Programm starten, welches wir als "sql_company_query.py" abgespeichert haben, erhalten wir das folgende Ergebnis in Abhängigkeit der tatsächlich enthaltenen Daten: 

$ python3 sql_company_query.py 
fetchall:
(1, 'William', 'Shakespeare', 'm', None, '1961-10-25')
(2, 'Frank', 'Schiller', 'm', None, '1955-08-17')
(3, 'Bill', 'Windows', 'm', None, '1963-11-29')
(4, 'Esther', 'Wall', 'm', None, '1991-05-11')
(5, 'Jane', 'Thunder', 'f', None, '1989-03-14')

fetch one:
(1, 'William', 'Shakespeare', 'm', None, '1961-10-25')


MySQL

Wenn mit Python2.x gearbeitet wird, kann das Modul MySQLdb installiert werden, was sich unter Debian und Ubuntu recht einfach gestaltet: 
sudo apt-get install python-MySQLdb
Wenn Sie jedoch Python3.x verwenden, so installieren Sie den python-mysql-connector: 
sudo apt-get install python3-mysql.connector
Außer dem import-Befehl und den Parametern in der connect-Methode bleibt alles andere wie gehabt, also wie in unserem SQLite-Beispiel: import und connect sehen wie folgt aus: 
import MySQLdb

connection = MySQLdb.connect (host = "localhost",
                              user = "testuser",
                              passwd = "testpass",
                              db = "company")
Die Variante für Python3.x sieht folgendermaßen aus: 
import mysql.connector as mc

connection = mc.connect (host = "localhost",
                         user = "pytester",
                         passwd = "monty",
                         db = "company")
Für die folgenden Beispiel nehmen wir an, dass es den Datenbank-Benutzer "pytester" gibt. Sie können diesen über die Kommandozeile wie folgt anlegen:

Zuerst öffnen die eine MySQL-Session: 
mysql -u root -p
Auf der MySQL-Shell fahren wir fort mit: 
mysql> CREATE USER 'pytester'@'localhost' IDENTIFIED BY 'monty';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL PRIVILEGES ON *.* TO 'pytester'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

mysql>
Wir prüfen die Version des MySQL-Servers mit der im folgenden erstellten Verbindung: 
import mysql.connector as mc

connection = mc.connect (host = "localhost",
                         user = "pytester",
                         passwd = "monty",
                         db = "company")
cursor = connection.cursor()
cursor.execute ("SELECT VERSION()")
row = cursor.fetchone()
print("server version:", row[0])
cursor.close()
connection.close()
Die Ausgabe sieht etwa so aus: 
server version: 5.5.52-0ubuntu0.14.04.1
Wie in unserem eingangs gezeigten SQLite3-Beispiel erstellen wir eine Tabelle "employee" und füllen Sie mit einigen Daten. Das Programm funktioniert nur unter Python3: 
import sys
import mysql.connector as mc

try:
    connection = mc.connect (host = "localhost",
                             user = "pytester",
                             passwd = "monty",
                             db = "company")
except mc.Error as e:
    print("Error %d: %s" % (e.args[0], e.args[1]))
    sys.exit(1)

cursor = connection.cursor()

cursor.execute ("DROP TABLE IF EXISTS employee")

# delete 
#cursor.execute("""DROP TABLE employee;""")

sql_command = """
CREATE TABLE employee ( 
staff_number INTEGER PRIMARY KEY, 
fname VARCHAR(20), 
lname VARCHAR(30), 
gender CHAR(1), 
joining DATE,
birth_date DATE);"""

cursor.execute(sql_command)

staff_data = [ ("William", "Shakespeare", "m", "1961-10-25"),
               ("Frank", "Schiller", "m", "1955-08-17"),
               ("Jane", "Wall", "f", "1989-03-14"),
               ]
               
for staff, p in enumerate(staff_data):
    format_str = """INSERT INTO employee (staff_number, fname, lname, gender, birth_date)
    VALUES ({staff_no}, '{first}', '{last}', '{gender}', '{birthdate}');"""

    sql_command = format_str.format(staff_no=staff, first=p[0], last=p[1], gender=p[2], birthdate = p[3])
    print(sql_command)
    cursor.execute(sql_command)
    
connection.commit()


cursor.close()
connection.close()
Die Ausgabe stimmt mit den Daten überein, welche in die Tabelle "employee" eingefügt wurden: 
INSERT INTO employee (staff_number, fname, lname, gender, birth_date)
    VALUES (0, 'William', 'Shakespeare', 'm', '1961-10-25');
INSERT INTO employee (staff_number, fname, lname, gender, birth_date)
    VALUES (1, 'Frank', 'Schiller', 'm', '1955-08-17');
INSERT INTO employee (staff_number, fname, lname, gender, birth_date)
    VALUES (2, 'Jane', 'Wall', 'f', '1989-03-14');
Anschliessend möchten wir die Datenbank nochmals abfragen: 
import sys
import mysql.connector as mc

try:
    connection = mc.connect (host = "localhost",
                             user = "pytester",
                             passwd = "monty",
                             db = "company")
except mc.Error as e:
    print("Error %d: %s" % (e.args[0], e.args[1]))
    sys.exit(1)

cursor = connection.cursor()

cursor.execute("SELECT * FROM employee") 
print('''Result of "SELECT * FROM employee":''')
result = cursor.fetchall() 
for r in result:
    print(r)

cursor.close()
connection.close()
Wir erhalten folgende Ausgabe: 
Result of "SELECT * FROM employee":
(0, 'William', 'Shakespeare', 'm', None, datetime.date(1961, 10, 25))
(1, 'Frank', 'Schiller', 'm', None, datetime.date(1955, 8, 17))
(2, 'Jane', 'Wall', 'f', None, datetime.date(1989, 3, 14))