Projets autour des BDD

Application : Meurtre à SQL city

Maintenant que vous connaissez toutes les bases de SQL et du modèle relationnel, nous allons pouvoir les mettre en œuvre sur une une base un peu plus complexe (jusque là nos bases ne comportaient que trois relations).

Les consignes

Vous êtes un enquêteur privé qui a réussi à récupérer la base de données de la police. Vous allez devoir l'interroger pour résoudre une affaire rapidement.
La seule information dont vous disposez est que le meurtre a eu lieu le 15 janvier 2018 dans la ville de SQL City.
Il semblerait également que le club de sport "Get Fit Now!" apparaisse dans cette enquête...

Attention vos requêtes devront être complètes, impossible pour vous d'utiliser un carnet pour noter les résultats d'une requête, l'utilisation de la commande JOIN est obligatoire !

Présentation de la base de données

voici un schéma complet de la base de donnée dont vous disposez.

Ce projet a été mis au point par la northwestern university (Chicago - San Francisco)

Vous pouvez télécharger la base de données sql-murder-mystery.db ici.
Pour information, quand vous ne connaissez pas le schéma de la base de données dont vous disposez, vous pouvez l'obtenir grâce à la commande :


SELECT `name`, `sql`
FROM `sqlite_master`
WHERE type='table';

Vous devez rendre un rapport d'enquête au format .odt regroupant :

les requêtes qui vous ont permis d'aboutir à la conclusion
le résultat de ces requêtes
une phrase de conclusion écrite (très courte) pour chaque requête

interrogation sur un modèle avec LIKE

Pour résoudre cette enquête vous aurez besoin de faire des recherches grâce à la commande SELECT...FROM...WHERE..., mais les témoins ne sont pas toujours précis, ils ne voient qu'une partie d'une plaque d'immatriculation, d'un nom sur une carte...Ainsi, la condition présente après WHERE


SELECT * FROM Lesfilms WHERE titre='cube';

n'est pas forcément "complète". Si l'on cherche tous les films dont le nom contient "ub" on pourra écrire :


SELECT * FROM Lesfilms WHERE titre LIKE'%ub%';

le signe % représentant ici n'importe quel groupe de caractère.
On peut ainsi chercher les films finissant par "ube" :


SELECT * FROM Lesfilms WHERE titre LIKE'%ube';

Vérifier la solution

Pour vérifier votre solution, il suffira d'insérer une nouvelle donnée dans la relation solution grâce à la commande

	
INSERT INTO solution VALUES (1, 'Insérer ici le nom de votre suspect');

Un déclencheur (notion hors programme) vous permettra de vérifier si votre solution est correcte grâce à la commande :

	
SELECT value FROM solution;

Voila , vous savez tout maintenant ! à vos claviers !

Projet : Base de donnée et Python

Comme vous avez pu le constater le langage SQL nécessite quelques connaissances et on ne peut pas demander à n'importe quel utilisateur de base de données de maîtriser ce langage.

Vous allez donc créer une interface ( avec le module Tkinter ) qui permettra de consulter et de modifier une base de données.

Les bases de données à exploiter

Une fois que vos groupes et que la base de données sur laquelle vous allez travailler vous auront été assignés, vous pourrez récupérer le fichier .db correspondant :

Une des premières choses à faire sera de connaître le schéma de cette base de données...

Ressources

Interaction Python/BDD

Vous utiliserez le module SQLite 3, intégré de base dans la distribution standard de Python.

Il permet de gérer une base de données en local, stockée dans un unique fichier d'extension .db.

Se connecter à la BDD

On importe le module en début de script :


import sqlite3

Ensuite, il faut établir une connexion avec la base de données en créant un premier objet à l'aide de la méthode connect :


connexion = sqlite3.connect("base_de_donnees.db")

Pour exécuter des requêtes SQL, nous allons utiliser un objet appelé curseur ( = cursor ).
Cet objet sera instancié en faisant appel à la méthode cursor appelée à partir de de notre objet connexion:


curseur = connexion.cursor()

Exécuter des requêtes

Pour exécuter une requête il suffit ensuite d'utiliser la méthode execute appelé depuis le curseur :


curseur.execute("SELECT * FROM LesFilms WHERE anSortie=2012 and vo='fr';")

Mais dans ce projet, les valeurs des attributs ne seront pas codés "en dur" dans les requêtes, puisqu'il faudra qu'ils puissent s'adapter à ceux qu'un utilisateur sélectionnera.

