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’écrire 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 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 requê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 interagit 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.

En Python, on peut utiliser le module sqlite3 – Référence

import sqlite3

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

c = sqlite3.connect("example.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:")

Dans notre cas, nous reprenons le fichier films.db avec mes films et acteurs. Placer ce fichier dans le même répertoire que le script python puis :

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

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")

L'exécution des requêtes demande l'utilisation d'un curseur. C'est un objet dont le rôle est justement 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.

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 serait naturel de faire cela, mais cela pose un gros problème de sécurité appelé injection SQL sur lequel on reviendra plus loin.

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.

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

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

a_inserer = [
    ('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 a_inserer:
    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(?, ?, ?, ?);", a_inserer)

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()

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

c.commit()

Et pour refermer la connexion :

c.close()

def suppression_artiste(idArtiste):
    requete = "DELETE FROM ARTISTE WHERE idArtiste = {}".format(idArtiste)
    cursor.execute(requete)