Introduction à la gestion des bases de données avec des logiciels libres

Atelier offert par Guillaume Larocque, professionnel de recherche au CSBQ, à l'Université du Québec à Rimouski le 21 février 2017.

Installation de PostgreSQL

  • Visitez la page de EntrepriseDB et téléchargez la version appropriée pour votre plate-forme. Choisissez la version 64 bits.
  • Éxécutez le fichier et suivez les étapes d'installation en acceptant les options par défaut.
  • Quand on vous le demande, choisissez un mot de passe approprié pour l'utilisateur postgres. Prenez en note ce mot de passe.

Dans un terminal de commande, tapez:

sudo apt-get install postgresql-9.5 libreoffice libreoffice-sdbc-postgresql

Il est possible que la version de PostgreSQL disponible pour votre distribution soit antérieure à 9.5. Dans ce cas, installez la version disponible.

Pour d'autres distributions, vérifiez cette page.

Installation de LibreOffice

Installation de MySQL

Sur Windows et Mac:

  • Trouvez PostgreSQL 9.6 dans le menu démarrer/Finder et cliquez sur “SQL shell (psql)”
  • Acceptez les paramètres par défaut en cliquant sur ENTER et tapez le mot de passe approprié. Vous ne verrez rien lorsque vous taperez votre mot de passe, c'est normal.

Sur Linux Tapez simplement:

psql

dans un terminal.

Si ça ne fonctionne pas sur Mac ou Linux...

Autres instructions sous Linux...


Discussion de groupe

Exercices PostgreSQL

Créer une nouvelle base de données:

Si vous voulez, vous pouvez créer un nom d'usager autre que 'postgres'. Pour ce faire, tapez la commande suivante dans psql, en remplacant nom_usager et mot_de_passe par des valeurs choisies.

CREATE USER your_username WITH SUPERUSER PASSWORD 'your_password';

Créer une base de données 'atelier'

CREATE DATABASE atelier;
  • Ouvrez Libre Office Base et sélectionnez “Nouvelle…base de données”.
  • Se connecter à une base existance>PostgreSQL
  • Pour l'URL de la source de données, tapez ceci:
dbname=atelier host=localhost port=5432
  • Cliquez sur suivant et indiquez le nom d'usager, et choisissez 'mot de passe requis'. Vous pouvez ensuite cliquer sur 'Tester la connexion' pour vous assurer que ça fonctionne.
  • Vous pouvez ensuite sauvez l'information de connexion à la base de données sous un fichier sur votre ordinateur, de façon à facilement ré-ouvrir la base de données par la suite.

Se connecter à la base de données

\c atelier

Voir la liste de bases de données disponibles:

\l

Voir la liste des tables:

\d+

Voir les colonnes dans une table

\d nom_de_la_table

Lister tous les usagers

\du

Quitter Postgresql

\q

Formats de colonnes les plus communs

fonctiondescriptionexemple
intinteger5
decimal(,)decimal(longueur totale, nombre de décimales)122.52
varchar()character(longueur maximale) 'hirondelle bicolore'
texttexte d'une longueur non-définie 'Il était une fois une hirondelle…'
timestampdate et heure '2012-10-21 10:03:21'

Liste complète des type de données PostgreSQL.

Assurez-vous d'avoir créé la base de données atelier et de vous y être connecté.

\c atelier

Créer la table

CREATE TABLE obs_oiseaux (obs_id SERIAL PRIMARY KEY,
obs_time timestamp,espece text,nombre integer,lat real,"long" real);

Note: nous utilisons les doubles guillements ”“ pour marquer l'utilisation d'un mot réservé, d'un mot avec des caractères spéciaux ou des espaces dans le nom d'une table ou d'une colonne.

Insérer une nouvelle ligne dans la table

INSERT INTO obs_oiseaux (obs_time,espece,nombre,lat,"long") VALUES ('2012-06-19 12:31:16','Sturnus vulgaris',40,45.3522,-73.7930);

Notez que nous n'avons pas entré le champ obs_id puisqu'il est entré automatiquement avec le type de caractère SERIAL, tel que spécifié lors de la création de la table.

Pour visualiser la table

SELECT * FROM obs_oiseaux;

Question 1 :?: Reproduisez la table suivante dans PostgreSQL, nommez la 'Acteur' et visualisez là dans PostgreSQL.

