Bonjour ! Comme d'habitude :

  • Shift-Entrée pour exécuter une cellule.
  • Menu Noyau > Rdémarrer en cas de problème.

Base de données : schéma relationnel.

D'après un TP du livre "Bases de données : de la modélisation au SQL" de Laurent Audibert et d’un TP de Franz Ridde.

On utilise une base de données de schéma relationnel :

  • $personne(\underline{idp:integer}, nom:text, prenom:text)$
  • $jouer(ida:integer, idf:integer, role:texte)$
  • $film(\underline{idf:integer}, idr:integer, titre:text, genre:text, annee:integer)$
  • $projection(idc:integer, idf:integer, jour:text)$
  • $cinema(\underline{idc:integer}, nom:text, adresse:text)$

C'est-à-dire qu'on dispose de 5 tables ($personne$, $jouer$, ...) et, par exemple, la table $personne$ a 3 attributs (=colonnes) : $idp$, $nom$, $prenom$. L'attribut souligné $idp$ est un identifiant unique, on parlera de clé primaire.

Une vue d'ensemble de la base de données est disponible : http://ljouhet.net/classes/python-1a/sql/recap-cinema.png. En général on n'a pas de vue d'ensemble de toute la base de données, mais seulement le schéma relationnel et quelques exemples.

JupyterHub

Dans JupyterHub, créer un nouveau notebook Python 3, et commencez par une cellule qui permet de se connecter à la base de données cinema.db

In [1]:
# Pour pouvoir utiliser SQL dans JupyterHub

%reload_ext sql
%config SqlMagic.displaycon = False
%config SqlMagic.autolimit = 100

# Pour charger la base de données 'cinema'

%sql sqlite:////srv/db/cinema.db

Dans la suite, toutes les cellules devront commencer par %%sql (sinon vous aurez une erreur de syntaxe) : c'est spécifique à JupyterHub, mais ça ne fait pas partie du tout du langage SQL :

In [2]:
SELECT *
FROM film
  File "<ipython-input-2-f8cf37093f2c>", line 1
    SELECT *
            ^
SyntaxError: invalid syntax
In [3]:
%%sql

SELECT *
FROM film
Done.
Out[3]:
idf idr titre genre annee
1 15 Crash Drame 1996
2 15 Faux-Semblants Epouvante 1988
3 14 Pulp Fiction Policier 1994
4 13 Breaking the waves Drame 1996
5 13 Dogville Drame 2002
6 12 Alamo Western 1960
7 18 Dangereusement vôtre Espionnage 1985
8 19 Chasseur blanc, coeur noir Drame 1989
9 19 Minuit dans le jardin du bien et du mal Policier 1998
10 21 American Beauty Drame 1999
11 19 L'Echange Drame 2008

Premiers exemples

  • Exécutez les requêtes suivantes.
  • Que font les requêtes suivantes ? Essayez de comprendre ce qui se passe.
  • Les mots-clés sont toujours dans le même ordre
  • Je n'affiche pas les résultats ici pour que vous le fassiez par vous-même...
  • N'oubliez pas %%sql en début de cellule...
SELECT *
FROM personne;
SELECT prenom
FROM personne
ORDER BY prenom ASC;     -- ASC : ascendant ou DESC : descendant
SELECT DISTINCT prenom
FROM personne;
SELECT *
FROM personne
WHERE prenom = "John";     -- attention aux guillemets, importants
SELECT idp
FROM personne
WHERE nom = "von Trier" AND prenom = "Lars";    -- attention, aux espaces, aux majuscules...
SELECT titre
FROM film
WHERE idr = (
    SELECT idp
    FROM personne
    WHERE nom = "von Trier" AND prenom = "Lars"
    );
-- Ici on fait un produit cartésien. On n'en fera plus jamais après, on fera des jointures !
SELECT *
FROM personne, film;           -- (ou) FROM personne JOIN film
-- ici on fait un requête qui ressemble fortement à une jointure, on s'interdira de faire ça et on écrira des jointures
SELECT film.titre
FROM personne, film
WHERE personne.idp = film.idr
AND personne.nom = 'von Trier' AND personne.prenom = "Lars";
-- Une jointure : oui !
SELECT film.titre
FROM personne JOIN film
    ON personne.idp = film.idr
