def add_to_db(jid, jdate, jvalue, jper):
add_jj = False
add_sum = False
jj = db_session.query(TblJijin).filter(TblJijin.jid == jid,
TblJijin.jdate == jdate).first()
if jj is None:
add_jj = True
jj = TblJijin()
jj.jid = jid
jj.jdate = jdate
jj.jvalue = jvalue
jsum = db_session.query(TblSum).filter(TblSum.jid == jid,
TblSum.jdate == jdate).first()
if jsum is None:
add_sum = True
jsum = TblSum()
jsum.jid = jid
jsum.jdate = jdate
jsum.jper = jper
try:
jsum.jinc = 1 if float(jper) > 0 else 0 if int(jper) == 0 else -1
except Exception as e:
jsum.jinc = 0
try:
if add_jj:
db_session.add(jj)
if add_sum:
db_session.add(jsum)
db_session.commit()
except Exception as e:
db_session.rollback()
db_session.close()
def init_data():
print('init_data...')
from database.db_config import db_session
from database.tbl_admin import TblAdmin
user = TblAdmin()
user.name = "__MAIL__"
user.value = "*****@*****.**"
user.type = 1
email = TblAdmin()
email.name = "__MAILPASSWORD__"
email.value = "xxxxxxxxxx"
email.type = 1
user_exist = db_session.query(
TblAdmin.name).filter(TblAdmin.name == user.name).first()
if user_exist is None:
db_session.add(user)
mail_exist = db_session.query(
TblAdmin.name).filter(TblAdmin.name == email.name).first()
if mail_exist is None:
db_session.add(email)
db_session.commit()
db_session.close()
print("done!")
def drop_less_data(jid):
db_session.query(TblSum).filter(TblSum.jid == jid).delete()
db_session.query(TblJijin).filter(TblJijin.jid == jid).delete()
try:
db_session.commit()
except Exception as e:
print(e)
db_session.rollback()
def drop_range_date_data():
db_session.query(TblSum).filter(TblSum.jdate >= "2021-10-01",
TblSum.jdate <= "2021-10-07").delete()
db_session.query(TblJijin).filter(TblJijin.jdate >= "2021-10-01",
TblJijin.jdate <= "2021-10-07").delete()
try:
db_session.commit()
except Exception as e:
print(e)
db_session.rollback()
def clear_history(days):
clear_date = DatetimeManage.get_days_ago(days)
try:
db_session.query(TblBrowsingHistory).filter(
TblBrowsingHistory.browsing_date <= clear_date).delete()
db_session.commit()
db_session.close()
weblog.info("db data history clear...")
except Exception as e:
print(e)
weblog.exception("data history error. {}".format(e))
def clear_old_data():
pass
data_clear_date = DatetimeManage.get_days_ago(365)
try:
pass
db_session.query(TblJijin).filter(
TblJijin.jdate <= data_clear_date).delete()
db_session.query(TblSum).filter(
TblSum.jdate <= data_clear_date).delete()
db_session.commit()
db_session.close()
except Exception as e:
print(e)
weblog.exception("data TblJijin/TblSum error. {}".format(e))
def get_jids():
from sqlalchemy.sql import func
jids = db_session.query(TblJijin.jid, func.count(TblJijin.jid)).group_by(
TblJijin.jid).all()
res = []
for jid in jids:
# print(jid.jid)
fjid = '{:0>6d}'.format(jid.jid)
res.append(fjid)
return res
def init_info():
from database.db_config import db_session
from database.tbl_admin import TblAdmin
user = TblAdmin()
user.name = "appinfo"
user.value = "1.0"
user.type = 1
user_exist = db_session.query(TblAdmin.name).filter(TblAdmin.name == user.name).first()
if user_exist is None:
db_session.add(user)
user = TblAdmin()
user.name = "appversion"
user.value = "1.0"
user.type = 1
mail_exist = db_session.query(TblAdmin.name).filter(TblAdmin.name == user.name).first()
if mail_exist is None:
db_session.add(user)
db_session.commit()
db_session.close()
def add_sum(jid, jdate, jvalue):
tsum = db_session.query(TblJijin).filter(
TblJijin.jid == jid,
TblJijin.jdate <= jdate).order_by(TblJijin.jdate.desc()).limit(2)
count = tsum.count()
if count != 2:
return None
inc = 0
try:
per = (float(tsum[0].jvalue) - float(tsum[1].jvalue)) / float(
tsum[1].jvalue)
if per > 0: inc = 1
if per < 0: inc = -1
per = round(per * 100, 3)
per = str(per)
except Exception as e:
per = "--"
tas = db_session.query(TblSum).filter(TblSum.jid == jid,
TblSum.jdate == jdate).first()
if tas is None:
tas = TblSum()
tas.jdate = jdate
tas.jid = jid
tas.jper = per
tas.jinc = inc
db_session.add(tas)
cjjlog.info("sum: {} {} {} add db.".format(jid, jdate, per))
else:
tas.jdate = jdate
tas.jid = jid
tas.jper = per
tas.jinc = inc
cjjlog.info("sum: {} {} {} exist then update.".format(jid, jdate, per))
# print(cjjlog)
# print("sum: {} {} {} exist then update.".format(jid, jdate, per))
try:
db_session.commit()
except Exception as e:
cjjlog.error("sum: {} {} {} add fail. {}".format(jid, jdate, per, e))
def add_data(jid, jdate, jvalue):
tjj = db_session.query(TblJijin).filter(TblJijin.jid == jid,
TblJijin.jdate == jdate).first()
if tjj is None:
tjj = TblJijin()
tjj.jid = jid
tjj.jdate = jdate
tjj.jvalue = jvalue
db_session.add(tjj)
weblog.info("{} {} {} add db.".format(jid, jdate, jvalue))
else:
tjj.jid = jid
tjj.jdate = jdate
tjj.jvalue = jvalue
weblog.info("{} {} {} exist then update.".format(jid, jdate, jvalue))
try:
db_session.commit()
except Exception as e:
weblog.error("{} {} {} add fail. {}".format(jid, jdate, jvalue, e))
def select_less_data():
# having(TblSum.id < 10)
two_weeks_before = datetime.now() + timedelta(days=0)
std_date = two_weeks_before.strftime("%Y-%m-%d")
print(std_date)
all_jid = db_session.query(
func.count(TblSum.jid).label("nums"),
TblSum.jid).filter(TblSum.jdate <= std_date).group_by(
TblSum.jid).all()
drop_data = []
for item in all_jid:
print(item.nums, item.jid)
if item.nums < 10:
pass
drop_data.append(item.jid)
# drop_data = ["161826", "001668"]
for jid in drop_data:
pass
drop_less_data(jid)
# create_table()
# init_account()
# init_data()
create_single_table()
# init_info()
# from database.db_config import db_session
# db_session.commit()
#
# get_table_propery("tbl_word")
# # init_account()
#
# db_session.add(TblCode(key="admin1", msg=1, code='4K6h3JXgrLXgu43jkKPjiZnjjL', user="******"))
# db_session.commit()
# all_code = db_session.query(TblCode).filter(TblCode.user == "admin1").all()
# for i in all_code:
# print(i.tojson())
from database.db_config import db_session
from database.tbl_jijin import TblJijin
from sqlalchemy.sql import func
jids = db_session.query(TblJijin.jid, func.count(TblJijin.jid)).group_by(TblJijin.jid).all()
res = []
for jid in jids:
fjid = '{:0>6d}'.format(jid.jid)
print(jid.jid, type(jid.jid), fjid)
res.append(jid)
TblJijin.jdate <= data_clear_date).delete()
db_session.query(TblSum).filter(
TblSum.jdate <= data_clear_date).delete()
db_session.commit()
db_session.close()
except Exception as e:
print(e)
weblog.exception("data TblJijin/TblSum error. {}".format(e))
def clear_history(days):
clear_date = DatetimeManage.get_days_ago(days)
try:
db_session.query(TblBrowsingHistory).filter(
TblBrowsingHistory.browsing_date <= clear_date).delete()
db_session.commit()
db_session.close()
weblog.info("db data history clear...")
except Exception as e:
print(e)
weblog.exception("data history error. {}".format(e))
if __name__ == '__main__':
clear_date = DatetimeManage.get_days_ago(1)
print(clear_date)
res = db_session.query(TblBrowsingHistory).filter(
TblBrowsingHistory.browsing_date <= clear_date)
for dd in res.all():
print(dd)
from database.db_config import db_session
from database.tbl_account import TblAccount
from handlers.common_handler import PAGESIZE, FIRST_PAGE
if __name__ == "__main__":
a = db_session.query(TblAccount).filter(TblAccount.id == 10)
print(len(a.all()))
a = a.all()
users = db_session.query(TblAccount).filter_by(userstate=0).order_by(
TblAccount.register_time.desc())
total_page = len(users.all()) // PAGESIZE + 1
current_page = 1
users = users.limit(PAGESIZE).offset((current_page - 1) * PAGESIZE)
print(users, total_page)
for us in users:
print(us)
db_session.commit()
def init_jijin():
from database.tbl_jijin import TblJijin
from datetime import datetime
dis = TblJijin()
dis.jid = "test2"
dis.jdate = '2020-05-05'
dis.jvalue = '2.13'
db_session.add(dis)
db_session.commit()
if __name__ == "__main__":
# init_admin()
# init_user()
# init_setting()
# init_version()
from sqlalchemy import func
from database.tbl_jijin import TblJijin
tj = db_session.query(TblJijin.jid, TblJijin.jdate,
func.min(
TblJijin.jvalue)).filter(TblJijin.jid == '1717')
for t in tj.all():
print(t)
# te = tj.delete()
# db_session.commit()