prenomnom_de_familledate_naissancesexegrandeurmarque_distinctive
BruceWillis1955-03-19M1.81Joue fréquemment des hommes qui souffrent d'une tragédie, ont perdu quelque chose, ou qui ont une crise de confiance ou de conscience.
EmmaWatson1990-04-15F1.65Joue souvent des personnages littéraires.

Note: quand on entre du texte, il faut doubler les ' dans le texte ('') pour éviter que PostgreSQL ne les utilise comme marqueur de fin de l'entrée texte.

Téléchargez les fichiers suivants sur votre ordinateur (utilisez le bouton de droite pour cliquer sur le lien… sauvergarder sous). Lakes.csv species_acro.csv lakes_species.csv

Sous Windows: copiez ces trois fichiers vers un dossier nommé 'bdlibre' dans le répertoire C: (i.e. C:/bdlibre). Sous Mac ou Linux: copiez ces trois fichiers vers un dossier facilement accessible (e.g. /home/votrenome/bdlibre). Modifiez les commandes plus bas en remplaçant "C:/bdlibre" avec le chemin vers ce dossier.

Créer une table contenant l'information environnementale de chaque lac. - Lakes.

CREATE TABLE lakes (lake_id SERIAL PRIMARY KEY,numero INT, lake_name text, 
province text, latitude DEC(10,5),longitude DEC(10,5), number_of_species INT, ecoprov VARCHAR(50), 
ecozone VARCHAR(50), gss DEC(10,2),gse DEC(10,2), gsl DEC(10,2), gdd10 DEC(10,2), egdd DEC(10,2), 
mean_ele DEC(10,2), pe_ann_p DEC(10,2), totp_ann DEC(10,2), srann_me DEC(10,2), shann_me DEC(10,2), 
tmax_ann DEC(10,2), tmean_an DEC(10,2), vpann_me DEC(10,2));

MySQL

Charger le fichier CSV dans cette table.

COPY lakes FROM 'C:/bdlibre/lakes.csv' WITH csv HEADER DELIMITER AS ',';

Si cette commande vous donne un message 'access denied' sous Windows

Si cette commande vous donne un message d'erreur 'access denied' sur Mac ou Linux

MySQL

Créez la table contentant les présences de espèces pour chaque lac et charger le fichier CSV dans cette table.- lakes_species

CREATE TABLE lakes_species (lake_id INT NOT NULL, species_id INT NOT NULL);
COPY lakes_species FROM 'C:/bdlibre/lakes_species.csv' WITH csv HEADER DELIMITER AS ',';

MySQL

Pour la troisième table, nous pourrions utiliser cette commande:

Commande

Par contre, nous allons utiliser LibreOffice pour se familiariser avec l'interface.

Importation d'une table

  • Ouvrez le fichier species_acro.csv avec LibreOffice Calc (Dans Windows, trouvez LibreOffice Calc dans le menu démarrer). Spécifiez que le fichier est séparé par des virgules.
  • Sélectionnez la feuille de calcul en entier avec CTRL-A.
  • Ouvrez LibreOffice Base et allez à Edition…coller. Nommez la table “public.species_acro”. Choisissez d'utiliser le nom des colonnes comme entêtes pour la table. Ne spécifiez pas la clé primaire pour l'instant.
  • Suivez les étapes… Déplacez les colonnes à droite. Ensuite, spécifiez le format de données (vérifiez le format des données dans le fichier csv). Cliquez sur le bouton de droite sur la colonne 'species_id' et sélectionnez 'Clé Primaire'. Spécifiez int [int4] pour species_id, text [varchar], 200 caractères pour full_name et text [varchar], 30 caractères pour short_name.

Fonctions mathématiques et d'aggrégation (GROUP BY)

AVG() La moyenne
COUNT(DISTINCT COLUMN) Le nombre d'entrées distinctes dans une colonne
COUNT() Le nombre de lignes
GROUP_CONCAT() Concaténation de multiples entrées textuelles
MAX() Valeur maximale
MIN() Valeur minimale
STDDEV_POP() Déviation standard de la population
STDDEV_SAMP() Déviation standard de l'échantillon
SUM() Somme
VAR_POP() Variance de la population
VAR_SAMP() Variance de l'échantillon

