.. _td2aecosqlcorrectionrst: ============================================== 2A.eco - Python et la logique SQL - correction ============================================== .. only:: html **Links:** :download:`notebook `, :downloadlink:`html `, :download:`python `, :downloadlink:`slides `, :githublink:`GitHub|_doc/notebooks/td2a_eco/td2a_eco_sql_correction.ipynb|*` Correction d’exercices sur SQL. .. code:: ipython3 from jyquickhelper import add_notebook_menu add_notebook_menu() .. contents:: :local: 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 `__. 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. .. code:: ipython3 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. -------------- 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 .. code:: ipython3 # 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 données SQL CreateDataBase.commit() 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 .. code:: ipython3 QueryCurs.execute('SELECT * FROM Clients') Values = QueryCurs.fetchall() print(Values) .. parsed-literal:: [(1, 'Toto', 'Munich', 'Germany', 5.2), (2, 'Bill', 'Berlin', 'Germany', 2.3), (3, 'Tom', 'Paris', 'France', 7.8), (4, 'Marvin', 'Miami', 'USA', 15.2), (5, 'Anna', 'Paris', 'USA', 7.8)] Passer en pandas ~~~~~~~~~~~~~~~~ Rien de plus simple : plusieurs manières de faire .. code:: ipython3 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()) .. parsed-literal:: 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 une DataFrame 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 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 .. code:: ipython3 # en SQL QueryCurs.execute('SELECT ID,City FROM Clients LIMIT 2') Values = QueryCurs.fetchall() print(Values) .. parsed-literal:: [(1, 'Munich'), (2, 'Berlin')] En pandas, la sélection de colonnes se fait en donnant une liste .. code:: ipython3 #sur la table df2[['ID','City']].head(2) .. raw:: html
ID City
0 1 Munich
1 2 Berlin
WHERE ~~~~~ En SQL, on utilise WHERE pour filtrer les tables selon certaines conditions .. code:: ipython3 QueryCurs.execute('SELECT * FROM Clients WHERE City=="Paris"') print(QueryCurs.fetchall()) .. parsed-literal:: [(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’ .. code:: ipython3 df2[df2['City'] == "Paris"] .. raw:: html
ID Name City Country Price
2 3 Tom Paris France 7.8
4 5 Anna Paris USA 7.8
.. code:: ipython3 df2.query('City == "Paris"') .. raw:: html
ID Name City Country Price
2 3 Tom Paris France 7.8
4 5 Anna Paris USA 7.8
Pour mettre plusieurs conditions, on utilise : - & en Python, AND en SQL - \| en python, OR en SQL .. code:: ipython3 QueryCurs.execute('SELECT * FROM Clients WHERE City=="Paris" AND Country == "USA"') print(QueryCurs.fetchall()) .. parsed-literal:: [(5, 'Anna', 'Paris', 'USA', 7.8)] .. code:: ipython3 df2.query('City == "Paris" & Country == "USA"') df2[(df2['City'] == "Paris") & (df2['Country'] == "USA")] .. raw:: html
ID Name City Country Price
4 5 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. .. code:: ipython3 QueryCurs.execute('SELECT Country, count(*) FROM Clients GROUP BY Country') print(QueryCurs.fetchall()) .. parsed-literal:: [('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. .. code:: ipython3 df2.groupby('Country').count() .. raw:: html
ID Name City Price
Country
France 1 1 1 1
Germany 2 2 2 2
USA 2 2 2 2
Pour réaliser la même chose qu’en SQL, il faut utiliser la méthode size() .. code:: ipython3 df2.groupby('Country').size() .. parsed-literal:: Country France 1 Germany 2 USA 2 dtype: int64 On peut aussi appliquer des fonctions plus sophistiquées lors d’un groupby .. code:: ipython3 QueryCurs.execute('SELECT Country, AVG(Price), count(*) FROM Clients GROUP BY Country') print(QueryCurs.fetchall()) .. parsed-literal:: [('France', 7.8, 1), ('Germany', 3.75, 2), ('USA', 11.5, 2)] Avec pandas, on peut appeler les fonctions classiques de numpy .. code:: ipython3 import numpy as np df2.groupby('Country').agg({'Price': np.mean, 'Country': np.size}) .. raw:: html
Price Country
Country
France 7.80 1
Germany 3.75 2
USA 11.50 2
Ou utiliser des fonctions lambda .. code:: ipython3 # par exemple calculer le prix moyen et le multiplier par 2 df2.groupby('Country')['Price'].apply(lambda x: 2*x.mean()) .. parsed-literal:: Country France 15.6 Germany 7.5 USA 23.0 Name: Price, dtype: float64 .. code:: ipython3 QueryCurs.execute('SELECT Country, 2*AVG(Price) FROM Clients GROUP BY Country').fetchall() .. parsed-literal:: [('France', 15.6), ('Germany', 7.5), ('USA', 23.0)] .. code:: ipython3 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()) .. parsed-literal:: [(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', 'USA', 7.8)] 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 .. code:: ipython3 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() .. code:: ipython3 QueryCurs.execute('''DROP TABLE Clients''') #QueryCurs.close() .. parsed-literal:: 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() .. code:: ipython3 #Se connecter à la base de données WORLD CreateDataBase = sqlite3.connect("./World.db3") QueryCurs = CreateDataBase.cursor() 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 .. code:: ipython3 # pour obtenir la liste des tables dans la base de données tables = QueryCurs.execute("SELECT name FROM sqlite_master WHERE type='table';").fetchall() # 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])).fetchall(), "\n") .. parsed-literal:: 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', 'LifeExpectancy', 'GNP', 'GNPOld', 'LocalName', 'GovernmentForm', 'HeadOfState', 'Capital', 'Code2'] 1ère ligne [('AFG', 'Afghanistan', 'Asia', 'Southern and Central Asia', 652090.0, 1919, 22720000, 45.9, 5976.0, None, 'Afganistan/Afqanestan', 'Islamic Emirate', 'Mohammad Omar', 1, 'AF')] Table : CountryLanguage Colonnes ['CountryCode', 'Language', 'IsOfficial', 'Percentage'] 1ère ligne [('AFG', 'Pashto', 1, 52.4)] Question 1 ---------- - Quels sont les 10 pays qui ont le plus de langues ? - Quelle langue est présente dans le plus de pays ? .. code:: ipython3 QueryCurs.execute("""SELECT CountryCode, COUNT(*) as NB FROM CountryLanguage GROUP BY CountryCode ORDER BY NB DESC LIMIT 10""").fetchall() .. parsed-literal:: [('AGO', 13), ('CAN', 12), ('CHN', 12), ('IND', 12), ('RUS', 12), ('USA', 12), ('TZA', 11), ('ZAF', 11), ('COD', 10), ('IRN', 10)] .. code:: ipython3 QueryCurs.execute('''SELECT Language, COUNT(*) as NB FROM CountryLanguage GROUP BY Language ORDER BY -NB LIMIT 1''').fetchall() .. parsed-literal:: [('English', 60)] 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 ? .. code:: ipython3 QueryCurs.execute('''SELECT DISTINCT GovernmentForm FROM Country''').fetchall() .. parsed-literal:: [('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',), ('Co-administrated',), ('Dependent Territory of the US',)] .. code:: ipython3 QueryCurs.execute('''SELECT GovernmentForm, SUM(Population) as Pop_Totale_Gouv FROM Country GROUP BY GovernmentForm ORDER BY Pop_Totale_Gouv DESC LIMIT 3 ''').fetchall() .. parsed-literal:: [('Federal Republic', 2009320500), ('Republic', 2003311200), ("People'sRepublic", 1277558000)] 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% ? .. code:: ipython3 QueryCurs.execute('''SELECT HeadOfState, Count(*) FROM Country WHERE HeadOfState = "Elisabeth II" ''').fetchall() .. parsed-literal:: [('Elisabeth II', 35)] .. code:: ipython3 # la population totale population_queen_elisabeth = QueryCurs.execute('''SELECT HeadOfState, SUM(Population) FROM Country WHERE HeadOfState = "Elisabeth II"''').fetchall() .. code:: ipython3 # La part de la population parlant anglais Part_parlant_anglais= QueryCurs.execute('''SELECT Language, SUM(Percentage*0.01*Population) FROM Country LEFT JOIN CountryLanguage ON Country.Code = CountryLanguage.CountryCode WHERE HeadOfState = "Elisabeth II" AND Language = "English" ''').fetchall() .. code:: ipython3 # La réponse est 78% d'après ces données Part_parlant_anglais[0][1]/population_queen_elisabeth[0][1] .. parsed-literal:: 0.7793618770018202 .. code:: ipython3 ## on trouve 83% si on ne fait pas attention au fait que dans certaines zones, 0% de la population parle anglais ## 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 Population_totale 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() .. parsed-literal:: [('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… 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 ? .. code:: ipython3 df = pd.read_sql_query('''SELECT Code, Name, Population, GNP , LifeExpectancy, COUNT(*) as Nb_langues_parlees, SUM(IsOfficial) as Nb_langues_officielles FROM Country INNER JOIN CountryLanguage ON Country.Code = CountryLanguage.CountryCode GROUP BY Country.Code''', CreateDataBase) df.head() .. raw:: html
Code Name Population GNP LifeExpectancy Nb_langues_parlees Nb_langues_officielles
0 ABW Aruba 103000 828.0 78.4 4 1
1 AFG Afghanistan 22720000 5976.0 45.9 5 2
2 AGO Angola 12878000 6648.0 38.3 13 1
3 AIA Anguilla 8000 63.2 76.1 1 1
4 ALB Albania 3401200 3205.0 71.6 3 1
.. code:: ipython3 df.corr() .. raw:: html
Population GNP LifeExpectancy Nb_langues_parlees Nb_langues_officielles
Population 1.000000 0.280376 0.006774 0.398681 -0.010049
GNP 0.280376 1.000000 0.164094 0.260181 0.019451
LifeExpectancy 0.006774 0.164094 1.000000 -0.317962 0.270675
Nb_langues_parlees 0.398681 0.260181 -0.317962 1.000000 -0.084946
Nb_langues_officielles -0.010049 0.019451 0.270675 -0.084946 1.000000