0Pricing
R Academy · Leçon

Bases de DBI et RSQLite

Connectez-vous à des bases SQLite, exécutez des requêtes et récupérez les résultats avec DBI.

Bases de DBI et RSQLite est une leçon R Academy gratuite sur CoddyKit. Ceci est la leçon 1 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.

Accéder aux bases de données dans R avec DBI

Le package DBI fournit une interface unifiée pour accéder aux bases de données dans R. Les mêmes fonctions fonctionnent avec différents moteurs de base de données (SQLite, PostgreSQL, MySQL, etc.) en associant DBI à un package de pilote adapté. RSQLite est le moteur le plus accessible : aucune installation de serveur n'est nécessaire.

library(DBI)
library(RSQLite)

# DBI + RSQLite: connect to an in-memory SQLite database
# ':memory:' creates a fresh database in RAM
con <- dbConnect(RSQLite::SQLite(), ':memory:')

cat('Connection class:', class(con), '\n')
cat('Database backend:', dbGetInfo(con)$dbname, '\n')

# Always close connection when done
dbDisconnect(con)
cat('Connection closed.')

dbConnect() — Créer une connexion

dbConnect(drv, ...) crée une connexion à une base de données. Le premier argument est l'objet pilote. Pour SQLite : RSQLite::SQLite(). Les arguments supplémentaires (comme le chemin du fichier de la base de données) dépendent du moteur utilisé.

library(DBI)
library(RSQLite)

# In-memory database (disappears when connection closes)
con_mem <- dbConnect(RSQLite::SQLite(), ':memory:')

# File-based SQLite database (persists to disk)
# con_file <- dbConnect(RSQLite::SQLite(), '/tmp/mydb.sqlite')

# Always wrap connections in tryCatch or use on.exit()
on.exit(dbDisconnect(con_mem))

cat('Connected! Is valid:', dbIsValid(con_mem))

dbWriteTable() — Charger des données dans une base

dbWriteTable(con, 'table_name', df) crée une table et y charge un bloc de données. Définissez overwrite=TRUE pour remplacer une table existante, ou append=TRUE pour ajouter des lignes à une table existante.

library(DBI)
library(RSQLite)

con <- dbConnect(RSQLite::SQLite(), ':memory:')

# Load a data frame into the database
students <- data.frame(
  id = 1:5,
  name = c('Alice','Bob','Carol','Dave','Eve'),
  score = c(85, 92, 78, 88, 95)
)

dbWriteTable(con, 'students', students)

# Verify: list tables
cat('Tables in DB:', dbListTables(con), '\n')

dbDisconnect(con)

dbListTables() et dbListFields()

dbListTables(con) renvoie les noms de toutes les tables de la base de données connectée. dbListFields(con, 'table') renvoie les noms de colonnes d'une table précise. Utilisez ces fonctions pour explorer une base de données inconnue.

library(DBI)
library(RSQLite)

con <- dbConnect(RSQLite::SQLite(), ':memory:')
dbWriteTable(con, 'students', data.frame(id=1:3, name=c('A','B','C'), score=c(80,90,85)))
dbWriteTable(con, 'courses', data.frame(code=c('R101','R201'), title=c('Intro','Advanced')))

cat('Tables:', paste(dbListTables(con), collapse=', '), '\n')
cat('Students fields:', paste(dbListFields(con, 'students'), collapse=', '), '\n')

dbDisconnect(con)

dbGetQuery() — Exécuter du SQL et renvoyer des données

dbGetQuery(con, sql) exécute une requête SELECT et renvoie le résultat sous forme de bloc de données. Il s'agit de la fonction principale pour lire des données dans une base de données. Elle combine dbSendQuery(), dbFetch() et dbClearResult() en une seule étape.

library(DBI)
library(RSQLite)

con <- dbConnect(RSQLite::SQLite(), ':memory:')
dbWriteTable(con, 'students',
  data.frame(id=1:5, name=c('Alice','Bob','Carol','Dave','Eve'),
             score=c(85,92,78,88,95)))

# Run SQL and get results as data frame
high_scorers <- dbGetQuery(con,
  'SELECT name, score FROM students WHERE score >= 88 ORDER BY score DESC'
)

print(high_scorers)
dbDisconnect(con)

dbFetch() — Récupérer les résultats par blocs

Pour les grands ensembles de résultats, utilisez l'approche en trois étapes : dbSendQuery() envoie la requête, dbFetch(con, n=1000) récupère des blocs de n lignes, et dbClearResult() effectue le nettoyage. Cette méthode permet de maîtriser l'utilisation de la mémoire pour les grandes tables.

library(DBI)
library(RSQLite)

con <- dbConnect(RSQLite::SQLite(), ':memory:')
dbWriteTable(con, 'data', data.frame(x=1:100, y=rnorm(100)))

# Three-step approach for chunked reading
rs <- dbSendQuery(con, 'SELECT * FROM data WHERE x <= 5')
chunk <- dbFetch(rs, n=3)  # Get first 3 rows
cat('Rows fetched so far:', nrow(chunk), '\n')
print(chunk)
dbClearResult(rs)  # Always clear!

dbDisconnect(con)

dbExecute() — Exécuter du SQL autre que SELECT

dbExecute(con, sql) exécute du SQL qui modifie les données (INSERT, UPDATE, DELETE, CREATE, DROP) et renvoie le nombre de lignes affectées. Utilisez cette fonction pour les opérations DDL et DML qui ne renvoient pas de lignes.

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

