SQLite in R

Come accennato alla fine del mio tutorial precedente, Guida per principianti a SQLite, i database SQLite danno il meglio insieme a R e Python. Finora su DataCamp hai visto come lavorare con database SQLite da Python (vedi il tutorial SQLite in Python di Sayak Paul per imparare a manipolare database SQLite tramite il pacchetto sqlite3 in Python). In questo tutorial, invece, ci concentreremo su come usare i database SQLite in R con il pacchetto RSQLite.

Vedremo le basi per svolgere attività essenziali come inviare query a un database SQLite o creare tabelle con RSQLite. Inoltre, tratterò come usare query parametrizzate e operazioni come INSERT o DELETE che non restituiscono risultati tabellari.

Creare database e tabelle

Il primo passo, come avrai intuito, è creare un database. RSQLite può creare database SQLite transitori in memoria, proprio come quando apri la riga di comando di SQLite. Tuttavia, di solito non è ciò che vuoi, quindi creiamo un vero database per il dataset mtcars usando la funzione dbConnect(), che accetta i seguenti argomenti:

• drv: Un driver di database
• path: Il percorso di un database SQLite. Se ne stai creando uno nuovo, assegnagli semplicemente un nome a tua scelta come faccio qui sotto. Se vuoi lavorare con un database transitorio in memoria, puoi omettere l'argomento path o digitare ":memory:").

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

Una volta creato il database e preparati i dati, puoi creare una tabella all'interno del database usando la funzione dbWriteTable(). Questa funzione può accettare molti argomenti ma, per ora, concentriamoci sui seguenti:

• conn: La connessione al tuo database SQLite
• name: Il nome che vuoi assegnare alla tua tabella
• value: I dati che vuoi inserire. Dovrebbero essere un data frame R o un oggetto convertibile in un data frame R.

Dopodiché, puoi usare la funzione dbListTables() passando la connessione al database SQLite come argomento per verificare di aver creato correttamente la tabella.

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

Una funzionalità estremamente utile quando si creano tabelle con RSQLite è la possibilità di aggiungere altri dati a una tabella esistente usando un ciclo, nel caso tu abbia diversi data frame, impostando l'argomento opzionale append = TRUE dentro dbWriteTable(). Per esempio, creiamo una nuova tabella di prova con alcune auto e i produttori aggiungendo due data frame diversi:

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

Verifichiamo ora che tutti i dati siano nella nuova tabella:

dbGetQuery(conn, "SELECT * FROM Cars_and_Makes")

Eseguire query SQL

Come hai visto sopra, è possibile eseguire query SQL valide tramite RSQLite usando la funzione dbGetQuery(), che ha i seguenti argomenti:

• conn: La connessione al database SQLite
• query: La query SQL da eseguire come stringa

Per mostrare ulteriormente la capacità di eseguire query SQL con RSQLite, vediamo altri esempi di query sulla tabella cars_data:

