def db_close(db): try: db.close() except BaseException as inst: print(inst) else: print("{now} Database Closed!".format( now=ft("%Y-%m-%d %H:%M:%S", lt(time()))))
def db_connect(): try: pmt = ["localhost", "Jinke", "000000", "project", "utf8"] db = pms.connect(host=pmt[0], user=pmt[1], passwd=pmt[2], db=pmt[3], charset=pmt[4]) except BaseException as inst: print(inst) else: print("{} Database Connected!".format( ft("%Y-%m-%d %H:%M:%S", lt(time())))) return db
def db_query(db, ds, cs): global one_day if cs > 6: cmp = '>' elif cs == 6: cmp = '=' else: cmp = '<' try: cursor = db.cursor() sql = \ ''' ( SELECT DATE(createtime) create_time, COUNT(*) count FROM cases{year} WHERE hospitalid = 'H10000107' AND casestate {cmp_char} 6 AND {now_days} - TO_DAYS(createtime) <= {days} - 1 GROUP BY create_time ) UNION ( SELECT DATE(createtime) create_time, COUNT(*) count FROM cases{laseyear} WHERE hospitalid = 'H10000107' AND casestate {cmp_char} 6 AND {now_days} - TO_DAYS(createtime) <= {days} - 1 GROUP BY create_time ) ORDER BY create_time '''.format(year=date.today().year, laseyear=date.today().year - 1, days=ds, now_days=365 + date.today().toordinal(), cmp_char=cmp) cursor.execute(sql) result = cursor.fetchall() t_file = open( "./day_{days}_state_{casestate}.json".format(days=ds, casestate=cs), 'w') t_file_7 = open("./day_7_state_{casestate}.json".format(casestate=cs), 'w') t_file_3 = open("./day_3_state_{casestate}.json".format(casestate=cs), 'w') except BaseException as inst: print(inst) else: data = [] # t_file.write("\n".join(map(lambda x: "\t".join(map(str, x)), result))) pos = 0 length = len(result) for i in range(ds - 1, -1, -1): if pos >= length - 1 or result[pos][0] != date.today() - timedelta( i): data.append([ (date.today() - timedelta(i)).strftime('%B %d,%Y'), 0 ]) else: data.append( [result[pos][0].strftime('%B %d,%Y'), result[pos][1]]) pos += 1 # data.reverse() data_str = json.dumps(data) t_file.write(data_str) t_file.flush() t_file.close() l = len(data) data_str = json.dumps(data[l - 7:l]) t_file_7.write(data_str) t_file_7.flush() t_file_7.close() data_str = json.dumps(data[l - 3:l]) t_file_3.write(data_str) t_file_3.flush() t_file_3.close() one_day.append(data[l - 1][1]) print("{now} state_{state} Query Successfully!".format(now=ft( "%Y-%m-%d %H:%M:%S", lt(time())), state=cs))