Les bases de données en Python

Introduction #

Les objectifs de ce chapitre sont :

• Manipuler des bases de données avec Python à travers l'utilisation de SQLite, qui est un système de gestion de base de données (SGBD) écrit en C et qui sauvegarde les bases de données sous forme d'un fichier multi-plateforme.
• SQLite est le moyen le plus rapide et le plus simple de gérer une base de données sous Python grâce à un module standard Python pour faire l'interface entre Python et SQLite.

Une base de données, c'est quoi ? #

• Une base de données (database en anglais) est un conteneur dans lequel il est possible de stocker des données de façon structurée sur des mémoires secondaires.
• Un langage standardisé SQL (Structured Query Language) est dédié à cette structure et permet de communiquer avec une base de données (faire des recherches, des ajouts, des modifications ou des suppressions).
• Les SGBD (systèmes de gestion de base de données) sont des programmes qui s'occupent du stockage des données.
• Un SGBD permet de :
  • Définir la structure d'une BD
  • Rechercher des données dans une BD
  • Insérer, modifier, supprimer des données dans une BD
  • Assurer la cohérence et la confidentialité des données
• Il ne faut donc pas confondre une BD qui est un conteneur et le SGBD qui est un logiciel de gestion de bases de données.

Un SGBDR, c'est quoi ? #

Les logiciels de gestion de bases de données les plus utilisés aujourd'hui sont des SGBDR — Système de gestion de base de données relationnelles — c'est-à-dire que les données sont liées les unes aux autres afin de garantir la cohérence des données. Les bases de données relationnelles sont basées sur la théorie des ensembles avec l'utilisation des opérateurs de l'algèbre relationnelle (l'union, l'intersection, la différence, la jointure, la projection, et la sélection).

Organisation des données dans un SGBDR

• Les données sont organisées sous forme de tableaux 2D : les tables.
• Chaque table représente une relation au sens mathématique du terme.
• Un attribut (champ) est un nom donné à une colonne d'une relation. Un attribut prend ses valeurs dans un domaine (entier, réel, chaîne, date, …).
• Un enregistrement est un élément d'une table (ligne, Tuple, N-Uplet, Record).
• La clé primaire est un sous-ensemble d'attributs qui permet de caractériser tout enregistrement d'une table de façon unique.
• La clé étrangère identifie un sous-ensemble d'attributs d'une table dont la valeur fait référence à la clé primaire d'une autre table.

Exemple d'une BD : bdipeis

La base de données bdipeis contient 5 tables :

• Matières : identifiées par un id (clé primaire) et le libellé.
• Enseignants : identifiés par un id. La clé étrangère idMatière référence la table Matières.
• Groupes : identifiés par un id et le libellé.
• Étudiants : identifiés par un id. La clé étrangère idGroupe référence la table Groupes.
• Notes : clé primaire composée de (idEtudiant, idMatière), deux clés étrangères.

SQL : Principales commandes

• LDD (Langage de Définition de Données) : CREATE, ALTER, DROP
• LMD (Langage de Manipulation de Données) : INSERT, UPDATE, DELETE, SELECT
• LCD (Langage de Contrôle de Données) : GRANT, REVOKE
• LCT (Langage de Contrôle des Transactions) : COMMIT, ROLLBACK

SQL : Un LDD #

Utiliser SQL pour créer une table (CREATE TABLE)

La commande CREATE TABLE permet de créer une table en SQL. En SQLite, les types disponibles sont : NULL, INTEGER, REAL, NUMERIC, TEXT, BLOB.

Pour chaque colonne, on peut définir des options :

• NOT NULL : empêche d'enregistrer une valeur nulle.
• DEFAULT : valeur par défaut si aucune donnée n'est indiquée.
• PRIMARY KEY : indique que cette colonne est la clé primaire.

CREATE TABLE nomTable (
    colonne1  type  [contraintesDeColonne],
    colonne2  type  [contraintesDeColonne],
    ...
    [contraintesDeTable]
)

-- Contraintes de colonne :
--   [NOT NULL | NULL]
--   [DEFAULT valeurDefault]
--   [CONSTRAINT nom] UNIQUE | PRIMARY KEY
--   CHECK(predicatDeColonne)
--   REFERENCES table(colonne)

