R Academy · Lektion

Ansluta till PostgreSQL och MySQL

Använd drivrutinerna RPostgres och RMySQL för att ansluta till serverbaserade databaser.

Lektion 2 av 413 steg

Ansluta till PostgreSQL och MySQL är en gratis lektion i R Academy på CoddyKit. Detta är lektion 2 av 4. Du kan läsa vilka 3 lektioner som helst i den här lärvägen kostnadsfritt i sin helhet – därefter låser CoddyKit PRO upp alla lektioner, plus praktisk övning med en inbyggd kodredigerare och en AI-lärare dygnet runt. Den ingår i lärvägen för R Academy, och Era framsteg synkroniseras mellan webben och CoddyKit-appen. Kursen i R Academy innehåller totalt 4 lektioner.

Databasanslutningar i produktion

RSQLite passar utmärkt för utveckling och testning, men produktionsdata finns ofta i PostgreSQL, MySQL, SQL Server eller molndatabaser. DBI-gränssnittet är fortfarande detsamma — endast drivrutinspaketet och anslutningsargumenten ändras. Detta är DBI:s främsta styrka.

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

Ansluta till PostgreSQL

RPostgres::Postgres() är drivrutinen för PostgreSQL. Ange anslutningsparametrarna host, port (standardvärde 5432), dbname, user och password. Hårdkoda aldrig inloggningsuppgifter — använd miljövariabler i stället.

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

Använda miljövariabler för inloggningsuppgifter

Att hårdkoda lösenord i kod är en säkerhetsrisk. Lagra inloggningsuppgifter som miljövariabler och läs dem med Sys.getenv(). I produktion kan ni ange dessa i .Renviron, .env-filer (som inte checkas in i git) eller via system för hantering av hemligheter.

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

Ansluta till MySQL / MariaDB

MySQL-anslutningar använder RMariaDB::MariaDB() (den moderna MySQL-drivrutinen) eller RMySQL::MySQL(). Anslutningsargumenten liknar dem för PostgreSQL. MariaDB är den rekommenderade drivrutinen för både MySQL- och MariaDB-servrar.

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

Timeout och återanslutning

Långkörande skript kan drabbas av timeouter för anslutningen. Kontrollera om anslutningen fortfarande är giltig med dbIsValid(con). För ETL-skript kan ni överväga att ansluta på nytt i början av varje bearbetningsbatch i stället för att hålla en anslutning öppen i timmar.

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

Konceptet anslutningspoolning

Det är resurskrävande att öppna en ny databasanslutning för varje fråga. Anslutningspoolning underhåller en uppsättning öppna anslutningar och återanvänder dem. Detta är avgörande för webb-API:er och Shiny-appar där många förfrågningar kommer in varje 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!')

Fråga med SQL-parametrar (säkert)

Sammanfoga aldrig användarinmatning direkt i SQL-strängar (risk för SQL-injektion). Använd parametriserade frågor med sqlInterpolate(con, sql, .dots=list(...)) eller glue_sql() från paketet glue för att infoga värden på ett säkert sätt.

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-tunnling för fjärrdatabaser

Många produktionsdatabaser är inte direkt åtkomliga från internet. Ett vanligt tillvägagångssätt är SSH-tunnling: vidarebefordra en lokal port till fjärrdatabasens port via SSH och anslut sedan R till 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äsa stora tabeller i delar

När en databastabell innehåller miljontals rader kan minnet ta slut om allt läses in samtidigt. Använd LIMIT/OFFSET eller hämta data via en cursor för att bearbeta tabellen i delar. Kombinera detta med dbFetch() för att strömma resultaten.

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

dbGetInfo(con) returnerar metadata om anslutningen: serverversion, databasnamn, användare med mera. Detta är användbart för loggning och diagnostik samt för att verifiera att ni anslöt till rätt databasinstans i skript som körs mot flera 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)

Jämförelse av användningen av SQLite och PostgreSQL

Välj SQLite för prototyper, inbäddade data och testning, och PostgreSQL för produktion, åtkomst för flera användare och avancerade SQL-funktioner. DBI-koden är nästan identisk – den enda skillnaden är drivrutinen och anslutningsparametrarna.

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

Snabbkontroll

Vilket är det rekommenderade sättet att ange databasautentiseringsuppgifter i ett R-skript?

Besvara frågan i den interaktiva lektionen

Repetition: PostgreSQL- och MySQL-anslutningar

Viktiga slutsatser om databasanslutningar i produktion:

PostgreSQL-drivrutin: RPostgres::Postgres() med argumenten host, port, dbname, user och password
MySQL/MariaDB-drivrutin: RMariaDB::MariaDB() med liknande argument
Använd alltid Sys.getenv('VAR') för autentiseringsuppgifter – hårdkoda dem aldrig
dbIsValid(con) kontrollerar om anslutningen fortfarande är aktiv
Paketet pool hanterar anslutningspooler för appar med hög trafik
sqlInterpolate() förhindrar SQL-injektion med värden från användare
SSH-tunnel: vidarebefordra en lokal port för åtkomst till privata databaser
Alla DBI-funktioner fungerar på samma sätt för olika backend-system

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 att börja

Lär dig R med en AI-lärare – gratis

Skriv och kör riktig kod i webbläsaren, få omedelbar hjälp av en AI-lärare dygnet runt och fortsätt där du slutade – på webben eller i appen.

Kurser: 43
Lektioner: 159

Börja lära dig gratis Visa hela kursen

Vanliga frågor

Är lektionen ”Ansluta till PostgreSQL och MySQL” gratis?

Ja – du kan läsa vilka 3 lektioner som helst i lärvägen R Academy, inklusive ”Ansluta till PostgreSQL och MySQL”, kostnadsfritt i sin helhet här på webben. Därefter låser CoddyKit PRO upp alla lektioner, plus interaktiv övning med en inbyggd kodredigerare och en AI-lärare dygnet runt. Kursen i R Academy innehåller totalt 4 lektioner.

Vad lär jag mig i ”Ansluta till PostgreSQL och MySQL”?

Använd drivrutinerna RPostgres och RMySQL för att ansluta till serverbaserade databaser. Ni övar på R Academy med praktisk kod som körs direkt i webbläsaren, medan en AI-handledare som är tillgänglig dygnet runt svarar på Era frågor under lektionen.

Behöver jag någon erfarenhet för att börja lära mig R Academy?

Du behöver inga förkunskaper. Utbildningen i R Academy på CoddyKit är upplagd för allt från nybörjare till avancerade elever, så att du kan börja här eller från början och gå fram i din egen takt. Detta är lektion 2 av 4.

Hur lång tid tar lektionen ”Ansluta till PostgreSQL och MySQL”?

De flesta CoddyKit-lektioner tar cirka 5–10 minuter. Varje lektion är kort och interaktiv, så att du gör stadiga framsteg och kan fortsätta precis där du slutade – på webben eller i appen.

Kan jag skriva och köra kod i den här R Academy-lektionen?

Ja. Varje R Academy-lektion innehåller en inbyggd kodredigerare, så att du kan skriva och köra riktig kod direkt i webbläsaren och få omedelbar AI-feedback – utan lokal installation.

Alla lektioner i den här kursen

Grunderna i DBI och RSQLite
Ansluta till PostgreSQL och MySQL
dbplyr: SQL via dplyr-syntax
Parametriserade frågor och transaktioner

← Tillbaka till R Academy