GoupillWiki

Outils pour utilisateurs

Outils du site

Piste : • sql_et_python
nsi:terminales:sql_et_python

Ceci est une ancienne révision du document !


Warning: Undefined array key "pos" in /home/goupillf/wiki.goupill.fr/lib/plugins/mdpage/src/DokuWiki/Plugin/Mdpage/MarkdownRendererTrait.php on line 100

Warning: Undefined array key "pos" in /home/goupillf/wiki.goupill.fr/lib/plugins/mdpage/src/DokuWiki/Plugin/Mdpage/MarkdownRendererTrait.php on line 100

Warning: Undefined array key "pos" in /home/goupillf/wiki.goupill.fr/lib/plugins/mdpage/src/DokuWiki/Plugin/Mdpage/MarkdownRendererTrait.php on line 100

Warning: Undefined array key "pos" in /home/goupillf/wiki.goupill.fr/lib/plugins/mdpage/src/DokuWiki/Plugin/Mdpage/MarkdownRendererTrait.php on line 100

Warning: Undefined array key "pos" in /home/goupillf/wiki.goupill.fr/lib/plugins/mdpage/src/DokuWiki/Plugin/Mdpage/MarkdownRendererTrait.php on line 100

Warning: Undefined array key "pos" in /home/goupillf/wiki.goupill.fr/lib/plugins/mdpage/src/DokuWiki/Plugin/Mdpage/MarkdownRendererTrait.php on line 100

Warning: Undefined array key "pos" in /home/goupillf/wiki.goupill.fr/lib/plugins/mdpage/src/DokuWiki/Plugin/Mdpage/MarkdownRendererTrait.php on line 100

Warning: Undefined array key "pos" in /home/goupillf/wiki.goupill.fr/lib/plugins/mdpage/src/DokuWiki/Plugin/Mdpage/MarkdownRendererTrait.php on line 100

Warning: Undefined array key "pos" in /home/goupillf/wiki.goupill.fr/lib/plugins/mdpage/src/DokuWiki/Plugin/Mdpage/MarkdownRendererTrait.php on line 100

Warning: Undefined array key "pos" in /home/goupillf/wiki.goupill.fr/lib/plugins/mdpage/src/DokuWiki/Plugin/Mdpage/MarkdownRendererTrait.php on line 100

Warning: Undefined array key "pos" in /home/goupillf/wiki.goupill.fr/lib/plugins/mdpage/src/DokuWiki/Plugin/Mdpage/MarkdownRendererTrait.php on line 100

Warning: Undefined array key "pos" in /home/goupillf/wiki.goupill.fr/lib/plugins/mdpage/src/DokuWiki/Plugin/Mdpage/MarkdownRendererTrait.php on line 100

Table des matières

SQL et Python

Requêtes via un langage de programmation

Nous avons étudié le langage SQL qui permet de gérer une base de données par l'intermédiaire de requêtes : CREATE TABLE, INSERT, UPDATE, SELECT, etc.

Nous avons utilisé un logiciel de gestion nous permettant d'ecrire directement les requêtes en langage SQL et de visualiser le contenu de la base un peu comme si nous utilisions un tableur.

Mais ceci n'est pas l'utilisation habituelle d'une base de données.

Si nous reprenons l'exemple de la base de données de films, l'utilisation habituelle serait plutôt celle ci :

  • Une fenêtre - par exemple une fenêtre web - met à disposition des boutons permettant à un utilisateur de consulter le contenu de la base ou d'en modifier le contenu.

  • Si l'utilisateur souhaite ajouter un acteur, on lui propose un bouton "Ajouter un acteur" qui l'amène à une page adaptée. Sur cette nouvelle page, l'utilisateur trouve des zones de saisie dont la signification est clairement donnée : nom, prénom, date de naissance, biographie...

    Cette page peut simplifier la saisie par exemple en proposant un bouton calendrier comme on envoie dans la plupart des sites internet où on doit entrer une date.

    Un bouton valider permet de conclure l'ajout.

  • Si l'utilisateur souhaite faire une recherche de film, on lui propose un champ de recherche dans lequel il écrit le nom du film.

