* Postgres-Datenbank-Treiber für Python
===== Transaction-Handling =====
* jede Aktion in der Datenbank (jedes aufrufen von cursor.execute()) erzeugt eine Transaktion (wenn nicht eine offene besteht)
* per Default ist autocommit=False für Datenbankverbindungen
* nach dem Absetzen von cursor.execute wird die Transaktion also nicht committed
* es muss also explizit committed werden
Es gibt mehrere Möglichkeiten das commit oder rollback über Context-Blöcke zu steuern
with psycopg.connect(someconnectionninformation) as connection:
cursor = connection.cursor()
cursor.execute(somestatement)
cursor.execute(someotherstatement)
^ In diesem Fall wird am Ende des Blocks commit gemacht oder rollback (abhängig davon ob eines der Statements fehlgeschlagen ist oder alle durchgelaufen sind). \\
Außerdem wird die Verbindung zur Datenbank danach geschlossen
with psycopg.connect(someconnectionninformation) as connection:
cursor = connection.cursor()
with connection.transaction():
cursor.execute(somestament)
cursor.execute(somestament)
with connection.transaction():
cursor.execute(somestament)
cursor.execute(somestament)
^ Die beiden transaction-Blöcke werden unabhängig voneinander commited.
Man muss allerdings aufpassen, wenn außerhalb der transaction-Blöcke Statements ausgeführt werden, dann starten diese implizit Transaktionen und die transaction-Blöcke erstellen dann nur savepoint sub-transactions. \\
Ggf. sollte man beim connect dann autocommit=True setzen, dann werden alle Statements außerhalb der transaction-Blöcke als eigene Transaktionen gehandhabt, die sofort commited sind.
===== Server-side binding =====
* Technik bei der das SQL-Statement und die Werte getrennt an die Datenbank gesendet werden
* Ziel ist u.a. die Vermeidung von sql-Injection durch Trennung von Statement und Werten
* die Datenbank wertet Werte nicht aus -> dadurch können sie keine Statements enthalten
* ist auf Statement limitiert
* ein execute()-Call kann nur ein Statement enthalten das Server-side-binding macht
conn.execute(insert into whatever values(%s); select * from somethingelse where id = %s, ("firstvalue", "secondvalue"))
* obiges funktioniert nicht
* wenn mehrere Statements vorhanden sind, aber nur eins Server-side-binding nutzt funktioniert das
===== Mischen von Transaktions- und Nicht-Transaktionsstatements in einem execute =====
*
* die meisten Kommandos (auch DDL-Kommandos) können in einer Transaktion ausgeführt werden
* es gibt aber Ausnahmen wie "create database"
* Postgres fasst intern alle Statements eines cursor()-Aufrufs zu einer Transaktion zusammen
* oder auch
* ^ deswegen kann man im gleichen execute()-Aufruf keine Kommandos mischen die Transaktionen unterstützen und solche die es nicht tun
===== Temporary tables ======
* Existieren nur in der Session in der sie erstellt wurden
* verschiedene Verbindungen zur Datenbank können also die gleiche Tabelle ggf. mit unterschiedlichem Inhalt haben
create temp table somename (somerow varchar) on commit delete rows;
* "on commit" definiert was bei einem Commit mit der Tabelle passieren soll
* preserve_rows (Default in postgres, aber nicht im Standard) -> erhalte die Tabelle wie sie ist, inkl. Inhalt
* delete rows -> truncate aka. lösche alle Einträge, aber erhalte die leere Tabelle
* drop -> lösche die Tabelle
===== Copy ======
* Eigentlich eine Möglichkeit eine größere Menge Einträge aus Dateien (oder stdin) einzulesen oder nach stout/Datei auszugeben
* ist meist effizienter als insert into
* statt einer Tabelle kann man auch sub-select-Statements benutzen
* schlägt das einfügen eines Datensatzes fehl wird alles rückabgewickelt
* im Gegensatz zu insert into gibt es kein "on conflict" -> alles oder nichts
* es dürfen also auch keine Doubletten in den Daten sein (die einen unique-Spalte betreffen würden)
Daten in eine Tabelle einfügen:
with cursor.copy("copy table_name (column1, column2) from stdin") as copy:
for record in records:
copy.write_row(record)
* record muss dabei ein iterateable sein, auch wenn nur eine Spalte eingefügt wird
* also zum Beispiel ein tuple
* liegt daran das es auch mehrere Spalten sein könnten -> (spalte1, spalte2 usw.)
Daten aus einer Tabelle auslesen:
with cursor.copy("copy table_name (column1, column2) to stdout") as copy:
for record in copy.rows():
Some action
Traceback (most recent call last):
File "mytest.py", line 24, in
copy.write_row(word)
File "/usr/lib/python3.8/contextlib.py", line 120, in __exit__
next(self.gen)
File "usr/share/virtualenvs/noise3-IfVx2oqf/lib/python3.8/site-packages/psycopg/cursor.py", line 902, in copy
raise ex.with_traceback(None)
psycopg.errors.BadCopyFileFormat: extra data after last expected column
CONTEXT: COPY personen, line 1: "H a n s"
^ Hier wurde vergessen das der Datensatz für die Spalte ein Iterateable sein muss. \\
Daher wurde "Getting" nicht als String, sondern als Sequenz betrachtet und versucht in mehr Spalten einzufügen als vorhanden sind.
Fehlschlagender Code (Beispiel):
names = ["Hans", "Peter", "Dieter"]
with cursor.copy("copy personen (vorname) from stdin") as copy:
for name in names:
copy.write_row(name)
* Daten werden aus einem iterateable names gelesen
* name ist eine Zeichenkette
* copy.write_row erwartet aber einen Datensatz
* ein Datensatz ist ein iterateable -> ein tuple, eine Liste usw.
* hier wurde ein String übergeben -> ein String ist auch ein iterateable -> also wird über den String iteriert
* da der String länger ist als die Menge der Spalten -> es gibt nur eine, kommt es zum Fehler
Hier sollte man auch aufpassen:
names = ["Hans", "Peter", "Dieter"]
with cursor.copy("copy personen (vorname) from stdin") as copy:
for name in names:
copy.write_row((name))
* name wird hier bei der Übergabe an copy.write_row scheinbar auf tuple gecastet
* allerdings nicht wirklich, es fehlt ein ","
* es muss (name,) heißen, sonst ist es ein String