SQLite in R

Wie am Ende meines vorherigen Tutorials, Beginners Guide to SQLite, erwähnt, spielen SQLite-Datenbanken ihre Stärken vor allem in Kombination mit R und Python aus. Auf DataCamp hast du bereits gesehen, wie man mit SQLite-Datenbanken aus Python arbeitet (siehe das SQLite in Python Tutorial von Sayak Paul, um zu lernen, wie du SQLite-Datenbanken mit dem sqlite3-Paket in Python manipulierst). In diesem Tutorial konzentrieren wir uns jedoch darauf, wie du SQLite-Datenbanken in R mithilfe des RSQLite-Pakets nutzt.

Wir gehen die Grundlagen durch, um typische Aufgaben zu erledigen – etwa Abfragen an eine SQLite-Datenbank senden oder Tabellen in RSQLite anlegen. Außerdem zeige ich dir, wie du parametrisierte Abfragen nutzt und Operationen wie INSERT oder DELETE ausführst, die keine tabellarischen Ergebnisse zurückgeben.

Datenbanken und Tabellen erstellen

Der erste Schritt ist – wenig überraschend – das Anlegen einer Datenbank. RSQLite kann flüchtige, im Speicher liegende SQLite-Datenbanken erstellen, ähnlich wie beim Öffnen der SQLite-Kommandozeile. Das ist jedoch meist nicht das Ziel. Also legen wir eine richtige Datenbank für den mtcars-Datensatz an – mit der Funktion dbConnect(), die folgende Argumente erwartet:

• drv: Ein Datenbanktreiber
• path: Der Pfad zu einer SQLite-Datenbank. Wenn du eine neue erstellst, gib ihr einfach einen Namen deiner Wahl, so wie unten. Möchtest du hingegen eine flüchtige In-Memory-Datenbank verwenden, kannst du das path-Argument weglassen oder ":memory:" angeben.

# Load the RSQLite Library
library(RSQLite)
# Load the mtcars as an R data frame put the row names as a column, and print the header.
data("mtcars")
mtcars$car_names <- rownames(mtcars)
rownames(mtcars) <- c()
head(mtcars)
# Create a connection to our new database, CarsDB.db
# you can check that the .db file has been created on your working directory
conn <- dbConnect(RSQLite::SQLite(), "CarsDB.db")

Wenn die Datenbank steht und deine Daten richtig vorbereitet sind, kannst du mit dbWriteTable() eine Tabelle in der Datenbank anlegen. Die Funktion akzeptiert viele Argumente, für den Anfang sind diese drei wichtig:

• conn: Die Verbindung zu deiner SQLite-Datenbank
• name: Der gewünschte Tabellenname
• value: Die einzufügenden Daten. Das sollte ein R Data Frame oder ein in einen Data Frame konvertierbares Objekt sein.

Anschließend kannst du mit dbListTables() und der Datenbankverbindung als Argument prüfen, ob die Tabelle erfolgreich erstellt wurde.

# Write the mtcars dataset into a table names mtcars_data
dbWriteTable(conn, "cars_data", mtcars)
# List all the tables available in the database
dbListTables(conn)

'cars_data'

Ein äußerst nützliches Feature beim Tabellenerstellen mit RSQLite: Du kannst weitere Daten an eine bestehende Tabelle anhängen, etwa in einer Schleife, wenn du mehrere Data Frames hast. Setze dazu das optionale Argument append = TRUE in dbWriteTable(). Beispiel: Wir erstellen eine kleine Beispieltabelle mit einigen Autos und Herstellern, indem wir zwei unterschiedliche Data Frames anhängen:

# Create toy data frames
car <- c('Camaro', 'California', 'Mustang', 'Explorer')
make <- c('Chevrolet','Ferrari','Ford','Ford')
df1 <- data.frame(car,make)
car <- c('Corolla', 'Lancer', 'Sportage', 'XE')
make <- c('Toyota','Mitsubishi','Kia','Jaguar')
df2 <- data.frame(car,make)
# Add them to a list
dfList <- list(df1,df2)
# Write a table by appending the data frames inside the list
for(k in 1:length(dfList)){
    dbWriteTable(conn,"Cars_and_Makes", dfList[[k]], append = TRUE)
}
# List all the Tables
dbListTables(conn)

1. 'Cars_and_Makes'
2. 'cars_data'

Schauen wir nach, ob alle Daten in der neuen Tabelle stehen:

dbGetQuery(conn, "SELECT * FROM Cars_and_Makes")

SQL-Abfragen ausführen

Wie du oben gesehen hast, lassen sich mit dbGetQuery() gültige SQL-Abfragen über RSQLite ausführen. Die Funktion erwartet:

