0Pricing
R Academy · Урок

Подключение к PostgreSQL и MySQL

Используйте драйверы RPostgres и RMySQL для подключения к базам данных на сервере.

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

Подключения к производственным базам данных

Хотя RSQLite отлично подходит для разработки и тестирования, в производственных системах данные хранятся в PostgreSQL, MySQL, SQL Server или облачных базах данных. Интерфейс DBI остаётся тем же — меняются только пакет драйвера и аргументы подключения. В этом заключается ключевое преимущество DBI.

library(DBI)

# Driver packages for each database:
# PostgreSQL  -> RPostgres::Postgres()
# MySQL       -> RMySQL::MySQL() or RMariaDB::MariaDB()
# SQL Server  -> odbc::odbc()
# BigQuery    -> bigrquery::bigquery()
# Redshift    -> RPostgres::Postgres()  (same protocol)
# DuckDB      -> duckdb::duckdb()

# All use the same DBI functions:
# dbConnect, dbGetQuery, dbExecute, dbDisconnect

Подключение к PostgreSQL

RPostgres::Postgres() — драйвер для PostgreSQL. Передайте параметры подключения: host, port (по умолчанию 5432), dbname, user и password. Никогда не встраивайте учётные данные в код — вместо этого используйте переменные окружения.

library(DBI)
# library(RPostgres)  # Uncomment when available

# PostgreSQL connection pattern:
# con <- dbConnect(
#   RPostgres::Postgres(),
#   host     = 'db.example.com',
#   port     = 5432,
#   dbname   = 'analytics',
#   user     = 'analyst',
#   password = 'secretpassword'
# )

# Query exactly like SQLite:
# result <- dbGetQuery(con, 'SELECT * FROM sales LIMIT 5')
# dbDisconnect(con)

cat('PostgreSQL uses the same DBI interface as SQLite!')

Использование переменных окружения для учётных данных

Хранение паролей непосредственно в коде создаёт угрозу безопасности. Сохраняйте учётные данные в переменных окружения и считывайте их с помощью Sys.getenv(). В производственной среде задавайте их в файле .Renviron, файлах .env (не добавляемых в git) или через системы управления секретами.

library(DBI)

# Set environment variables (normally done outside R):
# In .Renviron file:
# DB_HOST=db.example.com
# DB_NAME=analytics
# DB_USER=analyst
# DB_PASS=secretpassword

# Read credentials from environment
get_pg_connection <- function() {
  dbConnect(
    RPostgres::Postgres(),  # driver
    host     = Sys.getenv('DB_HOST'),
    port     = as.integer(Sys.getenv('DB_PORT', '5432')),
    dbname   = Sys.getenv('DB_NAME'),
    user     = Sys.getenv('DB_USER'),
    password = Sys.getenv('DB_PASS')
  )
}

cat('Sys.getenv() reads environment variables safely.')
cat('\nDB_HOST value:', nchar(Sys.getenv('DB_HOST')), 'chars')

Подключение к MySQL / MariaDB

Для подключений к MySQL используются RMariaDB::MariaDB() (современный драйвер MySQL) или RMySQL::MySQL(). Аргументы подключения похожи на аргументы для PostgreSQL. Драйвер MariaDB рекомендуется для серверов и MySQL, и MariaDB.

library(DBI)

# MySQL / MariaDB connection pattern:
# library(RMariaDB)
# con <- dbConnect(
#   RMariaDB::MariaDB(),
#   host     = Sys.getenv('MYSQL_HOST'),
#   port     = 3306,
#   dbname   = Sys.getenv('MYSQL_DB'),
#   user     = Sys.getenv('MYSQL_USER'),
#   password = Sys.getenv('MYSQL_PASS')
# )

# SSL connection:
# con <- dbConnect(
#   RMariaDB::MariaDB(),
#   host = 'secure-db.example.com',
#   ssl.ca = '/path/to/ca-cert.pem'
# )

cat('RMariaDB supports both MySQL and MariaDB servers.')

Тайм-аут подключения и повторное подключение

