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.
# 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)# mpg cyl disp hp drat wt qsec vs am gear carb car_names
# 1 21.0 6 160 110 3.90 2.620 16.46 0 1 4 4 Mazda RX4
# 2 21.0 6 160 110 3.90 2.875 17.02 0 1 4 4 Mazda RX4 Wag
# 3 22.8 4 108 93 3.85 2.320 18.61 1 1 4 1 Datsun 710
# 4 21.4 6 258 110 3.08 3.215 19.44 1 0 3 1 Hornet 4 Drive
# 5 18.7 8 360 175 3.15 3.440 17.02 0 0 3 2 Hornet Sportabout
# 6 18.1 6 225 105 2.76 3.460 20.22 1 0 3 1 Valiant# 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")# 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)# [1] "cars_data"# 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" "cars_data"# car make
# 1 Camaro Chevrolet
# 2 California Ferrari
# 3 Mustang Ford
# 4 Explorer Ford
# 5 Corolla Toyota
# 6 Lancer Mitsubishi
# 7 Sportage Kia
# 8 XE Jaguar# Gather the first 10 rows in the cars_data table
dbGetQuery(conn, "SELECT * FROM cars_data LIMIT 10")# mpg cyl disp hp drat wt qsec vs am gear carb car_names
# 1 21.0 6 160.0 110 3.90 2.620 16.46 0 1 4 4 Mazda RX4
# 2 21.0 6 160.0 110 3.90 2.875 17.02 0 1 4 4 Mazda RX4 Wag
# 3 22.8 4 108.0 93 3.85 2.320 18.61 1 1 4 1 Datsun 710
# 4 21.4 6 258.0 110 3.08 3.215 19.44 1 0 3 1 Hornet 4 Drive
# 5 18.7 8 360.0 175 3.15 3.440 17.02 0 0 3 2 Hornet Sportabout
# 6 18.1 6 225.0 105 2.76 3.460 20.22 1 0 3 1 Valiant
# 7 14.3 8 360.0 245 3.21 3.570 15.84 0 0 3 4 Duster 360
# 8 24.4 4 146.7 62 3.69 3.190 20.00 1 0 4 2 Merc 240D
# 9 22.8 4 140.8 95 3.92 3.150 22.90 1 0 4 2 Merc 230
# 10 19.2 6 167.6 123 3.92 3.440 18.30 1 0 4 4 Merc 280