• conn: Die Verbindung zur SQLite-Datenbank
• query: Die als String übergebene SQL-Abfrage

Um die Möglichkeiten von RSQLite weiter zu zeigen, gehen wir noch ein paar Abfragebeispiele auf der Tabelle cars_data durch:

Hinweis: Über RSQLite kannst du jede für SQLite gültige Abfrage ausführen – von einfachen SELECTs bis zu JOINS (ausgenommen RIGHT OUTER JOIN und FULL OUTER JOIN, die SQLite nicht unterstützt).

# Gather the first 10 rows in the cars_data table
dbGetQuery(conn, "SELECT * FROM cars_data LIMIT 10")

# Get the car names and horsepower of the cars with 8 cylinders
dbGetQuery(conn,"SELECT car_names, hp, cyl FROM cars_data
                 WHERE cyl = 8")

# Get the car names and horsepower starting with M that have 6 or 8 cylinders
dbGetQuery(conn,"SELECT car_names, hp, cyl FROM cars_data
                 WHERE car_names LIKE 'M%' AND cyl IN (6,8)")

# Get the average horsepower and mpg by number of cylinder groups
dbGetQuery(conn,"SELECT cyl, AVG(hp) AS 'average_hp', AVG(mpg) AS 'average_mpg' FROM cars_data
                 GROUP BY cyl
                 ORDER BY average_hp")

Wenn du die Ergebnisse deiner Abfragen für weitere Schritte in R als Data Frame speichern möchtest, weise das Abfrageergebnis einfach einer Variablen zu.

avg_HpCyl <- dbGetQuery(conn,"SELECT cyl, AVG(hp) AS 'average_hp'FROM cars_data
                 GROUP BY cyl
                 ORDER BY average_hp")
avg_HpCyl
class(avg_HpCyl)

'data.frame'

Variablen in Abfragen einsetzen (parametrisierte Abfragen)

Einer der größten Vorteile bei der Arbeit mit SQLite-Datenbanken aus R ist die Verwendung parametrisierter Abfragen. So kannst du Variablen aus deinem R-Workspace in SQL-Abfragen einsetzen. Hier ein Beispiel, wie du eine Variable in einer SQLite-Abfrage nutzt:

# Lets assume that there is some user input that asks us to look only into cars that have over 18 miles per gallon (mpg)
# and more than 6 cylinders
mpg <-  18
cyl <- 6
Result <- dbGetQuery(conn, 'SELECT car_names, mpg, cyl FROM cars_data WHERE mpg >= ? AND cyl >= ?', params = c(mpg,cyl))
Result

Wie du siehst, besteht der Unterschied zwischen einer normalen und einer parametrisierten Abfrage aus den Platzhaltern in der Abfrage (>= ?) und dem params-Argument von dbGetQuery(), das eine Liste oder einen Vektor mit den Werten für die Platzhalter erwartet (hier ein Vektor mit den Variablen mpg und cyl).

Was passiert nun, wenn du eine andere Abfrage ausführen willst? Im obigen Beispiel habe ich die Abfrage im Grunde fest verdrahtet. Sie erlaubt nur die Eingabe von mpg und cyl und liefert ausschließlich Autos mit größer-gleich mpg und cyl. In der Praxis möchtest du oft flexibler sein. Was, wenn jemand zusätzlich Autos mit größer-gleich Leistung und Gewicht sehen will? Mit der obigen Abfrage müsstest du sie umschreiben. Du könntest aber eine Funktion schreiben, die das überflüssig macht. Hier ein Beispiel:

# Assemble an example function that takes the SQLite database connection, a base query,
# and the parameters you want to use in the WHERE clause as a list
assembleQuery <- function(conn, base, search_parameters){
    parameter_names <- names(search_parameters)
    partial_queries <- ""
    # Iterate over all the parameters to assemble the query
    for(k in 1:length(parameter_names)){
        filter_k <- paste(parameter_names[k], " >= ? ")
        # If there is more than 1 parameter, add an AND statement before the parameter name and placeholder
        if(k > 1){
            filter_k <- paste("AND ", parameter_names[k], " >= ?")
        }
        partial_queries <- paste(partial_queries, filter_k)
    }
    # Paste all together into a single query using a WHERE statement
    final_paste <- paste(base, " WHERE", partial_queries)
    # Print the assembled query to show how it looks like
    print(final_paste)
    # Run the final query. I unlist the values from the search_parameters list into a vector since it is needed
    # when using various anonymous placeholders (i.e. >= ?)
    values <- unlist(search_parameters, use.names = FALSE)
    result <- dbGetQuery(conn, final_paste, params = values)
    # return the executed query
    return(result)
}

base <- "SELECT car_names, mpg, hp, wt FROM cars_data"
search_parameters <- list("mpg" = 16, "hp" = 150, "wt" = 2.1)
result <- assembleQuery(conn, base, search_parameters)
result

[1] "SELECT car_names, mpg, hp, wt FROM cars_data  WHERE  mpg  >= ?  AND  hp  >= ? AND  wt  >= ?"

Die Funktion ist simpel, zeigt aber gut, wie du R-Code schreiben kannst, der SQL-Abfragen für eine SQLite-Datenbank generiert. Wenn dich dieser Anwendungsfall interessiert, experimentiere weiter. Die Funktion lässt sich in vielerlei Hinsicht erweitern, etwa indem du die Annahme entfernst, dass Nutzende immer nach größer-gleich filtern möchten.

Anweisungen ohne tabellarische Ergebnisse

Manchmal willst du SQL-Anweisungen ausführen, die keine Tabellen zurückgeben – zum Beispiel Datensätze einfügen, aktualisieren oder löschen. Dafür verwenden wir dbExecute(). Die Funktion nimmt eine SQLite-Verbindung und eine SQL-Abfrage entgegen. Zwei Beispiele:

# Visualize the table before deletion
dbGetQuery(conn, "SELECT * FROM cars_data LIMIT 10")
# Delete the column belonging to the Mazda RX4. You will see a 1 as the output.
dbExecute(conn, "DELETE FROM cars_data WHERE car_names = 'Mazda RX4'")
# Visualize the new table after deletion
dbGetQuery(conn, "SELECT * FROM cars_data LIMIT 10")

1

Jetzt fügen wir die Daten des Mazda RX4 wieder ein:

# Insert the data for the Mazda RX4. This will also ouput a 1
dbExecute(conn, "INSERT INTO cars_data VALUES (21.0,6,160.0,110,3.90,2.620,16.46,0,1,4,4,'Mazda RX4')")
# See that we re-introduced the Mazda RX4 succesfully at the end
dbGetQuery(conn, "SELECT * FROM cars_data")

1

Wie du siehst, fügt die letzte Codezeile den Mazda RX4 am Ende der Tabelle wieder ein. Sobald du deine Arbeit mit der SQLite-Datenbank in R beendet hast, solltest du unbedingt dbDisconnect() aufrufen. So stellst du sicher, dass die von der Verbindung genutzten Ressourcen freigegeben werden – eine gute Praxis.

# Close the database connection to CarsDB
dbDisconnect(conn)

Fazit

In diesem Tutorial haben wir die wichtigsten Funktionen behandelt, um SQLite-Datenbanken in R mit RSQLite zu bearbeiten. Richtig eingesetzt sind SQLite-Datenbanken in R-Skripten ein äußerst nützliches Werkzeug. Ein Beispiel für die Stärke der Kombination aus R und SQLite sind parametrisierte Abfragen – etwa wenn du für eine R Shiny App Datenbankabfragen basierend auf Nutzereingaben benötigst. Ein weiteres Einsatzszenario für parametrisierte Abfragen sind virtuelle Assistenten oder Chatbots. Wenn du mehr dazu lernen willst, schau dir den DataCamp-Kurs Building Chatbots with Python an.

Auch das Schreiben von SQLite-Tabellen durch Anhängen von Data Frames ist sehr mächtig. Wie ich im Beginners Guide to SQLite erklärt habe, konnte ich damit ein Projekt zur sozialen Netzwerkanalyse umsetzen: Ich habe die Follower mehrerer interessanter Twitter-Accounts gesammelt und fortlaufend an eine Tabelle angehängt. Eine SQLite-Datenbank sparte mir Zeit, weil ich im Fall eines Stromausfalls oder Windows-Updates, das den Rechner zwangsweise neu startet, nicht von vorn beginnen musste. Passierte so etwas, musste ich nur beim nächsten Nutzer nach dem zuletzt gespeicherten weitermachen. Zur Einordnung: Alle Follower meiner Ziel-Accounts zu sammeln, dauerte wegen der Rate Limits der Twitter API fast vier Wochen. Von null zu starten, wäre entsprechend extrem unpraktisch gewesen.

Wie immer gilt: Bleib dran und lerne weiter, wie du mit SQLite-Datenbanken arbeitest und sie aus R heraus ansprichst. Ich empfehle dir außerdem, die vorherigen Tutorials zur Nutzung mit Python und der Kommandozeile durchzugehen, um zum echten SQLite-Profi zu werden. Lern weiter – es gibt keine Grenzen!