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'