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