Liste complète

Sélection conditionnelle pour utilisation dans une clause 'WHERE'

= Égal
> Plus grand que
< Moins que
>= Plus grand que ou égal
< = Plus petit que ou égal
<> or !=Non-égalité
LIKERequête d'équivalence d'entrées textuelles (string matching)

Ordre des opérations dans une requête 'SELECT':

SELECT columns FROM tables WHERE conditions JOIN GROUP BY columns HAVING condition ORDER BY columns;

Sélectionnez toutes les lignes de la table Lakes

SELECT * FROM lakes;

Sélectionner toutes les lignes, mais n'afficher que les noms des lacs et la province

SELECT lake_name, province FROM lakes;

Voir tous les noms de provinces différents

SELECT DISTINCT province from lakes;

Voir tous le noms de provinces, utiliser une alias (renommer cette colonne dans les résultats) et trier par ordre descendant de nom.

SELECT DISTINCT province as "province name" FROM lakes ORDER BY province DESC;

Voir l'année de naissance de chaque acteur

SELECT prenom, nom_de_famille, EXTRACT(year from date_naissance) FROM acteur;

Voir les initiales de chaque acteur

SELECT prenom, nom_de_famille, concat(substr(prenom,1,1),' ',substr(nom_de_famille,1,1)) as initiales FROM acteur;

Sélectionner tous les lacs du Québec où la température annuelle moyenne est en bas de -5 C.

SELECT lake_name, tmean_an FROM lakes WHERE province='QUEBEC' AND tmean_an<-5;

Ou, pour compter le nombre de lacs

SELECT count(*) FROM lakes WHERE province='QUEBEC' AND tmean_an<-5;
autre alternative:
SELECT count(lake_id) FROM lakes WHERE province='QUEBEC' AND tmean_an<-5;

Question 1 :?: - Combien de lacs en Colombie-Britannique reçoivent plus de 3000 mm de précipitation (colonne totp_ann)?

réponse

Question 2 :?: - Quel est l'altitude moyenne (colonne mean_ele) de tous les lacs dans l'écozone 'Montane Cordillera' (utilisez la fonction avg())?

réponse


Note: Le symbole % est utilisé pour remplacer le début ou la fin d'une entrée textuelle.
Sélectionnez tous les noms de lacs qui contiennent le mot 'Small'

SELECT lake_name FROM lakes WHERE lake_name like '%Small%';

Sélectionnez tous les noms de lacs qui contiennent le mot 'Small' et qui ne sont pas situés en Ontario

SELECT lake_name, province 
FROM lakes
WHERE lake_name like '%Small%' AND province!='ONTARIO';
autre possibilité:
SELECT lake_name, province 
FROM lakes 
WHERE lake_name like '%Small%' AND province NOT LIKE 'ONTARIO';

Question 3 :?: - Quelle est la latitude maximale de tous les lacs dans un Ecozone dont le nom commence par 'Taiga'

réponse

Question 4 :?: - Combien d'espèces de Daphnia sont-elles listées dans la table species_acro?

réponse

Calculez la température annuelle moyenne de tous les lacs dans chaque province

SELECT province, avg(tmean_an) as mean_an_t
FROM  lakes
GROUP BY province;

Même table, mais triée par ordre de températures croissantes

SELECT province, avg(tmean_an) as mean_an_t
FROM  lakes
GROUP BY province ORDER BY avg(tmean_an);

Question 5 :?: - Quelle province a le lac ayant la température maximale (tmax_ann) la plus élevée?

réponse

Commande 'UPDATE'

Changer toutes les noms de province qui contiennent 'NWT', pour qu'ils aient le même nom ('NWT'). - Faites attention, cette opération modifie la table!

UPDATE lakes
SET province='NWT'
WHERE province like '%NWT%';

Compter le nombre d'espèces dans chaque lac. Afficher le nom du lac et le nombre de chaque espèce.

SELECT lake_name, COUNT(DISTINCT species_id) as num_species FROM lakes, lakes_species 
WHERE lakes.lake_id=lakes_species.lake_id 
GROUP BY lakes.lake_id
ORDER BY num_species DESC;

Même requête, mais on limite l'affichage aux 20 premières entrées.

