SQLite en R

Como comenté al final de mi tutorial anterior, Beginners Guide to SQLite, las bases de datos SQLite son mucho más potentes cuando se usan junto con R y Python. Hasta ahora, en DataCamp has visto cómo trabajar con SQLite desde Python (consulta el tutorial SQLite in Python de Sayak Paul para aprender a manipular bases de datos SQLite con el paquete sqlite3 en Python). En este tutorial, nos centraremos en cómo usar bases de datos SQLite en R con el paquete RSQLite.

Veremos lo básico para realizar tareas esenciales, como enviar consultas a una base de datos SQLite o crear tablas con RSQLite. Además, cubriremos cómo usar consultas parametrizadas y operaciones como INSERT o DELETE que no devuelven resultados tabulares.

Crear bases de datos y tablas

El primer paso, como habrás imaginado, es crear una base de datos. RSQLite puede crear bases de datos SQLite transitorias en memoria, igual que cuando abres la línea de comandos de SQLite. Sin embargo, esto no suele ser lo que quieres, así que vamos a crear una base de datos en condiciones para el conjunto de datos mtcars usando la función dbConnect(), que acepta los siguientes argumentos:

• drv: Un controlador de base de datos
• path: La ruta a una base de datos SQLite. Si vas a crear una nueva, simplemente ponle el nombre que prefieras, como hago abajo. Si quieres trabajar con una base de datos transitoria en memoria, puedes omitir el argumento path o escribir ":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")

Cuando tengas la base de datos creada y los datos listos, puedes crear una tabla dentro de la base con la función dbWriteTable(). Esta función admite varios argumentos, pero por ahora céntrate en los siguientes:

• conn: La conexión a tu base de datos SQLite
• name: El nombre que quieres darle a tu tabla
• value: Los datos que quieres insertar. Debe ser un data frame de R u objeto convertible a data frame.

Después, puedes usar la función dbListTables() con la conexión a SQLite como argumento para comprobar que la tabla se ha creado correctamente.

# 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 función muy práctica al crear tablas con RSQLite es que puedes añadir más datos a una tabla existente recorriendo varios data frames si los tienes, estableciendo el argumento opcional append = TRUE dentro de dbWriteTable(). Por ejemplo, creemos una tabla sencilla con algunos coches y sus fabricantes añadiendo dos data frames distintos:

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

Comprobemos ahora que todos los datos están en la nueva tabla:

dbGetQuery(conn, "SELECT * FROM Cars_and_Makes")

Ejecutar consultas SQL

Como has visto arriba, es posible ejecutar consultas SQL válidas a través de RSQLite usando la función dbGetQuery(), que tiene los siguientes argumentos:

• conn: La conexión a la base de datos SQLite
• query: La consulta SQL que quieres ejecutar como cadena

Para mostrar mejor la capacidad de ejecutar consultas SQL con RSQLite, veamos algunos ejemplos más sobre la tabla cars_data:

Nota: Ten en cuenta que, con RSQLite, puedes ejecutar cualquier consulta que sea válida en SQLite, desde sentencias SELECT sencillas hasta JOINS (excepto RIGHT OUTER JOIN y FULL OUTER JOIN, que SQLite no permite).

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

Si quieres guardar los resultados de tus consultas para seguir trabajando con ellos en R como un data frame, basta con asignar el resultado de la consulta a una 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'

Insertar variables en consultas (consultas parametrizadas)

Una de las mayores ventajas de trabajar con bases de datos SQLite desde R es la posibilidad de usar consultas parametrizadas. Es decir, aprovechar variables disponibles en tu espacio de trabajo de R y usarlas para consultar tu base de datos SQLite. Te muestro un ejemplo de cómo usar una variable dentro de una consulta 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

Como ves, la única diferencia entre enviar una consulta normal y una parametrizada está en el marcador de posición de la consulta (>= ?) y el argumento params de dbGetQuery(), que recibe una lista o vector con los valores que quieres asignar a los marcadores (en este caso, un vector con las variables mpg y cyl).

Ahora bien, ¿qué pasa cuando quieres ejecutar una consulta diferente? En el ejemplo anterior, como ves, la consulta está hecha a medida: solo admite los valores de mpg y cyl, y la consulta solo recuperará los coches con mpg y cyl mayores o iguales. Puede haber situaciones en las que necesites más flexibilidad. ¿Qué ocurre si un usuario quiere ver también los coches con potencia y peso mayores o iguales que un valor específico? Con la consulta anterior tendrías que volver atrás y reescribirla, pero puedes crear una función que evite ese paso. Veamos 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 función anterior es sencilla, pero ilustra cómo puedes escribir código en R que genere consultas SQL ejecutables en una base de datos SQLite. Te animo a seguir explorando si te interesa este caso de uso. La función se puede mejorar de varias formas, empezando por eliminar la suposición de que quien aporta los parámetros siempre querrá ver valores mayores o iguales a los introducidos.

Sentencias que no devuelven resultados tabulares

A veces, querrás ejecutar consultas SQL que no devuelvan datos tabulares. Ejemplos de estas operaciones son insertar, actualizar o eliminar registros. Para ello, podemos usar la función dbExecute(), que recibe como argumentos una conexión SQLite y una consulta SQL. Aquí tienes un par de ejemplos:

# 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

Ahora probemos a insertar de nuevo los datos del 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

Como ves, la última línea de código añadió el Mazda RX4 al final de la tabla. Cuando termines de trabajar con tu base de datos SQLite en R, es importante llamar a la función dbDisconnect(). Así te aseguras de liberar cualquier recurso usado por la conexión a la base de datos, lo cual siempre es una buena práctica.

# Close the database connection to CarsDB
dbDisconnect(conn)

Conclusión

En este tutorial hemos visto las funciones esenciales para manipular bases de datos SQLite en R con RSQLite. Bien utilizadas, las bases de datos SQLite son una herramienta potentísima cuando las integras en tus scripts de R. Un ejemplo de esa potencia está en las consultas parametrizadas, que puedes usar si necesitas consultar una base de datos para mostrar información en función de entradas de usuario en una app de R Shiny. Otro caso de uso para consultas parametrizadas podrían ser los asistentes virtuales o chatbots. Si quieres profundizar, te animo a visitar el curso de DataCamp Building Chatbots with Python.

Escribir tablas SQLite añadiendo data frames también puede ser muy potente. Como comenté en el tutorial Beginners Guide to SQLite, esta capacidad me permitió completar un proyecto de análisis de redes sociales añadiendo a una tabla los seguidores de varios usuarios de Twitter de interés según los iba recopilando. Usé una base de datos SQLite para ahorrar tiempo y evitar empezar de cero en caso de un corte de luz o una actualización de Windows que apagara el ordenador a la fuerza. Si ocurría algo así, solo tenía que reanudar la recopilación a partir del usuario siguiente al último que ya tenía en la base. Para que te hagas una idea, el tiempo de cómputo para reunir los seguidores de todos mis usuarios de interés en Twitter fue de casi 4 semanas debido a los límites de la API de Twitter. Empezar desde cero por un corte de luz o una actualización habría sido muy poco deseable.

Como siempre, te animo a seguir aprendiendo sobre las bases de datos SQLite y cómo interactuar con ellas desde R. Te recomiendo encarecidamente que repases los tutoriales anteriores sobre su uso con Python y la línea de comandos: te ayudarán a mantenerte a la vanguardia con SQLite. ¡Sigue aprendiendo; el límite lo pones tú!