############################################## sqlite3 - DB-API 2.0 Schnittstelle für SQLite ############################################## Connection- und Cursorobjekt erzeugen ======================================== sqlite3 stellt eine zur DB-API 2.0 konsistente SQL Schnittstelle bereit. Das Modul wird mit:: from sqlite3 import dbapi2 as sqlite importiert. Für die Benutzung des Moduls muss ein ``Connection``-Objekt mit:: con = sqlite.connect("Data/trainingdb") erzeugt werden. Dieser Methode wird der Name der Datenbank übergeben - im Beispiel die Datenbank ``trainingdb`` im Unterverzeichnis ``Data``. Falls noch keine Datenbank mit dem spezifizierten Namen existiert, wird diese erzeugt. Das Verzeichnis muss jedoch schon existieren. Eine nicht-persistente Datenbank im RAM kann durch Angabe des Namens ``:memory:`` angelegt werden. Für das erzeugte ``Connection``-Objekt muss dann ein Cursor durch den Aufruf:: cur=con.cursor() erzeugt werden. Über die ``execute()``-Methode des Cursors können dann alle SQLite-Kommandos eingegeben und ausgeführt werden. Eine ausführliche Liste von SQLite Kommandos ist z.B. über http://sqlite.org/lang.html zugänglich. Das Python Modul ``sqlite3`` ist z.B. in http://docs.python.org/library/sqlite3.html# dokumentiert. Erzeugen einer Tabelle ========================= Für die angelegte Datenbank, kann nach dem Erzeugen des ``Connection``-Objekts und des entsprechenden Cursors eine neue Tabelle durch:: cur.execute('''create table if not exists running (date text, distance real, time real, shoe text)''') angelegt werden. Die hier erzeugte Tabelle mit dem Namen ``running`` enthält die Spalten ``date``, ``distance``, ``time`` und ``shoe``. In die Spalten ``date`` und ``shoe`` werden jeweils Objekte vom Typ ``TEXT`` geschrieben, in die beiden anderen Spalten Objekte vom Typ ``REAL``. Weitere SQLite typen und ihre entsprechenden Python-Typen sind folgender Tabelle zu entnehmen. +-----------------------------+-----------------+ | Python type | SQLite type | +=============================+=================+ | :const:`None` | ``NULL`` | +-----------------------------+-----------------+ | :class:`int` | ``INTEGER`` | +-----------------------------+-----------------+ | :class:`long` | ``INTEGER`` | +-----------------------------+-----------------+ | :class:`float` | ``REAL`` | +-----------------------------+-----------------+ | :class:`str` (UTF8-encoded) | ``TEXT`` | +-----------------------------+-----------------+ | :class:`unicode` | ``TEXT`` | +-----------------------------+-----------------+ | :class:`buffer` | ``BLOB`` | +-----------------------------+-----------------+ Datensätze in Tabelle eintragen ================================= Neue Datensätze können mit dem SQLite Schlüßelwort ``insert`` eingetragen werden. Mit den folgenden Kommandos werden 4 Datesnsätze in die oben angelegte Tabelle geschrieben:: cur.execute('''insert into running values('01.01.2010',15.3,61,'Brooks Adrenaline')''') cur.execute('''insert into running values('02.01.2010',20.2,88,'Asics Kayano')''') cur.execute('''insert into running values('03.01.2010',13,51,'Brooks Adrenaline')''') cur.execute('''insert into running values('05.01.2010',30.8,130,'Asics Kayano')''') con.commit() Zu beachten ist der finale Aufruf der ``Connection``-Methode ``commit()``. Damit erst werden die oben erzeugten Einträge in der Datenbank abgespeichert. SQL-Anweisungen wie ``insert`` können auch parameterisiert angegeben werden, z.B. wenn die Werte von Python-Variablen in die Datenbank geschrieben werden sollen. Für die parameterisierten Anweisungen ist in sqlite3 das Fragezeichen ``?`` als Platzhalter zu verwenden.:: liste = [('06.01.2010',18.5,80,'Brooks Adrenaline'), ('07.01.2010',10,38.45,'Brooks Adrenaline'), ('08.01.2010',12,54,'Asics Kayano')] for e in liste: cur.execute('insert into running values(?,?,?,?)',e) con.commit() Datenbankabfrage ===================== Für die Abfrage von Daten aus der Datenbank wird das SQLite Schlüßelwort ``select`` verwendet. Nach dem Aufruf der ``execute()``-Methode mit dem Schlüßelwort ``select``, enthält das Cursor-Objekt alle Elemente der Tabelle, die mit der Abfrage übereinstimmen. Für den Import der Daten in das Python-Programm stehen generell 3 Möglichkeiten bereit: **1. Cursor als Iterator einsetzen** Cursor-Objekte sind iterierbar. D.h. auf die nach einer Abfrage mit ``select`` erhaltenen Elemente des Cursor-Objekts, kann über eine einfache Schleife zugegriffen werden, wie folgendes Beispiel demonstriert.:: print type(cur.execute('select * from running order by distance')) cur.execute('select * from running order by distance') for a in cur: print a **2. Zugriff auf einzelne Elemente des Abfrageergebnisses** Nach der Abfrage über die ``execute()``-Methode des Cursors kann auf einzelne Elemente sequenziell mit der Cursor-Methode ``fetchone()`` zugegriffen werden. Die folgende Abfrage liefert z.B. zuerst den Eintrag der Datenbank, für den der distance-Wert minimal ist und danach den Eintrag mit zweitkleinster Distanz.:: cur.execute('select * from running order by distance') print cur.fetchone() print cur.fetchone() **3. Zugriff auf alle Elemente des Abfrageergebnisses** Auf alle Elemente des Afrageergebnisses kann neben der in Punkt 1 beschriebenen Methode auch die ``fetchall()``-Methode des Cursors eingesetzt werden. Z.B.:: cur.execute('select * from running order by distance') print cur.fetchall() Gefilterte Abfrage ===================== Für die Abfrage von Datenbankeinträgen, deren Tabelleneinträge vordefinierte Werte annehmen werden die SQLite-Schlüßelworte ``select`` und ``where`` eingesetzt. Die nach ``where`` definierte Bedingung gibt an, welche Werte die gewünschten Abfrageergebnisse annehmen müssen. Im folgenden Beispiel wird nach allen Einträgen mit einem bestimmten shoe-Wert gesucht und über alle diese Einträge die Distanz aufsummiert.:: sumKmBrooks=0 for a in cur.execute("select * from running where shoe='Brooks Adrenaline'"): print a sumKmBrooks+=a[1] print "Kilometer auf Brooks: ",sumKmBrooks Wenn die Bedingung nach ``where`` nicht auf exakte Gleichheit getestet werden soll, sondern nur z.B. ein vordefinierter Teilstring in den Resultaten enthalten sein soll, kann das SQLite-Schlüßelwort ``like`` verwendet werden. Z.B. könnte in der oben aufgeführten Anweisungssequenz, die zweite Zeile durch:: for a in cur.execute("select * from running where shoe like 'Brooks%'"): ersetzt werden. Dann werden alle Einträge geliefert, die zu beliebigen shoe-Einträgen mit dem Anfang ``Brooks`` passen. Mithilfe der Schlüßelworte ``select distinct`` werden nur die verschiedenen Werte der nach ``distinct`` angegebenen Spalten geliefert. Z.B. liefert:: cur.execute("select distinct shoe from running") cur.fetchall() eine Liste aller in der Datenbank aufgeführten shoe-Werte. Wenn nach ``distinct`` kein Spaltenname angegeben wird, dann liefert die Abfrage alle verschiedenen Einträge der Datenbank. Mehrfach vorkommende Einträge werden nicht mehrfach ausgegeben. Datensätze in der Tabelle modifizieren ======================================== Datensätze, die sich bereits in der Datenbank befinden können mit dem SQLite Schlüßelwort ``update`` modifiziert werden. Zum Beispiel wird mit folgender Anweisung die Distanz des Eintrags mit Datum ``08.01.2008`` auf 12.5 gesetzt.:: newdis=12.5 date="08.01.2010" cur.execute("Update running set distance = ? where date=?",[newdis,date]) con.commit() Hinzufügen von Spalten und Werte eintragen ============================================== In bestehende Tabellen können mit dem Befehl ``alter table`` neue Spalten eingefügt werden. Danach können in die Felder der neuen Spalte mit dem Befehl ``update`` Werte eingetragen werden. Im folgenden Beispiel wird zunächst in die bestehende Datenbank eine neue Tabelle eingefügt. In diese Tabelle werden 3 Zeilen eingetragen. Danach wird die neue Spalte ``bike`` angelegt. Schließlich werden in die neue Spalte 3 Werte geschrieben.:: cur.execute('''create table if not exists cycling (date text, distance real, time real)''') cur.execute('''insert into cycling values('01.02.2010',45,92)''') cur.execute('''insert into cycling values('02.02.2010',70,122)''') cur.execute('''insert into cycling values('03.02.2010',55,110)''') bikes=["mtb","racebike","racebike"] cur.execute('''alter table cycling add column bike text''') for i,b in enumerate(bikes): cur.execute("update cycling set bike=(?) where rowid=(?)",[bikes[i],i+1]) con.commit() Aus diesem Beispiel geht auch hervor, dass beim Anlegen einer SQLite-Tabelle automatisch eine Spalte mit dem Namen``rowid`` angelegt wird. Die erste Zeile der Tabelle erhält die ``rowid=1``, für alle weiteren Zeilen wird der Zähler einfach inkrementiert. Löschen von Einträgen ======================= Mit dem SQLite Schlüßelwort ``delete`` können Einträge in der Datenbank gelöscht werden. Mit der folgenden Anweisung werden z.B. alle Einträge in deren shoe Spalte der Wert ``Asics Kayano`` steht gelöscht.:: cur.execute('''delete from running where shoe="Asics Kayano"''') con.commit() Löschen einer Tabelle ======================= Mit dem SQLite Schlüßelwort ``drop table`` bzw. ``drop table if exists`` kann eine Tabelle in der Datenbank komplett gelöscht werden. Z.B. wird mit:: cur.execute('''drop table if exists running''') die Tabelle ``running`` gelöscht.