SELECT lake_name, COUNT(DISTINCT species_id) as num_species FROM lakes, lakes_species 
WHERE lakes.lake_id=lakes_species.lake_id 
GROUP BY lakes.lake_id
ORDER BY num_species DESC LIMIT 20;

Compter le nombre d'espèces dans chaque Ecozone et Ecoprovince.

SELECT ecozone, ecoprov, COUNT(species_id) as num_species FROM lakes, lakes_species 
WHERE lakes.lake_id=lakes_species.lake_id 
GROUP BY ecozone, ecoprov
ORDER BY ecozone, COUNT(species_id);

Question 6 :?: Générer une table qui liste le nom complet de chaque espèce de Daphnia et le nombre de lacs où on retrouve cette espèce.

Réponse


JOIN (INNNER JOIN, CROSS JOIN) Garder toutes les combinaisons possibles des lignes des tables A et B.
LEFT JOIN Garder toutes les entrées de la table A, et joindre avec les entrées de B qui se trouvent également dans A (via un identificateur conjoint).
RIGHT JOIN Garder toutes les entrées de la table B, et joindre avce les entrées de A qui se trouvent également dans B (via un identificateur conjoint).
OUTER JOIN Garder toutes les entrées des tables A et B

Créer une liste de toutes les espèces présentes dans les lacs du Québec, avec les noms complets des espèces et le nombre de lacs dans lesquelles elles sont présentes.

SELECT full_name, count(full_name) 
FROM species_acro,lakes_species,lakes 
WHERE species_acro.species_id=lakes_species.species_id AND  lakes.lake_id=lakes_species.lake_id AND province='QUEBEC' 
GROUP BY full_name;

De façon équivalente…

SELECT full_name, count(full_name) 
FROM lakes
LEFT JOIN lakes_species ON (lakes.lake_id=lakes_species.lake_id)
LEFT JOIN species_acro ON (species_acro.species_id=lakes_species.species_id)
WHERE province='QUEBEC' 
GROUP BY full_name;

Pour toutes les combinaisons possibles de lacs se trouvant dans l'Ecoprovince Baffin Uplands, calculer la différence entre leurs températures annuelles moyennes.

SELECT concat(a.lake_name,'-',b.lake_name) as lakes, a.tmean_an-b.tmean_an as temp_diff 
FROM lakes a, lakes b 
WHERE a.ecoprov='Baffin Uplands' AND b.ecoprov='Baffin Uplands';

Créez une table contenant le fréquence de chaque espèce dans les lacs au sud et au nord du 50e parallèle (latitude).

SELECT a.species_id, count_50south, count_50north
FROM 
 (SELECT species_id, count(d.species_id) as count_50south FROM lakes c, lakes_species d WHERE c.lake_id=d.lake_id AND latitude<=50 GROUP BY species_id) a,
 (SELECT species_id, count(f.species_id) as count_50north FROM lakes e, lakes_species f WHERE e.lake_id=f.lake_id AND latitude>50 GROUP BY species_id) b 
WHERE a.species_id=b.species_id;

:?: Question 7 En utilisant une sous-requête, trouvez l'élévation moyenne (colonne MEAN_ELE) des lacs dans lesquels au moins une espèce de Daphnia est présente.
Réponse

Utilisation de l'interface LibreOffice Base

On vous donne un jeu de données dans le format suivant

Parcelle 1
EspèceDHPÉtatCommentaires
Acer rubrum12.4vivant
Acer saccharum25.3vivant
Fagus grandifolia14.1vivant
Fagus grandifolia66.0mort
Fraxinus americana30.1vivantécorce endommagée
Parcelle 2
Quercus rubra12.4vivant
Fraxinus americana64.2vivantgros trou dans le tronc
Fraxinus americana53.1mortdéraciné
Carpinus caroliana10.3vivant

:?: On vous dit que des milliers d'autres arbres devront être entrés. Comment pensez-vous construire une base de données pour entreposer cette information?

:?: Créez la table suivante avec la commande 'CREATE TABLE' dans PostgreSQL. Spécifiez le champs espece_id comme la clé primaire avec le type de fichier SERIAL PRIMARY KEY. Vous pouvez ensuite remplir la table avec les valeurs dans Libreoffice Base. Nom de la table: especes_arbres

