9.2 BASES DE DONNÉES EN LIGNE

 

 

INTRODUCTION

 

Les bases de données sont extrêmement importantes dans notre monde actuel. Leur rôle principal consiste à stocker des données de manière structurée pour qu’il soit facile d’y accéder à l’aide de critères de recherche permettant de les mettre en relation. En raison de la forte interconnexion du réseau Internet et de l’usage extrêmement répandu des réseaux sociaux, d’énormes montagnes de données sont actuellement stockées dans des millions de bases de données. Il est de ce fait essentiel de comprendre comment les données sont gérées au sein des bases de données. Cela vous permettra également de mieux apprécier les risques liés à l’utilisation de systèmes basés sur le stockage de données.

Dans la plupart des bases de données informatisées, les informations sont stockées sous forme de tables reliées entre elles, raison pour laquelle on parle de bases de données relationnelles. Afin de gérer efficacement toutes ces tables, il est nécessaire de disposer d’un puissant logiciel appelé système de gestion de base de données relationnelle (SGBDR), ce qui se traduit en anglais par relational database management system (RDBMS). Il faut donc concevoir les bases de données comme l’ensemble des données qu’elles contiennent et les outils complexes permettant de gérer ces dernières.

Les bases de données simples peuvent être hébergées localement sur un ordinateur personnel et être gérées par une seule personne. Dans cette catégorie de bases de données se trouvent par exemple les listes de CDs ou de livres. Mais la plupart des bases de données sont hébergées sur l’Internet et sont de ce fait accessibles simultanément par plusieurs utilisateurs (bases de données en ligne). Ces systèmes client-serveur comportent un ordinateur qui joue le rôle de serveur de base de données (également appelé hôte) et de nombreux clients distribués. L’échange de données avec le serveur de base de données se fait par une connexion TCP, exactement comme pour un serveur Web, à la différence que le port IP est différent (3306 pour MySQL ou 1527 pour Derby). Une application spécifique développée dans un langage de haut niveau tel que Python s’exécute sur le client et se connecte à l’hôte distant.

 
Connexion directe
 
Connexion indirecte

La plupart du temps, le client n’est pas connecté directement au serveur de base de données mais plutôt au travers d’un serveur Web. Dans ce cas, l’ordinateur client ne fait qu’exécuter une application Web au sein de son navigateur Web et le programme en interaction avec la base de données s’exécute sur le serveur Web qui doit également être développé dans un langage de programmation approprié, très souvent le PHP. Que l’on développe un programme en connexion directe ou indirecte avec le serveur de base de données, il est nécessaire de disposer de solides connaissances de programmation en lien avec les bases de données. [plus... Le serveur de base de données et le serveur Web s’exécutent souvent sur des serveurs différents connectés
par Internet mais ils peuvent parfois s’exécuter sur la même machine
].

CONCEPTS DE PROGRAMMATION : Base de données, table, serveur de base de données

 

 

VOTRE PROPRE SERVEUR DE BASE DE DONNÉES

 

Il est délicat d’accéder directement à un serveur de base de données présent sur Internet en raison des fortes restrictions sécuritaires visant à éviter que n’importe qui aille stocker ou modifier n’importe quelle donnée n’importe où. Pour cette raison, vous allez installer votre propre serveur de base de données sur votre PC pour que vous puissiez l’utiliser directement en local ou depuis d’autres postes connectés au même réseau local LAN ou WLAN. Il est généralement nécessaire de s’authentifier auprès d’un serveur de base de données à l’aide d’un nom d’utilisateur et d’un mot de passe [plus... L’accès à un serveur de base de données depuis l’Internet est généralement protégé par un pare-feu (firewall).
L’avantage en installant votre propre serveur de base de données est que vous avez la possibilité de
configurer le pare-feu intégré à votre système d’exploitation en autorisant, pour votre adresse IP,
l’accès au port IP utilisé par la base de données, par exemple 1527 dans le cas de Derby
].

