SQLite no R

Como mencionei no fim do meu tutorial anterior, Beginners Guide to SQLite, bancos de dados SQLite ficam ainda mais poderosos quando usados junto com R e Python. No DataCamp, você já viu como operar bancos SQLite a partir do Python (confira o tutorial SQLite in Python do Sayak Paul para aprender a manipular bancos SQLite com o pacote sqlite3 no Python). Neste tutorial, porém, vamos focar em como usar bancos de dados SQLite no R com o pacote RSQLite.

Vamos passar pelo básico de como realizar tarefas essenciais, como enviar consultas para um banco SQLite ou criar tabelas usando o RSQLite. Além disso, vou mostrar como usar consultas parametrizadas e operações como INSERT ou DELETE, que não retornam resultados tabulares.

Criando bancos de dados e tabelas

O primeiro passo, como você deve imaginar, é criar um banco de dados. O RSQLite pode criar bancos SQLite efêmeros em memória, assim como quando você abre a linha de comando do SQLite. Porém, isso geralmente não é o que você quer. Então vamos criar um banco de dados de verdade para o conjunto de dados mtcars usando a função dbConnect(), que recebe os seguintes argumentos:

• drv: um driver de banco de dados
• path: o caminho para um banco de dados SQLite. Se for criar um novo, basta dar um nome de sua preferência, como faço abaixo. Se quiser operar com um banco transitório em memória, você pode omitir o argumento path ou usar ":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")

Com o banco criado e os dados no formato certo, você pode criar uma tabela dentro do banco usando a função dbWriteTable(). Ela aceita vários argumentos, mas, por agora, vamos focar nos seguintes:

• conn: a conexão com seu banco SQLite
• name: o nome que você quer dar à sua tabela
• value: os dados que você quer inserir. Deve ser um data frame do R ou um objeto convertível em data frame.

Depois disso, você pode usar a função dbListTables() com a conexão do SQLite como argumento para verificar se a tabela foi criada com sucesso.

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

Um recurso muito útil ao criar tabelas com o RSQLite é poder anexar mais dados a uma tabela existente usando um loop, caso você tenha vários data frames, definindo o argumento opcional append = TRUE dentro do dbWriteTable(). Por exemplo, vamos criar uma tabela simples com alguns carros e seus fabricantes anexando dois data frames diferentes:

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

Agora vamos conferir se todos os dados estão na nova tabela:

dbGetQuery(conn, "SELECT * FROM Cars_and_Makes")

Executando consultas SQL

Como você viu acima, é possível executar consultas SQL válidas pelo RSQLite usando a função dbGetQuery(), que recebe os seguintes argumentos:

• conn: a conexão com o banco SQLite
• query: a consulta SQL que você quer executar como string

Para mostrar melhor a capacidade de executar consultas SQL com o RSQLite, vamos ver mais alguns exemplos na tabela cars_data:

Observação: é importante lembrar que, com o RSQLite, você pode executar qualquer consulta válida para um banco SQLite, desde SELECT simples até JOINS (exceto RIGHT OUTER JOIN e FULL OUTER JOIN, que não são permitidos no 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")

Para guardar os resultados das suas consultas e fazer outras operações no R depois, como um data frame, basta atribuir o resultado da consulta a uma variável.

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'

Inserindo variáveis em consultas (consultas parametrizadas)

Uma das maiores vantagens de operar bancos SQLite a partir do R é a possibilidade de usar consultas parametrizadas. Ou seja, usar variáveis disponíveis no seu ambiente do R para consultar seu banco SQLite. Veja um exemplo de como usar uma variável dentro de uma 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 você pode ver, a única diferença entre enviar uma consulta normal e uma consulta parametrizada está no uso do placeholder na consulta (>= ?) e no argumento params de dbGetQuery(), que recebe uma lista ou um vetor com os valores que você quer atribuir aos placeholders (neste caso, um vetor contendo as variáveis mpg e cyl).

Agora, o que acontece quando você quer executar uma consulta diferente? No exemplo acima, como dá para ver, eu praticamente escrevi a consulta à mão. Ela só permite informar valores de mpg e cyl e a consulta apenas retorna os carros com mpg e cyl maiores ou iguais. Mas pode haver situações em que você queira ser mais flexível. E se um usuário quiser ver carros com potência e peso maiores ou iguais a um certo valor também? Com a consulta acima, você teria que reescrevê-la. Em vez disso, dá para escrever uma função que evita esse retrabalho. Veja um exemplo:

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

A função acima é simples, mas mostra como é possível escrever código em R que gera consultas SQL executáveis em um banco SQLite. Se esse caso de uso te interessa, vale explorar mais. Dá para melhorar a função de várias formas, começando por remover a suposição de que quem fornece os parâmetros sempre quer ver valores maiores ou iguais aos informados.

Instruções que não retornam resultados tabulares

Às vezes, você pode querer executar consultas SQL que não retornam dados em forma de tabela. Exemplos: inserir, atualizar ou excluir registros. Para isso, usamos a função dbExecute(), que recebe como argumentos uma conexão SQLite e uma consulta SQL. Veja alguns exemplos:

# 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

Agora vamos inserir novamente os dados do 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 dá para ver, a última linha de código adicionou o Mazda RX4 ao fim da tabela. Quando terminar de operar com seu banco SQLite no R, é importante chamar a função dbDisconnect(). Isso garante que os recursos usados pela conexão com o banco sejam liberados — uma boa prática sempre.

# Close the database connection to CarsDB
dbDisconnect(conn)

Conclusão

Neste tutorial, cobrimos as funções essenciais para manipular bancos SQLite no R usando o RSQLite. Bancos SQLite, quando bem utilizados, são ferramentas incrivelmente úteis dentro dos seus scripts em R. Um exemplo do poder da interseção entre R e SQLite aparece nas consultas parametrizadas, que você pode usar para consultar um banco e exibir informações com base em entradas do usuário em um app R Shiny. Outro uso possível para consultas parametrizadas são assistentes virtuais ou chatbots. Se quiser saber mais, recomendo o curso Building Chatbots with Python do DataCamp.

Escrever tabelas no SQLite anexando data frames também é muito poderoso. Como destaquei no tutorial Beginners Guide to SQLite, esse recurso me permitiu concluir um projeto de análise de redes sociais anexando os seguidores de vários usuários do Twitter de interesse a uma tabela conforme eu os coletava. Usei um banco SQLite para ganhar tempo e evitar ter que começar do zero em caso de queda de energia ou atualização do Windows que desligasse o computador à força. Se algo assim acontecesse, tudo que eu precisava fazer era retomar a coleta dos seguidores a partir do próximo usuário após o último salvo no banco. Para você ter uma ideia, o tempo de processamento para coletar os seguidores de todos os meus usuários de interesse levou quase 4 semanas por causa dos limites de taxa da API do Twitter. Portanto, recomeçar do zero por conta de uma queda de energia ou atualização seria péssimo.

Como sempre, incentivo você a continuar aprendendo sobre bancos de dados SQLite e como interagir com eles via R. Vale muito a pena mergulhar e conferir os tutoriais anteriores que cobrem o uso com Python e a linha de comando — isso vai te ajudar a se tornar fera em SQLite. Continue aprendendo: o céu é o limite!