def delete(self):
"""Deletes this exam from the database"""
where = "anno = ? and cdl = ? and insegnamento = ? and docenti = ?"
values = (self.anno, self.cdl, self.insegnamento, self.docenti)
DbManager.delete_from(table_name=self.table,
where=where,
where_args=values)
def bulk_save(cls, scrapables: list):
"""Saves multiple Scrapable objects at once in the database
Args:
scrapables: list of Scrapable objects to save
"""
if scrapables is None:
return
values = tuple(scrapable.values for scrapable in scrapables)
DbManager.insert_into(table_name=cls().table,
columns=cls().columns,
values=values,
multiple_rows=True)
def find(cls,
where_anno: str = "",
where_giorno: str = "",
where_nome: str = "") -> List['Lesson']:
"""Produces a list of lessons from the database, based on the provided parametes
Args:
where_anno: specifies the year. Defaults to "".
where_giorno: specifies the day. Defaults to "".
where_nome: specifies the name of the subject. Defaults to "".
Returns:
result of the query on the database
"""
if where_giorno:
where_giorno = f"and (giorno_settimana = {where_giorno})"
else:
where_giorno = ""
if where_anno:
where_anno = f"and (anno = {where_anno})"
else:
where_anno = ""
db_results = DbManager.select_from(
table_name=cls().table,
where=f"nome LIKE ? {where_giorno} {where_anno}",
where_args=(f'%{where_nome}%', ))
return cls._query_result_initializer(db_results)
def find(cls,
select_sessione: str = "",
where_sessione: str = "",
where_anno: str = "",
where_insegnamento: str = "") -> List['Exam']:
"""Produces a list of exams from the database, based on the provided parametes
Args:
select_sessione: which sessions to select. Defaults to "".
where_sessione: specifies what sessions can't be []. Defaults to "".
where_anno: specifies the year. Defaults to "".
where_insegnamento: specifies the subject. Defaults to "".
Returns:
result of the query on the database
"""
if not select_sessione:
select_sessione = "prima, seconda, terza, straordinaria"
if where_sessione:
where_sessione = f"and (not {where_sessione} = '[]')"
else:
where_sessione = ""
if where_anno:
where_anno = f"and (anno = '{where_anno}')"
else:
where_anno = ""
db_results = DbManager.select_from(
select=f"anno, cdl, docenti, insegnamento, {select_sessione}",
table_name=cls().table,
where=f"insegnamento LIKE ? {where_sessione} {where_anno}",
where_args=(f'%{where_insegnamento}%', ))
return cls._query_result_initializer(db_results)
def find_all(cls) -> list:
"""Finds all the scrapable objects present in the database
Returns:
list of all the scrapable objects
"""
db_results = DbManager.select_from(table_name=cls().table)
return cls._query_result_initializer(db_results)
def get_max_giorno(cls) -> int:
"""Finds the maximum value of giorno
Returns:
result of the query on the database
"""
db_results = DbManager.select_from(select="MAX(giorno) as g", table_name=cls().table)
if not db_results or db_results[0]['g'] is None:
return 0
return int(db_results[0]['g'])
def _find(cls, **kwargs) -> list:
"""Produces a list of scrapables from the database, based on the provided parametes
Returns:
result of the query on the database
"""
where = "and".join((f" {c} = ? " for c in kwargs))
values = tuple(v for v in kwargs.values())
db_results = DbManager.select_from(table_name=cls().table,
where=where,
where_args=values)
return cls._query_result_initializer(db_results)
def find(cls, where_name: str) -> List['Professor']:
"""Produces a list of professors from the database, based on the provided parametes
Args:
where_name: specifies the name and surname of the professor
Returns:
result of the query on the database
"""
db_results = DbManager.select_from(
table_name=cls().table,
where="nome LIKE ? OR cognome LIKE ?",
where_args=(f'%{where_name}%', f'%{where_name}%'))
return cls._query_result_initializer(db_results)
def find(cls, where_name: str) -> List['Professor']:
"""Produces a list of professors from the database, based on the provided parametes
Args:
where_name: specifies the name of the professor
Returns:
result of the query on the database
"""
where = " AND ".join(("nome LIKE ?" for name in where_name))
where_args = tuple(f'%{name}%' for name in where_name)
db_results = DbManager.select_from(table_name=cls().table,
where=where,
where_args=where_args)
return cls._query_result_initializer(db_results)
def count(cls,
where: str = "",
where_args: tuple = None,
group_by: str = "") -> int:
"""Count the number of scrapable objects present in the database, based on the parameters
Args:
where: where clause, with ? placeholders for the where_args. Defaults to "".
where_args: args used in the where clause. Defaults to None.
group_by: group by clause. Defaults to "".
Returns:
number of scrapable objects
"""
return DbManager.count_from(table_name=cls().table,
where=where,
where_args=where_args,
group_by=group_by)
def scrape(cls, delete: bool = False):
"""Scrapes the timetable slots of the provided year and stores them in the database
Args:
delete: whether the table contents should be deleted first. Defaults to False.
"""
timetable_slots = []
response = requests.get(read_md("aulario")).text
tables = pd.read_html(response)
for k, table in enumerate(tables):
rooms = table.iloc[:, 0]
schedule = table.iloc[:, 1:]
subjects = {}
for time in schedule:
for i, row in enumerate(table[time]):
if time[-1] == "1":
time = time[:3] + "30"
if not pd.isnull(row):
r = row[:20] + rooms[i]
if not r in subjects:
subjects[r] = cls(nome=row.replace('[]', '').replace('[', '(').replace(']', ')'),
giorno=k,
ora_inizio=time,
ora_fine=time,
aula=rooms[i])
else:
subjects[r].ora_fine = time
timetable_slots.extend(subjects.values())
if delete:
cls.delete_all()
offset = DbManager.count_from(table_name=cls().table) # number of rows already present
for i, timetable_slot in enumerate(timetable_slots):
timetable_slot.ID = i + offset # generate the ID of the timetable slot based on its position in the array
cls.bulk_save(timetable_slots)
logger.info("Aulario loaded.")
def delete_all(cls):
"""Deletes all the scrapable objects of this kind from the database"""
DbManager.delete_from(table_name=cls().table)
def delete(self):
"""Deletes this scrapable object from the database"""
where = " = ? and ".join(self.columns) + " = ?"
DbManager.delete_from(table_name=self.table,
where=where,
where_args=self.values)
def save(self):
"""Saves this scrapable object in the database"""
DbManager.insert_into(table_name=self.table,
columns=self.columns,
values=self.values)