Cliquer pour télécharger la présentation du cours
Installation des logiciels :
- R Studio : https://posit.co/download/rstudio-desktop/
- DBeaver Community : https://dbeaver.io/
Pour la configuration d’une base de données SQLite avec DBeaver:
Ou Menu > Fichiers > Nouveau > DBeaver > Connexion base de données > Suivant > SQLite
Puis indiquer la localisation du fichier (noter que sous Windows, les \ doivent être remplacé par /) et puis le bouton »Create… »
Normalement la base de données va être créée. Il faut en premier lie sélectionné la base de données nouvellement créée et puis ouvrir un fichier de Script SQL via le menu > Editeur SQL > Script SQL :
Indiquer le code SQL pour depuis le fichier suivant :
Voici un morceau du code pour la génération des 3 tables utilisés. Le fichier ci-dessus inclus en plus les données de tests :
CREATE TABLE IF NOT EXISTS patient (
id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
age REAL NOT NULL,
code_postal TEXT(5) NOT NULL,
taille INTEGER NOT NULL,
poids INTEGER NOT NULL,
sexe INTEGER NOT NULL
);
CREATE TABLE IF NOT EXISTS centre_prelevement(
id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
nom TEXT(255) NOT NULL,
code_postal TEXT(5) NOT NULL
);
CREATE TABLE IF NOT EXISTS analyse(
id_analyse INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
date_analyse DATETIME,
glycemie REAL NOT NULL,
id_centre INTEGER NOT NULL,
id_patient INTEGER NOT NULL
);
Puis CTRL+Entrée pour exécuter le code et vous pouvez vérifier que les données sont présentes dans les tables en les parcourant :
Accès à la base de données depuis R
La première fois, on doit télécharger le package via la commande suivante :
install.packages("RSQLite")
A chaque fois, on devra charger le package pour utiliser les fonctions spécifiques à la base de données.
Pour information, l’accès à un serveur de base de données Mysql suppose plusieurs informations techniques, l’adresse du serveur de base de données (ici 127.0.0.1), un compte sur cette base de données (ici root), un mot de passe correspondant à ce compte (ici « » pour indiquer mot de passe vide), un port de base de données (ici et par défaut 3306 mais sur Mamp cela peut être 8889) et enfin une base de données (ici sante)
DB <- dbConnect(MySQL(), user="root", host="127.0.0.1", password="",dbname="sante" , port=3306)Dans le cas de SQLite, les accès sont plus simple :
library(RSQLite)
db_path <- "C:/Users/pierre.jean/Desktop/database.db"
conn <- dbConnect(RSQLite::SQLite(), db_path)
Et enfin poser des questions en SQL
result <- dbGetQuery(conn, "SELECT * FROM analyse")
print( result )Importation en CSV
En premier lieu, télécharger les données au format CSV et dézipper le fichier data.csv:
En second lieu, choisir à gauche « tables » pour éviter d’importer dans une table existante, puis clic droit pour ouvrir le menu : « Import des données »
L’importation ensuite permet de vérifier que les colonnes sont bien générées avec les bons types de données. Après avoir indiqué le fichier data.csv d’importation, faire « Suivant » :
Ensuite, vérification de la correspondance des colonnes et de la création de la nouvelle table, puis faire « Suivant » :
Enfin, confirmation de la création de la nouvelle table en appuyant sur le bouton « Commencer »:
Accès en mode API sur un fichier JSON
Une seule fois installation du package Jsonlite :
install.packages("jsonlite")
Puis récupération des données, ici l’exemple utilise l’API du « Art Insitut Chicago » : https://api.artic.edu/docs/#quick-start
library("jsonlite")
url <- 'https://api.artic.edu/api/v1/artworks/search?q=cats'
json_data <- fromJSON(url)
print(json_data)
R Studio en SQL vers Microsoft Accces
En premier lieu il faut installer le module pour R pour accéder à Accces:
install.packages("RODBC")
library(RODBC)Une fois l’installation réalisée de ce module, on peut ouvrir le fichier Accès et intérroger en SQL la base de données:
con <- odbcConnectExcel2007("C:/Users/utilisateur/Documents/sante.accdb")
resultat <- sqlQuery(con,"select date_analyse from data")
print( resultat )On peut faire de manière similaire sur MySQL avec des commandes similaires, en premier lieu en installant le module pour la liaison avec le serveur de base de données MySQL :
install.packages("RMySQL")
library(RMySQL)
DB <- dbConnect(MySQL(), user="root", host="127.0.0.1", password="",dbname="sante" , port=3306)
resultat <- dbGetQuery(DB, "SELECT * FROM data")
print( resultat )L’accès à un serveur de base de données Mysql suppose plusieurs informations techniques, l’adresse du serveur de base de données (ici 127.0.0.1), un compte sur cette base de données (ici root), un mot de passe correspondant à ce compte (ici « » pour indiquer mot de passe vide), un port de base de données (ici et par défaut 3306) et enfin une base de données (ici sante)
DB <- dbConnect(MySQL(), user="root", host="127.0.0.1", password="",dbname="sante" , port=3306)Une fois la connexion réalisée, on peut lister les tables dans cette base de données sante.
dbListTables(DB)Et enfin poser des questions en SQL
resultat <- dbGetQuery(DB, "SELECT * FROM data")
print( resultat )Test unitaire avec R Studio
install.packages("testthat")
library(testthat)
Supposons cette requête SQL que je pense toujours vrai pour me donner une réponse fixe pour la première ligne :
library(RSQLite)
db_path <- "C:/Users/pierre.jean/Desktop/database.db"
conn <- dbConnect(RSQLite::SQLite(), db_path)
result <- dbGetQuery(conn, "SELECT count(*) as nombre_analyse , patient.id FROM patient, analyse WHERE analyse.id_patient = patient.id and patient.age > 55 and patient.age < 65 GROUP BY analyse.id_patient order by nombre_analyse desc limit 1 ;")
print(result["nombre_analyse"])
Ce qui donne ici :
nombre_analyse
1 16
On peut alors faire un test automatique via cette commande pour vérifier qu’une nouvelle inclusion de données ne modifie par nos « certitudes » :
expect_equal( 16, dbGetQuery(conn, "SELECT count(*) as nombre_analyse , patient.id FROM patient, analyse WHERE analyse.id_patient = patient.id and patient.age > 55 and patient.age < 65 GROUP BY analyse.id_patient order by nombre_analyse desc limit 1")[,1])
Si un autre chiffre au lieu de 16 était indiqué on pourrait vérifier que le test n’est plus valable. D’autres types de tests existent pour vérifier la stabilité de notre programme.