Nota: È importante ricordare che, con RSQLite, puoi eseguire qualsiasi query valida per un database SQLite, da semplici SELECT a JOIN (eccetto RIGHT OUTER JOIN e FULL OUTER JOIN, che non sono consentiti 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")

Per salvare i risultati delle tue query in un data frame su cui fare ulteriori operazioni in R, è sufficiente assegnare il risultato della query a una variabile.

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'

Inserire variabili nelle query (query parametrizzate)

Uno dei maggiori vantaggi di lavorare con database SQLite da R è la possibilità di usare query parametrizzate. Cioè, la possibilità di prendere variabili disponibili nel tuo workspace R e usarle per interrogare il tuo database SQLite. Ecco un esempio di come usare una variabile dentro una query SQLite:

# 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

Come vedi, l'unica differenza tra inviare una query normale e una parametrizzata sta nel valore segnaposto nella query (>= ?) e nell'argomento params di dbGetQuery(), che accetta una lista o un vettore con i valori da assegnare ai segnaposto (in questo caso abbiamo un vettore contenente le variabili mpg e cyl).

E cosa succede quando vuoi eseguire una query diversa? Nell'esempio sopra, come vedi, ho praticamente scritto la query a mano. Permette solo l'inserimento dei valori di mpg e cyl, e la query recupererà solo le auto con mpg e cyl maggiori o uguali. Tuttavia, potrebbero esserci situazioni in cui vuoi essere più flessibile. Cosa succede se un utente vuole vedere anche le auto con cavalli e peso maggiori o uguali a un certo valore? Con la query sopra dovresti tornare indietro e riscriverla, ma potresti scrivere una funzione che renda superfluo quel passaggio. Eccone una:

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

La funzione sopra è basilare, ma mostra come sia possibile scrivere codice R che genera query SQL eseguibili su un database SQLite. Ti invito a esplorare se questo caso d’uso ti interessa. La funzione può essere migliorata in diversi modi, a partire dall’eliminazione dell’assunzione che l’utente voglia vedere solo valori maggiori o uguali a quelli inseriti.

Istruzioni che non restituiscono risultati tabellari

A volte potresti voler eseguire query SQL che non restituiscono necessariamente dati tabellari. Esempi di queste operazioni sono l'inserimento, l'aggiornamento o l'eliminazione di record. Per questo possiamo usare la funzione dbExecute(), che accetta come argomenti una connessione a un database SQLite e una query SQL. Ecco un paio di esempi:

# 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

Ora proviamo a reinserire i dati della Mazda RX4:

# 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

Come puoi vedere, l'ultima riga di codice ha aggiunto la Mazda RX4 alla fine della tabella. Quando hai finito di lavorare con il tuo database SQLite in R, è importante chiamare la funzione dbDisconnect(). Questo assicura il rilascio delle risorse utilizzate dalla connessione al database, il che è sempre una buona pratica.

# Close the database connection to CarsDB
dbDisconnect(conn)

Conclusione

In questo tutorial abbiamo visto le funzioni essenziali per manipolare database SQLite in R con RSQLite. Se usati correttamente, i database SQLite sono uno strumento incredibilmente utile quando li integri nei tuoi script R. Un esempio della potenza dell'intersezione tra R e SQLite si vede nelle query parametrizzate, che potresti usare se devi interrogare un database per mostrare informazioni basate sull'input dell'utente in un'app R Shiny. Un altro possibile caso d'uso delle query parametrizzate sono gli assistenti virtuali o i chatbot. Se vuoi saperne di più, ti invito a dare un'occhiata al corso di DataCamp Building Chatbots with Python.

Scrivere tabelle SQLite aggiungendo data frame può essere altrettanto potente. Come ho notato nel tutorial Guida per principianti a SQLite, questa funzionalità mi ha permesso di completare un progetto di analisi di reti sociali aggiungendo alla tabella, man mano che li raccoglievo, i follower di vari utenti Twitter di interesse. Ho usato un database SQLite per risparmiare tempo ed evitare di dover ricominciare da zero in caso di blackout o aggiornamento di Windows che avesse spento forzatamente il computer. Se fosse successo qualcosa del genere, tutto ciò che avrei dovuto fare sarebbe stato riprendere a raccogliere i follower dall'utente successivo all'ultimo presente nel database. Per dare un'idea, il tempo di calcolo per raccogliere i follower di tutti gli utenti Twitter di interesse è stato di quasi 4 settimane a causa dei limiti di frequenza dell'API di Twitter. Quindi, dover ripartire da zero per un blackout o un aggiornamento di Windows sarebbe stato molto sgradito.

Come sempre, ti invito a continuare a imparare sui database SQLite e su come interagirci tramite R. Ti consiglio caldamente di approfondire e controllare i tutorial precedenti che ne trattano l'uso con Python e la riga di comando, che possono aiutarti a diventare un vero mago di SQLite. Continua a imparare: il cielo è il limite!