def run(): """Function allowing you to insert the equipment table data in the database""" tableau = equip.run() db, curseur =co.run() addEquipment= "INSERT OR IGNORE INTO equipment (id_equipment, nom_equipment, id_installation) VALUES(?,?,?)" for row in tableau: data= (row[0], row[1], row[2]) curseur.execute(addEquipment, data) db.commit() db.close()
def run(): """Function allowing you to insert the installation table data in the database""" tableau = inst.run() db, curseur = co.run() add_install= "INSERT OR IGNORE INTO installation (id_installation, nom_installation, adresse, code_postal, ville, latitude, longitude) VALUES(?,?,?,?,?,?,?)" for row in tableau: data= (row[0], row[1], row[2], row[3],row[4], row[5],row[6]) curseur.execute(add_install, data) db.commit() db.close()
def run(): """Function allowing you to insert the activity table data in the database""" tableau = act.run() db, curseur =co.run() add_act= "INSERT OR IGNORE INTO activity(id_activity, nom_activity) VALUES(?,?)" for row in tableau: data= (row[0], row[1]) curseur.execute(add_act, data) db.commit() db.close()
def activites(ville):
"""Function allowing you to collect data from the table activity of the database for a given city and then parse it into json arrays and finally returning it"""
db, curseur = co.run()
curseur.execute("select * from activity")
activity = curseur.fetchall()
list_activity = []
for row in activity:
d = collections.OrderedDict()
d['id_activity'] = row[0]
d['nom_activity'] = row[1]
list_activity.append(d)
activity = json.dumps(list_activity)
activity = json.loads(activity)
return activity
def run():
"""Function allowing you to create the tables in the database"""
db, curseur = co.run()
curseur.execute("DROP TABLE IF EXISTS installation")
cr_tb_install = "CREATE TABLE IF NOT EXISTS installation(id_installation INT, nom_installation TEXT PRIMARY KEY, adresse TEXT, code_postal TEXT, ville TEXT, latitude FLOAT, longitude FLOAT)"
curseur.execute("DROP TABLE IF EXISTS equipment")
cr_tb_equip = "CREATE TABLE IF NOT EXISTS equipment(id_equipment INT PRIMARY KEY , nom_equipment TEXT, id_installation INT)"
curseur.execute("DROP TABLE IF EXISTS activity")
cr_tb_act = "CREATE TABLE IF NOT EXISTS activity(id_activity INT PRIMARY KEY, nom_activity TEXT)"
curseur.execute("DROP TABLE IF EXISTS equipment_activity")
cr_tb_equip_act = "CREATE TABLE IF NOT EXISTS equipment_activity(id_equipment INT, id_activity INT, PRIMARY KEY (id_equipment, id_activity) FOREIGN KEY(id_equipment) REFERENCES equipment(id),FOREIGN KEY(id_activity) REFERENCES activity(id))"
curseur.execute(cr_tb_install)
curseur.execute(cr_tb_equip)
curseur.execute(cr_tb_act)
curseur.execute(cr_tb_equip_act)
db.commit()
db.close()
def run():
"""Function allowing you to collect data from the tables of the database and then parse it into json arrays and finally returning it"""
db, curseur = co.run()
curseur.execute("select * from activity")
activity = curseur.fetchall()
curseur.execute("select * from equipment")
equipment = curseur.fetchall()
curseur.execute("select * from installation")
installation = curseur.fetchall()
curseur.execute("select * from equipment_activity")
equipment_activity = curseur.fetchall()
list_activity = []
for row in activity:
d = collections.OrderedDict()
d['id_activity'] = row[0]
d['nom_activity'] = row[1]
list_activity.append(d)
list_equipment = []
for row in equipment:
d = collections.OrderedDict()
d['id_equipment'] = row[0]
d['nom_equipment'] = row[1]
d['id_installation'] = row[2]
list_equipment.append(d)
list_installation = []
for row in installation:
d = collections.OrderedDict()
d['id_installation'] = row[0]
d['nom_installation'] = row[1]
d['adresse'] = row[2]
d['code_postal'] = row[3]
d['ville'] = row[4]
d['latitude'] = row[5]
d['longitude'] = row[6]
list_installation.append(d)
list_equipment_activity = []
for row in equipment_activity:
d = collections.OrderedDict()
d['id_equipment'] = row[0]
d['id_activity'] = row[1]
list_equipment_activity.append(d)
activity = json.dumps(list_activity)
equipment = json.dumps(list_equipment)
installation = json.dumps(list_installation)
equipment_activity = json.dumps(list_equipment_activity)
activity = json.loads(activity)
equipment = json.loads(equipment)
installation = json.loads(installation)
equipment_activity = json.loads(equipment_activity)
return (activity, equipment, installation, equipment_activity)