R Academy · Lekcja

Łączenie z PostgreSQL i MySQL

Używaj sterowników RPostgres i RMySQL do łączenia się z bazami danych działającymi na serwerze.

Lekcja 2 z 413 kroki

Łączenie z PostgreSQL i MySQL to bezpłatna lekcja R Academy na CoddyKit. To lekcja 2 z 4. Możesz przeczytać całą lekcję poniżej za darmo — a potem ćwiczyć ją interaktywnie w przeglądarce z wbudowanym edytorem kodu i tutorem AI dostępnym 24/7. To część ścieżki edukacyjnej R Academy, a Twój postęp synchronizuje się między webem a aplikacją CoddyKit. Kurs R Academy zawiera 4 lekcji w sumie.

Produkcyjne połączenia z bazami danych

RSQLite świetnie sprawdza się podczas programowania i testowania, ale dane produkcyjne znajdują się w PostgreSQL, MySQL, SQL Server lub bazach w chmurze. Interfejs DBI pozostaje taki sam — zmieniają się tylko pakiet sterownika i argumenty połączenia. To najważniejsza zaleta 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

Łączenie z PostgreSQL

RPostgres::Postgres() to sterownik PostgreSQL. Należy przekazać parametry połączenia: host, port (domyślnie 5432), dbname, user i password. Nigdy nie należy wpisywać danych uwierzytelniających bezpośrednio w kodzie — zamiast tego należy używać zmiennych środowiskowych.

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

Używanie zmiennych środowiskowych do przechowywania danych uwierzytelniających

Wpisywanie haseł bezpośrednio w kodzie stanowi zagrożenie bezpieczeństwa. Należy przechowywać dane uwierzytelniające w zmiennych środowiskowych i odczytywać je za pomocą Sys.getenv(). W środowisku produkcyjnym należy ustawić je w plikach .Renviron, .env (których nie należy dodawać do git) lub za pomocą systemów zarządzania sekretami.

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

Łączenie z MySQL / MariaDB

Połączenia z MySQL korzystają z RMariaDB::MariaDB() (nowoczesnego sterownika MySQL) lub RMySQL::MySQL(). Argumenty połączenia są podobne jak w przypadku PostgreSQL. Sterownik MariaDB jest zalecany zarówno dla serwerów MySQL, jak i 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.')

Limit czasu połączenia i ponowne łączenie

Długotrwałe skrypty mogą napotkać limity czasu połączenia. Należy sprawdzać, czy połączenie jest nadal prawidłowe, za pomocą dbIsValid(con). W skryptach ETL warto rozważyć ponowne łączenie na początku każdej partii przetwarzania zamiast utrzymywania jednego połączenia przez wiele godzin.

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

Koncepcja puli połączeń

Otwieranie nowego połączenia z bazą dla każdego zapytania jest kosztowne. Pula połączeń utrzymuje zestaw otwartych połączeń i ponownie je wykorzystuje. Ma to kluczowe znaczenie w przypadku internetowych interfejsów API i aplikacji Shiny, do których napływa wiele żądań na sekundę.

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

Wykonywanie zapytań z parametrami SQL (bezpieczne)

Nigdy nie należy łączyć danych wejściowych użytkownika bezpośrednio z ciągami SQL (grozi to wstrzyknięciem SQL). Należy używać zapytań parametryzowanych z sqlInterpolate(con, sql, .dots=list(...)) lub funkcji glue_sql() z pakietu glue, aby bezpiecznie wstawiać wartości.

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)

Tunelowanie SSH do zdalnych baz danych

Wiele produkcyjnych baz danych nie jest bezpośrednio dostępnych z internetu. Typowym rozwiązaniem jest tunelowanie SSH: przekierowanie lokalnego portu na port zdalnej bazy danych za pośrednictwem SSH, a następnie połączenie R z adresem 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.')

Odczytywanie dużych tabel partiami

Gdy tabela w bazie danych zawiera miliony wierszy, jednoczesne załadowanie wszystkiego może wyczerpać pamięć. Należy użyć LIMIT/OFFSET lub pobierania opartego na kursorze, aby przetwarzać tabelę partiami. W celu strumieniowego pobierania wyników można połączyć to z 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() — metadane połączenia

