def get_user(*, db: Session, condition: dict):
try:
result = db.query(CovidUser).filter_by(**condition).all()
return result
except Exception as _:
db.rollback()
raise
finally:
db.close()
def get_region_list(*, db: Session):
try:
result = db.query(distinct(Covid19.country_en)).all()
return result
except Exception as _:
db.rollback()
raise
finally:
db.close()
def add_user(*, db: Session, email: str, token: str):
try:
new_user = CovidUser(email=email, token=token)
db.add(new_user)
db.commit()
except Exception as _:
db.rollback()
raise
finally:
db.close()
def get_last_update_date(*, db: Session):
try:
max_update_date = db.query(
func.max(Covid19.update_date).label("max_update_date")).all()
return max_update_date[0][0]
except Exception as _:
db.rollback()
raise
finally:
db.close()
def update_user(*, db: Session, condition: dict, data: dict):
try:
result = Session.query(CovidUser).filter_by(
**condition).update(data)
db.commit()
return result
except Exception as _:
db.rollback()
raise
finally:
db.close()
def add_captcha(*, db: Session, captcha: str, session_id: str,
expiration: str):
try:
new_captcha = Captcha(captcha=captcha,
session_id=session_id,
expiration=expiration)
db.add(new_captcha)
db.commit()
except Exception as _:
db.rollback()
raise
finally:
db.close()
def get_population(*, db: Session, country: str):
try:
if country:
filters = and_(Population.country_en == country)
else:
filters = and_(1 == 1)
result = db.query(Population).filter(filters).all()
return result
except Exception as _:
db.rollback()
raise
finally:
db.close()
def get_captcha_by_session(
*,
db: Session,
session: str,
):
try:
result = db.query(Captcha).filter_by(session_id=session).order_by(
Captcha.id.desc()).first()
return result
except Exception as _:
db.rollback()
raise
finally:
db.close()
def get_infection_global_data(*, db: Session):
try:
result = db.query(
Covid19.country_en,
func.sum(Covid19.confirmed_add).label("confirmed_add"),
func.sum(Covid19.deaths_add).label("deaths_add"),
func.sum(
Covid19.recovered_add).label("recovered_add")).group_by(
Covid19.country_en).all()
return result
except Exception as _:
db.rollback()
raise
finally:
db.close()
def get_area_list(*, db: Session, region: str, hmt: bool):
if hmt:
# 包含港澳台
filters = and_(Covid19.continents_en != "",
Covid19.country_en == region)
else:
# 不包含港澳台
filters = and_(Covid19.continents_en != "",
Covid19.province_en.notin_(HMT),
Covid19.country_en == region)
try:
result = db.query(distinct(
Covid19.province_en)).filter(filters).all()
return result
except Exception as _:
db.rollback()
raise
finally:
db.close()
def get_infection_city_data(*, db: Session, city: str, stime: str or None,
etime: str or None, country: str):
try:
if country:
# 查询条件中有国家
filters = and_(Covid19.province_en == city,
Covid19.country_en == country)
else:
# 查询条件中无国家
filters = and_(Covid19.province_en == city)
if stime and etime:
result = db.query(
func.sum(Covid19.confirmed_add).label("confirmed_add"),
func.sum(Covid19.deaths_add).label("deaths_add"),
func.sum(Covid19.recovered_add).label("recovered_add"),
).filter(and_(Covid19.update_date.between(stime, etime)),
filters).all()
return result
else:
# 获取最新时间
max_update_date = db.query(
func.max(
Covid19.update_date).label("max_update_date")).all()
result = db.query(
func.sum(Covid19.confirmed_add).label("confirmed_add"),
func.sum(Covid19.deaths_add).label("deaths_add"),
func.sum(Covid19.recovered_add).label("recovered_add"),
).filter(
and_(Covid19.update_date == str(max_update_date[0][0])),
filters).all()
return result
except Exception as _:
db.rollback()
raise
finally:
db.close()
def get_infection_country_area_data(*, db: Session, country: str,
start_date: str or None, end_date: str
or None, hmt: bool):
try:
if hmt:
# 包含港澳台
filters = and_(Covid19.continents_en != "")
else:
# 不包含港澳台
filters = and_(Covid19.continents_en != "",
Covid19.province_en.notin_(HMT))
if start_date and not end_date:
max_update_date = db.query(
func.max(
Covid19.update_date).label("max_update_date")).all()
end_date = str(max_update_date[0][0])
check_date_filter(
DateFilters(**{
'start_date': start_date,
'end_date': end_date
}))
if start_date and end_date:
result = db.query(
Covid19.update_date,
Covid19.province_en,
Covid19.confirmed_add,
Covid19.deaths_add,
Covid19.recovered_add,
Covid19.confirmed,
Covid19.deaths,
Covid19.recovered,
).filter(
and_(Covid19.country_en == country,
Covid19.update_date.between(start_date, end_date)),
filters).group_by(Covid19.update_date,
Covid19.province_en).all()
return result
else:
# 获取最新时间
max_update_date = db.query(
func.max(
Covid19.update_date).label("max_update_date")).all()
result = db.query(
Covid19.update_date,
Covid19.province_en,
Covid19.confirmed_add,
Covid19.deaths_add,
Covid19.recovered_add,
Covid19.confirmed,
Covid19.deaths,
Covid19.recovered,
).filter(
and_(Covid19.country_en == country,
Covid19.update_date == str(max_update_date[0][0])),
filters).group_by(Covid19.update_date,
Covid19.province_en).all()
return result
except Exception as _:
db.rollback()
raise
finally:
db.close()