SQLite en R

Comme mentionné à la fin de mon précédent tutoriel, Beginners Guide to SQLite, les bases de données SQLite sont particulièrement puissantes lorsqu'elles sont utilisées avec R et Python. Jusqu'ici sur DataCamp, vous avez vu comment interagir avec des bases SQLite depuis Python (consultez le tutoriel SQLite in Python de Sayak Paul pour apprendre à manipuler des bases SQLite via le package sqlite3 en Python). Dans ce tutoriel, nous allons toutefois nous concentrer sur l'utilisation de SQLite dans R avec le package RSQLite.

Nous passerons en revue les bases pour réaliser des opérations essentielles comme envoyer des requêtes à une base SQLite ou créer des tables via RSQLite. Nous verrons également comment utiliser des requêtes paramétrées et des opérations comme INSERT ou DELETE qui ne renvoient pas de résultats tabulaires.

Créer des bases de données et des tables

La première étape, vous vous en doutez, consiste à créer une base. RSQLite peut créer des bases SQLite éphémères en mémoire, comme lorsque vous ouvrez la ligne de commande SQLite. Ce n'est toutefois pas toujours ce que vous souhaitez. Créons donc une base dédiée au jeu de données mtcars avec la fonction dbConnect(), qui prend les arguments suivants :

• drv : un pilote de base de données
• path : le chemin de la base SQLite. Si vous en créez une nouvelle, donnez-lui simplement un nom, comme ci-dessous. Si vous voulez travailler avec une base transitoire en mémoire, vous pouvez omettre l'argument path ou saisir ":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")

Une fois la base créée et vos données prêtes, vous pouvez créer une table dans la base avec la fonction dbWriteTable(). Cette fonction accepte de nombreux arguments, mais concentrons-nous sur les suivants :

• conn : la connexion à votre base SQLite
• name : le nom à donner à votre table
• value : les données à insérer. Il doit s'agir d'un data frame R ou d'un objet convertible en data frame.

Ensuite, vous pouvez utiliser la fonction dbListTables() avec la connexion SQLite en argument pour vérifier que la table a bien été créée.

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

Une fonctionnalité très utile lors de la création de tables avec RSQLite est la possibilité d'ajouter des données à une table existante via une boucle si vous avez plusieurs data frames, en définissant l'argument optionnel append = TRUE dans dbWriteTable(). Par exemple, créons une table jouet listant quelques modèles et constructeurs en concaténant deux data frames :

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

Vérifions maintenant que toutes les données figurent bien dans la nouvelle table :

dbGetQuery(conn, "SELECT * FROM Cars_and_Makes")

Exécuter des requêtes SQL

Comme vous l'avez vu ci-dessus, il est possible d'exécuter des requêtes SQL valides via RSQLite grâce à la fonction dbGetQuery(), qui prend les arguments suivants :

• conn : la connexion à la base SQLite
• query : la requête SQL à exécuter sous forme de chaîne

Pour illustrer davantage l'exécution de requêtes SQL avec RSQLite, passons quelques exemples supplémentaires sur la table cars_data :