Dans l’exemple suivant, vous allez installer Derby, un produit libre de la fondation Apache qui a également développé le très fameux serveur Web Apache. Vous pourriez très bien aussi utiliser une autre base de données telle que MySQL. Pour installer Derby, il faut suivre les étapes décrites ci-dessous:

1. Télécharger le fichier tjderby.zip depuis ce lien.
2. Décompacter cette archive et copier les fichiers qu’elle contient dans le sous-dossier Lib du dossier dans lequel se trouve l’archive tigerjython2.jar.
3.

Se rendre dans le dossier Lib à l’aide d’un terminal en ligne de commandes
et démarrer le serveur à l’aide de la commande

java -jar derbynet.jar start

Attention! Il est nécessaire pour cela de disposer des privilèges administrateurs sur la machine [plus...Dans le cas d’un environnement sécurisé comme une salle de classe dans lequel il n’est pas possible
de disposer des droits administrateurs, il faut mettre sur pied une machine dédiée pour le serveur de base de données
].

Comme alternative vous pouvez démarer le serverv par les fichiers startderby.bat (pour Windows) ou startderby (pour Linux/Mac, donner les droits en execution) que vous trouvez dans le dossier Lib. Il est pratique de créer un lien vers ces fichiers et de le placer sur votre bureau pour pouvoir lancer le serveur en un clic. S’il est démarré de cette manière, le serveur ne sera accessible qu’en local (localhost). Pour le rendre accessible depuis le réseau local, il est nécessaire de démarrer le serveur en spécifiant l’adresse IP de l’interface réseau connectée au LAN. Ainsi, si votre adresse IP est 10.1.1.123, il faudra exécuter la commande
java -jar derbynet.jar start -h 10.1.1.123
ou, en utilisant le script de démarrage,
starderby 10.1.1.123.

Le serveur de base de données peut gérer plusieurs bases de données simultanément. Il est possible d’accéder à une base de données en connaissant son nom ainsi qu’une combinaison valide de nom d’utilisateur et mot de passe spécifique. Pour se connecter et utiliser une base de données depuis un programme Python, il faut utiliser la fonction connec(). Si la base de données en question n’est pas encore existante, elle sera automatiquement créée.

Avec Python le code pour manipuler la base de donnée est habituellement mis dans un bloc qui commence avec le mot clef with, parce que la connection avec la base de donnée est automatiquement fermée à la fin du bloc, même au cas où il y a des erreurs.

Une fois la connexion est établie avec succès,un objet de connexion con est disponible dont la méthode cursor() permet d’obtenir une clé d’accès (curseur) à la base de données. Une fois toutes ces étapes réalisées avec succès, toutes les portes de la base de données vous sont ouvertes depuis votre programme Python. On peut considérer une base de données comme un objet auquel on peut envoyer des commandes qui vont être scrupuleusement exécutées. Les commandes sont écrites dans un langage plus ou moins standardisé nommé SQL (Structured Query Language) qui est fortement basé sur une sorte d’argot anglais, de sorte qu’il est compréhensible par pratiquement n’importe qui. Il suffit, pour envoyer une commande sql, de l’écrire dans une chaine de caractères et de l’envoyer au serveur grâce à la méthode execute(sql). Pour rendre les instructions plus lisibles, on a l’habitude d’écrire les éléments propres au langage SQL en lettres capitales. Il est cependant tout à fait possible (mais pas conseillé) d’utiliser des minuscules ou un mélange des deux.

Comme première étape, il est nécessaire de créer une table à l’aide de l’instruction SQL CREATE TABLE. Comme d’habitude, les tables sont formées de lignes et de colonnes. Les colonnes, également appelées « champs », spécifient la nature des informations stockées dans la table. Il est nécessaire à ce titre de spécifier un nom et un type de données pour chacun des champs. Les types les plus courants sont listés dans le tableau suivant :

SQL data type (for Derby) Description
VARCHAR Chaines de caractères de longueur variable (<=255)
CHAR(M) Chaines de caractères de longueur M <= 255
INTEGER Nombre entier codé sur 4 octets
FLOAT Nombre flottant codé sur 64 bits (précision double)
DATE Date (java.sql.date)

