Schlagwort: sqlite

SQL Update: Datensatz ergänzen mit einem Wert aus einer anderen Tabelle

Aufbau der Tabellen
Aufbau der Tabellen
In einer Datenbank sind 2 Tabellen vorhanden: Account und AccountOld. In der Tabelle Account wird mit einer neuen Struktur gearbeitet, die von der Tabelle AccountOld abweicht. In der alten Tabelle ist die Gruppenzugehörigkeit gespeichert, welche jetzt doch noch übernommen werden soll.

Inhalt der Tabelle Account
Inhalt der Tabelle AccountOld

Um nun den Wert der Gruppenzugehörigkeit aus der Tabelle AccountOld zu übernehmen, haben wir folgende Möglichkeiten für die verschiedenen Datenbanksysteme:

MS SQL-Server
Beim Microsoft SQL-Server können wir dies auf 2 verschiedene Arten erledigen. Mit Hilfe einer Unterabfrage (Sub-Select) oder mit einem INNER-JOIN. Aus meiner Sicht ist jedoch die Variante mit dem INNER-JOIN zu bevorzugen.

Mit einem Sub-Select:

UPDATE Account SET Account.GroupID = (SELECT AccountOld.GroupID 
FROM AccountOLD WHERE Account.Name = AccountOld.Name)

Mit einem INNER JOIN:

UPDATE ACCOUNT SET Account.GroupID = AccountOld.GroupID 
FROM Account INNER JOIN AccountOLD ON Account.Name = AccountOld.Name

SQLite
Bei der SQLite DB funktioniert es nur mit Hilfe eines Sub-Select, da in einem Update-Statement kein FROM oder INNER-JOIN erlaubt ist.

Mit einem Sub-Select:

UPDATE Account SET GroupID = (SELECT AccountOld.GroupID 
FROM AccountOLD WHERE Account.Name = AccountOld.Name)

Resultat des Update-Befehl

Die Tabelle Account nach dem Update-Befehl

Anzeigen der Spaltennamen einer Tabelle, bei einer Sqlite Datenbank, mit Python

Die Spaltennamen einer Tabelle können mit dem „Cursor“ herausgelesen werden. Dazu verwendet man das Attribut „description“ des Cursors. Dieses Attribut enthält alle Spaltennamen der letzten Abfrage, auch wenn diese keine Resultate zurückgeliefert hat.


import sqlite3

#connect to in memory db
connection = sqlite3.connect(':memory:')
mycursor = connection.cursor()

# Create table
mycursor.execute("create table spydb (id INTEGER PRIMARY KEY ASC, ipsend text, iprecv text)")

#select on table
mycursor.execute('select * from spydb order by id desc')

#get col names with the description attribute
names = [tuple[0] for tuple in mycursor.description]
print names;

#close the cursor
mycursor.close()

In der Zeile 14 erhalten wir vom Attribut „description“ ein 7-Tupel pro Spalte. Dabei enthält das erste Element eines Tupels den Spaltennamen und die anderen 6 Elemente sind vom Typ „none“.

Die Ausgabe der Zeile 15: 

 ['id', 'ipsend', 'iprecv']

Verwendung von sqlite3 in Python

Beim Entwickeln von Python-Scripts benötigt man oft eine Möglichkeit, Daten schnell und komfortabel zu speichern. Dazu eignet sich die Programmbibliothek SQLite hervorragend. Bei SQLite befindet sich die gesamte relationale Datenbank in einer einzigen Datei, da dieses System für die Verwendung im Embedded-Bereich entworfen wurde. Dabei unterstützt SQLite viele SQL-Sprachbefehle, welche im SQL-92-Standard festgelegt wurden. Das sind Funktionen wie Transaktionen, Views, Subselects und Trigger. Um SQLite in Python zu verwenden müssen wir die Bibliothek erst einbinden:


import sqlite3

Der nächste Schritt ist das Erstellen einer Verbindung zu der SQLite-Datenbank. Dies geschieht mit Hilfe eines „connection objects“ das die Datenbank repräsentiert.


connection = sqlite3.connect('c:\\temp\spy.db')

Es existiert die Möglichkeit die Datenbank im Memory zu erzeugen, dazu wird anstelle des Pfades zur Datenbank „:memory:“ verwendet.

Hier ein simples Beispiel bei dem eine Tabelle mit dem Namen „spydb“ erstellt wird. Die Tabelle enthält 3 Spalten: id mit dem Autowert, ipsend und iprecv als Textfeld.
In der Zeile 8 und 9 werden Daten in die Tabelle geschrieben und in Zeile 12 mit einem „commit“ die Transaktion ausgeführt.


# create a cursor to work with the database
mycursor = connection.cursor()

# create a new table
mycursor.execute("create table spydb (id INTEGER PRIMARY KEY ASC, ipsend text, iprecv text)")

# insert data to the table
mycursor.execute("insert into spydb (ipsend , iprecv ) values('10.0.0.1','10.0.0.2')")
mycursor.execute("insert into spydb (ipsend , iprecv ) values('10.0.0.3','10.0.0.15')")

# save the changes
connection.commit();

# insert data to the table
mycursor.execute("insert into spydb (ipsend , iprecv ) values('10.0.13.99','10.0.0.15')")

# rollback the changes
connection.rollback();

mycursor.execute('select * from spydb order by id desc')
for row in mycursor:
    print row

# close the cursor if we are finish
mycursor.close()

Wenn wir nun auf der Zeile 20 alle Daten aus der Tabelle auswählen und ausgeben, sieht dies wie folgt aus:

In der Zeile 15 wird ein weiterer Datensatz hinzugefügt, dieser wird jedoch nicht ausgegeben, da die Transaktion rückgängig (Zeile 18) gemacht wurde.

Python Docs