espece_idnom_espece
1Quercus rubra
2Acer saccharum
3Acer rubrum
4Fagus grandifolia
5Fraxinus americana
6Carpinus caroliana

:?: Créez cette table dans PostgreSQL et créez un formulaire pour la remplir avec le mode ébauche de formulaire, avec des menus déroulants pour sélectionner les espèces et des boutons radios pour la santé des arbres. arbre_id est la clé primaire. Nom de la table: parcelles_arbres

parcelle_idarbre_idespece_iddhpetatcommentaires
11312.4vivant
12225.3vivant
13414.1mort
14466.0vivant
15430.1vivantécorce endommagée
26140.1vivant
27564.2vivantgros trou dans le tronc
28553.1mortdéraciné
29610.3vivant

Utiliser Microsoft Access avec PostgreSQL

  • Dans Windows, téléchargez le pilote PostgreSQLODBC et installez-le sur votre ordinateur.
  • Créez vos tables dans PostgreSQL.
  • Dans Access, cliquez sur “External data… ODBC database”, et ensuite sélectionnez “Link to the data source by creating a linked table” et choisissez un nouveau nom de DSN. Spécifiez un nom pour le fichier et remplissez l'information pour vous connecter à la base de données. Le serveur est localhost si la base de données est sur votre propre ordinateur. Vous pouvez ensuite choisir les tables que vous voulez lier.
  • Ces tables seront ensuite liées avec PostgreSQL et vous pouvez les modifier à partir de Access ou via des formulaire Access.

Création de formulaires pour le web

Liens utiles

Étapes principales

  1. Création d'une interface web avec le code html et CSS.
  2. Création d'une base de données pour accueillir les données du formulaire.
  3. Création d'un code PHP qui va recevoir les données du formulaire et qui va les envoyer vers la base de données.
  4. Enrichissement possible du formulaire avec Javascript/JQuery

Télécharger et installer le serveur WAPP pour Windows, ou MAPP pour Mac.

Pour linux:

sudo apt-get install postgresql-9.5 apache2 php5 phppgadmin php5-pgsql

en modifiant selon la version de postgresql disponible dans votre distribution.

Composantes nécessaires pour un fichier HTML

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<html>
<head>
<meta charset="UTF-8">
<title>Titre du document</title>
</head>
<body>
Le contenu du fichier est ici!
</body>
</html>

Création d'un formulaire

Le formulaire de base

<form method="POST" action="fichierphp.php">
<input type="text">
</form>

Liste des types d'entrée html

Entrée de texte

<input type="text" name="nom_texte">

Menu déroulant

<select name="nom_select">
<option value="1">Option 1</option>
<option value="2">Option 2</option>
<option value="3">Option 3</option>
</select>

Check box

<input type="checkbox" name="poutine" value="oui"> J'aime la poutine
<input type="checkbox" name="sushis" value="oui"> J'aime les sushis
<input type="checkbox" name="poutine_sushis" value="oui"> J'aime la poutine aux sushis

Bouttons radio

Je suis un(e)
<input type="radio" name="nom_radio" value="h" > Homme
<input type="radio" name="nom_radio" value="f"> Femme

Zone de texte

<textarea name="zone_texte">
</textarea>

Guide de référence CSS

On peut donner un “id” à n'importe quel élément html. Important: il ne peut y avoir qu'un id unique par élément

<input type="text" name="nom_texte" id="nom_texte">

Et on peut y faire référence dans l'entête de style css du fichier html avec le dièse ”#“

<head>
<style>
#nom_texte {
 width:300px;
 height:30px;
 color:red;
 background-color:#ffff00;
 font-family:verdana,arial;
 font-size:14px;
 font-weight:bold;
 padding:0px 5px 0px 5px; #top right bottom left
}
</style>
</head>

On peut aussi donner une “class” à n'importe quel élément html. Cette classe peut se répeter sur plusieurs éléments

<input type="text" name="nom_texte1" class="classe_texte">
<input type="text" name="nom_texte2" class="classe_texte">
<input type="text" name="nom_texte3" class="classe_texte">

Et on peut y faire référence dans l'entête de style css du fichier html avec le point ”.“