Afin d’administrer le système de réservation de sièges du casino pour un concert donné,une table reservation est crée. . Nous ajouterons les champs seat pour le numéro du siège, booked qui contiendra les caractères "N" ou "Y" pour déterminer si le siège en question a été réservé, et un numéro de clients cust qui identifie de manière unique la personne ayant effectué la réservation (non utilisé pour le moment).

from dbapi import *

serverURL = "derbyserver:localhost"
#serverURL = "derbyserver:10.1.1.123"
dbname = "casino"
username = "admin"
password = "solar"
tbl = "reservation"

with connect(serverURL, dbname, username, password) as con:
    cursor = con.cursor()
    sql = "CREATE TABLE " + tbl + "(seat INTEGER, booked CHAR(1), cust INTEGER)" 
    cursor.execute(sql)
    con.commit()
print "Table created"
Sélectionner le code (Ctrl+C pour copier, Ctrl+V pour coller )

 

 

MEMENTO

 

Si le programme s’exécute sans encombre, c’est que l’installation du serveur de base de données s’est déroulée avec succès. Si, au contraire, vous obtenez des messages d’erreur, reprenez le chapitre en relisant toutes les instructions une à une.

Avec la plupart des base de données, il est très courant que les commandes SQL ne soient effectivement prises en compte qu’après un appel à la méthode commit(). Cela permet de garantir, pour des opérations impliquant plusieurs requêtes (on parle de transaction dans le langage des bases de données), qu’elles soient exécutées en intégralité ou, qu’en cas d’erreur, elles ne soient pas exécutées du tout.

 

 

INSÉRER DES DONNÉES DANS UNE TABLE

 

Dans un second temps, nous allons insérer des données d’initialisation dans la table, notamment pour marquer tous les sièges comme disponibles et initialiser le numéro du client à 0. Pour ce faire, on utilise la commande INSERT du langage SQL. Pour le siège numéro 1, cela donnerait par exemple la requête

INSERT INTO reservation VALUES (1, 'N', 0)

Cette requête ajoute une ligne dans notre table. Une ligne est également couramment désignée par l’expression jeu de données (dataset en anglais) ou enregistrement (record en anglais).

from dbapi import *

serverURL = "derbyserver:localhost"
#serverURL = "derbyserver:10.1.1.123"
dbname = "casino"
username = "admin"
password = "solar"
tbl = "reservation"

with connect(serverURL, dbname, username, password) as con:
    cursor = con.cursor()
    for seatNb in range(1, 31):
        sql = "INSERT INTO " + tbl + " VALUES (" + str(seatNb) + ",'N',0)"
        cursor.execute(sql)
    con.commit()
print "Table initialized"
Sélectionner le code (Ctrl+C pour copier, Ctrl+V pour coller )

 

 

MEMENTO

 

Si le programme est exécuté deux fois, l’enregistrement sera également inséré deux fois dans la base de données.

 

 

LIRE DES DONNÉES DEPUIS UNE TABLE

 

Vous devez à présent vous demander si les données se trouvent réellement dans la table. Dans tous les cas, les SGBDR professionnels actuels sont tellement bétonnés que l’on peut raisonnablement supposer, en l’absence de tout message d’erreur, que la transaction avec la base de données s’est déroulée comme prévu. Pour accéder aux données présentes dans la table, on utilise l’instruction SQL la plus connue:

SELECT * FROM reservation

L’astérisque (caractère de remplacement) permet de lire l’ensemble des enregistrements de la table. Suite à un appel à la méthode execute(), il est possible d’obtenir les enregistrements retournés par le SGBDR grâce à la méthode fetchall() de l’objet cursor. Cette méthode retourne une liste de tuples (sorte de liste non mutable) représentant les enregistrements répondant à la requête. L’accès aux différents champs d’un enregistrement se fait à l’aide d’indices.

Il est important de pouvoir connaître le nombre d’enregistrements retournés par la commande SELECT. Ce nombre se trouve toujours enregistré dans la variable globale rowcount après une opération SELECT.

from dbapi import *