Bref, à aucun moment l'utilisateur n'a à entrer une requête SQL.

Le logiciel (par exemple le site internet) se charge de produire les requêtes SQL en fonction des actions de l'utilisateur. Le logiciel doit donc pouvoir dialoguer directement avec le SGBD base de donnée et lui transmettre des requêtes SQL. Il doit aussi pouvoir recevoir les réponses du SGBD.

  • Sur internet, un usage courant est de passer par le langage PHP. Les reqêtes http venant des clients et arrivant sur le serveur sont pris en charge par un script PHP, exécuté sur le serveur, et PHP intéragit avec un SGBD.

    Dans ce cas, le SGBD est souvent MySQL, MariaDB ou PostgreSQL

  • Un autre usage qui se développe est de faire exécuter un script en Javascript sur le serveur (au lieu de PHP). Dans ce cas, le SGBD MongoDB est adapté car il est fait pour fonctionner avec Javascript.

  • Nous allons étudier le cas de Python.

sqlite3

En Python, on peut utiliser le module sqlite3 

import sqlite3

Connexion à la base de données

On peut ouvrir ou créer un nouveau fichier base de données : 

c = sqlite3.connect('films.db')

Ici, la variable c est un objet qui consiste en la connexion au fichier base de données dont le nom est 'example.db'. Si le fichier n'existe pas, la base est créée.

On peut souhaiter travailler sur une base de données temporaire que l'on n'enregistre pas sur le disque dur. Dans ce cas on cette possibilité : 

c = sqlite3.connect(':memory:')

Activation des clés étrangères

La vérification de la cohérence des clé étrangères peut être une lourde charge pour le système. On est donc libre de l'activer ou pas. Si on ne l'active pas, bien sûr, il faudra être plus vigilant pour éviter les incohérences.

Nous souhaitons l'activer, nous ajoutons alors la commande : 

c.execute("PRAGMA foreign_keys = 1")

Vous pouvez voir que execute est une méthode de l'objet de connexion c.

Exécution de requêtes SQL

L'exécution des requêtes demande l'utilisation d'un curseur. C'est un objet dont le rôle est précisemment d'exécuter les requêtes. 

cursor = c.cursor()

Le curseur a lui aussi une méthode execute, c'est par elle qu'on lance les requêtes. Exemple : 

cursor.execute("CREATE TABLE ARTISTE (idArtiste INTEGER PRIMARY KEY, nom VARCHAR(20), prénom VARCHAR(20), biographie TEXT, naissance DATE);")

Écrit tout à la suite, ce n'est pas très lisible. On préfère généralement utiliser plusieurs lignes : 

cursor.execute("""CREATE TABLE ARTISTE (
                    idArtiste INTEGER PRIMARY KEY,
                    nom VARCHAR(20),
                    prénom VARCHAR(20),
                    biographie TEXT,
                    naissance DATE
                 );""")

Les espaces sur la partie gauche n'ont pas d'importance. Ils sont là pour aider la lecture.

Remarques :

  • Le ; final de la requête sert dans le cas où on souhaite faire plusieurs requêtes. Si on ne fait qu'une seule requête, le ; est inutile.
  • Avec sqlite3 on ne peut pas faire en une fois plusieurs requête. Si on a 3 requêtes à faire, il faudra appeler 3 fois la méthodes cursor.execute.

Exemple d'une insertion

Une insertion de base ne pose pas de problème : 

cursor.execute("INSERT INTO ARTISTE(prénom, nom, biographie, naissance) VALUES('Bruce', 'Willis', 'blabla.', '1955-03-19');")

Mais cette situation n'est pas réaliste. En général on sera plutôt dans un cas où nous avons obtenu les informations par ailleurs, depuis une interface web par exemple. 

