td2a_eco_sql_correction

td2a_eco_sql_correction
March 22, 2017
1
Python et la logique SQL - correction
In [ ]: from jyquickhelper import add_notebook_menu
add_notebook_menu()
Out[ ]: <IPython.core.display.HTML object>
SQL permet de créer des tables, de rechercher, d’ajouter, de modifier ou de supprimer des données
dans les bases de données. Un peu ce que vous ferez bientôt tous les jours. C’est un langage de
management de données, pas de nettoyage, d’analyse ou de statistiques avancées.
Les instructions SQL s’écrivent d’une manière qui ressemble à celle de phrases ordinaires en
anglais. Cette ressemblance voulue vise à faciliter l’apprentissage et la lecture. Il est néanmoins
important de respecter un ordre pour les différentes instructions.
Dans ce TD, nous allons écrire des commandes en SQL via Python.
Pour plus de précisions sur SQL et les commandes qui existent, rendez-vous là SQL, PRINCIPES
DE BASE.
1.1
Se connecter à une base de données
A la différence des tables qu’on utilise habituellement, la base de données n’est pas visible directement en ouvrant Excel ou un éditeur de texte. Pour avoir une vue de ce que contient la base de
données, il est nécessaire d’avoir un autre type de logiciel.
Pour le TD, nous vous recommandans d’installer SQLLiteSpy (disponible à cette adresse SqliteSpy
ou sqlite_bro si vous voulez voir à quoi ressemble les données avant de les utiliser avec Python.
In [ ]: import sqlite3
# on va se connecter à une base de données SQL vide
# SQLite stocke la BDD dans un simple fichier
filepath = "./DataBase.db"
open(filepath, 'w').close() #crée un fichier vide
CreateDataBase = sqlite3.connect(filepath)
QueryCurs = CreateDataBase.cursor()
La méthode cursor() est un peu particulière :
Il s’agit d’une sorte de tampon mémoire intermédiaire, destiné à mémoriser temporairement les
données en cours de traitement, ainsi que les opérations que vous effectuez sur elles, avant leur
transfert définitif dans la base de données. Tant que la méthode .commit() n’aura pas été appelée,
aucun ordre ne sera appliqué à la base de données.
1
A présent que nous sommes connectés à la base de données, on va créer une table qui contient
plusieurs variables de format différents - ID sera la clé primaire de la base - Nom, Rue, Ville, Pays
seront du text - Prix sera un réel
In [ ]: # On définit une fonction de création de table
def CreateTable(nom_bdd):
QueryCurs.execute('''CREATE TABLE IF NOT EXISTS ''' + nom_bdd + '''
(id INTEGER PRIMARY KEY, Name TEXT,City TEXT, Country TEXT, Price REAL)''')
# On définit une fonction qui permet d'ajouter des observations dans la table
def AddEntry(nom_bdd, Nom,Ville,Pays,Prix):
QueryCurs.execute('''INSERT INTO ''' + nom_bdd + '''
(Name,City,Country,Price) VALUES (?,?,?,?)''',(Nom,Ville,Pays,Prix))
def AddEntries(nom_bdd, data):
""" data : list with (Name,City,Country,Price) tuples to insert
"""
QueryCurs.executemany('''INSERT INTO ''' + nom_bdd + '''
(Name,City,Country,Price) VALUES (?,?,?,?)''',data)
### On va créer la table clients
CreateTable('Clients')
AddEntry('Clients','Toto','Munich','Germany',5.2)
AddEntries('Clients',
[('Bill','Berlin','Germany',2.3),
('Tom','Paris','France',7.8),
('Marvin','Miami','USA',15.2),
('Anna','Paris','USA',7.8)])
# on va "commit" c'est à dire qu'on va valider la transaction.
# > on va envoyer ses modifications locales vers le référentiel central - la base de don
CreateDataBase.commit()
1.1.1
Voir la table
Pour voir ce qu’il y a dans la table, on utilise un premier Select où on demande à voir toute la table
In [ ]: QueryCurs.execute('SELECT * FROM Clients')
Values = QueryCurs.fetchall()
print(Values)
[(1, 'Toto', 'Munich', 'Germany', 5.2), (2, 'Bill', 'Berlin', 'Germany', 2.3), (3, 'Tom', 'Paris
2
1.1.2
Passer en pandas
Rien de plus simple : plusieurs manières de faire
In [ ]: import pandas as pd
# méthode SQL Query
df1 = pd.read_sql_query('SELECT * FROM Clients', CreateDataBase)
print("En utilisant la méthode read_sql_query \n", df1.head(), "\n")
#méthode DataFrame en utilisant la liste issue de .fetchall()
df2 = pd.DataFrame(Values, columns=['ID','Name','City','Country','Price'])
print("En passant par une DataFrame \n", df2.head())
En utilisant la méthode read_sql_query
id
Name
City Country Price
0 1
Toto Munich Germany
5.2
1 2
Bill Berlin Germany
2.3
2 3
Tom Paris France
7.8
3 4 Marvin Miami
USA 15.2
4 5
Anna Paris
USA
7.8
En passant par
ID
Name
0 1
Toto
1 2
Bill
2 3
Tom
3 4 Marvin
4 5
Anna
1.2
1.2.1
une DataFrame
City Country Price
Munich Germany
5.2
Berlin Germany
2.3
Paris France
7.8
Miami
USA 15.2
Paris
USA
7.8
Comparaison SQL et pandas
SELECT
En SQL, la sélection se fait en utilisant des virgules ou * si on veut sélectionner toutes les colonnes
In [ ]: # en SQL
QueryCurs.execute('SELECT ID,City FROM Clients LIMIT 2')
Values = QueryCurs.fetchall()
print(Values)
[(1, 'Munich'), (2, 'Berlin')]
En pandas, la sélection de colonnes se fait en donnant une liste
In [ ]: #sur la table
df2[['ID','City']].head(2)
Out[ ]:
0
1
ID
City
1 Munich
2 Berlin
3
1.2.2
WHERE
En SQL, on utilise WHERE pour filtrer les tables selon certaines conditions
In [ ]: QueryCurs.execute('SELECT * FROM Clients WHERE City=="Paris"')
print(QueryCurs.fetchall())
[(3, 'Tom', 'Paris', 'France', 7.8), (5, 'Anna', 'Paris', 'USA', 7.8)]
Avec Pandas, on peut utiliser plusieurs manières de faire : - avec un booléen - en utilisant la
méthode ’query’
In [ ]: df2[df2['City'] == "Paris"]
Out[ ]:
2
4
ID Name City Country Price
3 Tom Paris France
7.8
5 Anna Paris
USA
7.8
In [ ]: df2.query('City == "Paris"')
Out[ ]:
2
4
ID Name City Country Price
3 Tom Paris France
7.8
5 Anna Paris
USA
7.8
Pour mettre plusieurs conditions, on utilise : - & en Python, AND en SQL - | en python, OR en
SQL
In [ ]: QueryCurs.execute('SELECT * FROM Clients WHERE City=="Paris" AND Country == "USA"')
print(QueryCurs.fetchall())
[(5, 'Anna', 'Paris', 'USA', 7.8)]
In [ ]: df2.query('City == "Paris" & Country == "USA"')
df2[(df2['City'] == "Paris") & (df2['Country'] == "USA")]
Out[ ]:
1.3
4
ID
5
Name City Country Price
Anna Paris
USA
7.8
GROUP BY
En pandas, l’opération GROUP BY de SQL s’effectue avec une méthode similaire : groupby()
groupby() sert à regrouper des observations en groupes selon les modalités de certaines variables
en appliquant une fonction d’aggrégation sur d’autres variables.
In [ ]: QueryCurs.execute('SELECT Country, count(*) FROM Clients GROUP BY Country')
print(QueryCurs.fetchall())
4
[('France', 1), ('Germany', 2), ('USA', 2)]
Attention, en pandas, la fonction count() ne fait pas la même chose qu’en SQL. Count() s’applique
à toutes les colonnes et compte toutes les observations non nulles.
In [ ]: df2.groupby('Country').count()
Out[ ]:
Country
France
Germany
USA
ID Name
1
2
2
City Price
1
2
2
1
2
2
1
2
2
Pour réaliser la même chose qu’en SQL, il faut utiliser la méthode size()
In [ ]: df2.groupby('Country').size()
Out[ ]: Country
France
1
Germany
2
USA
2
dtype: int64
On peut aussi appliquer des fonctions plus sophistiquées lors d’un groupby
In [ ]: QueryCurs.execute('SELECT Country, AVG(Price), count(*)
print(QueryCurs.fetchall())
FROM Clients GROUP BY Country')
[('France', 7.8, 1), ('Germany', 3.75, 2), ('USA', 11.5, 2)]
Avec pandas, on peut appeler les fonctions classiques de numpy
In [ ]: import numpy as np
df2.groupby('Country').agg({'Price': np.mean, 'Country': np.size})
Out[ ]:
Price Country
Country
France
7.80
1
Germany 3.75
2
USA
11.50
2
Ou utiliser des fonctions lambda
In [ ]: # par exemple calculer le prix moyen et le multiplier par 2
df2.groupby('Country')['Price'].apply(lambda x: 2*x.mean())
Out[ ]: Country
France
15.6
Germany
7.5
USA
23.0
Name: Price, dtype: float64
5
In [ ]: QueryCurs.execute('SELECT Country, 2*AVG(Price) FROM Clients GROUP BY Country').fetchall
Out[ ]: [('France', 15.6), ('Germany', 7.5), ('USA', 23.0)]
In [ ]: QueryCurs.execute('SELECT * FROM Clients WHERE Country == "Germany"')
print(QueryCurs.fetchall())
QueryCurs.execute('SELECT * FROM Clients WHERE City=="Berlin" AND Country == "Germany"')
print(QueryCurs.fetchall())
QueryCurs.execute('SELECT * FROM Clients WHERE Price BETWEEN 7 AND 20')
print(QueryCurs.fetchall())
[(1, 'Toto', 'Munich', 'Germany', 5.2), (2, 'Bill', 'Berlin', 'Germany', 2.3)]
[(2, 'Bill', 'Berlin', 'Germany', 2.3)]
[(3, 'Tom', 'Paris', 'France', 7.8), (4, 'Marvin', 'Miami', 'USA', 15.2), (5, 'Anna', 'Paris', '
1.4
Enregistrer une table SQL sous un autre format
On utilise le package csv, l’option ’w’ pour ’write’.
On crée l’objet "writer", qui vient du package csv.
Cet objet a deux méthodes : - writerow pour les noms de colonnes : une liste - writerows pour les
lignes : un ensemble de liste
In [ ]: data = QueryCurs.execute('SELECT * FROM Clients')
import csv
with open('./output.csv', 'w') as file:
writer = csv.writer(file)
writer.writerow(['id','Name','City','Country','Price'])
writer.writerows(data)
On peut également passer par un DataFrame pandas et utiliser .to_csv()
In [ ]: QueryCurs.execute('''DROP TABLE Clients''')
#QueryCurs.close()
Out[ ]: <sqlite3.Cursor at 0x1bb8f97a810>
1.5
Exercice
Dans cet exercice, nous allons manipuler les tables de la base de données World.
Avant tout, connectez vous à la base de donénes en utilisant sqlite3 et connect
Lien vers la base de données : World.db3 ou
from ensae_teaching_cs.data import simple_database
name = simple_database()
In [ ]: #Se connecter à la base de données WORLD
CreateDataBase = sqlite3.connect("./World.db3")
QueryCurs = CreateDataBase.cursor()
6
Familiarisez vous avec la base de données : quelles sont les tables ? quelles sont les variables de
ces tables ? - utilisez la fonction PRAGMA pour obtenir des informations sur les tables
In [ ]: # pour obtenir la liste des tables dans la base de données
tables = QueryCurs.execute("SELECT name FROM sqlite_master WHERE type='table';").fetchal
# on veut voir les colonnes de chaque table ainsi que la première ligne
for table in tables :
print("Table :", table[0])
schema = QueryCurs.execute("PRAGMA table_info({})".format(table[0])).fetchall()
print("Colonnes", ["{}".format(x[1]) for x in schema])
print("1ère ligne", QueryCurs.execute('SELECT * FROM {} LIMIT 1'.format(table[0])).f
Table : City
Colonnes ['ID', 'Name', 'CountryCode', 'District', 'Population']
1ère ligne [(1, 'Kabul', 'AFG', 'Kabol', 1780000)]
Table : Country
Colonnes ['Code', 'Name', 'Continent', 'Region', 'SurfaceArea', 'IndepYear', 'Population', 'Life
1ère ligne [('AFG', 'Afghanistan', 'Asia', 'Southern and Central Asia', 652090.0, 1919, 22720000
Table : CountryLanguage
Colonnes ['CountryCode', 'Language', 'IsOfficial', 'Percentage']
1ère ligne [('AFG', 'Pashto', 1, 52.4)]
1.6
Question 1
• Quels sont les 10 pays qui ont le plus de langues ?
• Quelle langue est présente dans le plus de pays ?
In [ ]: QueryCurs.execute("""SELECT CountryCode, COUNT(*) as NB
FROM CountryLanguage
GROUP BY CountryCode
ORDER BY NB DESC
LIMIT 10""").fetchall()
Out[ ]: [('AGO',
('CAN',
('CHN',
('IND',
('RUS',
('USA',
('TZA',
('ZAF',
('COD',
('IRN',
13),
12),
12),
12),
12),
12),
11),
11),
10),
10)]
7
In [ ]: QueryCurs.execute('''SELECT Language, COUNT(*) as NB
FROM CountryLanguage
GROUP BY Language
ORDER BY -NB
LIMIT 1''').fetchall()
Out[ ]: [('English', 60)]
1.7
Question 2
• Quelles sont les différentes formes de gouvernements dans les pays du monde ?
• Quels sont les 3 gouvernements où la population est la plus importante ?
In [ ]: QueryCurs.execute('''SELECT DISTINCT GovernmentForm FROM Country''').fetchall()
Out[ ]: [('Islamic Emirate',),
('Constitutional Monarchy',),
('Nonmetropolitan Territory of The Netherlands',),
('Republic',),
('US Territory',),
('Parliamentary Coprincipality',),
('Dependent Territory of the UK',),
('Emirate Federation',),
('Federal Republic',),
('Constitutional Monarchy, Federation',),
('Monarchy (Emirate)',),
('Monarchy',),
('Monarchy (Sultanate)',),
('Nonmetropolitan Territory of New Zealand',),
('Part of Denmark',),
('Overseas Department of France',),
('Special Administrative Region of China',),
('Dependent Territory of Norway',),
('Islamic Republic',),
('Administrated by the UN',),
('Territory of Australia',),
("People'sRepublic",),
('Socialistic Republic',),
('Constitutional Monarchy (Emirate)',),
('Socialistic State',),
('Occupied by Marocco',),
('Territorial Collectivity of France',),
('Commonwealth of the US',),
('Nonmetropolitan Territory of France',),
('Parlementary Monarchy',),
('Federation',),
('Independent Church State',),
('Autonomous Area',),
8
('Co-administrated',),
('Dependent Territory of the US',)]
In [ ]: QueryCurs.execute('''SELECT GovernmentForm, SUM(Population) as Pop_Totale_Gouv
FROM Country
GROUP BY GovernmentForm
ORDER BY Pop_Totale_Gouv DESC
LIMIT 3
''').fetchall()
Out[ ]: [('Federal Republic', 2009320500),
('Republic', 2003311200),
("People'sRepublic", 1277558000)]
2
Question 3
• Combien de pays ont Elisabeth II à la tête de leur gouvernement ?
• Quelle proporition des sujets de Sa Majesté ne parlent pas anglais ?
– 78 % ou 83% ?
In [ ]: QueryCurs.execute('''SELECT HeadOfState, Count(*)
FROM Country
WHERE HeadOfState = "Elisabeth II" ''').fetchall()
Out[ ]: [('Elisabeth II', 35)]
In [ ]: # la population totale
population_queen_elisabeth = QueryCurs.execute('''SELECT HeadOfState, SUM(Population)
FROM Country
WHERE HeadOfState = "Elisabeth II"''').fetchall()
In [ ]: # La part de la population parlant anglais
Part_parlant_anglais= QueryCurs.execute('''SELECT Language, SUM(Percentage*0.01*Populati
FROM
Country
LEFT JOIN
CountryLanguage
ON Country.Code = CountryLanguage.CountryCode
WHERE HeadOfState = "Elisabeth II"
AND Language = "English"
''').fetchall()
In [ ]: # La réponse est 78% d'après ces données
Part_parlant_anglais[0][1]/population_queen_elisabeth[0][1]
Out[ ]: 0.7793618770018202
9
In [ ]: ## on trouve 83% si on ne fait pas attention au fait que dans certaines zones, 0% de la
## La population totale n'est alors pas la bonne, comme dans cet exemple
QueryCurs.execute('''SELECT Language,
SUM(Population_pays*0.01*Percentage) as Part_parlant_anglais, SUM(Population_pays) as Po
FROM (SELECT Language, Code, Percentage, SUM(Population) as Population_pays
FROM
Country
LEFT JOIN
CountryLanguage
ON Country.Code = CountryLanguage.CountryCode
WHERE HeadOfState = "Elisabeth II" AND Language == "English"
GROUP BY Code)''').fetchall()
Out[ ]: [('English', 95762181.19999999, 114637500)]
Conclusion: il vaut mieux écrire deux requêtes simples et lisibles pour obtenir le bon résultat,
plutôt qu’une requête qui fait tout en une seule passe mais dont on va devoir vérifier la correction
longuement...
2.1
Question 4 - passons à Pandas
Créer une DataFrame qui contient les informations suivantes par pays : - le nom - le code du
pays - le nombre de langues parlées - le nombre de langues officielles - la population - le GNP l’espérance de vie
Indice : utiliser la commande pd.read_sql_query Que dit la matrice de corrélation de ces variables ?
In [ ]: df = pd.read_sql_query('''SELECT Code, Name, Population, GNP , LifeExpectancy,
COUNT(*) as Nb_langues_parlees, SUM(IsOfficial) as Nb_langues_offici
FROM Country
INNER JOIN CountryLanguage ON Country.Code = CountryLanguage.Country
GROUP BY Country.Code''',
CreateDataBase)
df.head()
Out[ ]:
0
1
2
3
4
0
1
2
Code
Name Population
GNP LifeExpectancy
ABW
Aruba
103000
828.0
78.4
AFG Afghanistan
22720000 5976.0
45.9
AGO
Angola
12878000 6648.0
38.3
AIA
Anguilla
8000
63.2
76.1
ALB
Albania
3401200 3205.0
71.6
Nb_langues_officielles
1
2
1
10
Nb_langues_parlees \
4
5
13
1
3
3
4
1
1
In [ ]: df.corr()
Out[ ]:
Population
GNP LifeExpectancy
Population
1.000000 0.280376
0.006774
GNP
0.280376 1.000000
0.164094
LifeExpectancy
0.006774 0.164094
1.000000
Nb_langues_parlees
0.398681 0.260181
-0.317962
Nb_langues_officielles -0.010049 0.019451
0.270675
Population
GNP
LifeExpectancy
Nb_langues_parlees
Nb_langues_officielles
\
Nb_langues_parlees Nb_langues_officielles
0.398681
-0.010049
0.260181
0.019451
-0.317962
0.270675
1.000000
-0.084946
-0.084946
1.000000
In [ ]:
11