Création d'une base SQLITE avec R

Fabrice Dessaint, Inra

L’objectif de cette fiche est de présenter un exemple simple de construction d’une base de données SQLite. L’approche ainsi que les fonctions utilisées sont suffisamment générales pour être appliquées à la construction d’autres types de bases de données.

Introduction

SQLite est un moteur de base de données qui propose les mêmes fonctionnalités de base de données que PostgreSQL, MYSQL, … sans avoir besoin d’installer de logiciels complexes. Il est facile à installer et à utiliser et se révèle bien adapté pour les petits projets et/ou les projets mono-utilisateur.

Une base SQLite utilise le langage de requête SQL et se comporte presque exactement de la même manière qu’une base de données MySQL ou PostgreSQL. Toutes les tables de la base de données sont regroupées dans un seul fichier et donc facilement portables d’un ordinateur à un autre.

Dans la suite de la fiche, on suppose que SQLite est déjà installée sur la machine hôte. C’est le cas pour les systèmes d’exploitation MacOS et Linux. Ce n’est généralement pas le cas pour les systèmes Windows. On trouve sur le Web de nombreux tutoriels expliquant l’installation de SQLIte.

Un exemple simple

Pour illustrer la démarche, on va créer une petite base contenant 3 tables. Les données sont disponibles et contenues dans trois tableaux (data.frame). Le tableau auteurs contient des données sur des auteurs:

auteurs <- data.frame(auteurID = 1:3, auteurNom = c("Nom1", "Nom2", "Nom3"), 
    auteurPrenom = c("Prenom1", "Prenom2", "Prenom3"))
auteurs

##   auteurID auteurNom auteurPrenom
## 1        1      Nom1      Prenom1
## 2        2      Nom2      Prenom2
## 3        3      Nom3      Prenom3

Le tableau articles, contient des informations sur les articles:

articles <- data.frame(articleID = 1:4, articleTitre = c("Titre1", "Titre2", 
    "Titre3", "Titre4"), articleAuteur = c(1, 1, 2, 3), articleAnnee = c(2013, 
    2013, 2014, 2014), articleRevue = c(1, 1, 2, 2))
articles

##   articleID articleTitre articleAuteur articleAnnee articleRevue
## 1         1       Titre1             1         2013            1
## 2         2       Titre2             1         2013            1
## 3         3       Titre3             2         2014            2
## 4         4       Titre4             3         2014            2

Enfin, le tableau revues décrit les revues:

revues <- data.frame(revueID = 1:3, revueNom = c("Revue1", "Revue2", "Revue3"), 
    revueISSN = seq(11, 1, -5), revueIF = c(10.5, 4.5, 1.2))
revues

##   revueID revueNom revueISSN revueIF
## 1       1   Revue1        11    10.5
## 2       2   Revue2         6     4.5
## 3       3   Revue3         1     1.2

Créer/ouvrir une base

Pour créer la base, on va utiliser le package RSQLite. Ce package est proposé par David A. James et Seth Falcon. Il utilise en interne les fonctions du package DBI.

library("RSQLite")

On commence par spécifer le type de base de données que l’on souhaite utiliser (driver). Dans notre cas, il s’agit d’une base SQLite.

drv <- SQLite()

On ouvre ensuite, une connexion vers la base de données avec la fonction dbConnect(). Lorsque la base n’existe pas, elle est créée.

db <- dbConnect(drv, dbname = "References.sqlite")

La base s’appelle References.sqlite et elle est stockée dans le répertoire de travail de R. On peut créer une base temporaire, en mémoire, en indiquant dbname=":memory:".

Créer/alimenter les tables

Création des tables

SQLite définit 5 types pour les champs: NULL, INTEGER, REAL, TEXT et BLOB. La fonction dbDataType() permet d’obtenir des informations sur la correspondance entre le type (class) des variables contenues dans un tableau

sapply(auteurs, class)

##     auteurID    auteurNom auteurPrenom 
##    "integer"     "factor"     "factor"

et les types reconnus par SQLite.

sapply(auteurs, function(x) dbDataType(db, x))

##     auteurID    auteurNom auteurPrenom 
##    "INTEGER"       "TEXT"       "TEXT"

Cette information est utilisée pour définir les champs des différentes tables de la base de données.

Dans le tableau auteurs, on a une variable de type INTEGER, deux variables de type TEXT. Les différents types sont ceux reconnus par SQLite.

On peut créer les tables de différentes façons mais dans tous les cas, il s’agit d’envoyer une requête SQL de création d’une table. La méthode la plus générale est obtenue en utilisant directement la fonction dbSendQuery() avec une requête de création de table.

On commence par créer la table TAuteurs. Cette table est constituée de 3 champs dont une clé primaire (auteurID):

dbSendQuery(conn = db, "CREATE TABLE TAuteurs (
            auteurID INTEGER,
            auteurNom TEXT NOT NULL,
            auteurPrenom TEXT NOT NULL,
            PRIMARY KEY (auteurID))")

## <SQLiteResult>
##   SQL  CREATE TABLE TAuteurs (
##             auteurID INTEGER,
##             auteurNom TEXT NOT NULL,
##             auteurPrenom TEXT NOT NULL,
##             PRIMARY KEY (auteurID))
##   ROWS Fetched: 0 [complete]
##        Changed: 0

Les noms utilisés pour les champs doivent être identiques à ceux utilisés dans le tableau de façon pouvoir ensuite alimenter la table.

On construit ensuite la table TRevues.

sapply(revues, function(x) dbDataType(db, x))