<head>
<style>
.classe_texte {
 width:300px;
 height:30px;
 color:red;
 background-color:#ffff00;
 font-family:verdana,arial;
 font-size:14px;
 font-weight:bold;
 padding:0px 5px 0px 5px; #top right bottom left
}
</style>
</head>

Partie 2 - Création de la base de données associée

Partie 3 - Bases du langage PHP

Tous les fichiers doivent commencer par:

<?php
Le code PHP est ici. 
?>

Commentaires

<?php
// Commentaire sur une ligne
/*
Section de commentaires
*/
?>
<?php
// Création de variables
$myvar=5;
$myvar='ceci est du texte';
 
// Création d'un array
$myarray=array('valeur1','valeur2','valeur3');
 
echo $myarray[0];
//valeur1
 
$myarray=[];
$myarray[0]='valeur1';
$myarray[1]='valeur2';
 
// Création d'un array "key-value"
$myarray=array(
'cle1'=>'valeur1',
'cle2'=>'valeur2',
'cle3'=>'valeur3',
);
 
echo $myarray['cle1'];
//valeur1
?>

Boucles et if/else

<?php
if ($f==5){
//Do something
}else{
//Do something else
}
 
for ($i=0;$i<10;$i++){
echo 'i='.$i;
}
//i=0
//i=1
//...
//i=9
 
 
foreach ($myarray as $my){
//Faire quelque chose avec $my
?>

Partie 4 - Insertion dans PostgreSQL via PHP

Fonctions PostgreSQL dans PHP

Ce fichier PHP est celui qui est spécifié dans le champ “ACTION” du formulaire html.

<?php
// Connexion et sélection de la base de données
$dbconn = pg_connect("host=localhost dbname=base_donnees user=nom_usager password=mot_de_passe");
 
// L'array $_POST contient toutes les données du formulaire. Chaque 'clé' de cet array correspond au nom donné à chacun des type d'entrées html. 
$element1=$_POST['nom_element1'];
$element2=$_POST['nom_element2'];
$element1=pg_escape_string($element1);
$element2=pg_escape_string($element2);
 
// Insertion dans la base de données
$query = "INSERT INTO ma_table (colonne1,colonne2) VALUES ($element1,$element2)";
$result = pg_query($query);
// Fermer la connexion
pg_close($dbconn);
echo "Formulaire entré dans la base de données avec succès!";
?>

Accéder à différents types d'éléments

//Check boxes
foreach ($_POST['checkbox'] as $check){
   //Faire quelque chose ici avec $check
}
//Convertir les valeurs en liste séparée par des virgules
$check=implode(',',$_POST['checkbox']);

Retrouver des éléments dans la base de données

// Connexion et sélection de la base de données
$dbconn = pg_connect("host=localhost dbname=base_donnees user=nom_usager password=mot_de_passe");
 
$query="SELECT * FROM nom_table";
// Insertion dans la base de données
$result = pg_query($query);
while ($t=pg_fetch_array($result)){
	echo $t['prenom'].' '.$t['nom_famille'];
}
 
// Fermer la connexion
pg_close($dbconn);

Lier R avec PostgreSQL

Dans R: lors de la première utilisation seulement :

| installer le paquet RMySQL
install.packages('RPostgreSQL')
| Code R
library(RPostgreSQL)
drv <- dbDriver("PostgreSQL")
con <- dbConnect(drv,host="localhost", user="your_username", password="your_password", dbname="atelier");
lakes <- dbGetQuery(con,'SELECT * FROM lakes');

S'assurer de changer le nom d'utilisateur et le mot de passe au besoin.

Générer un graphique à partir des données extraites
lakesqc <- dbGetQuery(con,"SELECT * FROM lakes WHERE province='QUEBEC'")
hist(lakes$tmean_an)
library(dplyr)
src<-src_postgres(dbname="workshop",host="localhost", port="5432",user="your_username",password="your_password")
lakes <- tbl(src, "lakes") # Define lakes table
lakes_qc<-filter(lakes, province  %=% 'QUEBEC') # Select lakes in Quebec
prov_tmean<-summarise(group_by(lakes, province), mean(tmean_an)) # Mean annual temperature per province
prov_tmean=collect(prov_tmean) # Transfer result to standard R data frame
lakes_qc2<-tbl(src, sql("SELECT * FROM lakes WHERE province='QUEBEC'")) #Perform any SQL statement