PostgreSQLとMySQLへの接続
RPostgresとRMySQLのドライバーを使って、サーバーベースのデータベースに接続します。
「PostgreSQLとMySQLへの接続」はCoddyKit上の無料R Academyレッスンです。 これはレッスン2/4です。 下記で完全なレッスンを無料で読むことができます。その後、ブラウザ内の組み込みコードエディタと24時間対応のAIチューターでハンズオン演習できます。 これは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, dbDisconnectPostgreSQLに接続
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と似ています。MySQLサーバーとMariaDBサーバーの両方には、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スクリプトでは、1つの接続を何時間も開いたままにするのではなく、処理バッチの開始時に再接続することを検討してください。
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))コネクションプーリングの概念
クエリごとに新しいデータベース接続を開くと、負荷が大きくなります。コネクションプーリングでは、複数の接続を開いた状態で保持し、再利用します。1秒間に多数のリクエストを受けるWeb 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パッケージのglue_sql()を使用して、安全に値を挿入してください。
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を、本番環境、複数ユーザーからのアクセス、高度なSQL機能にはPostgreSQLを選択します。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() - 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)
AI チューターと学ぶ R — 無料
ブラウザでリアルコードを書いて実行し、24/7 の AI チューターから瞬時にサポートを受け、ウェブまたはアプリで続きから学習できます。
- コース
- 43
- レッスン
- 159
よくある質問
「PostgreSQLとMySQLへの接続」レッスンは無料ですか？
はい。「PostgreSQLとMySQLへの接続」の完全なテキストはこのウェブで無料で読めます。インタラクティブに演習し（組み込みコードエディタと24時間対応のAIチューター）、R Academyコースの残りをアンロックするには、CoddyKit PROにアップグレードしてください。 R Academyコースには全4レッスンが含まれています。
「PostgreSQLとMySQLへの接続」で何を学びますか？
RPostgresとRMySQLのドライバーを使って、サーバーベースのデータベースに接続します。 ブラウザで直接実行するハンズオンコードでR Academyを演習し、24時間対応のAIチューターがレッスンを進める中での質問に答えます。
R Academyを始めるのに経験は必要ですか？
事前経験は必要ありません。CoddyKitのR Academyは初級者から上級者向けに構成されているため、ここから始めるか最初から始めて、自分のペースで進むことができます。 これはレッスン2/4です。
「PostgreSQLとMySQLへの接続」レッスンにはどのくらい時間がかかりますか？
ほとんどのCoddyKitレッスンは約5～10分かかります。各レッスンはコンパクトでインタラクティブなので、着実に進歩し、ウェブとアプリ全体で正確に前回の場所から再開できます。
このR Academyレッスンでコードを書いて実行できますか？
はい。すべてのR Academyレッスンに組み込みコードエディタが含まれているため、ブラウザでリアルコードを書いて実行し、即座のAIフィードバックを取得できます。ローカル設定は不要です。
このコースのすべてのレッスン
- DBIとRSQLiteの基礎
- PostgreSQLとMySQLへの接続
- dbplyr：dplyr構文でSQLを扱う
- パラメーター化クエリとトランザクション