from jyquickhelper import add_notebook_menu
add_notebook_menu()

"""
Explanation: 2A.eco - Python et la logique SQL
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.
End of explanation
"""


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()

"""
Explanation: 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.
End of explanation
"""


# 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()

"""
Explanation: 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
End of explanation
"""


QueryCurs.execute('SELECT * FROM Clients')
Values = QueryCurs.fetchall()
print(Values)

"""
Explanation: 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
End of explanation
"""


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())

"""
Explanation: Passer en pandas
Rien de plus simple : plusieurs manières de faire
End of explanation
"""


# en SQL
QueryCurs.execute('SELECT ID,City FROM Clients LIMIT 2')
Values = QueryCurs.fetchall()
print(Values)

"""
Explanation: 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
End of explanation
"""


#sur la table
df2[['ID','City']].head(2)

"""
Explanation: En pandas, la sélection de colonnes se fait en donnant une liste
End of explanation
"""


QueryCurs.execute('SELECT * FROM Clients WHERE City=="Paris"')
print(QueryCurs.fetchall())

"""
Explanation: WHERE
En SQL, on utilise WHERE pour filtrer les tables selon certaines conditions
End of explanation
"""


df2[df2['City'] == "Paris"]

df2.query('City == "Paris"')

"""
Explanation: Avec Pandas, on peut utiliser plusieurs manières de faire : 
    - avec un booléen
    - en utilisant la méthode 'query'
End of explanation
"""


QueryCurs.execute('SELECT * FROM Clients WHERE City=="Paris" AND Country == "USA"')
print(QueryCurs.fetchall())

df2.query('City == "Paris" & Country == "USA"')

df2[(df2['City'] == "Paris") & (df2['Country'] == "USA")]

"""
Explanation: Pour mettre plusieurs conditions, on utilise : 
- & en Python, AND en SQL
- | en python, OR en SQL
End of explanation
"""


QueryCurs.execute('SELECT Country, count(*)  FROM Clients GROUP BY Country')
print(QueryCurs.fetchall())

"""
Explanation: 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.
End of explanation
"""


df2.groupby('Country').count()

"""
Explanation: 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.
End of explanation
"""


df2.groupby('Country').size()

"""
Explanation: Pour réaliser la même chose qu'en SQL, il faut utiliser la méthode size.
End of explanation
"""


QueryCurs.execute('SELECT Country, AVG(Price), count(*)  FROM Clients GROUP BY Country')
print(QueryCurs.fetchall())

"""
Explanation: On peut aussi appliquer des fonctions plus sophistiquées lors d'un groupby
End of explanation
"""


import numpy as np
df2.groupby('Country').agg({'Price': np.mean, 'Country': np.size})

"""
Explanation: Avec pandas, on peut appeler les fonctions classiques de numpy
End of explanation
"""


# par exemple calculer le prix moyen et le multiplier par 2
df2.groupby('Country')['Price'].apply(lambda x: 2*x.mean())

QueryCurs.execute('SELECT Country, 2*AVG(Price) FROM Clients GROUP BY Country').fetchall()

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())

"""
Explanation: Ou utiliser des fonctions lambda.
End of explanation
"""


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)

"""
Explanation: 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
End of explanation
"""


QueryCurs.execute('''DROP TABLE Clients''')
QueryCurs.close()

"""
Explanation: On peut également passer par un DataFrame pandas et utiliser .to_csv()
End of explanation
"""


