def completionRequest(param):
conn = connection()
cursor = conn.cursor()
ask = formalizeStr(param)
cursor.execute("SELECT DISTINCT ville FROM installations WHERE ville LIKE '"+ask+"%' LIMIT 10;")
ville = cursor.fetchall()
cursor.execute("SELECT DISTINCT nom FROM activitee WHERE nom LIKE '"+ask+"%' LIMIT 10;")
act = cursor.fetchall()
cities = []
for row in ville:
cities.append(row[0])
activities = []
for row in act:
activities.append(row[0])
cities.sort()
activities.sort()
result = []
result.append(cities)
result.append(activities)
return result
def searchRequest(param):
conn = connection()
cursor = conn.cursor()
rep = formalizeStr(param)
recher = rep.split() #on transforme la chaine de caractère en tableau de caractère pour pouvoir traiter chaque élément
idAct = None
instal = set()
for r in recher:
cursor.execute("SELECT DISTINCT ID FROM activitee WHERE nom LIKE '"+r+"%';")
tmp = cursor.fetchone()
if(tmp):
idAct = tmp[0]
break;
cursor.execute("SELECT DISTINCT id_equ FROM equipement_activitee WHERE id_act="+str(idAct)+";")
idEqu = cursor.fetchall()
numInstall = []
for i in idEqu:
cursor.execute("SELECT DISTINCT num_install FROM equipement WHERE ID="+str(i[0])+";")
numInstall.append(cursor.fetchone()[0])
for t in recher:
cursor.execute("SELECT DISTINCT * FROM installations WHERE ville LIKE '"+t+"%';")
if(cursor.fetchall()):
for n in numInstall:
cursor.execute("SELECT DISTINCT nom, adresse, code_postal, ville FROM installations WHERE ID="+str(n)+" and ville LIKE '"+t+"%';")
result = cursor.fetchall()
if(result):
for row in result:
instal.add(row)
return list(instal)
def importall():
conn = connection()
Installation(conn, csv.reader(file(dir_path+'/../csv/installations.csv')) )
Equipement(conn, csv.reader(file(dir_path+'/../csv/equipements.csv')) )
Activity(conn, csv.reader(file(dir_path+'/../csv/activites.csv')) )
Act_Equ(conn, csv.reader(file(dir_path+'/../csv/activites.csv')) )
def purge():
conn = connection()
cursor = conn.cursor()
############################################################
# Vidange des tables ^^
cursor.execute('DROP TABLE equipement_activitee')
cursor.execute('DROP TABLE activitee')
cursor.execute('DROP TABLE equipement')
cursor.execute('DROP TABLE installations')
cursor.execute("CREATE TABLE IF NOT EXISTS `activitee` (`ID` int(11) NOT NULL,`nom` varchar(256) NOT NULL,PRIMARY KEY (`ID`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;")
cursor.execute("CREATE TABLE IF NOT EXISTS `equipement` (`ID` int(11) NOT NULL,`nom` varchar(256) NOT NULL,`num_install` int(11) NOT NULL,PRIMARY KEY (`ID`),KEY `num_install` (`num_install`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;")
cursor.execute("CREATE TABLE IF NOT EXISTS `equipement_activitee` (`id_equ` int(11) NOT NULL,`id_act` int(11) NOT NULL,PRIMARY KEY (`id_equ`,`id_act`),KEY `id_act` (`id_act`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;")
cursor.execute("CREATE TABLE IF NOT EXISTS `installations` (`ID` int(11) NOT NULL,`nom` varchar(256) NOT NULL,`adresse` varchar(256) NOT NULL,`code_postal` varchar(10) NOT NULL,`ville` varchar(40) NOT NULL,`latitude` float NOT NULL,`longitude` float NOT NULL,PRIMARY KEY (`ID`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;")
conn.commit()
cursor.close()
print 'Done Vidange'