WHERE personne.nom = 'von Trier' AND personne.prenom = "Lars";
  • Savez-vous ce qu'est une projection (SELECT) ? une sélection (WHERE ou HAVING) ?

À vous !

Écrire des requêtes permettant de :

  1. Déterminer les titres de films dont le genre est Drame (attention à la majuscule, et mettre des guillemets).
  2. Déterminer les titres des films réalisés dans les années 80.
  3. Déterminer le nombre de total projections de films (utiliser la fonction COUNT).
  4. Déterminer les titres de films dont le genre est Drame et qui ont été réalisés dans les années 2000.
  5. Déterminer les rôles joués par Kevin Spacey.

La suite

  • On va travailler avec les fichiers SQLite cinema.db, communes.db, triangles.db :
    • Sur le serveur JupyterHub : vous pouvez les utiliser directement avec %sql sqlite:////srv/db/cinema.db
    • Vous pouvez les télécharger si vous travailler dans Pyzo ou avec sqliteman par exemple : http://ljouhet.net/outils/cinema.db

Triangles

  • Base de données : triangles.db
  • Sujet : http://ljouhet.net/classes/python-1a/SQL-TP-cinema/sujet-original.pdf, partie 2
  • Attention, il y a beaucoup d'éléments : évitez de faire SELECT * FROM triangles
  • Si vous n'avez pas les droits de modifier la base de données, vous ne pourrez peut-être pas faire la question 2 (INSERT) qui n'est pas au programme.
  • L'objectif est d'apprendre à faire des calculs très très simples
  • Question 3 : les valeurs ont été tirées au hasard, il se peut que certains triangles ne puissent pas exister (inégalité triangulaire...)
In [4]:
%config SqlMagic.autolimit = 100
%sql sqlite:////srv/db/triangles.db
In [5]:
%%sql
SELECT *
FROM triangles
Done.
Out[5]:
idt ab bc ac
0 255 257 32
1 81 89 19
2 63 81 35
3 58 83 5
4 32 98 52
5 65 66 99
6 45 42 14
7 64 99 94
8 77 11 85
9 76 42 16
10 78 18 47
11 77 76 58
12 61 21 87
13 61 12 29
14 82 94 1
15 15 85 5
16 7 33 94
17 62 8 78
18 92 82 5
19 57 48 27
20 10 39 4
21 8 71 48
22 4 28 2
23 87 68 55
24 51 12 37
25 54 38 27
26 53 14 31
27 3 14 23
28 72 53 36
29 33 44 16
30 11 64 100
31 58 7 13
32 85 5 19
33 37 20 57
34 72 4 87
35 63 75 21
36 92 86 69
37 21 88 49
38 94 35 3
39 64 84 49
40 78 25 58
41 10 75 63
42 6 68 31
43 65 74 60
44 32 72 51
45 72 49 18
46 29 4 73
47 71 41 61
48 19 1 65
49 13 60 67
50 22 81 53
51 96 43 38
52 31 21 32
53 86 18 50
54 84 94 100
55 18 69 6
56 34 68 85
57 25 10 95
58 34 84 51
59 69 60 51
60 5 29 89
61 26 31 67
62 3 68 37
63 13 24 43
64 33 74 75
65 100 12 8
66 25 76 74
67 94 42 96
68 32 56 57
69 69 4 83
70 34 44 13
71 74 67 39
72 42 19 45
73 55 91 79
74 92 16 1
75 31 96 40
76 72 27 12
77 30 59 77
78 57 39 42
79 99 53 24
80 28 19 73
81 24 52 97
82 5 41 20
83 88 87 56
84 19 80 39
85 84 63 4
86 60 75 67
87 77 29 27
88 42 23 65
89 94 71 4
90 23 12 1
91 24 96 33
92 17 47 68
93 29 19 34
94 63 90 37
95 54 45 60
96 91 24 82
97 48 93 59
98 85 100 72
99 79 75 55