-- Contraintes de table :
--   CONSTRAINT nom
--   UNIQUE | PRIMARY KEY (listeColonnes)
--   CHECK (predicatDeTable)
--   FOREIGN KEY listeColonnes REFERENCES table(listeColonnes)

Exemples de création des tables de bdipeis :

-- Création de la table Matieres
CREATE TABLE Matieres (
    id      INTEGER PRIMARY KEY AUTOINCREMENT,
    Libelle TEXT
)

-- Création de la table Enseignants
CREATE TABLE Enseignants (
    id        INTEGER PRIMARY KEY AUTOINCREMENT,
    Prenom    TEXT,
    Nom       TEXT,
    Grade     TEXT CHECK (Grade IN ('A','MA','MC','Pr')),
    Statut    TEXT CHECK (Statut IN ('P','V')),
    idMatiere INTEGER REFERENCES Matieres(id)
)

Supprimer une table (DROP TABLE)

DROP TABLE nomTable

Modifier une table (ALTER TABLE)

Avec SQLite, la commande ALTER TABLE permet de :

-- Ajouter une colonne
ALTER TABLE nomTable ADD colonne type

-- Renommer une table
ALTER TABLE nomTable RENAME TO nouveauNomTable

Comment créer une BD sous Python #

• On manipule des BD sous Python à travers le module sqlite3.
• Pour importer le module : import sqlite3
• Pour créer une connexion : con = sqlite3.connect('mabase')
• Si la base n'existe pas, un fichier est créé dans le dossier du programme. S'il existe déjà, il sera réutilisé.
• On peut préciser un chemin : sqlite3.connect('c:/mabase')
• Base temporaire en mémoire : con = sqlite3.connect(':memory:')
• Par défaut la connexion n'est pas en autocommit. Pour valider les changements : con.commit()
• Pour fermer la connexion : con.close()
• Pour exécuter des requêtes SQL : créer un curseur, exécuter, fermer.

import sqlite3

# Connexion (crée le fichier si inexistant)
con = sqlite3.connect('mabase.sqlite')

# Créer un curseur et exécuter une requête
cur = con.cursor()
cur.execute('CREATE TABLE maTable(x INTEGER, y TEXT)')
cur.close()

# Valider et fermer
con.commit()
con.close()

Créer la table Matieres avec sqlite3 :

cur = con.cursor()
cur.execute("""CREATE TABLE Matieres(
    id      INTEGER PRIMARY KEY AUTOINCREMENT,
    Libelle TEXT
)""")
cur.close()

# Supprimer une table
cur = con.cursor()
cur.execute("DROP TABLE Matieres")
cur.close()

SQL : Un LMD #

Ajouter des données (INSERT)

# Méthode 1 : valeurs directement dans la chaîne
cur.execute("INSERT INTO Matieres(id, libelle) VALUES(1, 'Informatique')")

# Méthode 2 : paramètres sécurisés (recommandé)
cur.execute("INSERT INTO Matieres(id, libelle) VALUES(?, ?)", (2, 'Mathématique'))

# Méthode 3 : plusieurs insertions en une fois (executemany)
lstMat = []
lstMat.append((1, "Informatique"))
lstMat.append((2, "Mathématique"))
cur.executemany("INSERT INTO Matieres(id, libelle) VALUES(?,?)", lstMat)

Supprimer des données (DELETE)

# Vider la table entière
cur.execute("DELETE FROM Matieres")

# Supprimer un enregistrement spécifique
cur.execute("DELETE FROM Matieres WHERE id = 1")

Modifier des données (UPDATE)

Syntaxe : UPDATE nomTable SET col1=val1, ..., colN=valN WHERE condition

cur.execute("UPDATE matieres SET libelle = 'Automatique' WHERE id = 5")

Récupérer des données (SELECT)

Syntaxe : SELECT col1, ..., colN FROM nomTable WHERE condition GROUP BY expression HAVING condition ORDER BY ASC|DESC

# Récupérer toutes les matières
cur.execute("SELECT * FROM matieres")

# fetchone() : première ligne → tuple (1, 'Informatique')
mat1 = cur.fetchone()
print(mat1)

# fetchall() : toutes les lignes
cur.execute("SELECT * FROM matieres")
lignes = cur.fetchall()
for ligne in lignes:
    print('{0} : {1}'.format(ligne[0], ligne[1]))

# Le curseur fonctionne aussi comme itérateur
cur.execute("SELECT * FROM matieres")
for ligne in cur:
    print('{0} : {1}'.format(ligne[0], ligne[1]))

# Recherche spécifique avec WHERE
cur.execute("SELECT libelle FROM matieres WHERE id = 1")
reponse = cur.fetchone()
print(reponse)   # ('Informatique',)

Création de la base de données bdipeis avec Python #

import sqlite3

bd = sqlite3.connect('bdipeis.sqlite')
c = bd.cursor()

try:
    # Supprimer toutes les tables
    c.execute("DROP TABLE Matieres")
    c.execute("DROP TABLE Groupes")
    c.execute("DROP TABLE Enseignants")
    c.execute("DROP TABLE Etudiants")
    c.execute("DROP TABLE Notes")
except:
    pass

# Création de la table Matieres
c.execute("""CREATE TABLE Matieres(
    id      INTEGER PRIMARY KEY AUTOINCREMENT,
    Libelle TEXT
)""")

# Création de la table Groupes
c.execute("""CREATE TABLE Groupes(
    id      INTEGER PRIMARY KEY AUTOINCREMENT,
    Libelle TEXT
)""")

# Création de la table Enseignants
c.execute("""CREATE TABLE Enseignants(
    id        INTEGER PRIMARY KEY AUTOINCREMENT,
    prenom    TEXT,
    nom       TEXT,
    grade     TEXT CHECK(grade IN ('A','MA','MC','Pr')),
    statut    TEXT CHECK(statut IN ('P','V')),
    idMatiere REFERENCES Matieres(id)
)""")

# Création de la table Etudiants
c.execute("""CREATE TABLE Etudiants(
    id       INTEGER PRIMARY KEY AUTOINCREMENT,
    prenom   TEXT,
    nom      TEXT,
    idGroupe REFERENCES Groupes(id)
)""")

# Création de la table Notes
c.execute("""CREATE TABLE Notes(
    idEtudiant INTEGER REFERENCES Etudiants(id),
    idMatiere  INTEGER REFERENCES Matieres(id),
    Note       INTEGER CHECK(Note >= 0 AND Note <= 20),
    CONSTRAINT pk_Notes PRIMARY KEY(idEtudiant, idMatiere)
)""")

# Insertion des matières
c.execute("INSERT INTO Matieres(libelle) VALUES('Informatique')")
c.execute("INSERT INTO Matieres(libelle) VALUES('Mathématique')")
c.execute("INSERT INTO Matieres(libelle) VALUES('Physique')")
c.execute("INSERT INTO Matieres(libelle) VALUES('Français')")
c.execute("INSERT INTO Matieres(libelle) VALUES('Anglais')")
c.execute("INSERT INTO Matieres(libelle) VALUES('Chimie')")
c.execute("INSERT INTO Matieres(libelle) VALUES('STI')")

# Insertion des groupes (filière MP)
c.execute("INSERT INTO Groupes(libelle) VALUES('MP21')")
c.execute("INSERT INTO Groupes(libelle) VALUES('MP22')")
c.execute("INSERT INTO Groupes(libelle) VALUES('MP23')")
c.execute("INSERT INTO Groupes(libelle) VALUES('MP24')")
c.execute("INSERT INTO Groupes(libelle) VALUES('MP25')")
c.execute("INSERT INTO Groupes(libelle) VALUES('MP11')")
c.execute("INSERT INTO Groupes(libelle) VALUES('MP12')")
c.execute("INSERT INTO Groupes(libelle) VALUES('MP13')")
c.execute("INSERT INTO Groupes(libelle) VALUES('MP14')")
c.execute("INSERT INTO Groupes(libelle) VALUES('MP15')")
# Filière PC
c.execute("INSERT INTO Groupes(libelle) VALUES('PC21')")
c.execute("INSERT INTO Groupes(libelle) VALUES('PC22')")
c.execute("INSERT INTO Groupes(libelle) VALUES('PC23')")
c.execute("INSERT INTO Groupes(libelle) VALUES('PC11')")
c.execute("INSERT INTO Groupes(libelle) VALUES('PC12')")
c.execute("INSERT INTO Groupes(libelle) VALUES('PC13')")
# Filière PT
c.execute("INSERT INTO Groupes(libelle) VALUES('PT21')")
c.execute("INSERT INTO Groupes(libelle) VALUES('PT22')")
c.execute("INSERT INTO Groupes(libelle) VALUES('PT23')")
c.execute("INSERT INTO Groupes(libelle) VALUES('PT24')")
c.execute("INSERT INTO Groupes(libelle) VALUES('PT25')")
c.execute("INSERT INTO Groupes(libelle) VALUES('PT11')")
c.execute("INSERT INTO Groupes(libelle) VALUES('PT12')")
c.execute("INSERT INTO Groupes(libelle) VALUES('PT13')")
c.execute("INSERT INTO Groupes(libelle) VALUES('PT14')")
c.execute("INSERT INTO Groupes(libelle) VALUES('PT15')")
# Filière BG
c.execute("INSERT INTO Groupes(libelle) VALUES('BG21')")
c.execute("INSERT INTO Groupes(libelle) VALUES('BG22')")
c.execute("INSERT INTO Groupes(libelle) VALUES('BG23')")
c.execute("INSERT INTO Groupes(libelle) VALUES('BG11')")
c.execute("INSERT INTO Groupes(libelle) VALUES('BG12')")
c.execute("INSERT INTO Groupes(libelle) VALUES('BG13')")

# Insertion des enseignants
c.execute("""INSERT INTO Enseignants(prenom, nom, grade, statut, idMatiere)
             VALUES('Atef', 'MASMOUDI', 'MC', 'P', 1)""")
c.execute("""INSERT INTO Enseignants(prenom, nom, grade, statut, idMatiere)
             VALUES('Soufien', 'CHOUAYEKH', 'MA', 'P', 2)""")
c.execute("""INSERT INTO Enseignants(prenom, nom, grade, statut, idMatiere)
             VALUES('Hatem', 'ELLOUMI', 'Pr', 'P', 3)""")
c.execute("""INSERT INTO Enseignants(prenom, nom, grade, statut, idMatiere)
             VALUES('Said', 'ABID', 'Pr', 'P', 7)""")

# À vous de choisir la liste des étudiants ainsi que les notes

# ---- Exemples de recherches ----
c.execute("SELECT * FROM matieres")
mat1 = c.fetchone()
print(mat1)                  # (1, 'Informatique')

c.execute("SELECT * FROM matieres")
lignes = c.fetchall()
for ligne in lignes:
    print('{0} : {1}'.format(ligne[0], ligne[1]))
# 1 : Informatique
# 2 : Mathématique  ...

c.execute("SELECT * FROM matieres")
for ligne in c:
    print('{0} : {1}'.format(ligne[0], ligne[1]))

# Recherche spécifique
c.execute("SELECT libelle FROM matieres WHERE id = 1")
reponse = c.fetchone()
print(reponse)               # ('Informatique',)

bd.commit()
bd.close()

L'algèbre relationnelle #

• L'algèbre relationnelle a été inventée en 1970 par Edgar Frank Codd, le directeur de recherche du centre IBM de San José.
• Elle est constituée d'un ensemble d'opérations formelles sur les relations.
• Les opérations relationnelles permettent de créer une nouvelle relation (table) à partir d'opérations élémentaires sur d'autres relations (union, intersection, différence, etc.).

Notions de base

• Domaine : un ensemble de valeurs (chaînes de caractères, numérique, date, type énuméré, …).
• Attribut A : une caractéristique d'un objet représentée par une valeur prise dans un domaine D(A).
• N-uplet (tuple) : un vecteur de valeurs (v1 ∈ D(A1), v2 ∈ D(A2), …, vn ∈ D(An)). Chaque n-uplet est unique.
• Identifiant (Clé primaire) : sous-ensemble d'attributs I tel qu'il n'existe pas deux n-uplets dans R ayant même valeur sur I.
• Contrainte d'intégrité : propriété logique P telle que toute modification de R laisse P vraie.
• Clé étrangère (identifiant externe) : ensemble d'attributs d'une relation R¹ qui est un identifiant dans une autre relation R².

Opérateurs relationnels

On distingue trois catégories d'opérateurs :

• Opérateurs unaires : sélection et projection.
• Opérateurs binaires (même schéma) : union, intersection, différence.
• Opérateurs binaires (schémas différents) : jointure, produit cartésien.

SELECT * FROM etudiants
WHERE id >= 5 OR prenom = 'Hechmi'

SELECT DISTINCT prenom, nom FROM etudiants

SELECT * FROM R1
UNION
SELECT * FROM R2

SELECT * FROM R1
INTERSECT
SELECT * FROM R2

SELECT * FROM R1
EXCEPT
SELECT * FROM R2

SELECT * FROM R1, R2 WHERE P
-- ou bien
SELECT * FROM R1 JOIN R2 ON P

La clause WHERE

La clause WHERE filtre les lignes. Les opérateurs disponibles :

• Comparaison traditionnelle : =, <>, <, <=, >, >=
• BETWEEN expr1 AND expr2 — valeur entre deux bornes.
• IN (val1, val2, …) — valeur dans une liste.
• LIKE chaine — chaîne générique : _ (1 caractère), % (0 ou plusieurs caractères).
• Opérateurs logiques : AND, OR, NOT.

-- Étudiants dont l'identifiant est supérieur à 5
SELECT * FROM etudiants WHERE id > 5

-- Étudiants dont l'identifiant est entre 10 et 100
SELECT * FROM etudiants WHERE id BETWEEN 10 AND 100

-- Étudiants des groupes 1 ou 2
SELECT * FROM etudiants WHERE idgroupe IN (1, 2)

-- Étudiants dont le nom commence par M
SELECT * FROM etudiants WHERE nom LIKE 'M%'

Les fonctions d'agrégation

Ces fonctions s'appliquent à un ensemble de tuples :

• COUNT — cardinal (nombre de lignes)
• AVG — moyenne
• MIN — minimum
• MAX — maximum
• SUM — total

La clause GROUP BY

GROUP BY subdivise la table en groupes partageant une même valeur pour les expressions spécifiées.

-- Nombre d'étudiants par groupe
SELECT COUNT(*), idgroupe FROM etudiants GROUP BY idgroupe

-- HAVING filtre sur les résultats agrégés
-- Groupes comportant plus de 30 étudiants :
SELECT COUNT(*), idgroupe FROM etudiants
GROUP BY idgroupe
HAVING COUNT(*) > 30

Exercices avec corrigés #

import sqlite3

def BDDconnect(bdd):
    return sqlite3.connect(bdd)

def BDDclose(conn):
    conn.commit()
    conn.close()

def SQLquery(cmd, conn):
    c = conn.cursor()
    c.execute(cmd)
    rows = c.fetchall()
    for x in rows:
        print(x)

# Requêtes SQL — Exercice 1
Q1 = 'SELECT MIN(ab*ac*bc) FROM triangles WHERE ab+bc+ac >= 100'
Q2 = """SELECT ab, ac, bc FROM triangles
        WHERE ab+bc+ac >= 100
        AND ab*ac*bc IN (SELECT MIN(ab*ac*bc) FROM triangles WHERE ab+bc+ac >= 100)"""
Q3 = 'SELECT * FROM triangles WHERE ab*ab + ac*ac = bc*bc'
Q4 = 'SELECT COUNT(*) FROM triangles WHERE ab*ab + ac*ac = bc*bc'
Q5 = 'SELECT MAX(ab+bc+ac) FROM triangles WHERE ab*ab + ac*ac = bc*bc'
Q6 = 'SELECT * FROM triangles WHERE ab = ac AND ab = bc'
Q7 = 'SELECT * FROM triangles WHERE (ab+bc+ac) / 3.0 = 42'

def TrianglesTest():
    conn = BDDconnect('triangles.sqlite')
    print("la plus petite valeur des produits AB*AC*BC (périmètre >= 100)")
    SQLquery(Q1, conn)
    print("triangles pour lesquels le minimum précédent est atteint")
    SQLquery(Q2, conn)
    print("tous les triangles rectangles en A")
    SQLquery(Q3, conn)
    print("le nombre de tels triangles")
    SQLquery(Q4, conn)
    print("le maximum des périmètres des triangles rectangles en A")
    SQLquery(Q5, conn)
    print("tous les triangles équilatéraux")
    SQLquery(Q6, conn)
    print("triangles tels que (AB + AC + BC) / 3 = 42")
    SQLquery(Q7, conn)
    BDDclose(conn)

Q1 = """SELECT C.nom, D.nom, R.nom, C.pop
        FROM communes AS C
        JOIN departements AS D ON C.dep = D.id
        JOIN regions AS R ON R.id = D.reg"""

