TP révisions Base de données

TP révisions Base de données
Dans ce TP, nous allons réviser l’écriture de requêtes SQL tant pour créer une base (création de la structure et
remplissage) que pour exploiter la masse des données. Pour cela, on utilisera la bibliothèque spécialisée (module) de
Python qui se nomme sqlite3 ainsi qu’un petit logiciel de base de donnée Sqliteman ( Sqlitemanager add-on de
Firefox est aussi un bon choix).
I. Création de la base de données baseville.sqlite – Communication avec Python
Objectif : Création d’une base de données en utilisant le module sqlite3 de python
Rappel des fonctions principales du module sqlite3
from sqlite3 import*
baseville=connect("baseville1.sqlite")
c=baseville.cursor()
c.execute(" " " on écrit sa requête SQL ici" " ")
Importation du module
Connection à la base ou Création de la base
Création d’un curseur
La méthode execute de l’objet curseur permet de
réaliser une requête SQL
on travaille sur ces données puis
baseville.commit()
baseville.close()
On enregistre les modifs et on ferme
1. Observer à l’aide d’un petit éditeur (Wordpad, Notepad …) la structure du fichier bddmp.csv.
Comprendre le nom et le type des 12 champs renseignés.
2. A l’aide du module sqlite3, écrire un scprit Pyhton qui lance la création de la base et crée une table
nommée villes qui aura les noms de champs correspondants.
3. Remplissage de la table ville .
On se propose d’écrire un script Python qui importe les données du fichier bddmp.csv dans la table villes
de la base baseville1.sqlite. Avant de vous lancer posez vous les questions suivantes : Comment ouvrir un
fichier en mode lecture ? Quelle est l’instruction SQL qui permet d’insérer des données dans une table ?
Observez le fichier bddmp.csv . Quel nettoyage pour retirer les lignes inutiles, les retours à ligne, les
éventuels guillemets surnuméraires ? A l’issue de cette phase, vous devez avoir une base opérationnelle
II. Tracé d’une carte : Matplotlib + SQL
Pour ceux qui n’auraient pas fini la partie I, vous pouvez utilisez la base baseville2.sqlite pour continuer le
TP elle contient les données nécessaires .
On se propose de tracer la carte de France de la ruralité en faisant tracer à matplotlib la carte des villes de
France contenant plus de 4000 habitants. Pour cela on admettra qu’on peut repèrer la position d’une ville
par son abscisse (= cos(47°) *longitude) et son ordonnée ( = lattitude).
1
Vous devez obtenir quelque chose comme cela :
III. Exploitation d’une base de données
Pour la suite du TP, on travaillera sur la table baseville2.sqlite. Et on écrira les requêtes SQL directement
dans un logiciel dédié ( Sqliteman, Sqlitemanager ….)
1. Création de la table bigvilles
A partir de la table villes, générer une autre table bigvilles qui récence les villes de France qui ont une population (en
2012) de plus de 30000 habitants.
On utilisera DELETE
2. Ecriture de requêtes simples.
a. Combien il y a t’il de commune de Gironde (33) de plus de 20000 habitants ?
b. Donner le nom et la population des 8 villes de Gironde de plus de 20000 habitants les plus peuplées rangées par
ordre de population descendante
c. Que fait la requête suivante :
SELECT COUNT(ville_nom) FROM villes WHERE ville_population_2012< 1
d. La ville d’ORTHEZ à une superficie de 45,86 km² . Donner le nom et la densité de population (nombre d’habitants
au km2) des dix villes les plus densément peuplées de France.
3. Ecriture de requêtes sur plusieurs tables
a. Donner le nom de département, le nom de la commune et la perte de population pour les villes du LOT et du LOT
et Garonne qui ont perdu plus de 100 habitants entre les deux recensements.
b. Donner le code_departement, le Nomdep et la population des dix départements les plus peuplés
2