# au moment de faire l'insertion de l'artiste, on nous a fourni
# ces variables avec leur valeur
prénom = 'Bruce'
nom = 'Willis'
biographie = 'blabla.'
naissance = '1955-03-19'

On sait que la requête voulue est : 

INSERT INTO ARTISTE(prénom, nom, biographie, naissance) VALUES('Bruce', 'Willis', 'blabla.', '1955-03-19');

On se dit que l'on pourrait en Python fabriquer le texte de la requête de cette façon : 

texte_requete = "INSERT INTO ARTISTE (prénom, nom, biographie, naissance) VALUES ('{}', '{}', '{}', '{}');".format(prénom, nom, biographie, naissance)
 
# puis exécution
cursor.execute(texte_requete)

Il ne faut surtout pas faire cela !!!!!

C'est logique mais cela pose un gros problème de sécurité appelé injection SQL sur lequel on reviendra plus loin.

Bonne méthode 

data = (prénom, nom, biographie, naissance)
cursor.execute("INSERT INTO ARTISTE(prénom, nom, biographie, naissance) VALUES(?, ?, ?, ?);", data)
  1. Création du tupple contenant les informations à insérer, dans le bon ordre
  2. Lancement de la requête en prévoyant les trous à compléter sous forme de ?

Cette méthode ressemble à l'utilisation de format mais il y a une différence : elle faite pour éviter l'injection SQL.

id de l'insertion

Quand on insère un nouvel item, on ne précise pas son identifiant, on laisse le SGBD le choisir automatiquement. Mais on peut avoir besoin de l'identifiant de ce que l'on vient d'insérer...

On peut récupérer cet id directement après l'insertion : 

id_du_dernier_inséré = cursor.lastrowid

Exemple d'un insertion multiple

On pourrait décider d'insérer plusieurs artistes en même temps. 

à_insérer = [
    ('Bruce', 'Willis', 'blabla', '1955-03-19'),
    ('Rickman','Alan','blabla...','1946-02-21'),
    ('Bonnie','Bedelia','blabla','1946-03-25'),
    ('Gilliam','Terry','blablabla.','1940-11-22')
]

Comment les insérer ? On pourrait passer par une boucle for 

for item in à_insérer:
    cursor.execute("INSERT INTO ARTISTE(prénom, nom, biographie, naissance) VALUES(?, ?, ?, ?);", item)

Mais on dispose d'une fonction plus adaptée : 

cursor.executemany("INSERT INTO ARTISTE(prénom, nom, biographie, naissance) VALUES(?, ?, ?, ?);", à_insérer)

Exemple d'une lecture

Le plus souvent, on consulte le contenu de la base dans la modifier. On utilise pour cela une requête SELECT. 

cursor.execute("SELECT * FROM ARTISTE;")

La requête est alors exécutée mais on n'obtient rien.

Les résultats de la sélection sont contenus dans cursor : 

premier = cursor.fetchone() # fetch = chercher. On récupère le premier
suivant = cursor.fetchone() # récupère le suivant
suivant = cursor.fetchone() # encore le suivant
# quand on a tout récupéré, fetchone() renvoie None

On peut aussi récupérer les résultats tous d'un coup : 

resultats = cursor.fetchall()

Attention : un résultat aura la forme (1, 'Bruce', 'Willis', 'blabla.', '1955-03-19') . Il n'est pas précisé à quoi correspondent les différents champs. Mais comme nous connaissons la table, nous savons que le premier champ est idArtiste, le second est prénom, etc.

Fin des modifications

Pour que les modifications soit pris en compte dans la base de données, on utilise 

c.commit()

Et pour refermer la connexion : 

c.close()

Attention, la fermeture de la connexion de met pas à jour le fichier base de données et il faut donc penser à faire un commit avant la fermeture.

Injection SQL

Imaginons que l'utilisateur ait le droit de supprimer certains artistes, pas tous, par exemple ceux qu'il a lui même créé.