dbGetInfo(con) zwraca metadane połączenia: wersję serwera, nazwę bazy danych, użytkownika itd. Jest to przydatne podczas rejestrowania informacji, diagnostyki i sprawdzania, czy w skryptach działających w wielu środowiskach nawiązano połączenie z właściwą instancją bazy danych.

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)

Porównanie użycia SQLite i PostgreSQL

SQLite należy wybrać do prototypowania, przechowywania danych osadzonych i testów, a PostgreSQL — do środowisk produkcyjnych, dostępu wielu użytkowników i zaawansowanych funkcji SQL. Kod DBI jest niemal identyczny — różnią się tylko sterownik i parametry połączenia.

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

Szybkie sprawdzenie

Jaki jest zalecany sposób przekazywania danych uwierzytelniających do bazy danych w skrypcie R?

Odpowiedz w interaktywnej lekcji

Podsumowanie: połączenia z PostgreSQL i MySQL

Najważniejsze informacje dotyczące produkcyjnych połączeń z bazami danych:

Sterownik PostgreSQL: RPostgres::Postgres() z argumentami host, port, dbname, user i password
Sterownik MySQL/MariaDB: RMariaDB::MariaDB() z podobnymi argumentami
Do danych uwierzytelniających należy zawsze używać Sys.getenv('VAR') — nigdy nie wpisywać ich bezpośrednio w kodzie
dbIsValid(con) sprawdza, czy połączenie jest nadal aktywne
Pakiet pool zarządza pulami połączeń w aplikacjach o dużym ruchu
sqlInterpolate() zapobiega wstrzyknięciom SQL w przypadku wartości podawanych przez użytkowników
Tunel SSH: przekierowuje port lokalny, aby umożliwić dostęp do prywatnych baz danych
Wszystkie funkcje DBI działają tak samo niezależnie od używanego backendu

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)

Bezpłatny start

Ucz się R dzięki korepetycjom AI — za darmo

Pisz i uruchamiaj kod w przeglądarce, otrzymuj natychmiastową pomoc od korepetytora AI dostępnego 24/7 i kontynuuj naukę w sieci lub w aplikacji.

Kursy: 43
Lekcje: 159

Zacznij naukę bezpłatnie Wyświetl pełny kurs

Często zadawane pytania

Czy lekcja „Łączenie z PostgreSQL i MySQL” jest bezpłatna?

Tak — pełny tekst „Łączenie z PostgreSQL i MySQL” jest dostępny za darmo tutaj w sieci. Aby ćwiczyć ją interaktywnie (wbudowany edytor kodu i tutor AI dostępny 24/7) i odblokować resztę kursu R Academy, przejdź na CoddyKit PRO. Kurs R Academy zawiera 4 lekcji w sumie.

Co nauczysz się w „Łączenie z PostgreSQL i MySQL”?

Używaj sterowników RPostgres i RMySQL do łączenia się z bazami danych działającymi na serwerze. Ćwiczysz R Academy z praktycznym kodem, który uruchamiasz bezpośrednio w przeglądarce, a tutor AI dostępny 24/7 odpowiada na Twoje pytania podczas pracy nad lekcją.

Czy potrzebuję doświadczenia, aby zacząć R Academy?

Nie wymagamy żadnego doświadczenia. R Academy w CoddyKit jest strukturyzowany dla początkujących i zaawansowanych użytkowników, więc możesz zacząć tutaj lub od początku i uczyć się w swoim tempie. To lekcja 2 z 4.

Ile czasu zajmuje lekcja „Łączenie z PostgreSQL i MySQL”?

Większość lekcji CoddyKit trwa około 5–10 minut. Każda lekcja to mały, interaktywny krok, dzięki czemu robisz systematyczne postępy i zawsze wracasz dokładnie do tego samego miejsca — na webie i w aplikacji.

Czy mogę pisać i uruchamiać kod w tej lekcji R Academy?

Tak. Każda lekcja R Academy zawiera wbudowany edytor kodu, więc piszesz i uruchamiasz prawdziwy kod bezpośrednio w przeglądarce i od razu otrzymujesz sprzężenie zwrotne od AI — bez konfiguracji na komputerze.

Wszystkie lekcje w tym kursie

← Powrót do R Academy