SQLite in R

As mentioned at the end of my previous tutorial, Beginners Guide to SQLite, SQLite databases are most powerful when used along R and Python. So far in DataCamp, you have been exposed to how to operate with SQLite databases from Python (see the SQLite in Python tutorial by Sayak Paul to learn how to manipulate SQLite databases through the sqlite3 package in Python). However, in this tutorial, we will focus on how to use SQLite databases in R using the RSQLite package.

We will go over the basics of how to perform essential tasks such as sending queries to a SQLite database or creating tables using RSQLite. Furthermore, I will cover how to use parameterized queries and operations like INSERT or DELETE that do not return tabular results.

Creating Databases and Tables

The first step as you may have guessed is to create a database. RSQLite can create ephemeral in-memory transient SQLite databases just as it happens when you open the SQLite command line. However, this is not usually what you want, so let's create a proper database for the mtcars dataset using the function dbConnect(), which takes the following arguments:

• drv: A database driver
• path: The path to a SQLite database. If you are creating a new one, simply give it a name of your choice as I do below. If you want to operate with a transient in-memory database; however, you can omit the path argument or type ":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")

Once you have the database created and your data in proper shape, you can go ahead and create a table inside the database using the dbWriteTable() function. This function can take multiple arguments, but, for now, let's focus on the following:

• conn: The connection to your SQLite database
• name: The name you want to use for your table
• value: The data that you want to insert. It should be an R data frame or an object coercible to an R data frame.

After that, you can use the function dbListTables() with the SQLite database connection as an argument to check that you have successfully created the table.

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

An extremely useful feature when creating tables using RSQLite, is that you can append more data to an existing table using a loop in case that you have several data frames by setting the optional argument append = TRUE inside dbWriteTable(). For instance, let us create a new toy table with some cars and the manufacturers by appending two different 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'

Let us now make sure that all the data is in the new table:

dbGetQuery(conn, "SELECT * FROM Cars_and_Makes")

Executing SQL Queries

As you have seen above, it is possible to execute valid SQL queries through RSQLite using the dbGetQuery() function, which has the following arguments:

• conn: The connection to the SQLite Database
• query: The SQL query you want to execute as a string

To further showcase the capability to execute SQL queries with RSQLite, let's go over some more query examples on the cars_data table:

Note: It is important to keep in mind that, through RSQLIte, you can execute any query that is valid for a SQLite database from simple SELECT statements to JOINS (Except RIGHT OUTER JOINS and FULL OUTER JOINS, which are not allowed 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")

To store the results of your queries to do further operations in R down the line as a data frame, it is as simple as assigning the result of your query to a 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'

Insert Variables into Queries (Parameterised Queries)

One of the greatest advantages of operating with SQLite databases from R is the ability to use parameterized queries. That is, the ability to gather variables available in your R workspace and use them to query your SQLite database. Let me show an example of how to use a variable inside a SQLite query:

# 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

As you can see the only difference between sending a normal query and a parameterized query lies in the placeholder value in the query (>= ?) and the params argument of dbGetQuery(), which takes a list or a vector with the values you want to assign to the placeholders (in this case we have a vector containing the mpg and cyl variables).

Now, what happens when you want to execute a different query? In the example above, as you can see, I pretty much hand-crafted the query. It just allows the input of mpg and cyl values, and the query will only retrieve the cars with higher or equal mpg and cyl. However, there may be situations where you may want to be more flexible. What happens if a user wants to look at the cars that have the higher or equal horsepower and weight than a specific value as well? With the query above, you would have to go back and rewrite it, but you could write a function that would make that step unnecessary. Let's take a look at one:

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

The function above may be basic, but it illustrates how it is possible to write R code that generates SQL queries that can be executed on a SQLite database. I encourage you to keep exploring if you are interested in this use case. The function above can be enhanced in several ways starting with the removal of the assumption that the user providing the parameters will want to see the values that are higher or equal than those inputted.

Statements that do not Return Tabular Results

Sometimes, you may want to execute SQL queries that do not necessarily return tabular data. Examples of these operations are inserting, updating or deleting table records. For this, we can use the function dbExecute(), which takes as arguments a SQLite database connection and a SQL query. A couple of examples can be seen below:

# 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

Now lets try inserting the data of the Mazda RX4 back:

# 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

As you can see, the last line of code added the Mazda RX4 to the end of the table. Once you are done operating with your SQLite database within R, it is important to call the function dbDisconnect(). This ensures that we release any resources that the database connection has been using, which is always a good practice.

# Close the database connection to CarsDB
dbDisconnect(conn)

Conclusion

In this tutorial, we have covered the essential functions needed to manipulate SQLite databases in R using RSQLite. SQLite databases, if appropriately used, are an incredibly useful tool when combined inside your R scripts. An example of the power of the intersection between R and SQLite can be seen in parameterized queries, which could be used if you need to query a database in order to display information based on user input inside an R Shiny app. Another example use case or parameterized queries could be virtual assistants or chatbots. If you want to learn more about this, I encourage you to look into DataCamp's Building Chatbots with Python course.

Writing SQLite tables by appending data frames, can also be very powerful. As I noted in the Beginners Guide to SQLite tutorial, this capability allowed me to complete a social network analysis project by appending the followers of several Twitter users of interest to a table as I gathered them. I used a SQLite database for this, to save time by avoiding the need to start from scratch in the case of a power outage or Windows update, which could forcefully shut down my computer. If something like that happened, all that I had to do was restart gathering the followers of the user that was next to the last one that I had in the database. To put it into perspective, the computation time to gather the followers of all my Twitter users of interest, took nearly 4 weeks to complete due to the rate limits of the Twitter API. Therefore, starting from zero due to a power outage or Windows update would have been very undesirable.

As always, I encourage you to keep learning about SQLite databases and how to interact with them through R. I strongly encourage you to dive deeper an check the previous tutorials covering their usage with Python and the command line, which can further help you become a full SQLite wiz. Keep learning; the sky is the limit!