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')
