SQLite in R

Zoals aan het einde van mijn vorige tutorial, Beginners Guide to SQLite, al genoemd, komen SQLite-databases het best tot hun recht in combinatie met R en Python. Tot nu toe heb je op DataCamp gezien hoe je met SQLite-databases werkt vanuit Python (bekijk de SQLite in Python-tutorial van Sayak Paul om te leren hoe je SQLite-databases manipuleert via het sqlite3-pakket in Python). In deze tutorial focussen we ons echter op het gebruik van SQLite-databases in R met het RSQLite-pakket.

We behandelen de basis van essentiële taken zoals het versturen van queries naar een SQLite-database of het maken van tabellen met RSQLite. Daarnaast laat ik zien hoe je parameterized queries gebruikt en bewerkingen zoals INSERT of DELETE uitvoert die geen tabellaire resultaten teruggeven.

Databases en tabellen maken

De eerste stap, zoals je waarschijnlijk al raadt, is het aanmaken van een database. RSQLite kan vluchtige, in-memory, tijdelijke SQLite-databases maken, net zoals gebeurt wanneer je de SQLite-command line opent. Maar dat is meestal niet wat je wilt, dus laten we een echte database maken voor de mtcars-dataset met de functie dbConnect(), die de volgende argumenten accepteert:

• drv: Een databasedriver
• path: Het pad naar een SQLite-database. Als je een nieuwe maakt, geef je hem simpelweg een naam naar keuze, zoals ik hieronder doe. Als je met een tijdelijke in-memory database wilt werken, kun je het path-argument weglaten of ":memory:" typen).

# 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")

Zodra je de database hebt aangemaakt en je data in de juiste vorm staat, kun je een tabel in de database maken met de functie dbWriteTable(). Deze functie kan meerdere argumenten hebben, maar laten we ons nu richten op het volgende:

• conn: De verbinding met je SQLite-database
• name: De naam die je aan je tabel wilt geven
• value: De data die je wilt invoegen. Dit moet een R-dataframe zijn of een object dat naar een R-dataframe kan worden omgezet.

Daarna kun je de functie dbListTables() gebruiken met de SQLite-databaseverbinding als argument om te controleren of de tabel succesvol is aangemaakt.

# 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'

Een enorm handige functie bij het maken van tabellen met RSQLite is dat je in een lus extra data kunt toevoegen aan een bestaande tabel als je meerdere dataframes hebt, door het optionele argument append = TRUE in dbWriteTable() te zetten. Laten we bijvoorbeeld een nieuwe speelse tabel maken met wat auto’s en de fabrikanten door twee verschillende dataframes toe te voegen:

# 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'

Laten we nu controleren of alle data in de nieuwe tabel staat:

dbGetQuery(conn, "SELECT * FROM Cars_and_Makes")

SQL-queries uitvoeren

Zoals je hierboven hebt gezien, kun je geldige SQL-queries uitvoeren via RSQLite met de functie dbGetQuery(), die de volgende argumenten heeft:

• conn: De verbinding met de SQLite-database
• query: De SQL-query die je als string wilt uitvoeren

Om de mogelijkheden voor het uitvoeren van SQL-queries met RSQLite verder te laten zien, bekijken we nog wat meer queryvoorbeelden op de cars_data-tabel:

Opmerking: Houd er rekening mee dat je via RSQLite elke query kunt uitvoeren die geldig is voor een SQLite-database, van eenvoudige SELECT-statements tot JOINS (behalve RIGHT OUTER JOINS en FULL OUTER JOINS, die niet zijn toegestaan in SQLite).

# 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")

Om de resultaten van je queries op te slaan als dataframe voor verdere bewerkingen in R, hoef je simpelweg het resultaat van je query aan een variabele toe te wijzen.

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'

Variabelen in queries invoegen (geparameteriseerde queries)

Een van de grootste voordelen van werken met SQLite-databases vanuit R is de mogelijkheid om parameterized queries te gebruiken. Dat wil zeggen: variabelen uit je R-werkruimte gebruiken om je SQLite-database te bevragen. Ik laat je zien hoe je een variabele in een SQLite-query gebruikt:

# 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

Zoals je ziet is het enige verschil tussen het versturen van een normale query en een geparameteriseerde query de placeholder in de query (>= ?) en het params-argument van dbGetQuery(), dat een lijst of vector met de waarden accepteert die je aan de placeholders wilt toewijzen (in dit geval hebben we een vector met de variabelen mpg en cyl).

