M -am încurcat cu DBI și RSQLite și am aflat că este de fapt destul de simplu de utilizat în R – doar conectați, scrieți tabele și folosiți interogări SQL fără toate lucrurile de server complicate. Mulțumim lui Alec Wong pentru că ați sugerat acest lucru!
Motivație
După ultimul nostru blog, prietenul meu Alec Wong a sugerat să schimb stocarea datelor din fișierele CSV la SQLite atunci când construiesc API -ul instalatorului. Nu aveam idee că fișierele CSV pot fi corupte atunci când mai mulți utilizatori au lovit API -ul în același timp! SQLite gestionează acest lucru automat și vă permite să vă validați datele fără a fi nevoie să configurați orice chestii de server complicate. Este de fapt destul de simplu, iată o notă pentru mine cu privire la unele funcții simple și frecvente.
Obiective
Conectarea la o bază de date
library(DBI) library(RSQLite) library(tidyverse) con <- dbConnect(drv = RSQLite::SQLite(), "test.sqlite")
Asta este! Dacă fișierul nu există, acesta va crea unul.
Tabelele de listă
Să scriem un eșantion de date și să scriem într -un tabel din baza de date
# example df
employees <- tibble(
name = c("John Doe", "Jane Smith", "Bob Johnson", "Alice Brown"),
department = c("IT", "HR", "Finance", "Marketing"),
salary = c(75000, 65000, 80000, 70000)
)
# write df to dataframe
dbWriteTable(conn = con, name = "employees", value = employees)
# See What talbes are in the database
tables <- dbListTables(con)
tables
## (1) "employees"
Destul de simplu!
Verificați datele
## Method 1 employees_db <- tbl(con, "employees") employees_db |> collect() ## # A tibble: 4 × 3 ## name department salary #### 1 John Doe IT 75000 ## 2 Jane Smith HR 65000 ## 3 Bob Johnson Finance 80000 ## 4 Alice Brown Marketing 70000
Trebuie să folosească collect Pentru a returna un df. În schimb, putem face acest lucru
## Method 2 dbGetQuery(con, "select * from employees") ## name department salary ## 1 John Doe IT 75000 ## 2 Jane Smith HR 65000 ## 3 Bob Johnson Finance 80000 ## 4 Alice Brown Marketing 70000
Adăugați date
## Create New Row of Data new_employee <- data.frame( name = "Sarah Johnson", department = "Research", salary = 78000 ) ## Write to existing table dbWriteTable(conn = con, name = "employees", value = new_employee, append = TRUE) tbl(con, "employees") |> collect() ## # A tibble: 5 × 3 ## name department salary #### 1 John Doe IT 75000 ## 2 Jane Smith HR 65000 ## 3 Bob Johnson Finance 80000 ## 4 Alice Brown Marketing 70000 ## 5 Sarah Johnson Research 78000
DataFrame trebuie să conțină aceleași nume și număr de coloane. Altfel, nu va funcționa
## New column new_employee <- data.frame( name = "Sarah Johnson", department = "Research", salary = 78000, something_new = 12321321 ) dbWriteTable(con, "employees", value = new_employee, append = T) # OR # dbAppendTable(con, "employees", new_employee) Error: Columns `something_new` not found
Date de interogare
Filtra
dbGetQuery(con, "select * from employees where department = 'Research'") ## name department salary ## 1 Sarah Johnson Research 78000
Filtrează cu operator de potrivire
dbGetQuery(con, "select * from employees where name like '%john%'") ## name department salary ## 1 John Doe IT 75000 ## 2 Bob Johnson Finance 80000 ## 3 Sarah Johnson Research 78000
Observați că este insensibil la caz atunci când folosim like.
dbGetQuery(con, "select * from employees where name like 's%'") ## name department salary ## 1 Sarah Johnson Research 78000
Departamentul de grup și salariul mediu de retur
dbGetQuery(con, "select department, avg(salary) as avg_salary
from employees
group by department")
## department avg_salary
## 1 Finance 80000
## 2 HR 65000
## 3 IT 75000
## 4 Marketing 70000
## 5 Research 78000
Suma salariu cu nume de coloană nou
dbGetQuery(con, "select sum(salary) as total_salary from employees") ## total_salary ## 1 368000
Numărați numărul de departamente
dbGetQuery(con, "select count(distinct department) as distinct_department
from employees")
## distinct_department
## 1 5
Folosind glue_sql
var <- c("name","department")
table <- "employees"
query <- glue::glue_sql("select {`var`*} from {`table`}", .con = con)
dbGetQuery(con, query)
## name department
## 1 John Doe IT
## 2 Jane Smith HR
## 3 Bob Johnson Finance
## 4 Alice Brown Marketing
## 5 Sarah Johnson Research
Observați asteriscul (*) după {var} – Acest lucru spune glue_sql() Pentru a se alătura automat elementele cu virgule. glue_sql oferă un f-string simțiți codul.
Eliminați datele
## Delete Using Filter dbExecute(con, "delete from employees where name = 'Sarah Johnson'") ## (1) 1 dbGetQuery(con, "select * from employees") ## name department salary ## 1 John Doe IT 75000 ## 2 Jane Smith HR 65000 ## 3 Bob Johnson Finance 80000 ## 4 Alice Brown Marketing 70000
Scoateți cu filtru
dbGetQuery(con, "select * from employees") ## name department salary ## 1 John Doe IT 75000 ## 2 Jane Smith HR 65000 ## 3 Bob Johnson Finance 80000 ## 4 Alice Brown Marketing 70000 dbExecute(con, "delete from employees where salary >= 75000 and department = 'Finance'") ## (1) 1 dbGetQuery(con, "select * from employees") ## name department salary ## 1 John Doe IT 75000 ## 2 Jane Smith HR 65000 ## 3 Alice Brown Marketing 70000
Observați cum = necesită sensibil la caz F pe Finance Pentru a filtra cu exactitate? Bob nu mai este în DataFrame!
Deconecta
dbDisconnect(con)
Confirmare
Vă mulțumim din nou pentru ALEC pentru a sugera îmbunătățiri în proiectul nostru anterior!
Pentru extremitate plumber.R
library(plumber)
library(tidyverse)
library(lubridate)
library(DBI)
library(RSQLite)
path <- "" #set your own path
con <- dbConnect(RSQLite::SQLite(), paste0(path,"migraine.sqlite"))
#* @apiTitle Migraine logger
#* Return HTML content
#* @get /
#* @serializer html
function() {
# Return HTML code with the log button
html_content <- '
Migraine Logger
'
return(html_content)
}
#* logging
#* @post /log
function(){
date_now <- tibble(date=Sys.time())
dbWriteTable(con, "migraine", date_now, append = TRUE)
list(paste0("you have logged ", date_now$date(1), " to migraine database"))
}
#* download data
#* @get /download
#* @serializer contentType list(type="text/csv")
function(){
# Just return the raw CSV content
df <- tbl(con, "migraine") |> collect() |> mutate(date = as_datetime(date, tz = "America/New_York"))
format_csv(df)
}
#* Check datetime on browser
#* @get /table
function(){
df <- tbl(con, "migraine") |> collect() |> mutate(date = as_datetime(date, tz = "America/New_York"))
list(df)
}
Lecții învățate
- O mulțime de bunătăți pe site -ul oficial DBI
- Am învățat cum să configurați SQLite pe RPI, l -a încorporat pe registrul anterior de migrenă
- Cu siguranță trebuie să fii confortabil cu SQL pentru a utiliza acest lucru
- S -ar putea să fie o idee bună pentru a adăuga acest lucru și la registrul de presiune! Poate în aceeași bază de date, dar tabel diferit!
Dacă vă place acest articol:
