def readEquipment(self):
"""
Read one equipment and insert it in the database
"""
data = json.load(self.path)
for row in data["data"]:
self.result.append(
Equipment(row["EquipementId"], row["EquNom"],
row["InsNumeroInstall"]))
def read_Equipments(self):
"""
Reading of the entire installation table
"""
c = self.conn.cursor()
c.execute('SELECT * FROM equipement')
result = c.fetchall()
equipements = []
for e in result:
equipements.append(Equipment(e[0], e[1], e[2]))
return equipements
def read_equipments(self):
"""
Reads all the equipments from the 'equipements' table
"""
c = self.conn.cursor()
c.execute('SELECT * FROM equipements')
rows = c.fetchall()
equipments = []
for row in rows:
equipments.append(Equipment(row[0], row[1], row[2]))
return equipments
def read_One_Equipment(self, number):
"""
Read the entry of the given number
"""
c = self.conn.cursor()
c.execute('SELECT * FROM equipement WHERE number = :givenNumber',
{'givenNumber': number})
result = c.fetchone()
try:
equipement = Equipment(result[0], result[1], result[2])
except:
return "Il n'y a pas d'équipement qui possède ce numéro"
return equipement
def read_equipment(self, number):
"""
Reads the equipment which has the given number from the 'equipments' table
"""
c = self.conn.cursor()
c.execute('SELECT * FROM equipements WHERE numero = :numero',
{'numero': number})
row = c.fetchone()
try:
equipment = Equipment(row[0], row[1], row[2])
except TypeError:
return '<h2>Aucun équipement ne correspond à ce numéro</h2>'
return equipment
def read_Informations(self, activity, city):
"""
Read information about a sport in a city
"""
c = self.conn.cursor()
#c.execute("""SELECT i.number, i.name, e.number, e.name, a.number, a.name FROM installation i JOIN equipement e ON i.number = e.installationNumber JOIN equipement_activite ea ON e.number = ea.number_equipment JOIN activite a ON ea.number_activity = a.number WHERE i.city = ' + city + ' AND a.name LIKE '% + activity + %'""")
c.execute(
"SELECT DISTINCT i.number, i.name, e.number, e.name, a.number, a.name FROM installation i, equipement e, equipement_activite ea, activite a WHERE i.number = e.installationNumber AND e.number = ea.number_equipment AND ea.number_activity = a.number AND i.city = ' + city + ' AND a.name LIKE '% + activity + %'"
"")
result = c.fetchall()
query = []
for r in result:
query.append(Installation(r[0], r[1]), Equipment(r[0], r[1]),
Activity(r[0], r[1]))
print(query)
return query
def get_infos(self, activity_name, city):
"""
Gives all the installations, equipments and activities which match the given activity and city
"""
c = self.conn.cursor()
c.execute("""SELECT i.numero, i.nom, e.numero, e.nom, a.numero, a.nom
FROM installations i
JOIN equipements e ON i.numero = e.numero_installation
JOIN equipements_activites ea ON e.numero = ea.numero_equipement
JOIN activites a ON ea.numero_activite = a.numero
WHERE LOWER(i.ville) = LOWER('""" + city + """') AND a.nom LIKE '%""" +
activity_name + """%'""")
rows = c.fetchall()
results = []
for row in rows:
results.append(
(Installation(row[0],
row[1]), Equipment(row[2], row[3], row[0]),
Activity(row[4], row[5], row[2])))
return results
def read_equipments(self, path):
"""
Reads a JSON file which contains data for the equipments
>>> reader = ReadJSON()
>>> equipments = reader.read_equipments("data/test_equipements.json")
>>> (equipments[0].number, equipments[0].name, equipments[0].installation_number)
('225442', "Carrière d'été", '490260005')
"""
file = open(path)
data = json.load(file)
result = []
pbar = ProgressBar(
widgets=['Reading equipments JSON: ',
Percentage(), ' ',
ETA()])
for line in pbar(data["data"]):
result.append(
Equipment(line["EquipementId"], line["EquNom"],
line["InsNumeroInstall"]))
return result
def migrate_equipment():
try:
cnx = mysql.connector.connect(
host=self.host,
user=self.username,
password=self.password,
database=self.database,
)
cursor = cnx.cursor()
query = "SELECT * FROM equipement"
cursor.execute(query)
for (id, name, status, sn, secret, extra, company, category,
notification, calibrated, createdat, creator) in cursor:
u = Equipment()
u.id = id
u.category = category
u.name = name
u.notification_on = notification
u.status = status
u.serial_number = sn
u.secret = secret
u.extra = extra
u.company = company
u.calibrated_on = calibrated
u.created_by = 10
u.created_on = createdat
db.session.add(u)
db.session.commit()
cursor.close()
cnx.close()
return "done"
except Exception as e:
return str(e), 500