0Pricing
R Academy · Leçon

Se connecter à PostgreSQL et MySQL

Utilisez les pilotes RPostgres et RMySQL pour vous connecter à des bases de données hébergées sur un serveur.

Se connecter à PostgreSQL et MySQL est une leçon R Academy gratuite sur CoddyKit. Ceci est la leçon 2 sur 4. Tu peux lire la leçon complète ci-dessous gratuitement — puis la pratiquer en direct dans le navigateur avec un éditeur de code intégré et un tuteur IA 24/7. Elle fait partie du parcours d'apprentissage R Academy, et ta progression se synchronise sur le web et l'application CoddyKit. Le cours R Academy comprend 4 leçons au total.

Connexions aux bases de données en production

RSQLite est très pratique pour le développement et les tests, mais les données de production se trouvent dans PostgreSQL, MySQL, SQL Server ou des bases de données infonuagiques. L'interface DBI reste la même : seuls le package de pilote et les arguments de connexion changent. C'est le principal atout de 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

Se connecter à PostgreSQL

RPostgres::Postgres() est le pilote pour PostgreSQL. Transmettez les paramètres de connexion : host, port (5432 par défaut), dbname, user et password. N'inscrivez jamais les identifiants directement dans le code : utilisez plutôt des variables d'environnement.

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

Utiliser des variables d'environnement pour les identifiants

Inscrire des mots de passe directement dans le code constitue un risque de sécurité. Stockez les identifiants dans des variables d'environnement et lisez-les avec Sys.getenv(). En production, définissez-les dans .Renviron, dans des fichiers .env (qui ne doivent pas être suivis par git) ou au moyen de systèmes de gestion des secrets.

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

Se connecter à MySQL / MariaDB

Les connexions MySQL utilisent RMariaDB::MariaDB() (le pilote MySQL moderne) ou RMySQL::MySQL(). Les arguments de connexion sont similaires à ceux de PostgreSQL. MariaDB est le pilote recommandé pour les serveurs MySQL et 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.')

Délai d'expiration et reconnexion

Les scripts de longue durée peuvent subir des délais d'expiration de connexion. Vérifiez que la connexion est toujours valide avec dbIsValid(con). Pour les scripts ETL, envisagez de vous reconnecter au début de chaque lot de traitement plutôt que de maintenir une connexion ouverte pendant des heures.

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

Principe du regroupement de connexions

Ouvrir une nouvelle connexion à la base de données pour chaque requête est coûteux. Le regroupement de connexions maintient un ensemble de connexions ouvertes et les réutilise. Il est essentiel pour les API Web et les applications Shiny qui reçoivent de nombreuses requêtes par seconde.

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

Interroger avec des paramètres SQL (méthode sûre)

Ne concaténez jamais une saisie utilisateur dans des chaînes SQL (risque d'injection SQL). Utilisez des requêtes paramétrées avec sqlInterpolate(con, sql, .dots=list(...)) ou glue_sql() du package glue pour insérer les valeurs en toute sécurité.

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)

Tunnellisation SSH pour les bases de données distantes

De nombreuses bases de données de production ne sont pas directement accessibles depuis Internet. Une méthode courante consiste à créer un tunnel SSH : redirigez un port local vers le port de la base de données distante via SSH, puis connectez 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.')

Lire de grandes tables par blocs

Lorsqu'une table de base de données contient des millions de lignes, tout charger en une seule fois peut épuiser la mémoire. Utilisez LIMIT/OFFSET ou une récupération fondée sur un curseur pour traiter la table par blocs. Combinez cette méthode avec dbFetch() pour obtenir les résultats en flux continu.

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() — Métadonnées de connexion

dbGetInfo(con) renvoie les métadonnées de connexion : version du serveur, nom de la base de données, utilisateur, etc. Cette fonction est utile pour la journalisation, les diagnostics et la vérification de la connexion à la bonne instance de base de données dans les scripts exécutés sur plusieurs environnements.

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)

Comparaison de l’utilisation de SQLite et PostgreSQL

Choisissez SQLite pour le prototypage, les données intégrées et les tests ; choisissez PostgreSQL pour la production, l’accès multiutilisateur et les fonctionnalités SQL avancées. Le code DBI est presque identique : seules le pilote et les paramètres de connexion diffèrent.

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

Vérification rapide

Quelle est la méthode recommandée pour fournir les identifiants de base de données dans un script R ?

Récapitulatif : connexions PostgreSQL et MySQL

Points clés concernant les connexions aux bases de données de production :

  • Pilote PostgreSQL : RPostgres::Postgres() avec les arguments host, port, dbname, user et password
  • Pilote MySQL/MariaDB : RMariaDB::MariaDB() avec des arguments similaires
  • Utilisez toujours Sys.getenv('VAR') pour les identifiants, et ne les inscrivez jamais directement dans le code
  • dbIsValid(con) vérifie si la connexion est toujours active
  • Le package pool gère les pools de connexions pour les applications à fort trafic
  • sqlInterpolate() empêche les injections SQL avec les valeurs fournies par l’utilisateur
  • Tunnel SSH : redirigez un port local pour accéder aux bases de données privées
  • Toutes les fonctions DBI fonctionnent de manière identique avec les différents moteurs
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)

Questions Fréquemment Posées

La leçon « Se connecter à PostgreSQL et MySQL » est-elle gratuite ?

Oui — le texte complet de « Se connecter à PostgreSQL et MySQL » est gratuit à lire ici sur le web. Pour la pratiquer de manière interactive (un éditeur de code intégré et un tuteur IA 24/7) et déverrouiller le reste du cours R Academy, passe à CoddyKit PRO. Le cours R Academy comprend 4 leçons au total.

Qu'est-ce que j'apprendrai dans « Se connecter à PostgreSQL et MySQL » ?

Utilisez les pilotes RPostgres et RMySQL pour vous connecter à des bases de données hébergées sur un serveur. Tu pratiques R Academy avec du code pratique que tu exécutes directement dans le navigateur, et un tuteur IA 24/7 répond à tes questions au fur et à mesure que tu avances dans la leçon.

Dois-je avoir de l'expérience pour commencer R Academy ?

Aucune expérience préalable n'est requise. R Academy sur CoddyKit est structuré pour les débutants jusqu'aux apprenants avancés, donc tu peux commencer ici ou depuis le début et avancer à ton rythme. Ceci est la leçon 2 sur 4.

Combien de temps prend la leçon « Se connecter à PostgreSQL et MySQL » ?

La plupart des leçons CoddyKit prennent environ 5–10 minutes. Chacune est courte et interactive, tu progresses régulièrement et tu repiques exactement où tu t'es arrêté sur le web et l'app.

Peux-tu écrire et exécuter du code dans cette leçon R Academy ?

Oui. Chaque leçon R Academy inclut un éditeur de code intégré, tu écris et exécutes du vrai code directement dans ton navigateur et tu reçois des retours IA instantanés — aucune configuration locale requise.

Toutes les leçons de ce cours

  1. Bases de DBI et RSQLite
  2. Se connecter à PostgreSQL et MySQL
  3. dbplyr : SQL avec la syntaxe dplyr
  4. Requêtes paramétrées et transactions
← Retour à R Academy