import os
os.chdir (r'P:\python\fichiers s3db')

import sqlite3

# Connexion `a la base :
cnx = sqlite3 . connect ("VillesFrance.s3db")
# Sp´ecification de l’encodage :
cnx . text_factory = lambda x :str(x ,"latin -1")

# Cr´eation d’un curseur :
curseur = cnx . cursor ()


## quelques requetes
# 
# curs1=cnx.cursor()
# 
# Req="Select distinct Ville from Clients"
# curs1.execute(Req)
# 
# Liste_Villes=[]
# for L in curs1:
#     Liste_Villes.append(L[0])                     
#  
# #
#    
# curs2=cnx.cursor()
# 
# Req1="Select Nom, count(*), Ville 
#           from Clients join Commandes on NCli=NClient 
#           group by NCli"
# Req2="Select Ville, count(*) from Clients group by Ville"
# 
# for Ville in Liste_Villes:
#     curs1.execute(Req1)
#     curs2.execute(Req2)
#     for L in curs2:
#         if L[0]==Ville:
#             print(L[0],":",L[1],"clients")
#     for L in curs1:
#         if L[2]==Ville:
#             print("    ",L[0],":",L[1],"commandes")
#     
# curs1.close()
# curs2.close()
# cnx.close()
# 
# #3. SELECT count(*) FROM Villes
# #La base de donn�es contient 35\,251 villes.
# 
# #4. SELECT population FROM Villes WHERE Nom="Toulouse"
# 
# 
#  #SELECT Nom FROM Villes WHERE CodePostal="31790"
# 
# 
# #
# 
# cnx=sqlite3.connect("VillesFrance.s3db")
# cnx.text_factory = lambda x: str(x, 'latin-1')
# curseur=cnx.cursor()
# 
# #
# 
# Req="Select Nom, Population from Villes where Population>100000"
# curseur.execute(Req)
# for L in curseur:
#     print(L[0],":",L[1],"habitants.")
# 
# Req="Select count(*) from Villes where Population>100000"
# curseur.execute(Req)
# for L in curseur:
#     print(L[0],"villes ont plus de 100 000 habitants.")
# 
# #On obtient 34 villes.
# 
# 
# #
# 
# Req="SELECT Departements.Nom, sum(Population) 
#          FROM Departements JOIN Villes 
#          ON Num=Departement
#          GROUP BY Num"
# curseur.execute(Req)
# for L in curseur:
#     print(L[0],":",L[1],"habitants.")

## Q3.
import matplotlib.pyplot as plt

Req="SELECT Longitude, Latitude FROM Villes where Population >2000"
curseur.execute(Req)

for L in curseur:
    x=float(L[0])
    y=float(L[1])
    plt.plot([x],[y],marker='.')
    
plt.axis("equal")



## coordonées

fichier=open('Coordonnees_France.txt')

X=[]
Y=[]
for L in fichier:
    # print(L)
    L2=L.split()
    X.append(float(L2[0]))
    Y.append(float(L2[1]))
    
fichier.close()
    
plot(X,Y,color='black')
axis("equal")

## Questions suivantes

import sqlite3

cnx=sqlite3.connect("VillesFrance.s3db")
cnx.text_factory = lambda x: str(x, 'latin-1')

curseur=cnx.cursor()

# c

Req="""SELECT Longitude,Latitude FROM Villes WHERE population>10000"""
curseur.execute(Req)

for L in curseur:
    x=float(L[0])
    y=float(L[1])
    plot([x],[y],'g',marker='.')
   
# d

Req="SELECT Longitude, Latitude 
         FROM Villes JOIN Departements ON INSEEPrefecture=INSEE"
curseur.execute(Req)

for L in curseur:
    x=float(L[0])
    y=float(L[1])
    if y>0:
        plot([x],[y],'r',marker='.')   
   
# e

n=input("Donner un num�ro de d�partement : ")

Req="SELECT Longitude, Latitude FROM Villes WHERE Departement='"+n+"'"
curseur.execute(Req)

for L in curseur:
    x=float(L[0])
    y=float(L[1])
    plot([x],[y],'b',marker='.')   
     
curseur.close()
cnx.close()

show()

from matplotlib.pyplot import *
import os

os.chdir('C:\\...\\Python\\TP09\\Coordonn�es_France')

L=os.listdir()
for C in L:
    C2=C.split(".")
    if C2[-1]=='txt':
        C3=C2[0]
        if len(C3)>=19 and C3[:19]=="Coordonnees_France_":
            C4=C3[19:]
            if 1<=len(C4)<=2:
                k=int(C4)
                fichier=open(C)
                X=[]
                Y=[]
                for L in fichier:
                    L2=L.split()
                    X.append(float(L2[0]))
                    Y.append(float(L2[1]))
                fichier.close()
                    
                plot(X,Y,color='black')
axis("equal")
show()








fichier=open('Coordonnees_France_Dept.txt')

X=[]
Y=[]
for L in fichier:
    if L[0]!="C" and L[0]!="D":
        L2=L.split()
        X.append(float(L2[0]))
        Y.append(float(L2[1]))
    else:
        plot(X,Y,color='black')
        X=[]
        Y=[]
plot(X,Y,color='black')

fichier.close()
axis("equal")

## 

import sqlite3

os.chdir('..')
cnx=sqlite3.connect("VillesFrance.s3db")
cnx.text_factory = lambda x: str(x, 'latin-1')

curseur=cnx.cursor()

Req="SELECT Longitude, Latitude FROM Villes WHERE population>10000"
curseur.execute(Req)

for L in curseur:
    x=float(L[0])
    y=float(L[1])
    plot([x],[y],'g',marker='.')

Req="SELECT Longitude, Latitude 
           FROM Villes JOIN Departements ON INSEEPrefecture=INSEE"
curseur.execute(Req)

for L in curseur:
    x=float(L[0])
    y=float(L[1])
    if y>0:
        plot([x],[y],'r',marker='.')   

n=input("Donner un num�ro de d�partement : ")

Req="SELECT Longitude, Latitude FROM Villes WHERE Departement='"+n+"'"
curseur.execute(Req)

for L in curseur:
    x=float(L[0])
    y=float(L[1])
    plot([x],[y],'b',marker='.')   
     
curseur.close()
cnx.close()

show()