def create_backup(): connection = get_connection() if not connection or not connection.is_connected(): db_log.error('Brak polaczenia z baza.', dict(connection_info=str(connection))) return 99 db_log.info('Tworzenie kopii rozpoczete') (filename, ctime) = gen_filename(config.BACK_DBNAME) command = 'mysqldump -u {} -p{} -h {} {} > backups/{}' \ .format(config.BACK_DBUSER, config.BACK_DBPASS, config.BACK_DBHOST, config.BACK_DBNAME, filename) start = datetime.datetime.now() result = os.system(command) end = datetime.datetime.now() diff = end - start size = os.path.getsize('backups/' + filename) / 1024.0 / 1024.0 if result == 0: metrics = dict(command=command, filename=filename, elapsedTime=diff.total_seconds(), filesize='{0:.3f}'.format(round(size, 3))) db_log.info('Wykonano zrzut bazy', metrics) back_info = Backup(filename, ctime, result) db_session.add(back_info) db_session.commit() else: db_log.error(u'Błąd przy wykonywaniu zrzutu.', dict(command=command, errCode=result)) return result
def update_schedule(schedule): session = DBSession() try: session.query(Schedule).filter_by(date=schedule.date, user_id=schedule.user_id).update({"end_time": schedule.end_time, "modify": schedule.modify}) session.commit() except Exception as e: error('error at update_schedule object') error('error info: %s' % str(e)) session.rollback() finally: session.close()
def insert_object(object): session = DBSession() try: session.add(object) session.commit() except Exception as e: error('error at inserting object') error('error info: %s' % str(e)) session.rollback() finally: session.close()
def delete_token(token): session = DBSession() try: session.delete(token) session.commit() except Exception as e: db_log.error('error at delete_token object') db_log.error('error info: %s' % str(e)) session.rollback() finally: session.close()
def update_user(face_id, avatar): session = DBSession() try: session.query(User).filter_by(face_id=face_id).update({"avatar": avatar}) session.commit() except Exception as e: error('error at update_schedule object') error('error info: %s' % str(e)) session.rollback() finally: session.close()
def check_schedule(date, user_id): session = DBSession() try: records = session.query(Schedule).filter_by(date=date, user_id=user_id).all() except Exception as e: error('error info: %s' % str(e)) error('error at check_user_by_date_and_user with date: {0}; user_id: {1}'.format(date, user_id)) records = [] finally: session.close() return records
def insert_list_object(list_object): session = DBSession() try: session.bulk_save_objects(list_object) session.commit() except Exception as e: error('error at inserting %d objects' % len(list_object)) error('error info: %s' % str(e)) session.rollback() finally: session.close()
def get_schedule_by_date(date): session = DBSession() try: query = session.query(db_model.LogCheck).filter_by(date=date) records = query.all() return records except Exception as e: db_log.error('error info: %s' % str(e)) db_log.error('error at get_schedule_by_date with date: %s' % date) return {} finally: session.close()
def get_user_by_id(user_id): session = DBSession() try: query = session.query(db_model.User).filter_by(user_id=user_id) records = query.all() return records except Exception as e: db_log.error('error info: %s' % str(e)) db_log.error('error at get_user_by_id id of user: %s' % user_id) return {} finally: session.close()
def update_token(token_id, user, revoked): session = DBSession() try: session.query(db_model.TokenBlacklist).filter_by( id=token_id, user_identity=user).update({"revoked": revoked}) session.commit() except Exception as e: db_log.error('error at update_token object') db_log.error('error info: %s' % str(e)) session.rollback() finally: session.close()
def get_connection(): ping_response = subprocess.Popen(["ping", config.BACK_DBHOST, "-c", '1'], stdout=subprocess.PIPE).stdout.read() if 'unreachable' in ping_response: return None try: connection = mysql.connector.connect(user=config.BACK_DBUSER, password=config.BACK_DBPASS, host=config.BACK_DBHOST) return connection except mysql.connector.Error as err: db_log.error('Blad polaczenia z baza.', dict(error=err.errno))
def check_token(decoded_token): jti = decoded_token['jti'] session = DBSession() try: record = session.query( db_model.TokenBlacklist).filter_by(jti=jti).one() except Exception as e: db_log.error('error info: %s' % str(e)) db_log.error('error at check_token with jti: {0}'.format(jti)) return False finally: session.close() return record.revoked
def get_token(access_token): decoded_token = decode_token(access_token, allow_expired=True) jti = decoded_token['jti'] session = DBSession() try: records = session.query( db_model.TokenBlacklist).filter_by(jti=jti).all() except Exception as e: db_log.error('error info: %s' % str(e)) db_log.error('error at get_token') records = [] finally: session.close() return records
def update_adj_price_data(sec_id, date, close, price_open, high, low): session = DBSession() try: session.query(HistoricalData) \ .filter(HistoricalData.sec_id == sec_id) \ .filter(HistoricalData.date == date). \ update({"adj_close": close, "adj_open": price_open, "adj_high": high, "adj_low": low}) session.commit() except Exception as e: error('error at update_adj_price_data object') error('error info: %s' % str(e)) session.rollback() finally: session.close()
def update_admin(date): session = DBSession() try: records = session.query(db_model.Schedule)\ .filter(db_model.Schedule.date == date).all() for record in records: user_id = record.user_id date = record.date start_time = record.start_time end_time = record.end_time url_image = record.url_image records_formtime_in = session.query(db_model.FormTime)\ .filter(db_model.FormTime.start < start_time)\ .filter(db_model.FormTime.end > start_time).all() if len(records_formtime_in) == 0: money = -1 else: money = records_formtime_in[0].money records_formtime_out = session.query(db_model.FormTime) \ .filter(db_model.FormTime.id == 6).all() if len(records_formtime_out) == 0: out_early = -1 else: duration = datetime.datetime.combine( datetime.date.min, records_formtime_out[0].start) - datetime.datetime.combine( datetime.date.min, end_time) if duration.total_seconds() < 0: out_early = 0 else: out_early = duration.total_seconds() / 60 records_ot = session.query(db_model.OT) \ .filter(db_model.OT.user_id == user_id) \ .filter(db_model.OT.date_ot == date).all() if len(records_ot) == 0: ot_id = -1 else: ot_id = records_ot[0].id adminSchedule = db_model.AdminSchedule(user_id, date, start_time, end_time, url_image, money, str(out_early), ot_id) insert_object(adminSchedule) except Exception as e: db_log.error('error info: %s' % str(e)) db_log.error('error at update_admin') finally: session.close()
def update_lastest_tick_data(sec_id, trading_date, trade_price, total_volume, last_update): session = DBSession() try: session.query(LastestTickData) \ .filter(LastestTickData.sec_id == sec_id) \ .filter(LastestTickData.trading_date == trading_date). \ update({"trade_price": trade_price, "total_vol": total_volume, "last_update": last_update}) session.commit() except Exception as e: error('error at update_lastest_tick_data object') error('error info: %s' % str(e)) session.rollback() finally: session.close()
def delete(back_id): back = db_session.query(Backup).filter(Backup.id == back_id).first() back.status = 255 resp = dict(status='ok') try: os.remove('backups/{}'.format(back.filename)) db_log.info('Usunieto plik kopii', dict(file=back.filename)) except Exception as err: resp['status'] = 'error' resp['filename'] = back.filename db_log.error('Nie mozna usunac pliku', dict(file=back.filename, error=err.message)) if resp['status'] == 'ok': db_session.merge(back) db_session.commit() return resp
def get_ot(start_date, user_id, end_date): session = DBSession() try: query = session.query(db_model.OT) \ .filter(or_(db_model.OT.date_ot > start_date, db_model.OT.date_ot == start_date)) \ .filter(db_model.OT.date_ot < end_date) \ .filter(db_model.OT.user_id == user_id) records = query.all() except Exception as e: db_log.error('error info: %s' % str(e)) db_log.error('error at get_ots') records = [] finally: session.close() return records
def get_schedule_late(start_date, user_id, end_date): session = DBSession() try: query = session.query(db_model.AdminSchedule) \ .filter(or_(db_model.AdminSchedule.date > start_date, db_model.AdminSchedule.date == start_date)) \ .filter(db_model.AdminSchedule.date < end_date) \ .filter(db_model.AdminSchedule.user_id == user_id) \ .filter(or_(db_model.AdminSchedule.in_late > 0, db_model.AdminSchedule.out_early > 0)) records = query.all() except Exception as e: db_log.error('error info: %s' % str(e)) db_log.error('error at get_schedule_late') records = [] finally: session.close() return records
def get_daily_data(sec_id, date): session = DBSession() try: query = session.query(DailyData).filter_by(sec_id=sec_id, date=date) records = query.all() except Exception as e: error('========================***========================') error('error info: %s' % str(e)) error('error at check_record_daily') error('========================***========================') records = [] finally: session.close() return records
def get_all_user(face_id=None, start=None, end=None): session = DBSession() try: if face_id is not None: records = session.query(User)\ .filter(User.face_id == face_id)\ .order_by(asc(User.face_id)).all() elif start is not None and end is not None: records = session.query(User) \ .filter(or_(User.date_created > start, User.date_created == start)) \ .filter(User.date_created < end) \ .order_by(asc(User.face_id)).all() else: records = session.query(User) \ .order_by(asc(User.face_id)).all() except Exception as e: error('error info: %s' % str(e)) error('error at get_all_user') records = [] finally: session.close() return records
def get_lastest_tick_data(sec_id, trading_date): session = DBSession() try: query = session.query(LastestTickData).filter_by( sec_id=sec_id, trading_date=trading_date) records = query.all() except Exception as e: error('========================***========================') error('error info: %s' % str(e)) error('error at get_lastest_tick_data') error('========================***========================') records = [] finally: session.close() return records
def get_last_historical_data(sec_id): session = DBSession() try: query = session.query(HistoricalData).filter_by( sec_id=sec_id).order_by(desc(HistoricalData.date)).limit(1) records = query.all() except Exception as e: error('========================***========================') error('error info: %s' % str(e)) error('error at get_last_historical_data') error('========================***========================') records = [] finally: session.close() return records
def get_business_plan(sec_id, year): session = DBSession() try: query = session.query(BusinessPlanData).filter_by(sec_id=sec_id, year=year) records = query.all() except Exception as e: error('========================***========================') error('error info: %s' % str(e)) error('error at check_record_business_plane') error('========================***========================') records = [] finally: session.close() return records
def get_quarterly_data(sec_id, year, quarter): session = DBSession() try: query = session.query(QuarterlyData).filter_by(sec_id=sec_id, year=year, quarter=quarter) records = query.all() except Exception as e: error('========================***========================') error('error info: %s' % str(e)) error('error at check_record_quater') error('========================***========================') records = [] finally: session.close() return records
def get_all_security(): session = DBSession() try: # query = session.query(Security).filter_by(title=title, subtitle=subtitle, link=link, news_type=news_type) query = session.query(Security) records = query.all() # if len(records) < 1: # print('article is not exist') except Exception as e: error('========================***========================') error('error info: %s' % str(e)) error('error at get_all_security') error('========================***========================') records = [] finally: session.close() return records
def get_user_id(face_id): session = DBSession() try: query = session.query(User).filter_by(face_id=face_id) records = query.all() if len(records) < 1: error('Cant find user has face_id: %s' % str(face_id)) except Exception as e: error('error info: %s' % str(e)) error('error at get_user_by_id id of user: %s'%face_id) records = [] finally: session.close() return records
def error_listener(event): db_log.error('Blad przy wykonywaniu zaplanowego zadania', dict(job_id=event.job_id, exception=event.exception))