SQL et PYTHON 2

November 6, 2022

1 Travaux Pratiques
1.1 SQL et Python
Pour utiliser SQLite en Python sur votre ordinateur, vous devez disposer du module sqlite3 et
d’une base de données dans votre répertoire de travail. Commencez donc par récupérer le fichier
[Link] contenant cette BDR (Base de données Rélationnelles DR. Placez ce fichier dans le
répertoire courant puis écrire en Python les lignes suivantes

[1]: import sqlite3

bdr = [Link]('./[Link]') # connexion à la base de donnée
cur = [Link]()
#
# requête et traitement
#
[Link]()

L’objet créé à la troisième ligne est un curseur ; c’est par l’intermédiaire de deux méthodes ap-
pliquées à cet objet que nous allons interagir avec la base de données : execute et fetchall.
– [Link](“SELECT … FROM …”) exécute une requête SQL passée en paramètre sous la forme
d’une chaîne de caractères.
– result = [Link]() récupère les résultats de la requête sous la forme d’une liste de tuples.

1.2 1. Interrogations de la base de données.

La base de données contient 5 tables : communes,depts,pointscomm,pointsdep et sqlite_sequence.
Ne vous occupez pas de la dernière tables.
Nous travaillerons dans un premièr temps avec deux tables :
• la table depts qui décrit les départements de la france métropolitaine
• la table communes qui décrit les communes
Les attributs de ces deux tables sont
Pour la table depts

1
 id code nom chef_lieu
entier chaine de caractères chaine de caractères entier

clé primaire de le code administratif le nom du départment l’identifiant du

l’enregistrement du département chef-lieu

CREATE TABLE depts (

id integer primary key autoincrement,
code varchar,
nom varchar,
chef_lieu int,
foreign key(chef_lieu) references communes(id)
)
Pour la table communes

id code postal nom superficie altitude population dep_id

entier chaine de chaine de chaine de flottant flottant entier entier
carac- carac- carac-
teres teres teres

clé code ad- le code le nom la l’altitude la popu- l’identifiant

primaire ministraif postal de de la superficie de la lation du
de la la commne en commune d’une departe-
commune commune hectares en mètres commune ment où
, en se situe
millier la
d’abitants commune

CREATE TABLE communes (

id integer primary key autoincrement,
code varchar,
postal varchar,
nom varchar,
superficie float,
population float,
altitude int,
dep_id int,
foreign key(dep_id) references depts(id)
)
Rédaction de requète SQL simples
Question 1. Donner le nom des communes dont la population est supérieure à 100 000 habitants

[2]: import sqlite3

2
bdr = [Link]('./[Link]') # connexion à la base de donnée
cur = [Link]()

requete = "SELECT nom FROM communes WHERE population > 100;"

[Link](requete)
for rep in cur:
print(rep)

[Link]()
[Link]()
[Link]()

('NICE',)
('AIX-EN-PROVENCE',)
('CAEN',)
('DIJON',)
('BESANCON',)
('BREST',)
('NIMES',)
('TOULOUSE',)
('BORDEAUX',)
('MONTPELLIER',)
('RENNES',)
('TOURS',)
('GRENOBLE',)
('SAINT-ETIENNE',)
('NANTES',)
('ORLEANS',)
('ANGERS',)
('REIMS',)
('NANCY',)
('METZ',)
('LILLE',)
('CLERMONT-FERRAND',)
('PERPIGNAN',)
('STRASBOURG',)
('MULHOUSE',)
('VILLEURBANNE',)
('LE MANS',)
('PARIS-11E-ARRONDISSEMENT',)
('PARIS-12E-ARRONDISSEMENT',)
('PARIS-13E-ARRONDISSEMENT',)
('PARIS-14E-ARRONDISSEMENT',)
('PARIS-15E-ARRONDISSEMENT',)
('PARIS-16E-ARRONDISSEMENT',)
('PARIS-17E-ARRONDISSEMENT',)
('PARIS-18E-ARRONDISSEMENT',)
('PARIS-19E-ARRONDISSEMENT',)

3
('PARIS-20E-ARRONDISSEMENT',)
('LE HAVRE',)
('ROUEN',)
('AMIENS',)
('TOULON',)
('LIMOGES',)
('BOULOGNE-BILLANCOURT',)
('MONTREUIL',)
('SAINT-DENIS',)
('ARGENTEUIL',)
Question 2. Donner le nom de la commune avec le moins d’habitants
Question 3. Donner le nom et l’altitude des 5 villes les plus haute de France
Question 4. Donner la superficie et la population du village de Lias
Question 5. Donner le nom des villes ayant pour superficie 2365 hectares
Question 6. Donnez le nombre de villes contenant ‘SAINT-AUBIN’ dans leurs noms.
Question 7. Donner la ville ayant le nom le plus long.
Question 8. Donner la ville ayant le nom le plus long et son département.
Question 9. Donner la ville ayant le nom le moins long.
Question 10. Donner la ville ayant le nom le moins long et son departement.
Question 11. Donner le nombre d’habitants de Paris.
Question 12. Donner la liste des dix communes les plus vastes de France, classées par ordre
décroissant de taille, en précisant pourchacune d’elles le nom du département où elles se situent.
Question 13. Quelle est la commune de plus de 10 000 habitants la plus haute de France ? Dans
quel département se situe-t-elle ?
Question 14. Quel est le chef-lieu du département le moins peuplé de France ?
Question 15. Quelle est la superficie moyenne (en hectares) des communes corses ? Rappelons que
la Corse est constituée de deux départements de codes administratifs 2A et 2B.
Question 16. En considérant que la superficie d’un département est égal à la somme des superficies
des communes qu’il abrite,calculer la superficie moyenne (en km2) des départements métropoli-
tains.”
Question 17. Quel est le département le plus vaste ?
Question 18. Parmi les communes de plus de 1 000 habitants, quel est celle dont la densité est la
plus faible ? (c’est-à-dire dont le nombre d’habitants par mètre carré est le plus petit). Dans quel
département se trouve-t-elle ? On exprimera sa densité ennombre d’habitants par kilomètre carré.”
Question 19. Existe-t-il dans le Pas-de-Calais (code administratif 62) deux communes ayant même
superficie et situées à la même altitude ?
Question 20. Enfin, déterminer le code postal attribué au nombre le plus élevé de communes
différentes (on en précisera ce nombre)

4
Rédaction de requètes SQL plus élaborées
Question 21. Donner la liste des dix communes les plus vastes de France, classées par ordre
décroissant de taille, en précisant pourchacune d’elles le nom du département où elles se situent.
Question 22. Quelle est la commune de plus de 10 000 habitants la plus haute de France ? Dans
quel département se situe-t-elle ?
Question 23. Quel est le chef-lieu de département le moins peuplé de France ?
Question 24. Quelle est la superficie moyenne (en hectares) des communes corses ? Rappelons que
la Corse est constituée de deux départements de codes administratifs 2A et 2B.
Question 25. En considérant que la superficie d’un département est égal à la somme des superficies
des communes qu’il abrite, calculer la superficie moyenne (en km2) des départements métropolitains.
Question 26. Quel est le département le plus vaste ?
Question 27. Parmi les communes de plus de 1 000 habitants, quel est celle dont la densité est la
plus faible ? (c’est-à-dire dont le nombre d’habitants par mètre carré est le plus petit). Dans quel
département se trouve-t-elle ? On exprimera sa densité en nombre d’habitants par kilomètre carré.
Question 28. Existe-t-il dans le Pas-de-Calais (code administratif 62) deux communes ayant même
superficie et situées à la même altitude ?
Question 29. Enfin, déterminer le code postal attribué au nombre le plus élevé de communes
différentes (on en précisera ce nombre).