SQLite in R

Creating Databases and Tables

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

conn <- dbConnect(RSQLite::SQLite(), "CarsDB.db")

dbWriteTable(conn, "cars_data", mtcars, overwrite = TRUE)
dbListTables(conn)

## [1] "Cars_and_Makes" "cars_data"

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)

dfList <- list(df1,df2)

for(k in 1:length(dfList)){
    dbWriteTable(conn,"Cars_and_Makes", dfList[[k]], append = TRUE)
}

dbListTables(conn)

## [1] "Cars_and_Makes" "cars_data"

dbGetQuery(conn, "SELECT * FROM Cars_and_Makes")

##           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
## 9      Camaro  Chevrolet
## 10 California    Ferrari
## 11    Mustang       Ford
## 12   Explorer       Ford
## 13    Corolla     Toyota
## 14     Lancer Mitsubishi
## 15   Sportage        Kia
## 16         XE     Jaguar
## 17     Camaro  Chevrolet
## 18 California    Ferrari
## 19    Mustang       Ford
## 20   Explorer       Ford
## 21    Corolla     Toyota
## 22     Lancer Mitsubishi
## 23   Sportage        Kia
## 24         XE     Jaguar

Executing SQL Queries

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

dbGetQuery(conn,"SELECT car_names, hp, cyl FROM cars_data
                 WHERE cyl = 8")

##              car_names  hp cyl
## 1    Hornet Sportabout 175   8
## 2           Duster 360 245   8
## 3           Merc 450SE 180   8
## 4           Merc 450SL 180   8
## 5          Merc 450SLC 180   8
## 6   Cadillac Fleetwood 205   8
## 7  Lincoln Continental 215   8
## 8    Chrysler Imperial 230   8
## 9     Dodge Challenger 150   8
## 10         AMC Javelin 150   8
## 11          Camaro Z28 245   8
## 12    Pontiac Firebird 175   8
## 13      Ford Pantera L 264   8
## 14       Maserati Bora 335   8

dbGetQuery(conn,"SELECT car_names, hp, cyl FROM cars_data
                 WHERE car_names LIKE 'M%' AND cyl IN (6,8)")

##       car_names  hp cyl
## 1     Mazda RX4 110   6
## 2 Mazda RX4 Wag 110   6
## 3      Merc 280 123   6
## 4     Merc 280C 123   6
## 5    Merc 450SE 180   8
## 6    Merc 450SL 180   8
## 7   Merc 450SLC 180   8
## 8 Maserati Bora 335   8

dbGetQuery(conn,"SELECT cyl, AVG(hp) AS 'average_hp', AVG(mpg) AS 'average_mpg' FROM cars_data
                 GROUP BY cyl
                 ORDER BY average_hp")

##   cyl average_hp average_mpg
## 1   4   82.63636    26.66364
## 2   6  122.28571    19.74286
## 3   8  209.21429    15.10000

avg_HpCyl <- dbGetQuery(conn,"SELECT cyl, AVG(hp) AS 'average_hp'FROM cars_data
                 GROUP BY cyl
                 ORDER BY average_hp")
avg_HpCyl

##   cyl average_hp
## 1   4   82.63636
## 2   6  122.28571
## 3   8  209.21429

class(avg_HpCyl)

## [1] "data.frame"

Insert Variables into Queries (Parameterised Queries)

mpg <-  18
cyl <- 6
Result <- dbGetQuery(conn, 'SELECT car_names, mpg, cyl FROM cars_data WHERE mpg >= ? AND cyl >= ?', params = c(mpg,cyl))
Result

##           car_names  mpg cyl
## 1         Mazda RX4 21.0   6
## 2     Mazda RX4 Wag 21.0   6
## 3    Hornet 4 Drive 21.4   6
## 4 Hornet Sportabout 18.7   8
## 5           Valiant 18.1   6
## 6          Merc 280 19.2   6
## 7  Pontiac Firebird 19.2   8
## 8      Ferrari Dino 19.7   6

assembleQuery <- function(conn, base, search_parameters){
    parameter_names <- names(search_parameters)
    partial_queries <- ""
    for(k in 1:length(parameter_names)){
        filter_k <- paste(parameter_names[k], " >= ? ")
        if(k > 1){
            filter_k <- paste("AND ", parameter_names[k], " >= ?")
        }
        partial_queries <- paste(partial_queries, filter_k)
    }

    final_paste <- paste(base, " WHERE", partial_queries)
    print(final_paste)

    values <- unlist(search_parameters, use.names = FALSE)
    result <- dbGetQuery(conn, final_paste, params = values)
    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)

## [1] "SELECT car_names, mpg, hp, wt FROM cars_data  WHERE  mpg  >= ?  AND  hp  >= ? AND  wt  >= ?"

result

##           car_names  mpg  hp    wt
## 1 Hornet Sportabout 18.7 175 3.440
## 2        Merc 450SE 16.4 180 4.070
## 3        Merc 450SL 17.3 180 3.730
## 4  Pontiac Firebird 19.2 175 3.845
## 5      Ferrari Dino 19.7 175 2.770

Statements that do not Return Tabular Results

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

dbExecute(conn, "DELETE FROM cars_data WHERE car_names = 'Mazda RX4'")

## [1] 1

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.875 17.02  0  1    4    4     Mazda RX4 Wag
## 2  22.8   4 108.0  93 3.85 2.320 18.61  1  1    4    1        Datsun 710
## 3  21.4   6 258.0 110 3.08 3.215 19.44  1  0    3    1    Hornet 4 Drive
## 4  18.7   8 360.0 175 3.15 3.440 17.02  0  0    3    2 Hornet Sportabout
## 5  18.1   6 225.0 105 2.76 3.460 20.22  1  0    3    1           Valiant
## 6  14.3   8 360.0 245 3.21 3.570 15.84  0  0    3    4        Duster 360
## 7  24.4   4 146.7  62 3.69 3.190 20.00  1  0    4    2         Merc 240D
## 8  22.8   4 140.8  95 3.92 3.150 22.90  1  0    4    2          Merc 230
## 9  19.2   6 167.6 123 3.92 3.440 18.30  1  0    4    4          Merc 280
## 10 17.8   6 167.6 123 3.92 3.440 18.90  1  0    4    4         Merc 280C

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

## [1] 1

dbGetQuery(conn, "SELECT * FROM cars_data")

##     mpg cyl  disp  hp drat    wt  qsec vs am gear carb           car_names
## 1  21.0   6 160.0 110 3.90 2.875 17.02  0  1    4    4       Mazda RX4 Wag
## 2  22.8   4 108.0  93 3.85 2.320 18.61  1  1    4    1          Datsun 710
## 3  21.4   6 258.0 110 3.08 3.215 19.44  1  0    3    1      Hornet 4 Drive
## 4  18.7   8 360.0 175 3.15 3.440 17.02  0  0    3    2   Hornet Sportabout
## 5  18.1   6 225.0 105 2.76 3.460 20.22  1  0    3    1             Valiant
## 6  14.3   8 360.0 245 3.21 3.570 15.84  0  0    3    4          Duster 360
## 7  24.4   4 146.7  62 3.69 3.190 20.00  1  0    4    2           Merc 240D
## 8  22.8   4 140.8  95 3.92 3.150 22.90  1  0    4    2            Merc 230
## 9  19.2   6 167.6 123 3.92 3.440 18.30  1  0    4    4            Merc 280
## 10 17.8   6 167.6 123 3.92 3.440 18.90  1  0    4    4           Merc 280C
## 11 16.4   8 275.8 180 3.07 4.070 17.40  0  0    3    3          Merc 450SE
## 12 17.3   8 275.8 180 3.07 3.730 17.60  0  0    3    3          Merc 450SL
## 13 15.2   8 275.8 180 3.07 3.780 18.00  0  0    3    3         Merc 450SLC
## 14 10.4   8 472.0 205 2.93 5.250 17.98  0  0    3    4  Cadillac Fleetwood
## 15 10.4   8 460.0 215 3.00 5.424 17.82  0  0    3    4 Lincoln Continental
## 16 14.7   8 440.0 230 3.23 5.345 17.42  0  0    3    4   Chrysler Imperial
## 17 32.4   4  78.7  66 4.08 2.200 19.47  1  1    4    1            Fiat 128
## 18 30.4   4  75.7  52 4.93 1.615 18.52  1  1    4    2         Honda Civic
## 19 33.9   4  71.1  65 4.22 1.835 19.90  1  1    4    1      Toyota Corolla
## 20 21.5   4 120.1  97 3.70 2.465 20.01  1  0    3    1       Toyota Corona
## 21 15.5   8 318.0 150 2.76 3.520 16.87  0  0    3    2    Dodge Challenger
## 22 15.2   8 304.0 150 3.15 3.435 17.30  0  0    3    2         AMC Javelin
## 23 13.3   8 350.0 245 3.73 3.840 15.41  0  0    3    4          Camaro Z28
## 24 19.2   8 400.0 175 3.08 3.845 17.05  0  0    3    2    Pontiac Firebird
## 25 27.3   4  79.0  66 4.08 1.935 18.90  1  1    4    1           Fiat X1-9
## 26 26.0   4 120.3  91 4.43 2.140 16.70  0  1    5    2       Porsche 914-2
## 27 30.4   4  95.1 113 3.77 1.513 16.90  1  1    5    2        Lotus Europa
## 28 15.8   8 351.0 264 4.22 3.170 14.50  0  1    5    4      Ford Pantera L
## 29 19.7   6 145.0 175 3.62 2.770 15.50  0  1    5    6        Ferrari Dino
## 30 15.0   8 301.0 335 3.54 3.570 14.60  0  1    5    8       Maserati Bora
## 31 21.4   4 121.0 109 4.11 2.780 18.60  1  1    4    2          Volvo 142E
## 32 21.0   6 160.0 110 3.90 2.620 16.46  0  1    4    4           Mazda RX4

dbDisconnect(conn)

Code taken from: SQLite in R.

Part 1. RSQLite()

Javier Aguilar

2025-10-26

SQLite in R

Creating Databases and Tables

Executing SQL Queries

Insert Variables into Queries (Parameterised Queries)

Statements that do not Return Tabular Results