R Academy · Урок

Основы DBI и RSQLite

Подключайтесь к базам данных SQLite, выполняйте запросы и получайте результаты с помощью DBI.

«Основы DBI и RSQLite» — бесплатный урок R Academy на CoddyKit. Это урок 1 из 4. Ты можешь прочитать весь урок бесплатно ниже — а потом практиковать его прямо в браузере с встроенным редактором кода и ИИ-репетитором 24/7. Это часть пути обучения R Academy, и твой прогресс синхронизируется между веб-версией и приложением CoddyKit. Курс R Academy содержит 4 уроков всего.

Доступ к базам данных в R с помощью DBI

Пакет DBI предоставляет единый интерфейс для доступа к базам данных в R. Одни и те же функции работают с разными серверными частями баз данных (SQLite, PostgreSQL, MySQL и другими), если использовать DBI вместе с пакетом драйвера соответствующей серверной части. RSQLite — наиболее доступная серверная часть: установка сервера не требуется.

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() — создание подключения

dbConnect(drv, ...) создаёт подключение к базе данных. Первый аргумент — объект драйвера. Для SQLite используется RSQLite::SQLite(). Дополнительные аргументы (например, путь к файлу базы данных) зависят от серверной части.

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() — загрузка данных в базу данных

dbWriteTable(con, 'table_name', df) создаёт таблицу и загружает в неё таблицу данных. Установите overwrite=TRUE, чтобы заменить существующую таблицу, или append=TRUE, чтобы добавить строки в существующую таблицу.

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()

dbListTables(con) возвращает имена всех таблиц в подключённой базе данных. dbListFields(con, 'table') возвращает имена столбцов указанной таблицы. Используйте эти функции для изучения неизвестной базы данных.

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 и возврат данных

dbGetQuery(con, sql) выполняет запрос SELECT и возвращает результат в виде таблицы данных. Это основная функция для чтения данных из базы данных. Она объединяет dbSendQuery() + dbFetch() + dbClearResult() в один шаг.

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() — получение результатов частями

Для больших наборов результатов используйте трёхэтапный подход: dbSendQuery() отправляет запрос, dbFetch(con, n=1000) получает части по n строк, а dbClearResult() выполняет очистку. Это позволяет контролировать использование памяти для больших таблиц.

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

dbExecute(con, sql) выполняет SQL-команды, изменяющие данные (INSERT, UPDATE, DELETE, CREATE, DROP), и возвращает количество затронутых строк. Используйте её для операций DDL и DML, которые не возвращают строки.

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()

dbExistsTable(con, 'name') проверяет существование таблицы (возвращает TRUE/FALSE). dbRemoveTable(con, 'name') удаляет таблицу. Эти функции полезны для безопасной настройки и очистки в сценариях, которые могут выполняться многократно.

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() — закрытие подключений

Всегда закрывайте подключения к базе данных с помощью dbDisconnect(con), когда закончите работу. Открытые подключения расходуют память и дескрипторы файлов. Используйте on.exit(dbDisconnect(con)) внутри функций, чтобы гарантировать очистку даже при возникновении ошибки.

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

Практический пример: загрузите встроенный набор данных mtcars в SQLite, а затем выполните над ним SQL-запросы. Это демонстрирует полный рабочий процесс DBI и позволяет попрактиковаться в использовании SQL из R.

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

Рабочий процесс DBI следует единому шаблону независимо от серверной части базы данных: подключение → загрузка или запрос → отключение. Один и тот же код работает с SQLite, PostgreSQL, MySQL и другими системами — достаточно заменить драйвер в dbConnect().

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)

Быстрая проверка

В чём разница между dbGetQuery() и dbExecute() в DBI?

Итоги: DBI и RSQLite

Основные выводы о DBI и RSQLite:

dbConnect(RSQLite::SQLite(), ':memory:') — создаёт подключение к SQLite в памяти
dbWriteTable(con, 'name', df) — загружает таблицу данных в таблицу базы данных
dbListTables(con) — перечисляет таблицы; dbListFields(con, 'tbl') — перечисляет столбцы
dbGetQuery(con, sql) — выполняет SELECT и возвращает таблицу данных
dbFetch(rs, n=1000) — получает большие результаты частями
dbExecute(con, sql) — выполняет INSERT/UPDATE/DELETE/DDL
dbDisconnect(con) — всегда закрывает подключение; используйте on.exit() в функциях
DBI работает с PostgreSQL, MySQL, BigQuery и другими системами при замене драйвера

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

Часто задаваемые вопросы

Урок «Основы DBI и RSQLite» бесплатный?

Да — полный текст урока «Основы DBI и RSQLite» бесплатно доступен здесь в веб-версии. Чтобы практиковать его интерактивно (встроенный редактор кода и ИИ-репетитор 24/7) и разблокировать остальной курс R Academy, подпишись на CoddyKit PRO. Курс R Academy содержит 4 уроков всего.

Чему я научусь в уроке «Основы DBI и RSQLite»?

Подключайтесь к базам данных SQLite, выполняйте запросы и получайте результаты с помощью DBI. Ты практикуешь R Academy с помощью реального кода, который запускаешь прямо в браузере, и ИИ-репетитор 24/7 отвечает на твои вопросы во время урока.

Нужен ли мне опыт, чтобы начать R Academy?

Предыдущий опыт не требуется. R Academy на CoddyKit структурирован для всех уровней — от новичков до продвинутых, поэтому ты можешь начать отсюда или с самого начала и учиться в своем темпе. Это урок 1 из 4.

Сколько времени занимает урок «Основы DBI и RSQLite»?

Большинство уроков CoddyKit занимают около 5–10 минут. Каждый из них компактный и интерактивный, поэтому ты постоянно делаешь прогресс и продолжаешь с того же места в веб-версии и приложении.

Можно ли писать и запускать код в этом уроке R Academy?

Да. Каждый урок R Academy включает встроенный редактор кода, поэтому ты пишешь и запускаешь реальный код прямо в браузере и получаешь моментальную обратную связь от AI — локальная установка не требуется.

Все уроки этого курса

← Назад к R Academy