Pour cela, comme la requête est une chaîne de caractères, on pourrait simplement concaténer à sa suite les variables contenant les valeur des attributs à utiliser :


annee_de_sortie = 2012
langue_du_film = "fr"
curseur.execute("SELECT * FROM LesFilms WHERE anSortie=" + str(annee_de_sortie) + " and vo=" + langue_du_film + ";")

Mais c'est une très mauvaise pratique, car dans un véritable environnement de production ( sites web notamment ), c'est la porte ouverte aux injections SQL, qui permettent aux pirates de s'introduire dans les BDD des sites.

Même si dans le cadre de ce projet, ce problème ne se pose pas, autant faire les choses bien, en utilisant plutôt les requêtes préparées :

dans la requête, on substitue les valeurs des attributs par le caractère générique ? :
```
SELECT * FROM LesFilms WHERE anSortie = ? and vo = ?;
		    
```
lors de l'exécution de la requête, les valeurs réelles des attributs seront alors passées à la méthode execute sous forme de tuple, dans l'ordre de leur apparition dans la requête :
```
curseur.execute("SELECT * FROM LesFilms WHERE anSortie = ? and vo = ?;", (annee_de_sortie, langue_du_film))
		    
```

Récupérer les données issues de la requête

Comme le résultat de la requête est une relation, nous devons utiliser la méthode fetchall de l'objet cursor, qui renvoie un tableau des résultats de la requête :


resultats = curseur.fetchall()

On peut alors manipuler ce tableau pour en extraire les informations requises.

Se déconnecter de la BDD

Au cas où la base a été modifiée, on utilisera la méthode commit pour enregistrer effectivement les changements dans la BDD :


connexion.commit()

Enfin, une fois la base utilisée, il sera nécessaire de se déconnecter avec :


connexion.close()

Entraînez-vous à utiliser ce module pour bien comprendre son fonctionnement ( installer auparavant le module dans l'éditeur ci-dessous, et charger la base datafilms.db ).

N'hésitez pas au besoin à consulter la documentation de ce module.

import sqlite3 # création de la connexion connexion = sqlite3.connect("datafilms.db") curseur = connexion.cursor() # requête préparée annee_de_sortie = 2012 langue_du_film = "fr" curseur.execute("SELECT * FROM LesFilms WHERE anSortie = ? and vo = ?;", (annee_de_sortie, langue_du_film)) # récupération et affichage des résultats resultats = curseur.fetchall() print(resultats) # validation des requêtes et déconnexion connexion.commit() connexion.close()

Interfaces graphiques avec Tkinter

La référence principale est la page du cours de Première, qui vous donnera également des liens vers des informations plus poussées.

Cahier des charges

L'interface devra permettre à l'utilisateur de réaliser les opérations suivantes :

faire une requête multi-critères dans la base de données
afficher les résultats de ces requêtes
insérer une nouvelle entrée dans la BDD
modifier une entrée
supprimer une entrée

Parmi les requêtes possibles, il faudra qu'il y en ait au moins une qui fasse appel à une jointure.

Étude préliminaire ( sur papier )

Vous devrez rendre un document qui présente :

le schéma relationnel ( sous forme "graphique" : cf exemples dans le cours ) de la base de données utilisée
vos pistes de réflexion sur l'aspect de l'interface graphique
les différentes requêtes que vous pouvez proposer à l'utilisateur
la répartition du travail

Le programme

Il devra être le plus modulaire possible; il faudra ( au moins ) qu'il sépare l'aspect visuel ( l'interface ) et la logique de fonctionnement ( récupération des résultats, requêtes,...)

Le code devra bien entendu être documenté.

Améliorations et prolongements

Une fois votre programme fonctionnel, vous pourrez envisager de l'améliorer. En effet, l'utilisateur a le droit à l'erreur (lors de la saisie). On peut imaginer qu'il tape Tim Burtin à la place de Tim Burton. Vous pourrez utiliser la commande SQL LIKE (abordée dans l’enquête précédente) et générer une requête du type :


SELECT *
FROM LesRealisateurs
WHERE realisateur like "%im Burtin" or realisateur like "T%m burtin" or ....  realisateur like "Tim burt%n";

Ou la commande SOUNDEX qui permet de comparer la sonorité de deux chaînes de caractères différentes :

				
SELECT *
FROM LesRealisateurs
WHERE soundex("Tim Burton")=soundex(realisateur);