##   revueID  revueNom revueISSN   revueIF 
## "INTEGER"    "TEXT"    "REAL"    "REAL"

Cette table a 4 champs dont une clé primaire (revuesID):

dbSendQuery(conn = db, "CREATE TABLE TRevues (
            revueID INTEGER PRIMARY KEY,
            revueNom TEXT NOT NULL,
            revueISSN REAL UNIQUE NOT NULL,
            revueIF REAL NOT NULL)")

## <SQLiteResult>
##   SQL  CREATE TABLE TRevues (
##             revueID INTEGER PRIMARY KEY,
##             revueNom TEXT NOT NULL,
##             revueISSN REAL UNIQUE NOT NULL,
##             revueIF REAL NOT NULL)
##   ROWS Fetched: 0 [complete]
##        Changed: 0

Enfin, on définit la table TArticles.

sapply(articles, function(x) dbDataType(db, x))

##     articleID  articleTitre articleAuteur  articleAnnee  articleRevue 
##     "INTEGER"        "TEXT"        "REAL"        "REAL"        "REAL"

Cette table possède 5 champs, dont une clé primaire (articleID) et 2 clés étrangères (articleAuteurID et articleRevue):

dbSendQuery(conn = db, "CREATE TABLE TArticles (
            articleID INTEGER PRIMARY KEY,
            articleTitre TEXT NOT NULL,
            articleAuteur REAL NOT NULL,
            articleAnnee REAL NOT NULL,
            articleRevue REAL NOT NULL,
            FOREIGN KEY (articleAuteur) REFERENCES TAuteur (auteurID),
            FOREIGN KEY (articleRevue) REFERENCES TRevue (revueID))")

## <SQLiteResult>
##   SQL  CREATE TABLE TArticles (
##             articleID INTEGER PRIMARY KEY,
##             articleTitre TEXT NOT NULL,
##             articleAuteur REAL NOT NULL,
##             articleAnnee REAL NOT NULL,
##             articleRevue REAL NOT NULL,
##             FOREIGN KEY (articleAuteur) REFERENCES TAuteur (auteurID),
##             FOREIGN KEY (articleRevue) REFERENCES TRevue (revueID))
##   ROWS Fetched: 0 [complete]
##        Changed: 0

Information sur la base

On peut obtenir des informations générales sur la base avec la fonction dbGetInfo(). On obtient la liste des tables présentes dans la base avec la fonction dbListTables().

dbListTables(db)

## [1] "TArticles" "TAuteurs"  "TRevues"

Deux autres fonctions sont aussi disponibles pour

vérifier l’existence d’une table: dbExistsTable()

dbExistsTable(db, "TAuteurs")

## [1] TRUE

supprimer une table: dbRemoveTable()

dbRemoveTable(db, "TAuteurs")
dbExistsTable(db, "TAuteurs")

## [1] FALSE

dans la base de données.

dbSendQuery(conn = db, "CREATE TABLE TAuteurs (
            auteurID INTEGER,
            auteurNom TEXT NOT NULL,
            auteurPrenom TEXT NOT NULL,
            PRIMARY KEY (auteurID))")

## <SQLiteResult>
##   SQL  CREATE TABLE TAuteurs (
##             auteurID INTEGER,
##             auteurNom TEXT NOT NULL,
##             auteurPrenom TEXT NOT NULL,
##             PRIMARY KEY (auteurID))
##   ROWS Fetched: 0 [complete]
##        Changed: 0

Insérer des valeurs dans la table

L’alimentation de la base se fait avec la fonction dbWriteTable()

dbWriteTable(conn = db, name = "TAuteurs", value = auteurs, row.names = FALSE, 
    append = TRUE)
dbWriteTable(conn = db, name = "TRevues", value = revues, row.names = FALSE, 
    append = TRUE)
dbWriteTable(conn = db, name = "TArticles", value = articles, row.names = FALSE, 
    append = TRUE)

L’argument append=TRUE permet d’ajouter des enregistrements à une table existente. Si une table du même nom existe déjà, R affiche un message d’erreur.

dbWriteTable(conn = db, name = "TAuteurs", value = auteurs, row.names = FALSE, 
    append = FALSE)

## Erreur : Table TAuteurs exists in database, and both overwrite and append are FALSE

Lorsqu’il est égal à append=FALSE, R essaie de créer la table:

dbWriteTable(conn = db, name = "TTmp", value = auteurs[1:3, ], row.names = FALSE, 
    append = FALSE)

dbListTables(db)

## [1] "TArticles" "TAuteurs"  "TRevues"   "TTmp"

dbRemoveTable(db, "TTmp")

Visualiser le contenu d’une table

Pour chaque table, on peut obtenir la liste des champs avec la fonction dbListFields()

dbListFields(db, "TArticles")

## [1] "articleID"     "articleTitre"  "articleAuteur" "articleAnnee" 
## [5] "articleRevue"

et son contenu avec la fonction dbReadTable()

dbReadTable(db, "TAuteurs")

##   auteurID auteurNom auteurPrenom
## 1        1      Nom1      Prenom1
## 2        2      Nom2      Prenom2
## 3        3      Nom3      Prenom3

Fermeture de la connexion

On ferme la connexion avec la fonction dbDisconnect()

dbDisconnect(db)

Pour aller plus loin

La documentation de SQLite est disponible sur le site web de SQLite

Versions des outils utilisés

R version 3.4.2 (2017-09-28), RSQLite version 2.0

Thèmes de la fiche

Thèmes Base de données