def antarctica_analysis(self, csv_file): try: all_db_patients = self.select_all_tasks("patients") with open(csv_file, encoding="utf-8-sig") as an_file: all_rows = csv.reader(an_file, delimiter=';') for one_row in all_rows: #print(one_row) for one_patient in all_db_patients: #print(type(one_row[2]), one_row[2], type(one_patient[3]), one_patient[3]) if one_row[0] != 'id': if one_row[2] == str(one_patient[3]): #porównuje pesele aby dostać id one_row[4] = str(datetime.strptime(one_row[4], "%d.%m.%Y %H:%M")) if one_row[5] == 'T': one_row[5] = 'True' else: one_row[5] = 'False' self.insert_analysis( probe_number=one_row[1], analysis_id=one_row[3], patient_id=one_patient[0], #odnosimy się do bazy danych do tablicy patients i wyciągamy id collection_time=one_row[4], result=one_row[5] ) if one_row[5] == "False": week_days = ["Poniedzialek", "Wtorek", "Środa", "Czwartek", "Piątek", "Sobota", "Niedziela"] obj_datetime = datetime.strptime(one_row[6], "%d.%m.%Y %H:%M") week_number = obj_datetime.weekday() msg = f'Dzień dobry, ' \ f'Zapraszamy na wizytę kontrolną w {week_days[week_number]} dokładna data ' \ f'{one_row[6]} o godzinie {obj_datetime.time()}' send_sms(msg, one_row[7]) except Exception as e: logger.error("Antarctica analysis failed ", exc_info=True)
def close_conn(self): try: self.conn.close() #AttributeError: 'NoneType' object has no attribute 'close' except AttributeError as e: logger.error( f"Can not close connection because connection was not established: {e}" )
def select_all_tasks(self, table_name): try: conn = self.connect() cur = conn.cursor() cur.execute(f"SELECT * FROM {table_name}") rows = cur.fetchall() return rows except Exception as e: logger.error(f"Can not fetch data from {table_name} \nError {e}")
def select_all_tasks(self, table_name): try: #conn = self.connect() cur = self.conn.cursor() cur.execute(f"SELECT * FROM {table_name}") rows = cur.fetchall() return rows except Exception as e: logger.error(f"Can not fetch database {table_name} data \n" + str(e))
def execute_sql(self, sql, params=None): if params is None: params = [] conn = self.connect() if conn: try: cur = self.conn.cursor() cur.execute(sql, params) self.conn.commit() except Exception as e: logger.error(f"Błąd {e}")
def execute_sql(self, sql, params=None): if params is None: params = [] if self.conn: try: cur = self.conn.cursor() cur.execute(sql, params) self.conn.commit() except Exception as e: logger.error( f"Can not execute sql: \n{str(sql)}\nError details: {e}")
def insert_analysis(self, probe_number, analysis_id,patient_id, collection_time, result): sql = """ INSERT INTO analysis (probe_number, analysis_id, patient_id, collection_time, result) VALUES (?,?,?,?,?) """ analysis_data = [probe_number, analysis_id,patient_id, collection_time, result] try: super().__init__(self.db_name) super().execute_sql(sql, analysis_data) except Exception as e: logger.error(f"Can not insert new analysis probe_number {probe_number} {e}") exit()
def insert_new_patient(self, id, name, surname, pesel): sql_insert = """ INSERT INTO patients (id, name, surname, pesel) VALUES (?,?,?,?) """ patient_data = [id, name, surname, pesel] try: #super().__init__(self.db_name) super().execute_sql(sql_insert, patient_data) except Exception as e: logger.error("Can not insert patient data: " + str(e)) exit()
def diagnostica_analysis(self): try: with open(r"C:\Users\Kasia\PycharmProjects\W1_cwiczenia\Project\Diagnostica.csv.csv", encoding='UTF8') as csv_file: all_row = csv.reader(csv_file, delimiter=',') for row in all_row: #print(row) if row[1] == 'blood': row[1] = '1' if row[3] != 'collection_time': row[3] = str(datetime.strptime(row[3], "%d.%m.%Y %H:%M")) if row[0] != "external_patient_id": # opuszczamy 1 wiersz self.insert_analysis(probe_number=row[2], analysis_id=row[1], patient_id=row[0], collection_time=row[3], result=row[4]) except Exception as e: logger.error("Diagnostica analysis failed", exc_info=True)
def insert_new_patient(self, id, name, surname, pesel): sql = """ INSERT INTO patients (id, name, surname, pesel) VALUES (?,?,?,?) """ patient_data = [id, name, surname, pesel] #ustawi parametry w odpowiedniej kolejności try: super().__init__(self.db_name) super().execute_sql(sql, patient_data) except Exception as e: logger.error(f"Can not insert patient data {e}") exit()
def create_analysis_table(self): sql_create_analysis = """ CREATE TABLE IF NOT EXISTS analysis( probe_number integer PRIMARY KEY, analysis_id integer, patient_id integer, collection time timestamp without time zone, result text) ; """ try: super().__init__(self.db_name) super().execute_sql(sql_create_analysis) except Exception as e: logger.error(f"Can not create table analysis {e}")
def create_analysis_table(self): sql_create_table = f""" CREATE TABLE IF NOT EXISTS analysisss( probe_number integer PRIMARY KEY, analysis_id integer, patient_id integer, collection_time timestamp without time zone, result text ) ; """ try: #super().__init__(self.db_name) #obiekt klasy Database super().execute_sql(sql_create_table) except Exception as e: logger.error("Can not create table analysis: " + str(e))
def wrapper(**kwargs): tries = kwargs['tries'] delay = kwargs['delay'] db_name = kwargs['db_name'] for i in range(int(tries)): logger.info(f'It is y {i} try to connect') conn = None if os.path.isfile(db_name): try: conn = sqlite3.connect(db_name) except Exception as e: logger.error(f' The {i} attempt failed', exc_info=True) time.sleep(delay) else: logger.error return conn else: logger.error(f'No database {db_name}')
def wrapper(**kwargs): tries = kwargs['tries'] delay = kwargs['delay'] db_name = kwargs['db_name'] for i in range(int(tries)): logger.info(f"It is my {i+1} try to connect") conn = None if os.path.isfile( db_name ): #tutaj sprawdzenie czy jest taki plik bo wcześniej nam tworzył a tego nie chcemy try: conn = sqlite3.connect(db_name, timeout=20) except Exception as e: logger.error(f"The {i+1} attempt failed", exc_info=True) time.sleep(delay) else: logger.info(f"Connection established") return conn else: logger.error(f"No database {db_name}")
def hospital_analysis(self, csv_path): try: with open(csv_path, encoding="UTF8") as csv_hospital: all_rows = csv.reader(csv_hospital, delimiter=',') for one_row in all_rows: if one_row[0] != 'external_patient_id': one_row[2] = str( datetime.strptime(one_row[2], "%d.%m.%Y %H:%M")) if one_row[4] == 'blood': one_row[4] = '1' if one_row[3] == 'T': one_row[3] = 'True' else: one_row[3] = "False" self.insert_analysis(probe_number=one_row[1], analysis_id=one_row[4], patient_id=one_row[0], collection_time=one_row[2], result=one_row[3]) except Exception as e: logger.error("Hospital analysis failed", exc_info=True)
def antarctica_analysis(self, csv_antarctica_path): try: with open(csv_antarctica_path, encoding="UTF-8-SIG") as csv_antarctica: all_row = csv.reader(csv_antarctica, delimiter=';') all_row_db = self.select_all_tasks("patients") week_days = [ "Poniedzialek", "Wtorek", "Środa", "Czwartek", "Piątek", "Sobota", "Niedziela" ] for row in all_row: for row_db in all_row_db: if row[2] == str(row_db[3]): if row[4] != 'collection_time': row[4] = str( datetime.strptime(row[4], "%d.%m.%Y %H:%M")) if row[5] == 'T': row[5] = 'True' if row[5] == 'F': row[5] = 'False' if row[0] != "id": # opuszczamy 1 wiersz self.insert_analysis(probe_number=row[1], analysis_id=row[3], patient_id=row_db[0], collection_time=row[4], result=row[5]) if row[5] == 'False': obj_date = datetime.strptime( row[6], "%d.%m.%Y %H:%M") week_num = obj_date.weekday() send_sms( f'Zapraszamy na wizyte kontrolna w {week_days[week_num]} dokładna data {obj_date.date()} ' f'o godzinie {obj_date.time()}', row[7]) except Exception as e: logger.error("Antarctica analysis failed", exc_info=True)
def close_conn(self): try: self.conn.close() except AttributeError as e: logger.error('No connection')