Выполняющиеся длительное время сценарии могут столкнуться с тайм-аутами подключения. Проверяйте, действительно ли подключение, с помощью dbIsValid(con). Для сценариев ETL рассмотрите возможность повторного подключения в начале каждой партии обработки вместо того, чтобы удерживать одно подключение открытым часами.

library(DBI)
library(RSQLite)

# Safe function with reconnection
query_with_check <- function(con, sql) {
  if (!dbIsValid(con)) {
    stop('Connection is no longer valid. Reconnect.')
  }
  dbGetQuery(con, sql)
}

# Demonstrate with SQLite
con <- dbConnect(RSQLite::SQLite(), ':memory:')
dbWriteTable(con, 't', data.frame(x=1:3))

cat('Connection valid:', dbIsValid(con), '\n')
print(query_with_check(con, 'SELECT * FROM t'))

dbDisconnect(con)
cat('After disconnect, valid:', dbIsValid(con))

Принцип пула подключений

Создание нового подключения к базе данных для каждого запроса требует значительных ресурсов. Пул подключений поддерживает набор открытых подключений и повторно использует их. Это особенно важно для веб-API и приложений Shiny, где каждую секунду поступает множество запросов.

# The 'pool' package provides connection pooling for R
# library(pool)

# Create a pool (connections managed automatically)
# my_pool <- pool::dbPool(
#   drv      = RPostgres::Postgres(),
#   dbname   = Sys.getenv('DB_NAME'),
#   host     = Sys.getenv('DB_HOST'),
#   user     = Sys.getenv('DB_USER'),
#   password = Sys.getenv('DB_PASS'),
#   minSize  = 2,   # Always keep 2 connections ready
#   maxSize  = 10   # Maximum 10 simultaneous connections
# )

# Use the pool like a regular connection:
# result <- dbGetQuery(my_pool, 'SELECT * FROM table')

# Close the pool on shutdown:
# pool::poolClose(my_pool)

cat('pool package: connection reuse for high-traffic apps!')

Запросы с параметрами SQL (безопасный подход)

Никогда не объединяйте пользовательский ввод со строками SQL (это создаёт риск SQL-инъекции). Используйте параметризованные запросы с sqlInterpolate(con, sql, .dots=list(...)) или glue_sql() из пакета glue, чтобы безопасно вставлять значения.

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

# UNSAFE (SQL injection possible):
# name_input <- 'Alice' # imagine user-provided
# dbGetQuery(con, paste('SELECT * FROM students WHERE name =', name_input))

# SAFE: use sqlInterpolate
name_input <- 'Alice'
safe_sql <- sqlInterpolate(con,
  'SELECT * FROM students WHERE name = ?name',
  name = name_input
)
print(dbGetQuery(con, safe_sql))
dbDisconnect(con)

SSH-туннелирование для удалённых баз данных

Многие производственные базы данных недоступны напрямую из интернета. Распространённый подход — SSH-туннелирование: перенаправьте локальный порт на порт удалённой базы данных через SSH, а затем подключите R к localhost:local_port.

# SSH tunnel setup (run in terminal before connecting from R):
# ssh -N -L 5433:db-server.internal:5432 user@jump-host.example.com

# Then connect in R as if the DB is local:
# con <- dbConnect(
#   RPostgres::Postgres(),
#   host     = 'localhost',
#   port     = 5433,   # Local forwarded port
#   dbname   = 'analytics',
#   user     = Sys.getenv('DB_USER'),
#   password = Sys.getenv('DB_PASS')
# )

# You can also script the tunnel with:
# system('ssh -fN -L 5433:db:5432 user@jump-host')

cat('SSH tunneling makes private databases accessible to R.')

Чтение больших таблиц частями

Если таблица базы данных содержит миллионы строк, загрузка всех данных сразу может исчерпать память. Используйте LIMIT/OFFSET или получение данных на основе курсора, чтобы обрабатывать таблицу частями. Для потоковой обработки результатов объединяйте этот подход с dbFetch().

library(DBI)
library(RSQLite)

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

# Process in chunks of 25 rows
chunk_size <- 25
offset <- 0
total_processed <- 0

