Datenbankzugriff

Aus R und Python verbinden, abfragen, schreiben und sauber schliessen.

Kernideen

• Verbindung aufbauen, Abfrage senden, Ergebnis als Data Frame entgegennehmen. Danach wird gearbeitet wie mit jeder anderen Tabelle.
• Werte gehören als Parameter in die Abfrage, nie in eine zusammengebaute Zeichenkette. Sonst drohen SQL-Injektion und Fehler bei Apostrophen.
• Filtern und Aggregieren in der Datenbank, Modellieren und Zeichnen danach in R oder Python.
• Die Datenbank kennt eigene Typen. SQLite speichert Datum als Text und Wahrheitswerte als 0 und 1.
• Transaktionen sind ganz oder gar nicht. Nach einem Fehler bleibt nichts halb Geschriebenes zurück.
• Verbindungen gehören geschlossen, am besten über einen Kontextmanager.

Erklärung

Vorwissen: SQL für die Abfragen selbst und Datenmodellierung für den Aufbau der Tabellen. Was mit dem Ergebnis danach geschieht, steht unter Data Wrangling.

Die Bausteine

Schritt	R (DBI)	Python
verbinden	dbConnect(RSQLite::SQLite(), "laden.sqlite")	sqlite3.connect("laden.sqlite")
abfragen	dbGetQuery(con, sql)	pd.read_sql_query(sql, verbindung)
ändern	dbExecute(con, sql)	verbindung.execute(sql)
Parameter	params = list(wert)	params=(wert,)
schreiben	dbWriteTable(con, "tabelle", df)	df.to_sql("tabelle", verbindung)
Tabellen auflisten	dbListTables(con)	SELECT name FROM sqlite_master
schliessen	dbDisconnect(con)	verbindung.close()

Für andere Datenbanken wechselt nur der Treiber: RPostgres::Postgres() beziehungsweise psycopg, RMariaDB::MariaDB() beziehungsweise mysql-connector. Die Abfragen selbst bleiben gleich, siehe PostgreSQL und MySQL.

Wieviel gehört in die Datenbank

Filtern und Aggregieren möglichst früh in SQL, damit nur die benötigte Menge über die Leitung geht. Modellierung, Grafik und alles Iterative danach in R oder Python. Eine Million Zeilen zu laden, um daraus fünf Gruppensummen zu bilden, ist die häufigste vermeidbare Wartezeit.

Zeichenketten nie selbst zusammenbauen

Ein Wert, der aus einer Eingabe stammt, wird als Parameter übergeben. Die Datenbank behandelt ihn dann als Wert und nie als Teil der Abfrage. Das verhindert zweierlei: die absichtliche Manipulation (SQL-Injektion) und den alltäglichen Fehler, dass ein Apostroph im Namen die Abfrage zerlegt.

Typen an der Grenze

SQLite kennt nur wenige Typen. Ein Datum wird als Text gespeichert, ein Wahrheitswert als 0 oder 1, und beim Lesen kommt genau das zurück. Wer Datumsangaben weiterverarbeiten will, wandelt sie nach dem Lesen um (parse_dates beziehungsweise as.Date), siehe Datum und Zeit. Grössere Datenbanken haben echte Datums- und Wahrheitstypen, aber auch dort lohnt der Blick auf die Typen nach dem ersten Import.

Kurz nachgeschlagen

Aufgabe	R	Python
Verbindung mit Kontextmanager	on.exit(dbDisconnect(con))	with sqlite3.connect(...) as v:
Transaktion	dbBegin(con), dbCommit(con), dbRollback(con)	with verbindung:
viele Zeilen einfügen	dbAppendTable(con, "t", df)	executemany(sql, zeilen)
Tabelle überschreiben	dbWriteTable(..., overwrite = TRUE)	to_sql(..., if_exists="replace")
Ergebnis stückweise lesen	dbSendQuery mit dbFetch(n = 1000)	pd.read_sql_query(..., chunksize=1000)
Spaltentypen ansehen	dbGetQuery(con, "PRAGMA table_info(t)")	dieselbe Abfrage

Beispiele

Beispiel 1: Abfragen und was zurückkommt

Frage und Datenlage

Der Umsatz je Kategorie, und ein Blick auf die Typen: Was macht die Datenbank aus dem Datum?

