GoupillWiki

Cette page est en lecture seule. Vous pouvez afficher le texte source, mais ne pourrez pas le modifier. Contactez votre administrateur si vous pensez qu'il s'agit d'une erreur.
====== Projet SQL et Python ======

On souhaite développer une application de gestion d'une base de données.

Vous êtes libre du contenu de la BDD. Par exemple :
  * élèves d'un lycée,
  * site marchand,
  * déroulement du championnat de ligue 1 de football,
  * banque de données sur des jeux vidéos,
  * etc.

===== Votre travail =====

  - Vous devez définir votre base de données,
    * en donnant le diagramme entité - association
    * en donnant les requêtes SQL permettant de créer la base vide.
  - Vous devez créer une interface simple permettant de manipuler le contenu de la BDD :
    * ajouter des items,
    * consulter le contenu
    * naviguer d'un item à l'autre

<WRAP important>Votre application doit pouvoir être utilisée par un non informaticien : L'utilisateur n'a pas à produire lui-même des requêtes SQL.</WRAP>

===== Exemple =====

Reprenons la base de données sur les films que nous avons déjà rencontrée. Soit le diagramme suivant :

{{ :nsi:terminales:sql-1.png?direct&400 |}}

On sait déjà -- voir [[nsi:terminales:sql_requests|ce cours]] -- qu'une telle base peut être crée par les requêtes

<code sql linenums>
CREATE TABLE ARTISTE (
    idArtiste INTEGER PRIMARY KEY,
    nom VARCHAR(20),
    prénom VARCHAR(20),
    biographie TEXT,
    naissance DATE
);

CREATE TABLE FILM (
    idFilm INTEGER PRIMARY KEY,
    titreVO VARCHAR(40),
    titreVF VARCHAR(40),
    année INTEGER,
    idReal INTEGER NOT NULL,
    FOREIGN KEY(idReal) REFERENCES ARTISTE(idArtiste)
);

CREATE TABLE JOUEDANS (
    idArtiste INTEGER NOT NULL,
    idFilm INTEGER NOT NULL,
    rôle VARCHAR(20),
    PRIMARY KEY (idArtiste, idFilm),
	FOREIGN KEY(idArtiste) REFERENCES ARTISTE(idArtiste),
    FOREIGN KEY(idFilm) REFERENCES FILM(idFilm)
);
</code>

<WRAP tip>Vous êtes libre d'utiliser un logiciel comme SQLiteBrowser pour créer la vase vide.</WRAP>

Supposons que nous avons créé la base {{ :nsi:terminales:database:films.db |}}. Elle est vide à la première utilisation mais au gré des utilisations, elle va se remplir.

Votre application, en Python, propose une interface utilisateur permettant la manipulation de la BDD.

<WRAP tip>Je présente un exemple très simple en mode texte. Vous êtes libres de prévoir plus compliqué, par exemple avec [[nsi:modules:tkinter|tkinter]]. Faites attention : Une interface graphique n'est pas difficile à faire mais cela prend beaucoup de temps car chaque type de vue doit être réalisée spécifiquement. Par exemple ici, il faudrait créer un formulaire spécial pour l'ajout d'artiste, un formulaire pour l'ajout de film, un formulaire pour l'ajout de rôle, etc. C'est un travail répétitif et long.
</WRAP>

Je propose une interface en mode texte.

=== Écran d'accueil ===

Lors du démarrage de l'application, on affiche :

<code lang-none>
1. Ajouter un artiste
2. Ajouter un film
3. Voir les artistes
4. Voir les films
5. Quitter
Entrez une réponse.
</code>

=== Ajout d'artiste ===

Si on sélectionne "1. Ajouter un artiste" on obtient la série de questions :

<code lang-none>
Entrez un nom :

Entrez un prénom :

Entrez une biographie :

Entrée une date de naissance :
</code>

<WRAP tip>Si vous souhaitez que la biographie contienne des retours lignes, il faudra réfléchir à une interface adéquat. Il faudra aussi tenir compte du format particulier des dates.</WRAP>

