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)