Rechnung

R

ergebnis <- dbGetQuery(con, "
  SELECT a.kategorie, SUM(b.betrag) AS umsatz
  FROM bestellung b JOIN artikel a ON a.artikel_id = b.artikel_id
  GROUP BY a.kategorie ORDER BY umsatz DESC")
ergebnis

   kategorie umsatz
1 Elektronik   2400
2  Pneumatik   1968
3   Mechanik    740

sapply(ergebnis, class)

  kategorie      umsatz 
"character"   "numeric" 

roh <- dbGetQuery(con, "SELECT * FROM bestellung LIMIT 2")
sapply(roh, class)

 bestell_id  artikel_id       datum       menge      betrag 
  "integer"   "integer" "character"   "integer"   "numeric" 

roh$datum <- as.Date(roh$datum)
sapply(roh, class)

bestell_id artikel_id      datum      menge     betrag 
 "integer"  "integer"     "Date"  "integer"  "numeric" 

Python

ergebnis = frage("""SELECT a.kategorie, SUM(b.betrag) AS umsatz
                    FROM bestellung b JOIN artikel a ON a.artikel_id = b.artikel_id
                    GROUP BY a.kategorie ORDER BY umsatz DESC""")
print(ergebnis.to_string(index=False))

 kategorie  umsatz
Elektronik  2400.0
 Pneumatik  1968.0
  Mechanik   740.0

print(ergebnis.dtypes.astype(str).to_dict())

{'kategorie': 'object', 'umsatz': 'float64'}

roh = frage("SELECT * FROM bestellung LIMIT 2")
print(roh.dtypes.astype(str).to_dict())

{'bestell_id': 'int64', 'artikel_id': 'int64', 'datum': 'object', 'menge': 'int64', 'betrag': 'float64'}

mit_datum = pd.read_sql_query("SELECT * FROM bestellung LIMIT 2", verbindung,
                              parse_dates=["datum"])
print(mit_datum.dtypes.astype(str).to_dict())

{'bestell_id': 'int64', 'artikel_id': 'int64', 'datum': 'datetime64[ns]', 'menge': 'int64', 'betrag': 'float64'}

Output Zeile für Zeile

Kategorie	Umsatz	Erklärung
Elektronik	2400.0
Pneumatik	1968.0
Mechanik	740.0	dieselben Zahlen wie unter SQL, Beispiel 3

Spalte	Typ nach dem Lesen	Erklärung
kategorie	Text
umsatz	numeric beziehungsweise float64	Die Summe kommt als Zahl zurück.
datum	Text, nicht Datum	SQLite hat keinen Datumstyp; gespeichert ist die Zeichenkette 2026-01-14.
datum nach der Umwandlung	Date beziehungsweise datetime64[ns]	as.Date() beziehungsweise parse_dates=["datum"] beim Lesen.

Interpretation und Ergebnissatz

Das Ergebnis einer Abfrage ist ein gewöhnlicher Data Frame, und damit endet die Datenbankarbeit. Der einzige Punkt, der Aufmerksamkeit braucht, sind die Typen an der Grenze: Ein Datum, das als Text ankommt, sortiert und gruppiert falsch, sobald das Format nicht JJJJ-MM-TT ist.

Die Abfrage liefert drei Zeilen mit Elektronik an der Spitze (2400.0). Die Spalte datum kommt als Text zurück, weil SQLite keinen Datumstyp kennt, und muss nach dem Lesen umgewandelt werden.

Beispiel 2: Parameter statt zusammengebauter Zeichenkette

Frage und Datenlage

Eine Abfrage filtert nach einer Kategorie, die aus einer Eingabe stammt. Was passiert bei der Eingabe x' OR '1'='1, und was bei einer Bezeichnung mit Apostroph?

Rechnung

R

eingabe <- "Pneumatik"
nrow(dbGetQuery(con, paste0("SELECT * FROM artikel WHERE kategorie = '",
                            eingabe, "'")))

[1] 2

boese <- "x' OR '1'='1"
gebaut <- paste0("SELECT * FROM artikel WHERE kategorie = '", boese, "'")
gebaut

[1] "SELECT * FROM artikel WHERE kategorie = 'x' OR '1'='1'"

nrow(dbGetQuery(con, gebaut))                                   # alle Zeilen

[1] 5

nrow(dbGetQuery(con, "SELECT * FROM artikel WHERE kategorie = ?",
                params = list(boese)))                          # keine

[1] 0

tue("INSERT INTO artikel VALUES (6, ?, 'Mechanik')", params = list("Winkel 90'"))
versuch <- try(dbGetQuery(con, paste0(
  "SELECT * FROM artikel WHERE bezeichnung = '", "Winkel 90'", "'")), silent = TRUE)
if (inherits(versuch, "try-error")) "Syntaxfehler" else "gelungen"

[1] "Syntaxfehler"

nrow(dbGetQuery(con, "SELECT * FROM artikel WHERE bezeichnung = ?",
                params = list("Winkel 90'")))

[1] 1

Python

eingabe = "Pneumatik"
print(len(frage(f"SELECT * FROM artikel WHERE kategorie = '{eingabe}'")))

2

boese = "x' OR '1'='1"
gebaut = f"SELECT * FROM artikel WHERE kategorie = '{boese}'"
print(gebaut)

SELECT * FROM artikel WHERE kategorie = 'x' OR '1'='1'

print("zusammengebaut:", len(frage(gebaut)), "Zeilen")

zusammengebaut: 5 Zeilen

print("mit Parameter:", len(frage("SELECT * FROM artikel WHERE kategorie = ?",
                                  params=(boese,))), "Zeilen")

mit Parameter: 0 Zeilen

_ = verbindung.execute("INSERT INTO artikel VALUES (6, ?, 'Mechanik')", ("Winkel 90'",))
verbindung.commit()
try:
    frage("SELECT * FROM artikel WHERE bezeichnung = 'Winkel 90''")
except Exception as fehler:
    print("zusammengebaut:", type(fehler).__name__)

zusammengebaut: DatabaseError

print("mit Parameter:", len(frage("SELECT * FROM artikel WHERE bezeichnung = ?",
                                  params=("Winkel 90'",))))

mit Parameter: 1

Output Zeile für Zeile

Fall	Ergebnis	Erklärung
harmlose Eingabe, zusammengebaut	2 Zeilen	Funktioniert, solange niemand etwas Ungewöhnliches eingibt.
Eingabe x' OR '1'='1, zusammengebaut	alle Zeilen der Tabelle	Der Apostroph beendet die Zeichenkette, der Rest wird zur Bedingung: WHERE kategorie = 'x' OR '1'='1'. Das ist immer wahr.
dieselbe Eingabe als Parameter	0 Zeilen	Die Datenbank sucht nach einer Kategorie, die wörtlich so heisst. Es gibt keine.
Bezeichnung Winkel 90', zusammengebaut	Syntaxfehler	Derselbe Mechanismus, nur ohne böse Absicht: Der Apostroph im Namen zerlegt die Abfrage.
dieselbe als Parameter	1 Zeile

Interpretation und Ergebnissatz

Beide Zeilen der Tabelle, die Manipulation und der Apostroph, haben dieselbe Ursache: Werte und Anweisung werden vermischt. Mit Parametern ist die Trennung eingebaut, und das ist der einzige zuverlässige Schutz. Selbstgebautes Maskieren von Anführungszeichen ist fehleranfällig. Die Regel gilt unabhängig davon, ob eine Anwendung öffentlich erreichbar ist: Der Apostroph in O'Brien kommt auch ohne Angreifer vor.

Die Eingabe x' OR '1'='1 liefert über eine zusammengebaute Abfrage alle Artikel statt keinem; als Parameter übergeben liefert sie null Zeilen. Ein Apostroph im Namen führt beim Zusammenbauen zu einem Syntaxfehler, als Parameter funktioniert er.

Beispiel 3: Aggregieren in der Datenbank oder im Speicher

Frage und Datenlage

Der Umsatz je Artikel. Einmal, indem alle Bestellungen geladen und in pandas beziehungsweise dplyr gruppiert werden, einmal, indem die Datenbank gruppiert.

Rechnung

R

alles <- dbGetQuery(con, "SELECT * FROM bestellung")
nrow(alles)

[1] 10

tapply(alles$betrag, alles$artikel_id, sum)

   1    2    3    4    5 
1200  768  560  180 2400 

in_db <- dbGetQuery(con, "SELECT artikel_id, SUM(betrag) AS umsatz
                          FROM bestellung GROUP BY artikel_id")
nrow(in_db)

[1] 5

in_db

  artikel_id umsatz
1          1   1200
2          2    768
3          3    560
4          4    180
5          5   2400

Python

alles = frage("SELECT * FROM bestellung")
print(len(alles))

10

print(alles.groupby("artikel_id")["betrag"].sum().to_dict())

{1: 1200.0, 2: 768.0, 3: 560.0, 4: 180.0, 5: 2400.0}

in_db = frage("""SELECT artikel_id, SUM(betrag) AS umsatz
                 FROM bestellung GROUP BY artikel_id""")
print(len(in_db))

5

print(dict(zip(in_db["artikel_id"], in_db["umsatz"])))

{1: 1200.0, 2: 768.0, 3: 560.0, 4: 180.0, 5: 2400.0}

Output Zeile für Zeile

Weg	übertragene Zeilen	Ergebnis
alles laden, dann gruppieren	10	1200.0, 768.0, 560.0, 180.0, 2400.0
in der Datenbank gruppieren	5	dieselben Zahlen

Bei zehn Bestellungen ist der Unterschied zwei gegen fünf Zeilen und damit belanglos. Der Punkt ist das Verhältnis: Übertragen wird einmal die Datenmenge, einmal das Ergebnis. Bei einer Million Bestellungen und fünf Artikeln bleibt das Ergebnis fünf Zeilen gross, während der andere Weg eine Million Zeilen durch die Leitung und in den Arbeitsspeicher schiebt.

Interpretation und Ergebnissatz

Die Regel lautet deshalb: Alles, was die Datenmenge verkleinert (filtern, gruppieren, zusammenfassen), gehört in die Abfrage. Alles, was Ausprobieren verlangt (Modelle, Grafiken, Kennzahlen), gehört danach in R oder Python. Wer unsicher ist, wie teuer eine Abfrage ist, sieht sie sich mit EXPLAIN QUERY PLAN an.

Beide Wege liefern dieselben fünf Umsätze, der eine überträgt dafür zehn Zeilen, der andere fünf. Das Verhältnis bleibt gleich, wenn die Tabelle wächst; die Gruppierung in der Datenbank ist dann der einzige gangbare Weg.

Beispiel 4: Einen Data Frame schreiben

Frage und Datenlage

Eine kleine Tabelle mit einem Wahrheitswert und einem fehlenden Wert wird in die Datenbank geschrieben und wieder gelesen. Kommt dasselbe zurück?

Rechnung

R

neu <- data.frame(artikel_id = c(1L, 2L),
                  aktiv = c(TRUE, FALSE),
                  notiz = c("a", NA))
sapply(neu, class)

 artikel_id       aktiv       notiz 
  "integer"   "logical" "character" 

invisible(dbWriteTable(con, "zusatz", neu, overwrite = TRUE))
zurueck <- dbGetQuery(con, "SELECT * FROM zusatz")
zurueck

  artikel_id aktiv notiz
1          1     1     a
2          2     0  <NA>

sapply(zurueck, class)

 artikel_id       aktiv       notiz 
  "integer"   "integer" "character" 

frage("SELECT name, type FROM pragma_table_info('zusatz')")

name	type
artikel_id	INTEGER
aktiv	INTEGER
notiz	TEXT

Python

neu = pd.DataFrame({"artikel_id": [1, 2], "aktiv": [True, False],
                    "notiz": ["a", None]})
print(neu.dtypes.astype(str).to_dict())

{'artikel_id': 'int64', 'aktiv': 'bool', 'notiz': 'object'}

_ = neu.to_sql("zusatz_py", verbindung, index=False, if_exists="replace")
zurueck = frage("SELECT * FROM zusatz_py")
print(zurueck.to_string(index=False))

 artikel_id  aktiv notiz
          1      1     a
          2      0  None

print(zurueck.dtypes.astype(str).to_dict())

{'artikel_id': 'int64', 'aktiv': 'int64', 'notiz': 'object'}

print(frage("SELECT name, type FROM pragma_table_info('zusatz_py')"
            ).to_string(index=False))

      name    type
artikel_id INTEGER
     aktiv INTEGER
     notiz    TEXT

Output Zeile für Zeile

Spalte	vorher	in der Datenbank	nach dem Lesen	Erklärung
artikel_id	ganze Zahl	INTEGER	ganze Zahl
aktiv	TRUE, FALSE	INTEGER	1 und 0	SQLite hat keinen Wahrheitstyp. Wer TRUE zurück will, wandelt nach dem Lesen um.
notiz	"a", fehlend	TEXT	"a", NA beziehungsweise None	Der fehlende Wert überlebt als NULL.

Interpretation und Ergebnissatz

Schreiben ist bequem und hat dieselbe Grenze wie CSV, nur eine Stufe höher: Die Datenbank speichert, was ihre Typen hergeben. Bei SQLite sind das fünf Typen, weshalb Wahrheitswerte zu Zahlen und Datumsangaben zu Text werden. Für Zwischenstände einer Auswertung ist das in Ordnung, solange man die Umwandlung beim Lesen wieder einbaut. Wichtiger ist die Wahl von overwrite beziehungsweise if_exists: Voreingestellt ist das Anhängen oder ein Fehler, nicht das Ersetzen.

Der Wahrheitswert TRUE kommt als 1 zurück, weil SQLite nur INTEGER dafür hat; der fehlende Wert bleibt als NULL erhalten. Die Spaltentypen der erzeugten Tabelle lassen sich mit pragma_table_info nachsehen.

Beispiel 5: Transaktion und Verbindung

Frage und Datenlage

Zwei Bestellungen sollen zusammen eingefügt werden, die zweite verweist auf einen nicht existierenden Artikel. Was steht danach in der Tabelle?

Rechnung

R

tue("PRAGMA foreign_keys = ON")
vorher <- dbGetQuery(con, "SELECT COUNT(*) AS n FROM bestellung")$n

dbBegin(con)
versuch <- try({
  dbExecute(con, "INSERT INTO bestellung VALUES (20,1,'2026-04-01',5,100.0)")
  dbExecute(con, "INSERT INTO bestellung VALUES (21,999,'2026-04-02',5,100.0)")
}, silent = TRUE)

if (inherits(versuch, "try-error")) {
  dbRollback(con)
  "abgebrochen und zurueckgerollt"
} else {
  dbCommit(con)
  "durchgelaufen"
}

[1] "abgebrochen und zurueckgerollt"

c(vorher = vorher,
  nachher = dbGetQuery(con, "SELECT COUNT(*) AS n FROM bestellung")$n)

 vorher nachher 
     10      10 

Python

_ = verbindung.execute("PRAGMA foreign_keys = ON")
vorher = frage("SELECT COUNT(*) AS n FROM bestellung").iloc[0, 0]

try:
    with verbindung:                      # commit am Ende, rollback bei Fehler
        _ = verbindung.execute("INSERT INTO bestellung VALUES (20,1,'2026-04-01',5,100.0)")
        _ = verbindung.execute("INSERT INTO bestellung VALUES (21,999,'2026-04-02',5,100.0)")
except sqlite3.IntegrityError as fehler:
    print("abgebrochen:", fehler)

abgebrochen: FOREIGN KEY constraint failed

nachher = frage("SELECT COUNT(*) AS n FROM bestellung").iloc[0, 0]
print(vorher, nachher)

10 10

with verbindung:
    _ = verbindung.execute("INSERT INTO bestellung VALUES (22,1,'2026-04-03',5,100.0)")
print(frage("SELECT COUNT(*) AS n FROM bestellung").iloc[0, 0])

11

Output Zeile für Zeile

Schritt	Ergebnis	Erklärung
vor der Transaktion	10 Bestellungen
zweiter Einfügeversuch	FOREIGN KEY constraint failed	Der Artikel 999 existiert nicht.
nach dem Abbruch	10, unverändert	Auch die erste Einfügung ist zurückgenommen. Genau das macht die Transaktion aus: ganz oder gar nicht.
nach einer erfolgreichen Transaktion	11

Ohne Transaktion stünde die erste Bestellung jetzt in der Tabelle und die zweite nicht, und niemand wüsste, dass der Vorgang unvollständig ist. Der with-Block in Python und dbBegin/dbRollback in R leisten dasselbe.

Interpretation und Ergebnissatz

Transaktionen sind kein Thema für Fortgeschrittene, sondern die Antwort auf eine alltägliche Frage: Was passiert, wenn es mittendrin schiefgeht? Alles, was fachlich zusammengehört (eine Bestellung mit ihren Positionen, ein Import mit mehreren Tabellen), gehört in eine Transaktion. Und die Verbindung gehört danach geschlossen, in Python am einfachsten über den Kontextmanager, in R mit dbDisconnect() und on.exit().

Nach dem fehlgeschlagenen zweiten Einfügen enthält die Tabelle unverändert zehn Bestellungen: Die Transaktion hat auch die bereits gelungene erste Einfügung zurückgenommen. Erst die erfolgreiche Transaktion erhöht den Bestand auf elf.

Verständnisfragen

Warum sollen Werte als Parameter übergeben und nicht in die Abfrage geschrieben werden?

Sonst kann eine Eingabe die Abfrage verändern, und ein Apostroph zerlegt sie

Richtig. In Beispiel 2 liefert x' OR '1'='1 alle Zeilen statt keiner, und Winkel 90' erzeugt einen Syntaxfehler. Parameter trennen Wert und Anweisung.

Parameter sind schneller

Sie sind es oft, weil der Abfrageplan wiederverwendet wird, aber das ist nicht der Hauptgrund.

Nur bei öffentlichen Anwendungen nötig

Der Apostroph in einem Namen kommt auch ohne Angreifer vor.

Eine Auswertung lädt eine Million Zeilen und bildet daraus zwölf Monatssummen. Was ist daran ungünstig?

Die Aggregation hätte in der Datenbank stattfinden können, dann wären zwölf Zeilen übertragen worden

Richtig. Filtern und Gruppieren möglichst früh in SQL; danach kommt die Auswertung.

Nichts, pandas kann eine Million Zeilen verarbeiten

Kann es, nur werden Übertragung und Arbeitsspeicher unnötig belastet.

Monatssummen gehören nicht in eine Datenbank

Genau dafür ist sie gemacht.

Eine Spalte wurde mit TRUE und FALSE in eine SQLite-Datenbank geschrieben und kommt als 1 und 0 zurück. Ist das ein Fehler?

Nein, SQLite hat keinen Wahrheitstyp und speichert INTEGER

Richtig. Die Umwandlung gehört nach dem Lesen in den Code, wie beim Datum, das als Text zurückkommt.

Ja, die Spalte wurde falsch angelegt

Auch mit BOOLEAN in der Definition speichert SQLite eine Zahl.

Ja, to_sql beziehungsweise dbWriteTable hat einen Fehler

Beide tun, was die Datenbank zulässt.

Ein Import schreibt zwei zusammengehörige Zeilen, und die zweite scheitert. Ohne Transaktion steht die erste in der Tabelle. Warum ist das schlimmer als ein vollständiger Abbruch?

Der Datenbestand ist halb geschrieben, und niemand sieht es ihm an

Richtig. Eine Transaktion macht daraus ganz oder gar nicht, wie in Beispiel 5: nach dem Abbruch stehen unverändert zehn Zeilen da.

Es ist nicht schlimmer, man kann die zweite Zeile nachtragen

Nur wenn jemand merkt, dass sie fehlt.

Die Datenbank wird dadurch langsamer

Die Geschwindigkeit ist hier nicht das Thema.

Eine Abfrage gibt die Spalte datum zurück, und die Sortierung nach Monat ist falsch. Was ist zu prüfen?

Ob das Datum als Text zurückkommt und in welchem Format

Richtig. SQLite speichert Datumsangaben als Text. Im Format JJJJ-MM-TT sortiert Text zufällig richtig, in jedem anderen Format nicht. Nach dem Lesen umwandeln.

Ob die Datenbank einen Index auf datum hat

Ein Index ändert die Sortierreihenfolge nicht.

Ob ORDER BY vor GROUP BY steht

Die Reihenfolge im Text spielt keine Rolle, die Auswertungsreihenfolge ist festgelegt.

Verlinkte Ressourcen

• DBI: Datenbankzugriff in R
• sqlite3 in der Python-Dokumentation
• SQLAlchemy für andere Datenbanken