8  SQL (with SQLite sample)

Автор

Юрій Клебан

We are going to review working with database on SQLite, becouse it allows us not to install DB-server and start working with simple file.

For now we will use RSQLite package.

# install.packages("RSQLite")
library(RSQLite)
# let's use mtcars dataset

data("mtcars") # loads the data
head(mtcars) # preview the data
A data.frame: 6 × 11
mpg cyl disp hp drat wt qsec vs am gear carb
<dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
Mazda RX4 21.0 6 160 110 3.90 2.620 16.46 0 1 4 4
Mazda RX4 Wag 21.0 6 160 110 3.90 2.875 17.02 0 1 4 4
Datsun 710 22.8 4 108 93 3.85 2.320 18.61 1 1 4 1
Hornet 4 Drive 21.4 6 258 110 3.08 3.215 19.44 1 0 3 1
Hornet Sportabout 18.7 8 360 175 3.15 3.440 17.02 0 0 3 2
Valiant 18.1 6 225 105 2.76 3.460 20.22 1 0 3 1

I need this code for book successful building (remove database file if exists):

#Define the file name that will be deleted
fn <- paste0("../../data/cars.sqlite")
#Check its existence
if (file.exists(fn)) {
  #Delete file if it exists
  file.remove(fn)
}
TRUE

Now, let’s create new:

# create new db file
db_path = paste0("../../data/cars.sqlite")
# create connection
conn <- dbConnect(RSQLite::SQLite(), 
                    db_path,
                    overwrite = TRUE, append = FALSE) # for lecture content only
# Write the mtcars dataset into a table names mtcars_data
dbWriteTable(conn, "cars_table", mtcars)
# List all the tables available in the database
dbListTables(conn)
'cars_table' 
table_data <- dbGetQuery(conn, "SELECT * FROM cars_table")
head(table_data)
A data.frame: 6 × 11
mpg cyl disp hp drat wt qsec vs am gear carb
<dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
1 21.0 6 160 110 3.90 2.620 16.46 0 1 4 4
2 21.0 6 160 110 3.90 2.875 17.02 0 1 4 4
3 22.8 4 108 93 3.85 2.320 18.61 1 1 4 1
4 21.4 6 258 110 3.08 3.215 19.44 1 0 3 1
5 18.7 8 360 175 3.15 3.440 17.02 0 0 3 2
6 18.1 6 225 105 2.76 3.460 20.22 1 0 3 1 
# close connection
dbDisconnect(conn)

You can write complex queries for many tables if you knowledge of SQL allows.

8.1 Набори даних

  1. https://github.com/kleban/r-book-published/tree/main/datasets/telecom_users.csv
  2. https://github.com/kleban/r-book-published/tree/main/datasets/telecom_sers.xlsx
  3. https://github.com/kleban/r-book-published/tree/main/datasets/Default_Fin.csv
  4. https://github.com/kleban/r-book-published/tree/main/datasets/employes.xml

8.2 References

  1. SQLite in R. Datacamp
  2. Tidyverse googlesheets4 0.2.0
  3. Binanace spot Api Docs
  4. Web Scraping in R: rvest Tutorial by Arvid Kingl