repeat {
  chunk <- dbGetQuery(con, sprintf(
    'SELECT * FROM big_table LIMIT %d OFFSET %d',
    chunk_size, offset
  ))
  if (nrow(chunk) == 0) break
  total_processed <- total_processed + nrow(chunk)
  offset <- offset + chunk_size
}

cat('Total rows processed:', total_processed, '\n')
dbDisconnect(con)

dbGetInfo() — метаданные подключения

dbGetInfo(con) возвращает метаданные подключения: версию сервера, имя базы данных, пользователя и другие сведения. Это полезно для ведения журнала, диагностики и проверки того, что в сценариях, работающих с несколькими окружениями, вы подключились к правильному экземпляру базы данных.

library(DBI)
library(RSQLite)

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

info <- dbGetInfo(con)
print(info)

# For PostgreSQL, info would include:
# $host, $port, $dbname, $username, $server.version

dbDisconnect(con)

Сравнение использования SQLite и PostgreSQL

Выбирайте SQLite для прототипирования, встраиваемых данных и тестирования; PostgreSQL — для рабочей среды, многопользовательского доступа и расширенных возможностей SQL. Код DBI практически идентичен — различаются только драйвер и параметры подключения.

library(DBI)
library(RSQLite)

# SQLite (development/testing)
con_dev <- dbConnect(RSQLite::SQLite(), ':memory:')
dbWriteTable(con_dev, 'data', mtcars)
dev_result <- dbGetQuery(con_dev, 'SELECT COUNT(*) n FROM data')
cat('SQLite dev DB rows:', dev_result$n, '\n')
dbDisconnect(con_dev)

# PostgreSQL (production) — same interface:
# con_prod <- dbConnect(RPostgres::Postgres(),
#   host=Sys.getenv('PG_HOST'), dbname=Sys.getenv('PG_DB'),
#   user=Sys.getenv('PG_USER'), password=Sys.getenv('PG_PASS'))
# prod_result <- dbGetQuery(con_prod, 'SELECT COUNT(*) n FROM data')
# dbDisconnect(con_prod)

cat('Same DBI code works for both!')

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

Как рекомендуется указывать учетные данные базы данных в скрипте R?

Итоги: подключения к PostgreSQL и MySQL

Основные выводы о подключениях к базам данных в рабочей среде:

  • Драйвер PostgreSQL: RPostgres::Postgres() с аргументами host, port, dbname, user, password
  • Драйвер MySQL/MariaDB: RMariaDB::MariaDB() с аналогичными аргументами
  • Всегда используйте Sys.getenv('VAR') для учетных данных — никогда не встраивайте их в код напрямую
  • dbIsValid(con) проверяет, остается ли подключение активным
  • Пакет pool управляет пулами подключений в приложениях с высокой нагрузкой
  • sqlInterpolate() предотвращает внедрение SQL-кода при работе со значениями, предоставленными пользователем
  • SSH-туннель: перенаправляет локальный порт для доступа к закрытым базам данных
  • Все функции DBI работают одинаково с разными серверными системами
library(DBI)
library(RSQLite)

# Production-ready connection pattern (SQLite for demo)
open_connection <- function() {
  con <- dbConnect(RSQLite::SQLite(), ':memory:')
  if (!dbIsValid(con)) stop('Failed to connect')
  cat('Connected successfully\n')
  con
}

run_query <- function(con, sql) {
  if (!dbIsValid(con)) stop('Connection lost')
  dbGetQuery(con, sql)
}

con <- open_connection()
dbWriteTable(con, 't', data.frame(x=1:3, y=4:6))
print(run_query(con, 'SELECT * FROM t'))
dbDisconnect(con)

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

Урок «Подключение к PostgreSQL и MySQL» бесплатный?

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

Чему я научусь в уроке «Подключение к PostgreSQL и MySQL»?

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

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

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

Сколько времени занимает урок «Подключение к PostgreSQL и MySQL»?

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

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

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

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

  1. Основы DBI и RSQLite
  2. Подключение к PostgreSQL и MySQL
  3. dbplyr: SQL через синтаксис dplyr
  4. Параметризованные запросы и транзакции
← Назад к R Academy