พื้นฐาน DBI และ RSQLite

การเข้าถึงฐานข้อมูลใน R ด้วย DBI

library(DBI)
library(RSQLite)

# DBI + RSQLite: connect to an in-memory SQLite database
# ':memory:' creates a fresh database in RAM
con <- dbConnect(RSQLite::SQLite(), ':memory:')

cat('Connection class:', class(con), '\n')
cat('Database backend:', dbGetInfo(con)$dbname, '\n')

# Always close connection when done
dbDisconnect(con)
cat('Connection closed.')

dbConnect() — สร้างการเชื่อมต่อ

library(DBI)
library(RSQLite)

# In-memory database (disappears when connection closes)
con_mem <- dbConnect(RSQLite::SQLite(), ':memory:')

# File-based SQLite database (persists to disk)
# con_file <- dbConnect(RSQLite::SQLite(), '/tmp/mydb.sqlite')

# Always wrap connections in tryCatch or use on.exit()
on.exit(dbDisconnect(con_mem))

cat('Connected! Is valid:', dbIsValid(con_mem))

dbWriteTable() — โหลดข้อมูลเข้าสู่ฐานข้อมูล

library(DBI)
library(RSQLite)

con <- dbConnect(RSQLite::SQLite(), ':memory:')

# Load a data frame into the database
students <- data.frame(
  id = 1:5,
  name = c('Alice','Bob','Carol','Dave','Eve'),
  score = c(85, 92, 78, 88, 95)
)

dbWriteTable(con, 'students', students)

# Verify: list tables
cat('Tables in DB:', dbListTables(con), '\n')

dbDisconnect(con)

dbListTables() และ dbListFields()

library(DBI)
library(RSQLite)

con <- dbConnect(RSQLite::SQLite(), ':memory:')
dbWriteTable(con, 'students', data.frame(id=1:3, name=c('A','B','C'), score=c(80,90,85)))
dbWriteTable(con, 'courses', data.frame(code=c('R101','R201'), title=c('Intro','Advanced')))

cat('Tables:', paste(dbListTables(con), collapse=', '), '\n')
cat('Students fields:', paste(dbListFields(con, 'students'), collapse=', '), '\n')

dbDisconnect(con)

dbGetQuery() — เรียกใช้ SQL และส่งคืนข้อมูล

library(DBI)
library(RSQLite)

con <- dbConnect(RSQLite::SQLite(), ':memory:')
dbWriteTable(con, 'students',
  data.frame(id=1:5, name=c('Alice','Bob','Carol','Dave','Eve'),
             score=c(85,92,78,88,95)))

# Run SQL and get results as data frame
high_scorers <- dbGetQuery(con,
  'SELECT name, score FROM students WHERE score >= 88 ORDER BY score DESC'
)

print(high_scorers)
dbDisconnect(con)

dbFetch() — ดึงผลลัพธ์เป็นส่วน ๆ

library(DBI)
library(RSQLite)

con <- dbConnect(RSQLite::SQLite(), ':memory:')
dbWriteTable(con, 'data', data.frame(x=1:100, y=rnorm(100)))

# Three-step approach for chunked reading
rs <- dbSendQuery(con, 'SELECT * FROM data WHERE x <= 5')
chunk <- dbFetch(rs, n=3)  # Get first 3 rows
cat('Rows fetched so far:', nrow(chunk), '\n')
print(chunk)
dbClearResult(rs)  # Always clear!

dbDisconnect(con)

dbExecute() — เรียกใช้ SQL ที่ไม่ใช่ SELECT

library(DBI)
library(RSQLite)

con <- dbConnect(RSQLite::SQLite(), ':memory:')
dbWriteTable(con, 'students',
  data.frame(id=1:4, name=c('Alice','Bob','Carol','Dave'), score=c(85,92,78,88)))

# Update a record
rows_affected <- dbExecute(con,
  'UPDATE students SET score = 95 WHERE name = \'Alice\''
)
cat('Rows updated:', rows_affected, '\n')

# Verify
print(dbGetQuery(con, 'SELECT * FROM students WHERE name = \'Alice\''))
dbDisconnect(con)

dbExistsTable() และ dbRemoveTable()

library(DBI)
library(RSQLite)

con <- dbConnect(RSQLite::SQLite(), ':memory:')

cat('Before: exists?', dbExistsTable(con, 'results'), '\n')

dbWriteTable(con, 'results', data.frame(x=1:3, y=4:6))
cat('After write: exists?', dbExistsTable(con, 'results'), '\n')

dbRemoveTable(con, 'results')
cat('After remove: exists?', dbExistsTable(con, 'results'), '\n')

dbDisconnect(con)

dbDisconnect() — ปิดการเชื่อมต่อ

library(DBI)
library(RSQLite)

# Safe connection pattern using on.exit()
safe_query <- function(query) {
  con <- dbConnect(RSQLite::SQLite(), ':memory:')
  on.exit(dbDisconnect(con))  # Runs even if error!
  dbWriteTable(con, 'data', data.frame(x=1:5, y=6:10))
  dbGetQuery(con, query)
}

result <- safe_query('SELECT * FROM data WHERE x > 3')
print(result)

การโหลด mtcars เข้าสู่ SQLite

library(DBI)
library(RSQLite)

con <- dbConnect(RSQLite::SQLite(), ':memory:')

# Load mtcars into the database
dbWriteTable(con, 'cars', mtcars, overwrite=TRUE)

# SQL query
result <- dbGetQuery(con, '
  SELECT cyl,
         COUNT(*) AS n,
         ROUND(AVG(mpg), 1) AS avg_mpg,
         ROUND(AVG(hp), 0) AS avg_hp
  FROM cars
  GROUP BY cyl
  ORDER BY cyl
')

print(result)
dbDisconnect(con)

สรุปกระบวนการทำงานของ DBI

library(DBI)
library(RSQLite)

# Complete DBI workflow
con <- dbConnect(RSQLite::SQLite(), ':memory:')

# 1. Create table
dbWriteTable(con, 'orders',
  data.frame(order_id=1:4, customer=c('Alice','Bob','Alice','Carol'),
             amount=c(100,200,150,80)))

# 2. Query
totals <- dbGetQuery(con,
  'SELECT customer, COUNT(*) orders, SUM(amount) total
   FROM orders GROUP BY customer ORDER BY total DESC')
print(totals)

# 3. Clean up
dbDisconnect(con)

ตรวจสอบความเข้าใจอย่างรวดเร็ว

สรุป: DBI และ RSQLite

library(DBI)
library(RSQLite)

con <- dbConnect(RSQLite::SQLite(), ':memory:')
on.exit(dbDisconnect(con))

dbWriteTable(con, 'scores',
  data.frame(student=c('Alice','Bob','Carol'), score=c(85,92,78)))

dbExecute(con, 'UPDATE scores SET score = score + 5 WHERE score < 80')

dbGetQuery(con, 'SELECT * FROM scores ORDER BY score DESC')