Wat gebeurt er als je een andere query wilt uitvoeren? In het bovenstaande voorbeeld heb ik de query min of meer met de hand geschreven. Die staat alleen invoer van mpg- en cyl-waarden toe, en de query haalt alleen de auto’s op met een grotere of gelijke mpg en cyl. Er zijn echter situaties waarin je flexibeler wilt zijn. Wat als een gebruiker ook auto’s wil zien met groter of gelijk vermogen en gewicht dan een bepaalde waarde? Met de bovenstaande query moet je terug en hem herschrijven, maar je kunt een functie schrijven die dat overbodig maakt. Laten we er een bekijken:

# 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  >= ?"

De bovenstaande functie is misschien basic, maar illustreert hoe je R-code kunt schrijven die SQL-queries genereert die op een SQLite-database kunnen worden uitgevoerd. Verdiep je hier vooral verder in als dit je interesseert. De functie kan op meerdere manieren worden verbeterd, te beginnen met het loslaten van de aanname dat de gebruiker alleen waarden wil zien die groter of gelijk zijn aan de ingevoerde waarden.

Statements die geen tabellaire resultaten teruggeven

Soms wil je SQL-queries uitvoeren die niet per se tabellaire data teruggeven. Voorbeelden hiervan zijn het invoegen, updaten of verwijderen van tabelrecords. Hiervoor kunnen we de functie dbExecute() gebruiken, die als argumenten een SQLite-databaseverbinding en een SQL-query neemt. Hieronder zie je een paar voorbeelden:

# 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

Proberen we nu de gegevens van de Mazda RX4 weer in te voegen:

# 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

Zoals je ziet heeft de laatste regel code de Mazda RX4 aan het einde van de tabel toegevoegd. Als je klaar bent met werken met je SQLite-database binnen R, is het belangrijk om de functie dbDisconnect() aan te roepen. Dit zorgt ervoor dat we alle resources vrijgeven die de databaseverbinding gebruikte, wat altijd een goede gewoonte is.

# Close the database connection to CarsDB
dbDisconnect(conn)

Conclusie

In deze tutorial hebben we de essentiële functies behandeld die je nodig hebt om SQLite-databases in R te manipuleren met RSQLite. Als ze op de juiste manier worden gebruikt, zijn SQLite-databases een ongelooflijk handig hulpmiddel in je R-scripts. Een voorbeeld van de kracht van de kruising tussen R en SQLite zie je in geparameteriseerde queries, die je kunt gebruiken als je een database moet bevragen om informatie te tonen op basis van gebruikersinvoer in een R Shiny-app. Een ander voorbeeldgebruik van geparameteriseerde queries is in virtuele assistenten of chatbots. Wil je hier meer over leren, bekijk dan de DataCamp-cursus Building Chatbots with Python.

SQLite-tabellen vullen door dataframes toe te voegen kan ook erg krachtig zijn. Zoals ik al opmerkte in de tutorial Beginners Guide to SQLite, stelde deze mogelijkheid me in staat een socialenetwerkanalyseproject te voltooien door de volgers van verschillende interessante Twitter-gebruikers aan een tabel toe te voegen terwijl ik ze verzamelde. Ik gebruikte hiervoor een SQLite-database om tijd te besparen en te voorkomen dat ik bij een stroomstoring of Windows-update, die mijn computer geforceerd zou kunnen afsluiten, helemaal opnieuw moest beginnen. Als zoiets gebeurde, hoefde ik alleen maar opnieuw te beginnen bij het verzamelen van de volgers van de gebruiker die na de laatste in de database kwam. Ter illustratie: het verzamelen van de volgers van al mijn interessante Twitter-gebruikers kostte door de snelheidslimieten van de Twitter API bijna 4 weken. Opnieuw moeten beginnen door een stroomstoring of Windows-update zou dus zeer onwenselijk zijn geweest.

Zoals altijd raad ik je aan om te blijven leren over SQLite-databases en hoe je ermee werkt via R. Duik vooral dieper in de eerdere tutorials over het gebruik met Python en de commandline, die je verder kunnen helpen om een echte SQLite-wizard te worden. Blijf leren; de lucht is de limiet!