# Update a record
rows_affected <- dbExecute(con,
  'UPDATE students SET score = 95 WHERE name = \'Alice\''
)
cat('Rows updated:', rows_affected, '\n')

# Verify
print(dbGetQuery(con, 'SELECT * FROM students WHERE name = \'Alice\''))
dbDisconnect(con)

dbExistsTable() et dbRemoveTable()

dbExistsTable(con, 'name') vérifie si une table existe (renvoie TRUE/FALSE). dbRemoveTable(con, 'name') supprime une table. Ces fonctions sont utiles pour préparer et démanteler proprement des scripts susceptibles d'être exécutés plusieurs fois.

library(DBI)
library(RSQLite)

con <- dbConnect(RSQLite::SQLite(), ':memory:')

cat('Before: exists?', dbExistsTable(con, 'results'), '\n')

dbWriteTable(con, 'results', data.frame(x=1:3, y=4:6))
cat('After write: exists?', dbExistsTable(con, 'results'), '\n')

dbRemoveTable(con, 'results')
cat('After remove: exists?', dbExistsTable(con, 'results'), '\n')

dbDisconnect(con)

dbDisconnect() — Fermer les connexions

Fermez toujours les connexions aux bases de données avec dbDisconnect(con) lorsque vous avez terminé. Les connexions ouvertes consomment de la mémoire et des descripteurs de fichiers. Utilisez on.exit(dbDisconnect(con)) dans les fonctions pour garantir le nettoyage, même en cas d'erreur.

library(DBI)
library(RSQLite)

# Safe connection pattern using on.exit()
safe_query <- function(query) {
  con <- dbConnect(RSQLite::SQLite(), ':memory:')
  on.exit(dbDisconnect(con))  # Runs even if error!
  dbWriteTable(con, 'data', data.frame(x=1:5, y=6:10))
  dbGetQuery(con, query)
}

result <- safe_query('SELECT * FROM data WHERE x > 3')
print(result)

Charger mtcars dans SQLite

Voici un exemple pratique : chargez le jeu de données intégré mtcars dans SQLite, puis exécutez des requêtes SQL sur celui-ci. Cet exemple présente l'ensemble du flux de travail DBI et vous permet de vous exercer à utiliser SQL depuis R.

library(DBI)
library(RSQLite)

con <- dbConnect(RSQLite::SQLite(), ':memory:')

# Load mtcars into the database
dbWriteTable(con, 'cars', mtcars, overwrite=TRUE)

# SQL query
result <- dbGetQuery(con, '
  SELECT cyl,
         COUNT(*) AS n,
         ROUND(AVG(mpg), 1) AS avg_mpg,
         ROUND(AVG(hp), 0) AS avg_hp
  FROM cars
  GROUP BY cyl
  ORDER BY cyl
')

print(result)
dbDisconnect(con)

Résumé du flux de travail DBI

Le flux de travail DBI suit un schéma cohérent quel que soit le moteur de base de données : se connecter → charger/interroger → se déconnecter. Le même code fonctionne avec SQLite, PostgreSQL, MySQL et d'autres systèmes ; il suffit de remplacer le pilote dans dbConnect().

library(DBI)
library(RSQLite)

# Complete DBI workflow
con <- dbConnect(RSQLite::SQLite(), ':memory:')

# 1. Create table
dbWriteTable(con, 'orders',
  data.frame(order_id=1:4, customer=c('Alice','Bob','Alice','Carol'),
             amount=c(100,200,150,80)))

# 2. Query
totals <- dbGetQuery(con,
  'SELECT customer, COUNT(*) orders, SUM(amount) total
   FROM orders GROUP BY customer ORDER BY total DESC')
print(totals)

# 3. Clean up
dbDisconnect(con)

Vérification rapide

Quelle est la différence entre dbGetQuery() et dbExecute() dans DBI ?

Récapitulatif : DBI et RSQLite

Points essentiels pour DBI et RSQLite :

  • dbConnect(RSQLite::SQLite(), ':memory:') — crée une connexion SQLite en mémoire
  • dbWriteTable(con, 'name', df) — charge un bloc de données dans une table
  • dbListTables(con) — liste les tables ; dbListFields(con, 'tbl') — liste les colonnes
  • dbGetQuery(con, sql) — exécute SELECT et renvoie un bloc de données
  • dbFetch(rs, n=1000) — récupération par blocs pour les grands résultats
  • dbExecute(con, sql) — exécute INSERT/UPDATE/DELETE/DDL
  • dbDisconnect(con) — fermez toujours la connexion ; utilisez on.exit() dans les fonctions
  • DBI fonctionne avec PostgreSQL, MySQL, BigQuery, etc. en remplaçant le pilote
library(DBI)
library(RSQLite)

con <- dbConnect(RSQLite::SQLite(), ':memory:')
on.exit(dbDisconnect(con))

dbWriteTable(con, 'scores',
  data.frame(student=c('Alice','Bob','Carol'), score=c(85,92,78)))

dbExecute(con, 'UPDATE scores SET score = score + 5 WHERE score < 80')

dbGetQuery(con, 'SELECT * FROM scores ORDER BY score DESC')

Questions Fréquemment Posées

La leçon « Bases de DBI et RSQLite » est-elle gratuite ?

Oui — le texte complet de « Bases de DBI et RSQLite » 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 « Bases de DBI et RSQLite » ?

Connectez-vous à des bases SQLite, exécutez des requêtes et récupérez les résultats avec DBI. 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 1 sur 4.

Combien de temps prend la leçon « Bases de DBI et RSQLite » ?

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