Q2 = """SELECT C.nom, D.nom, R.nom, C.pop
        FROM communes AS C
        JOIN departements AS D ON C.dep = D.id
        JOIN regions AS R ON R.id = D.reg
        ORDER BY C.pop DESC"""

Q3 = """SELECT COUNT(C.nom) FROM communes AS C
        WHERE C.pop >= (SELECT pop FROM communes WHERE nom = 'Strasbourg')"""

Q4 = 'SELECT nom, pop FROM communes WHERE nom LIKE "Pa%is"'

Q5 = 'SELECT nom, pop FROM communes WHERE LENGTH(nom) > pop'

def CommunesTest():
    conn = BDDconnect('communes_departements_regions.sqlite')
    print("liste de toutes les communes avec département, région et population")
    SQLquery(Q1, conn)
    print("triée par ordre décroissant de population")
    SQLquery(Q2, conn)
    print("rang de la ville de Strasbourg dans ce classement")
    SQLquery(Q3, conn)
    print("communes dont le nom commence par 'Pa' et finit par 'is'")
    SQLquery(Q4, conn)
    print("communes avec moins d'habitants que de lettres dans leur nom")
    SQLquery(Q5, conn)
    BDDclose(conn)

Q1 = "SELECT * FROM prenoms WHERE prenom = 'Abdallah' ORDER BY annee"

Q2 = "SELECT annee, COUNT(DISTINCT prenom) FROM prenoms GROUP BY annee"

Q3 = 'SELECT prenom FROM prenoms GROUP BY prenom HAVING SUM(nombre) = 100'

Q4 = """SELECT prenom, SUM(nombre) AS total FROM prenoms
        GROUP BY prenom ORDER BY total DESC LIMIT 1"""

Q5 = 'SELECT prenom, annee, MAX(nombre) FROM prenoms GROUP BY annee'

def PrenomsTest():
    conn = BDDconnect('prenoms_paris.sqlite')
    print("nombre de fois 'Abdallah' a été donné (trié par années)")
    SQLquery(Q1, conn)
    print("pour chaque année : nombre de prénoms différents enregistrés")
    SQLquery(Q2, conn)
    print("prénoms donnés exactement 100 fois sur la période")
    SQLquery(Q3, conn)
    print("prénom le plus donné sur l'ensemble de la période")
    SQLquery(Q4, conn)
    print("pour chaque année : prénom le plus donné et nombre de fois")
    SQLquery(Q5, conn)
    BDDclose(conn)

Q1  = 'SELECT nom FROM profs'
Q2  = 'SELECT COUNT(*) FROM colles WHERE note = 20'
Q3  = """SELECT note FROM colles, eleves
         WHERE eleve = ide AND nom = 'Lions' ORDER BY semaine"""
Q3p = """SELECT note FROM colles JOIN eleves ON eleve = ide
         WHERE nom = 'Lions' ORDER BY semaine"""
Q4  = """SELECT e.nom, p.nom, note, semaine
         FROM colles
         JOIN eleves AS e ON eleve = ide
         JOIN profs  AS p ON prof  = idp
         WHERE note >= 19"""
Q5  = """SELECT AVG(note) FROM colles
         JOIN eleves AS e ON eleve = ide WHERE nom = 'Lions'"""
Q6  = """SELECT nom FROM
         (SELECT eleve FROM
           (SELECT eleve, COUNT(*) AS nb FROM colles
            WHERE note < 10 GROUP BY eleve) WHERE nb >= 10)
         JOIN eleves AS e ON eleve = ide"""
Q7  = """SELECT nom FROM
         (SELECT eleve FROM
           (SELECT eleve, COUNT(*) AS nb FROM colles
            WHERE note > 18 GROUP BY eleve) WHERE nb >= 6)
         JOIN eleves AS e ON eleve = ide"""
Q8  = """SELECT nom, AVG(note) FROM colles
         JOIN eleves AS e ON eleve = ide
         GROUP BY nom ORDER BY AVG(note)"""
Q9  = """SELECT nom FROM colles JOIN profs ON prof = idp
         GROUP BY nom ORDER BY AVG(note) DESC LIMIT 1"""
Q10 = """SELECT nom, AVG(note) AS moyenne,
                AVG(note*note) - AVG(note)*AVG(note) AS variance
         FROM colles JOIN profs ON prof = idp GROUP BY nom"""