serverURL = "derbyserver:localhost"
#serverURL = "derbyserver:10.1.1.123"
dbname = "casino"
username = "admin"
password = "solar"
tbl = "reservation"

with connect(serverURL, dbname, username, password) as con:
    cursor = con.cursor()
    sql = "SELECT * FROM " + tbl
    cursor.execute(sql)
    result = cursor.fetchall() # list of tuples
    for record in result:
        print "seatNb:", record[0], " booked:", record[1]," cust:", record[2]
Sélectionner le code (Ctrl+C pour copier, Ctrl+V pour coller )

 

 

MEMENTO

 

En plus de fetchall(), il est également possible d’utiliserfetchmany(n) et fetchone() pour récupérer les données du curseur. À chaque appel de l’une de ces méthodes, le curseur est pour ainsi dire avancé comme un pointeur du nombre d’enregistrements lus (d’où le nom curseur). Un appel subséquent de l’une des méthodes fetchall(), fetchmany() ou fetchone() lira donc les enregistrements à partir de la dernière position du curseur. Lorsque le curseur atteint la fin de la table, ces méthodes retournent la valeur None.

 

 

SUPPRIMER UNE TABLE

 

Pour terminer cette petite interaction avec la base de données, supprimons la table que nous venons de créer. Dans le jargon des bases de données, on dit qu’on « dépose la table » (drop en anglais). La commande SQL correspondante est :

DROP TABLE reservation

Après cette opération, la base de données casino ne comporte plus aucune table définie par l’utilisateur.

from dbapi import *

serverURL = "derbyserver:localhost"
#serverURL = "derbyserver:10.1.1.123"
dbname = "casino"
username = "admin"
password = "solar"
tbl = "reservation"

with connect(serverURL, dbname, username, password) as con:
    cursor = con.cursor()
    sql = "DROP TABLE " + tbl
    cursor.execute(sql)
    con.commit()
print "Table removed"
Sélectionner le code (Ctrl+C pour copier, Ctrl+V pour coller )

 

 

MEMENTO

 

a base de données nommée pythondb continue d’exister même après la suppression de la table. De plus, le sous dossiers pythondb du dossier dans lequel vous avez installé le serveur Derby (en l’occurrence Lib/pythondb) contient encore de nombreux fichiers. Pour effacer toute trace de cet exercice, on peut simplement supprimer le dossier pythondb.

 

 

EXERCICES

 

1.


Pour réserver le siège 6 pour le client 33, il faut ajouter une instruction UPDATE au programme ci-dessus avant l’appel à showAll().

sql = "UPDATE " + tbl + " SET booked='Y', cust=33 WHERE seat=6"

Réserver également le siège numéro 5 pour le même client. De plus, réservez les sièges 10, 11 et 12 pour le client numéro 34. Examiner la réussite en affichant le contenu de la table.

from dbapi import *

username = "admin"
password = "solar"
dbname = "casino"
serverURL = "localhost"
table = "res_20140915"

def showAll():
    SQL = "SELECT * FROM " + table 
    cursor.execute(SQL)
    con.commit()
    result = cursor.fetchall()
    for record in result:
        print "seatNb:", record[0]," booked:",record[1],"customer:",record[2]
        
con = getDerbyConnection(serverURL, dbname, username, password)
cursor = con.cursor()
showAll()
cursor.close()
con.close()

2.

Il est possible d’afficher l’ensemble des sièges réservés à l’aide de l’instruction

sql = "SELECT * FROM " + tbl + " WHERE booked='Y'"        
          
a. Ecrire un program de telle manière que seuls les sièges réservés soient affichés
b. Modifier la requête pour n’afficher que les sièges réservés par le client numéro 34.

3.

Le client 34 a annulé toutes ses réservations. Implémenter la requête de mise à jour correspondante.

4.

Suite à une rénovation de la salle de concert, les sièges 24 – 30 ne sont plus disponibles. Supprimer ces enregistrements de la table.

Pour ce faire, utiliser une instruction DELETE selon le schéma suivant

sql = "DELETE * FROM " + tbl + " WHERE ..."