Cinéma encore

  • Base de données : cinema.db
  • Sujet de Franz Ridde (Partie 1) : sujet, corrigé
In [6]:
%sql sqlite:////srv/db/cinema.db
In [7]:
%%sql

SELECT *
FROM film
Done.
Out[7]:
idf idr titre genre annee
1 15 Crash Drame 1996
2 15 Faux-Semblants Epouvante 1988
3 14 Pulp Fiction Policier 1994
4 13 Breaking the waves Drame 1996
5 13 Dogville Drame 2002
6 12 Alamo Western 1960
7 18 Dangereusement vôtre Espionnage 1985
8 19 Chasseur blanc, coeur noir Drame 1989
9 19 Minuit dans le jardin du bien et du mal Policier 1998
10 21 American Beauty Drame 1999
11 19 L'Echange Drame 2008

Communes

In [8]:
%sql sqlite:////srv/db/communes.db
In [10]:
%%sql

SELECT c.nom, d.nom
FROM communes AS c JOIN departements AS d
ON c.dep = d.id
Done.
Out[10]:
nom nom_1
L'Abergement-Clémenciat Ain
L'Abergement-de-Varey Ain
Ambérieu-en-Bugey Ain
Ambérieux-en-Dombes Ain
Ambléon Ain
Ambronay Ain
Ambutrix Ain
Andert-et-Condon Ain
Anglefort Ain
Apremont Ain
Aranc Ain
Arandas Ain
Arbent Ain
Arbignieu Ain
Arbigny Ain
Argis Ain
Armix Ain
Ars-sur-Formans Ain
Artemare Ain
Asnières-sur-Saône Ain
Attignat Ain
Bâgé-la-Ville Ain
Bâgé-le-Châtel Ain
Balan Ain
Baneins Ain
Beaupont Ain
Beauregard Ain
Bellignat Ain
Béligneux Ain
Bellegarde-sur-Valserine Ain
Belley Ain
Belleydoux Ain
Belmont-Luthézieu Ain
Bénonces Ain
Bény Ain
Béon Ain
Béréziat Ain
Bettant Ain
Bey Ain
Beynost Ain
Billiat Ain
Birieux Ain
Biziat Ain
Blyes Ain
La Boisse Ain
Boissey Ain
Bolozon Ain
Bouligneux Ain
Bourg-en-Bresse Ain
Bourg-Saint-Christophe Ain
Boyeux-Saint-Jérôme Ain
Boz Ain
Brégnier-Cordon Ain
Brénaz Ain
Brénod Ain
Brens Ain
Bressolles Ain
Brion Ain
Briord Ain
Buellas Ain
La Burbanche Ain
Ceignes Ain
Cerdon Ain
Certines Ain
Cessy Ain
Ceyzériat Ain
Ceyzérieu Ain
Chalamont Ain
Chaleins Ain
Chaley Ain
Challes-la-Montagne Ain
Challex Ain
Champagne-en-Valromey Ain
Champdor Ain
Champfromier Ain
Chanay Ain
Chaneins Ain
Chanoz-Châtenay Ain
La Chapelle-du-Châtelard Ain
Charix Ain
Charnoz-sur-Ain Ain
Château-Gaillard Ain
Châtenay Ain
Châtillon-en-Michaille Ain
Châtillon-la-Palud Ain
Châtillon-sur-Chalaronne Ain
Chavannes-sur-Reyssouze Ain
Chavannes-sur-Suran Ain
Chaveyriat Ain
Chavornay Ain
Chazey-Bons Ain
Chazey-sur-Ain Ain
Cheignieu-la-Balme Ain
Chevillard Ain
Chevroux Ain
Chevry Ain
Chézery-Forens Ain
Civrieux Ain
Cize Ain
Cleyzieu Ain
In [ ]: