R Academy · Lektion

Forbindelse til PostgreSQL og MySQL

Brug RPostgres- og RMySQL-drivere til at oprette forbindelse til serverbaserede databaser.

Lektion 2 af 413 trin

Forbindelse til PostgreSQL og MySQL er en gratis R Academy-lektion på CoddyKit. Dette er lektion 2 af 4. Du kan læse alle 3 lektioner i dette læringsspor gratis i deres fulde længde — derefter låser CoddyKit PRO alle lektioner op samt praktiske øvelser med en indbygget kodeeditor og en AI-underviser døgnet rundt. Den er en del af læringsforløbet i R Academy, og dine fremskridt synkroniseres på tværs af nettet og CoddyKit-appen. R Academy-kurset indeholder 4 lektioner i alt.

Databaseforbindelser i produktion

Selvom RSQLite er velegnet til udvikling og test, ligger produktionsdata i PostgreSQL, MySQL, SQL Server eller cloud-databaser. DBI-grænsefladen forbliver den samme — det er kun driverpakken og forbindelsesargumenterne, der ændres. Det er DBI's vigtigste styrke.

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

Opret forbindelse til PostgreSQL

RPostgres::Postgres() er driveren til PostgreSQL. Angiv forbindelsesparametre: host, port (standardværdien er 5432), dbname, user og password. Indlej aldrig legitimationsoplysninger direkte i koden — brug i stedet miljøvariabler.

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

Brug af miljøvariabler til legitimationsoplysninger

Det er en sikkerhedsrisiko at indlejre adgangskoder direkte i koden. Gem legitimationsoplysninger som miljøvariabler, og læs dem med Sys.getenv(). I produktion kan du angive dem i .Renviron, .env-filer (som ikke skal lægges i git) eller via systemer til håndtering af hemmeligheder.

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

Opret forbindelse til MySQL / MariaDB

MySQL-forbindelser bruger RMariaDB::MariaDB() (den moderne MySQL-driver) eller RMySQL::MySQL(). Forbindelsesargumenterne ligner dem for PostgreSQL. MariaDB er den anbefalede driver til både MySQL- og MariaDB-servere.

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

Forbindelsestimeout og genforbindelse

Langvarige scripts kan ramme forbindelsestimeouts. Kontrollér, om forbindelsen stadig er gyldig, med dbIsValid(con). Ved ETL-scripts kan du overveje at oprette forbindelse igen ved starten af hver behandlingsportion i stedet for at holde én forbindelse åben i timevis.

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

Begrebet forbindelsespooling

Det er ressourcekrævende at åbne en ny databaseforbindelse for hver forespørgsel. Forbindelsespooling vedligeholder et sæt åbne forbindelser og genbruger dem. Det er afgørende for web-API'er og Shiny-apps, hvor mange forespørgsler ankommer hvert 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!')

Forespørgsler med SQL-parametre (sikkert)

Indsæt aldrig brugerinput ved at sammenkæde det med SQL-strenge (risiko for SQL-injektion). Brug parametriserede forespørgsler med sqlInterpolate(con, sql, .dots=list(...)) eller glue_sql() fra pakken glue for at indsætte værdier sikkert.

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-tunnel til eksterne databaser

Mange produktionsdatabaser er ikke direkte tilgængelige fra internettet. Et almindeligt mønster er SSH-tunneling: Videresend en lokal port til den eksterne databaseport via SSH, og opret derefter forbindelse fra R til 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.')

Læsning af store tabeller i portioner

Når en databasetabel har millioner af rækker, kan det udtømme hukommelsen at indlæse alt på én gang. Brug LIMIT/OFFSET eller markørbaseret hentning til at behandle tabellen i portioner. Kombinér det med dbFetch() for at streame resultater.

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

dbGetInfo(con) returnerer forbindelsesmetadata: serverversion, databasenavn, bruger osv. Det er nyttigt til logning, diagnosticering og kontrol af, at du har oprettet forbindelse til den korrekte databaseinstans i scripts, der kører mod flere miljøer.

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)

Sammenligning af brugen af SQLite og PostgreSQL

Vælg SQLite til prototyper, indlejrede data og test; vælg PostgreSQL til produktion, adgang for flere brugere og avancerede SQL-funktioner. DBI-koden er næsten identisk — den eneste forskel er driveren og forbindelsesparametrene.

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

Hurtigt tjek

Hvad er den anbefalede måde at angive databaselegitimationsoplysninger i et R-script på?

Besvar den i den interaktive lektion

Opsummering: PostgreSQL- og MySQL-forbindelser

Vigtigste pointer om databaseforbindelser i produktion:

PostgreSQL-driver: RPostgres::Postgres() med argumenterne host, port, dbname, user og password
MySQL/MariaDB-driver: RMariaDB::MariaDB() med tilsvarende argumenter
Brug altid Sys.getenv('VAR') til legitimationsoplysninger — hardkod dem aldrig
dbIsValid(con) kontrollerer, om forbindelsen stadig er aktiv
Pakken pool administrerer forbindelsespuljer til apps med meget trafik
sqlInterpolate() forhindrer SQL-injektion med værdier fra brugere
SSH-tunnel: viderestil en lokal port for at få adgang til private databaser
Alle DBI-funktioner fungerer ens på tværs af backends

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)

Gratis at komme i gang

Lær R med en AI-underviser — gratis

Skriv og kør rigtig kode i din browser, få øjeblikkelig hjælp fra en AI-underviser døgnet rundt, og fortsæt, hvor du slap, på web eller i appen.

Kurser: 43
Lektioner: 159

Start gratis Se hele kurset

Ofte stillede spørgsmål

Er lektionen “Forbindelse til PostgreSQL og MySQL” gratis?

Ja — alle 3 lektioner i læringssporet R Academy, inklusive “Forbindelse til PostgreSQL og MySQL”, kan læses gratis i deres fulde længde her på webstedet. Derefter låser CoddyKit PRO alle lektioner op samt interaktive øvelser med en indbygget kodeeditor og en AI-underviser døgnet rundt. R Academy-kurset indeholder 4 lektioner i alt.

Hvad lærer jeg i “Forbindelse til PostgreSQL og MySQL”?

Brug RPostgres- og RMySQL-drivere til at oprette forbindelse til serverbaserede databaser. Du øver dig i R Academy med praktisk kode, som du kører direkte i browseren, og en AI-vejleder døgnet rundt besvarer dine spørgsmål, mens du arbejder dig gennem lektionen.

Skal jeg have erfaring for at begynde på R Academy?

Der kræves ingen tidligere erfaring. R Academy på CoddyKit er tilrettelagt for både begyndere og øvede, så du kan starte her eller fra begyndelsen og lære i dit eget tempo. Dette er lektion 2 af 4.

Hvor lang tid tager lektionen “Forbindelse til PostgreSQL og MySQL”?

De fleste CoddyKit-lektioner tager cirka 5–10 minutter. Hver lektion er kort og interaktiv, så du gør løbende fremskridt og kan fortsætte, hvor du slap – på både web og app.

Kan jeg skrive og køre kode i denne R Academy-lektion?

Ja. Alle R Academy-lektioner har en indbygget kodeeditor, så du kan skrive og køre rigtig kode direkte i din browser og få øjeblikkelig feedback fra AI – uden lokal opsætning.

Alle lektioner i dette kursus

Grundlæggende DBI og RSQLite
Forbindelse til PostgreSQL og MySQL
dbplyr: SQL via dplyr-syntaks
Parameteriserede forespørgsler og transaktioner

← Tilbage til R Academy