Par exemple, il demande la suppression de l'artiste idArtiste = 1, c'est à dire dans notre cas, Bruce Willis.

Le système doit donc fabriquer la requête avec le paramètre idArtiste = 1. Supposons que l'on fasse ceci : 

# on sait que idArtiste = 1
requete = "DELETE FROM ARTISTE WHERE idArtiste = {}".format(idArtiste)
# on a donc :
# requete = "DELETE FROM ARTISTE WHERE idArtiste = 1"
cursor.execute(requete)
# tout se passe bien

Maintenant imaginons qu'un hacker s'arrange pour transmettre cette requête : 

idArtiste = '1 OR 1 = 1'

Le hacker est maître du client sur lequel il exécute sa requête, il peut donc envoyer la requête qu'il veut. Que se passe-t-il si on fait la même chose qu'avant ? 

# on sait que idArtiste = '1 OR 1 = 1'
requete = "DELETE FROM ARTISTE WHERE idArtiste = {}".format(idArtiste)
# on a donc :
# requete = "DELETE FROM ARTISTE WHERE idArtiste = 1 OR 1 = 1"
cursor.execute(requete)
# aie aie aie !

1 = 1 est toujours vrai, donc la condition du WHERE sera toujours vraie et on va donc supprimer tous les artistes de la base !!!

Nous devons donc empêcher que de telles situations se produisent.

Les langages proposent des fonctions, comme le execute de sqlite avec le ?, qui se chargent de filtrer tous les cas à problème. C'est pourquoi on ne doit surtout pas essayer de compléter la requête nous-même, on doit utiliser les fonctions fournies.

Travail

Voilà, vous savez tout. Voilà votre travail.

Créer une interface simple qui utilisera la base de données films.db. Vous trouverez un début de fichier Python dans interface.py, pour vous donner des idées (vous êtes libre de faire complètement autrement).

Cette interface se comportera comme suit :

  • Au démarrage, un menu nous donne le choix de

    1. Ajouter une données
    2. Afficher les données

    L'utilisateur est invité à sélectionner 1 ou 2

  • Quand l'utilisateur choisit 1, il a un nouveau choix à faire :

    1. Ajouter un artiste
    2. Ajouter un film
    3. Ajouter un rôle d'un artiste dans un film
    • Dans le cas 1) on demande à l'utilisateur à la suite prénom, nom, biographie et naissance.
    • Dans le cas 2), on demande à l'utilisateur le titreVF, titreVO et l'id du réalisateur
    • Dans le cas 3), on demande le rôle, l'id du film et l'id de l'artiste

    À chaque fois, un message indique si l'insertion a bien eu lieu et on retourne au menu de départ.

  • Quand l'utilisateur choisit 2, il a un nouveau choix :

    1. Liste des artistes
    2. Liste des films

    S'il choisit 1) il voit la liste des noms d'artistes avec leur id. Il est invité à sélectionner un numéro d'artiste. Quand il l'a fait on voit le nom, prénom de l'artiste ainsi que sa date de naissance, sa biographie et la liste des films dans lesquels il a joué et ceux qu'il a réalisé.

    S'il choisit 2) il voit les infos sur le film avec la liste des noms d'acteurs avec leur identifiant, leur rôle dans le film, et le réalisateur avec son identifiant.

    Dans les deux cas, on propose à l'utilisateur de choisir un nombre. Si c'est un nombre correspondant à un des identifiants afficher, il affiche le contenu correspondant, sinon retour au menu initial.

Cela fait beaucoup de choses. Allez progressivement. Si vous n'arrivez pas à tout faire, ce n'est pas grave.

Vous pouvez commencer par :

  1. Permettre l'ajout d'artistes
  2. Permettre l'affichage de la liste d'artistes.
nsi/terminales/sql_et_python.1618320488.txt.gz · Dernière modification : de goupillwiki