Remarque : gardez à l'esprit qu'avec RSQLite, vous pouvez exécuter toute requête valide pour SQLite, des simples requêtes SELECT aux JOINS (à l'exception des RIGHT OUTER JOIN et FULL OUTER JOIN, non pris en charge par 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")

Pour stocker les résultats de vos requêtes et poursuivre l'analyse dans R sous forme de data frame, il suffit d'assigner le résultat à une variable.

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'

Insérer des variables dans des requêtes (requêtes paramétrées)

L'un des grands avantages d'opérer avec des bases SQLite depuis R est la possibilité d'utiliser des requêtes paramétrées. Autrement dit, exploiter des variables disponibles dans votre espace de travail R pour interroger votre base SQLite. Voici un exemple d'utilisation d'une variable dans une requête 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

Comme vous le voyez, la seule différence entre une requête « classique » et une requête paramétrée tient au caractère générique dans la requête (>= ?) et à l'argument params de dbGetQuery(), qui prend une liste ou un vecteur avec les valeurs à attribuer aux espaces réservés (ici un vecteur contenant les variables mpg et cyl).

Que se passe-t-il si vous souhaitez exécuter une autre requête ? Dans l'exemple ci-dessus, la requête est écrite à la main : elle ne prend en compte que des valeurs de mpg et de cyl, et ne renverra que les voitures dont le mpg et le cyl sont supérieurs ou égaux. Mais certaines situations exigent plus de flexibilité. Et si un utilisateur souhaite aussi filtrer sur une puissance et un poids supérieurs ou égaux à une valeur donnée ? Avec la requête précédente, il faudrait la réécrire. Vous pouvez cependant créer une fonction qui vous évite cette étape. En voici une :

# 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 fonction ci-dessus est simple, mais elle illustre comment écrire du code R qui génère des requêtes SQL exécutables sur une base SQLite. Si ce cas d'usage vous intéresse, je vous invite à aller plus loin. Vous pouvez améliorer cette fonction de plusieurs façons, à commencer par lever l'hypothèse selon laquelle l'utilisateur souhaite forcément filtrer sur des valeurs supérieures ou égales à celles saisies.

Instructions qui ne renvoient pas de résultats tabulaires

Parfois, vous voudrez exécuter des requêtes SQL qui ne renvoient pas de données tabulaires. C'est le cas des opérations d'insertion, de mise à jour ou de suppression d'enregistrements. Pour cela, utilisez la fonction dbExecute(), qui prend en arguments une connexion SQLite et une requête SQL. Voici deux exemples :

# 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

Essayons maintenant de réinsérer les données de la 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

Comme vous pouvez le constater, la dernière ligne de code a ajouté la Mazda RX4 à la fin de la table. Une fois votre travail terminé avec la base SQLite dans R, pensez à appeler la fonction dbDisconnect(). Cela permet de libérer les ressources utilisées par la connexion à la base, ce qui est une bonne pratique.

# Close the database connection to CarsDB
dbDisconnect(conn)

Conclusion

Dans ce tutoriel, nous avons couvert les fonctions essentielles pour manipuler des bases SQLite dans R à l'aide de RSQLite. Bien utilisées, les bases SQLite sont un outil extrêmement utile au sein de vos scripts R. La puissance de l'intersection entre R et SQLite apparaît notamment avec les requêtes paramétrées, par exemple pour interroger une base afin d'afficher des informations selon des saisies utilisateur dans une application R Shiny. Autre cas d'usage des requêtes paramétrées : des assistants virtuels ou chatbots. Pour en savoir plus, consultez le cours Building Chatbots with Python de DataCamp.

L'écriture de tables SQLite par ajout successif de data frames peut aussi s'avérer très puissante. Comme je l'ai indiqué dans le tutoriel Beginners Guide to SQLite, cette capacité m'a permis de mener à bien un projet d'analyse de réseau social en ajoutant, au fil de la collecte, les abonnés de plusieurs utilisateurs Twitter d'intérêt dans une table. J'ai utilisé une base SQLite pour gagner du temps et éviter de repartir de zéro en cas de coupure de courant ou de mise à jour Windows entraînant l'arrêt forcé de l'ordinateur. Dans un tel cas, il suffisait de reprendre la collecte à l'utilisateur suivant le dernier enregistré dans la base. Pour situer l'ordre de grandeur, la collecte des abonnés de tous mes utilisateurs d'intérêt a pris près de 4 semaines à cause des limites de l'API Twitter. Repartir de zéro à cause d'un incident aurait été très problématique.

Comme toujours, continuez à explorer les bases SQLite et leurs interactions avec R. Je vous recommande vivement d'approfondir en consultant les précédents tutoriels sur leur utilisation avec Python et la ligne de commande, pour devenir incollable sur SQLite. Continuez à apprendre : tout est possible !