def clear_db():
'''
Purge entièrement la base de données
'''
if os.path.exists(DB_FULLPATH):
try:
printerr('starting database emptying')
conn = sqlite3.connect(DB_FULLPATH)
cursor = conn.cursor()
cursor.execute("""
DROP TABLE IF EXISTS installation;
""")
cursor.execute("""
DROP TABLE IF EXISTS adresse;
""")
cursor.execute("""
DROP TABLE IF EXISTS equipements;
""")
cursor.execute("""
DROP TABLE IF EXISTS activites;
""")
conn.commit()
printerr('database emptied successfully')
except Exception as e:
conn.rollback()
printerr('exception occurred while emptying database')
printex(e)
finally:
conn.close()
def i_get_object_by_id(i_id=-1):
'''
Retourne un set des instllations existantes en base
'''
installations = set()
try:
conn = sqlite3.connect(DB_FULLPATH)
cur = conn.cursor()
if (i_id == -1): # toutes
cur.execute(
"""SELECT i.numero, i.nom, a.adresse, a.code_postal, a.ville FROM installation i, adresse a WHERE i.numero=a.numero"""
)
else: # via id
cur.execute(
"""SELECT i.numero, i.nom, a.adresse, a.code_postal, a.ville FROM installation i, adresse a WHERE i.numero=a.numero AND i.numero=?""",
[i_id])
rows = cur.fetchall()
for row in rows:
installations.add(
Installation(row[0], row[1], row[2], row[3], row[4]))
except Exception as e:
printex(e)
finally:
conn.close()
return installations
def a_get_nums(nom):
'''
Récupère un set des `numero_equipements` en connaissant le `nom` exact d'une activité
'''
nums_equips = set()
try:
conn = sqlite3.connect(DB_FULLPATH)
cur = conn.cursor()
cur.execute("""SELECT a.numero_equipements FROM activite a WHERE a.nom=?""", [nom])
nums_equips = cur.fetchall()
except Exception as e:
printex(e)
finally:
conn.close()
return nums_equips
def cp_and_v_getall():
'''
Retourne un set des codes postaux existants en base
'''
cp_and_v = set()
try:
conn = sqlite3.connect(DB_FULLPATH)
cur = conn.cursor()
cur.execute("""SELECT code_postal,ville FROM adresse""")
for v in cur.fetchall():
cp_and_v.add(v)
except Exception as e:
printex(e)
finally:
conn.close()
return cp_and_v
def v_getall():
'''
Retourne un set des villes existantes en base
'''
villes = set()
try:
conn = sqlite3.connect(DB_FULLPATH)
cur = conn.cursor()
cur.execute("""SELECT ville FROM adresse""")
for v in cur.fetchall():
villes.add(v)
except Exception as e:
printex(e)
finally:
conn.close()
return villes
def a_get_object_by_num_act(num_act):
'''
Retourne l'ensemble des activitées ayant le num_act == numero_activite
'''
activites = set()
try:
conn = sqlite3.connect(DB_FULLPATH)
cur = conn.cursor()
cur.execute("""SELECT a.id, a.nom, a.numero_activites, a.numero_equipements, a.desc_act FROM activite a WHERE a.numero_activites=?""", [num_act])
rows = cur.fetchall()
for row in rows:
activites.add(Activite(row[0], row[1], row[2], row[3], row[4]))
except Exception as e:
printex(e)
finally:
conn.close()
return activites
def cp_getville(cp):
'''
Retourne les villes correspondant à un code postal
'''
villes = set()
try:
conn = sqlite3.connect(DB_FULLPATH)
cur = conn.cursor()
cur.execute("""SELECT ville FROM adresse AS a WHERE a.code_postal=?""",
[cp])
for v in cur.fetchall():
villes.add(v)
except Exception as e:
printex(e)
finally:
conn.close()
return villes
def i_get_object_by_cp(code_postal):
'''
Retourne les installations correspondant à un code postal donné
'''
installations = set()
try:
conn = sqlite3.connect(DB_FULLPATH)
cur = conn.cursor()
cur.execute(
"""SELECT i.numero, i.nom, a.adresse, a.code_postal, a.ville FROM installation AS i, adresse AS a WHERE i.numero=a.numero AND a.code_postal=?""",
[code_postal])
rows = cur.fetchall()
for row in rows:
installations.add(
Installation(row[0], row[1], row[2], row[3], row[4]))
except Exception as e:
printex(e)
finally:
conn.close()
return installations
def a_get_object_by_id(a_id = -1):
'''
Retourne l'activite avec l'id passé en param ou si id == -1 retourne l'ensemble des activites contenus dans la base de données sous forme d'objets Activite
'''
activites = set()
try:
conn = sqlite3.connect(DB_FULLPATH)
cur = conn.cursor()
if(a_id == -1): # toutes
cur.execute("""SELECT a.id, a.nom, a.numero_activites, a.numero_equipements, a.desc_act FROM activite a""")
else: # via id
cur.execute("""SELECT a.id, a.nom, a.numero_activites, a.numero_equipements, a.desc_act FROM activite a WHERE a.id=?""", [a_id])
rows = cur.fetchall()
for row in rows:
activites.add(Activite(row[0], row[1], row[2], row[3], row[4]))
except Exception as e:
printex(e)
finally:
conn.close()
return activites
def create_db():
'''
Crée la base de données
'''
if not os.path.exists(DB_FULLPATH):
path1 = PROJECT_ROOT + os.path.sep + 'data'
# on crée le dossier s'il n'existe pas
if not os.path.exists(path1):
os.mkdir(path1)
printerr('directory created : "' + path1 + '"')
# on crée le dossier s'il n'existe pas
if not os.path.exists(DB_DIR):
os.mkdir(DB_DIR)
printerr('directory created : "' + path1 + '"')
try:
printerr('starting database creation')
conn = sqlite3.connect(DB_FULLPATH)
cursor = conn.cursor()
#numero== id instal
cursor.execute("""CREATE TABLE IF NOT EXISTS installation(
numero INTEGER PRIMARY KEY UNIQUE,
nom TEXT
)
""")
#numero == a numero dans installation
cursor.execute("""CREATE TABLE IF NOT EXISTS adresse(
numero INTEGER PRIMARY KEY UNIQUE,
adresse TEXT,
code_postal INTEGER,
ville TEXT,
FOREIGN KEY (numero) REFERENCES installation(numero)
)
""")
#numero_equipements==EquipementId
cursor.execute("""CREATE TABLE IF NOT EXISTS equipement(
numero_equipements INTEGER PRIMARY KEY UNIQUE,
nom TEXT,
numero_installation INTEGER,
latitude REAL,
longitude REAL,
FOREIGN KEY (numero_installation) REFERENCES installation(numero)
)
""")
cursor.execute("""CREATE TABLE IF NOT EXISTS activite(
id INTEGER PRIMARY KEY,
numero_activites INTEGER,
numero_equipements INTEGER,
desc_act TEXT,
nom TEXT,
FOREIGN KEY (numero_equipements) REFERENCES equipements(numero_equipements)
)
""")
conn.commit()
printerr('table "adresse" successfully created')
printerr('table "installation" successfully created')
printerr('table "equipement" successfully created')
printerr('table "activite" successfully created')
printerr('database successfully created')
except Exception as e:
printerr('exception ocurred while creating database')
printex(e)
conn.rollback()
finally:
conn.close()