Q11 = """SELECT AVG(moyenne) FROM
         (SELECT AVG(note) AS moyenne FROM colles
          JOIN eleves AS e ON eleve = ide GROUP BY nom)"""

def CollesTest():
    conn = BDDconnect('notes_colles.sqlite')
    print("la liste des professeurs")
    SQLquery(Q1, conn)
    print("le nombre de 20 qui ont été attribués")
    SQLquery(Q2, conn)
    print("notes de Jacques-Louis Lions (semaines croissantes)")
    SQLquery(Q3, conn)
    print("quadruplets (élève, prof, note, semaine) pour note >= 19")
    SQLquery(Q4, conn)
    print("moyenne des notes de colle de Jacques-Louis Lions")
    SQLquery(Q5, conn)
    print("élèves ayant eu au moins 10 notes sous la moyenne")
    SQLquery(Q6, conn)
    print("élèves ayant eu au moins 6 notes supérieures à 18")
    SQLquery(Q7, conn)
    print("liste des couples (élève, moyenne)")
    SQLquery(Q8, conn)
    print("colleur qui tend à donner les meilleures notes en moyenne")
    SQLquery(Q9, conn)
    print("pour chaque colleur : moyenne et variance des notes")
    SQLquery(Q10, conn)
    print("la moyenne des moyennes des élèves")
    SQLquery(Q11, conn)
    BDDclose(conn)

import sqlite3

con = sqlite3.connect('bd.sqlite')
c = con.cursor()

try:
    c.execute("DROP TABLE personne")
except:
    pass

# Création de la table Personne
c.execute("""CREATE TABLE Personne(
    id    INTEGER PRIMARY KEY AUTOINCREMENT,
    nom   TEXT,
    age   INTEGER,
    ville TEXT
)""")

# Insertion des tuples
c.execute("INSERT INTO PERSONNE(nom, age, ville) VALUES('Omar',   21, 'Sfax')")
c.execute("INSERT INTO PERSONNE(nom, age, ville) VALUES('Ali',    20, 'Tunis')")
c.execute("INSERT INTO PERSONNE(nom, age, ville) VALUES('Ahmed',  25, 'Sousse')")
c.execute("INSERT INTO PERSONNE(nom, age, ville) VALUES('Mohamed',18, 'Sfax')")
c.execute("INSERT INTO PERSONNE(nom, age, ville) VALUES('Youssef',30, 'Nabeul')")

# Q3.a : σ_{Age=30}(PERSONNE)
print('Q3.a : Afficher les personnes qui ont age = 30')
c.execute("SELECT * FROM personne WHERE age = 30")
for ligne in c:
    print(ligne)

# Q3.b : Π_{Age}(PERSONNE)
print('Q3.b : Afficher les différents âges')
c.execute("SELECT DISTINCT age FROM personne")
for ligne in c:
    print(ligne[0])

# Q3.c : Π_{Age}(σ_{Nom='Ahmed'}(PERSONNE))
print("Q3.c : Afficher l'âge de Ahmed")
c.execute("SELECT DISTINCT age FROM personne WHERE nom = 'Ahmed'")
for ligne in c:
    print(ligne[0])

# Q4.a : personnes habitant à Sfax
print('Q4.a : Les personnes qui habitent à Sfax')
c.execute("SELECT * FROM personne WHERE ville = 'Sfax'")
for ligne in c:
    print(ligne)

# Q4.b : personnes ayant moins de 20 ans
print('Q4.b : Personnes ayant moins de 20 ans')
c.execute("SELECT * FROM personne WHERE age < 20")
for ligne in c:
    print(ligne)

# Q4.c : villes et leur nombre
print('Q4.c : Villes dans PERSONNE et leur nombre')
c.execute("SELECT DISTINCT ville FROM personne")
lignes = c.fetchall()
for ligne in lignes:
    print(ligne)
print('Nombre de villes = ' + str(len(lignes)))

# Q4.d : nombre de personnes par ville
print('Q4.d : Nombre de personnes par ville')
c.execute("SELECT ville, COUNT(*) FROM personne GROUP BY ville")
for ligne in c.fetchall():
    print(ligne)

# Bonus : nombre par ville dont le nom commence par S
print('Villes dont le nom commence par S')
c.execute("SELECT ville, COUNT(*) FROM personne GROUP BY ville HAVING ville LIKE 'S%'")
for ligne in c.fetchall():
    print(ligne)

con.commit()
con.close()