=== Ajout de film ===

Si on sélectionne "2. Ajouter un film" on obtient la série de questions :

<code lang-none>
Entrez un titre VO :

Entrez un titre VF :

Entrez une année :
</code>

Puis il faudrait prévoir une liste des artistes afin de pouvoir en sélectionner un comme réalisateur. Cette interface pourrait ressembler à ce qui va suivre pour l'affichage des artistes.

=== Affichage des artistes ===

<code lang-none>
1. Brice Willis
2. John McTiernan
3. Alan Rickman
4. Bonnie Bedelia
5. Terry Gilliam
6. Madeleine Stowe
7. Christopher Plummer
8. Brad Pitt
9. Quentin Tarantino
P. Précédent
S. Suivant
R. Abandon - Retour menu

Entrez votre choix :
</code>

Le menu est assez clair pour se passer de commentaires. Ce menu pourrait servir à sélectionner un artiste lors de la création d'un film et il sert bien-sûr à atteindre la fiche de chaque artiste.

=== Affichage artiste ===

Si on a demander à voir un artiste, on obtient une fiche comme :

<code lang-none>
Bruce Willis - Né le 19/03/1955
------------
Né sur une base américaine à Idar-Oberstein en Allemagne de l'Ouest où son père, un soldat américain, était affecté, Bruce Willis passe le reste de son enfance dans le New Jersey. Au Collège d'Etat de Montclair, il s'adonne à la musique, joue de l'harmonica et suit les cours de la section théâtrale.

Rôles
-----
1. John Mc Lain dans Piège de Cristal
2. James Cole dans L'armée des 12 singes
8. Butch Coolidge dans Pulp Fiction

Action
------
Entrez un nombre pour voir le film
A          pour ajouter un rôle
R          pour retour menu principal
SupRole    pour supprimer un rôle
SupArtiste pour supprimer l'artiste
</code>

=== Etc. ===

Je vous laisse imaginer les autres menus utiles.

Comme déjà dit c'est un travail répétitif et il faut bien vous organiser pour ne pas perdre du temps à réécrire 2x le même code.

===== Aide pour organiser votre code =====

Vous avez intérêt à bien séparer la partie SQL de l'interface graphique.

==== Exemple de ce qu'il ne faut pas faire ====

Supposons que je travaille sur la base des films et que je veux gérer l'ajout d'un artiste. Je peux être tenté d'écrire :

<code python>
import sqlite3

# variables globales
c = sqlite3.connect("films.db")
c.execute("PRAGMA foreign_keys = 1")

def ajout_artiste():
    prenom = input("Donnez un prénom :")
    nom = input("Donnez un nom :")
    naissance = input("Donnez une date de naissance :")
    bio = input("Donnez une biographie :")
    cursor = c.cursor()
    data = (prenom, nom, biographie, naissance)
    cursor.execute("INSERT INTO ARTISTE(prénom, nom, biographie, naissance) VALUES(?, ?, ?, ?);", data)
</code>

Ce code mélange l'interface graphique avec le code SQL. Ce n'est pas une bonne chose.

==== Ce qu'il vaut mieux faire ====

Il est beaucoup mieux de placer le SQL dans un module à part. On pourrait faire ceci :

<code python>
# fichier sql.py, compris par Python comme le module sql
import sqlite3

# variables globales
c = sqlite3.connect("films.db")
c.execute("PRAGMA foreign_keys = 1")

def ajout_artiste(prenom, nom, naissance, bio):
    cursor = c.cursor()
    data = (prenom, nom, biographie, naissance)
    cursor.execute("INSERT INTO ARTISTE(prénom, nom, biographie, naissance) VALUES(?, ?, ?, ?);", data)
</code>

<code python>
# module principal
import sql

def ajout_artiste():
    prenom = input("Donnez un prénom :")
    nom = input("Donnez un nom :")
    naissance = input("Donnez une date de naissance :")
    bio = input("Donnez une biographie :")
    sql.ajout_artiste(prenom, nom, naissance, bio)
</code> 

Remarquez qu'on utilise la notation avec le point pour signifier que ''ajout_artiste'' est une fonction de module ''sql''. C'est bienvenue car cela nous permet de mieux trier notre code et de bien voir où sont les choses.

Vous avez intérêt, dès que votre application prend du volume, à en distribuer les parties en autant de modules que possible afin de maintenir l'ensemble mieux organiser.

==== Encore mieux avec une classe ====

C'est encore mieux si on emballe les fonctions sql dans une classe faite exprès.

<code python>
# fichier sql.py, compris par Python comme le module sql
import sqlite3

class SQL:
    def __init__(self):
        self.c = sqlite3.connect("films.db")
        self.c.execute("PRAGMA foreign_keys = 1")

    def ajout_artiste(self, prenom, nom, naissance, bio):
        cursor = c.cursor()
        data = (prenom, nom, biographie, naissance)
        cursor.execute("INSERT INTO ARTISTE(prénom, nom, biographie, naissance) VALUES(?, ?, ?, ?);", data)
</code>

<code python>
# module principal
from sql import SQL

# au début on pense à créer :
db = SQL() # crée l'objet et exécute le __init__

def ajout_artiste():
    prenom = input("Donnez un prénom :")
    nom = input("Donnez un nom :")
    naissance = input("Donnez une date de naissance :")
    bio = input("Donnez une biographie :")
    db.ajout_artiste(prenom, nom, naissance, bio)
</code>

==== Enrichir la classe ====

La classe contient toutes les requêtes utiles. Supposez que vous ayez besoin d'une requête pour obtenir uniquement une liste de noms et prénoms d'artistes, alors vous écrivez une fonction qui fait cela. Si vous avez besoin d'une requête listant les films pour un certain //idReal//, vous faites une fonction pour...

À la fin, il n'y a pas une seule requête SQL dans le fichier principal.

Exemple de classe :

<code python>
# module sql

import sqlite3

class SQL:
    def __init__(self):
        self.c = sqlite3.connect("films.db")
        self.c.execute("PRAGMA foreign_keys = 1")
        self.__is_closed = False

    def __init_db(self):
        # crée les tables en cas de première utilisation
        # suffit d'utiliser le mot clé IF NOT EXISTS
        # de sorte que si elles existent déjà, rien n'est fait
        cursor = self.c
        cursor.execute("CREATE TABLE IF NOT EXISTS ARTISTE (idArtiste INTEGER PRIMARY KEY, nom VARCHAR(20), prénom VARCHAR(20), biographie TEXT, naissance DATE);")
        
        # on peut ainsi créer les autres
        
    def close(self):
        # À appeler à la fermeture pour appliquer les modifs et libérer le fichier
        self.c.commit()
        self.c.close()
        self.__is_closed = True

    def ajout_artiste(self, prenom:str, nom:str, naissance:str, bio:str):
        assert not self.__is_closed, "La base est fermée..."
        cursor = c.cursor()
        data = (prenom, nom, biographie, naissance)
        cursor.execute("INSERT INTO ARTISTE(prénom, nom, biographie, naissance) VALUES(?, ?, ?, ?);", data)

    def get_films_from_real(self, id_real:int) -> list:
        # liste des films pour un réal donné
        assert not self.__is_closed, "La base est fermée..."
        cursor = c.cursor()
        cursor.execute("SELECT * FROM FILMS WHERE idReal = ?", (id_real,))
        return cursor.fetchall()
</code>

==== Pousser plus loin ====

Dès que l'application prend du volume, on se retrouve avec un mélange compliqué de code, d'input, de chaînes de textes... Comme on a séparé les requêtes SQL du reste, on va avoir intérêt à mettre tout ce qui est texte de côté.

C'est plus compliqué alors pour cela j'ouvre une [[.:sql_controleur|nouvelle page]].