def is_database_empty():
session = Session()
is_empty = session.query(Place).first() is None
session.close()
return is_empty
def reply_cases(update: Updater, place_ids):
with Session() as session:
m1 = aliased(Measures)
m2 = aliased(Measures)
# moto = session.query(Moto).filter_by(id=1).one()
results = session.query(m1, Place).\
join(Place,
(Place.code == m1.place_code)
&
(Place.type == m1.place_type),
isouter=True
).\
join(m2,
(m1.place_code == m2.place_code)
&
(m1.place_type == m2.place_type)
&
(m1.date_reg < m2.date_reg),
isouter=True
).\
filter(m2.id == None).\
filter(Place.id.in_(place_ids) )
logger.info(results)
for measures, place in results:
update.message.reply_text(
f'{measures.dt_reg} - Casos por 100.000 habitantes acumulados en 14 días en {place.name}:\n'
f'{str(measures.pdia_14d_rate)}',
reply_markup=ReplyKeyboardRemove())
def pull_measures(measures_json_list=None, date=datetime.date.today()):
"""It will use the specified JSON or query it to the API for inserting measures
for the selected datetime"""
import os
# Get the process ID of
# the current process
pid = os.getpid()
# Print the process ID of
# the current process
print(f'process id: {pid}')
received_measures_list = json_source.get_measures(measures_json_list)
received_measures_list_comp = {
MeasuresCodeComparator(i)
for i in received_measures_list
}
# logger.info("Received lenght: "+str(len(received_measures_list_comp)))
session = Session()
# TODO implement merge with measures from same date
db_date_measures_list = queries.retrieve_measures_from_date(date, session)
db_date_measures_list_comp = {
MeasuresCodeComparator(i)
for i in db_date_measures_list
}
# logger.info("DB lenght: "+str(len((db_date_measures_list_comp))))
new_measures_list_comp = list(received_measures_list_comp -
db_date_measures_list_comp)
# logger.info("New length "+str(len((new_measures_list_comp))))
new_measures_list = [ml.measures for ml in new_measures_list_comp]
session.bulk_save_objects(new_measures_list)
for existing_measures in list(
MeasuresCodeComparator(i) for i in db_date_measures_list):
matching = next((rmlc for rmlc in received_measures_list_comp
if existing_measures == rmlc), None)
if matching is not None:
model_update.update_measures_model(existing_measures.measures,
matching.measures)
session.commit()
session.close()
def search_place(update: Updater, context: CallbackContext) -> Place:
"""Search places and return multiple matches as buttons."""
place_map = context.user_data.get('place_map', {})
place = place_map.get(update.message.text)
if (place):
del context.user_data['place_map']
return place
with Session() as session:
likesearch = f'%{update.message.text}%'
coincidences = session.query(Place.name, func.count('name').label('coincidences')).\
group_by(Place.name).subquery()
results = session.query(Place, coincidences.c.coincidences).\
join(coincidences, Place.name == coincidences.c.name).\
filter(Place.name.ilike(likesearch)).\
order_by(Place.name)
if (results.count() == 1):
place, _ = results[0]
try:
del context.user_data['place_map']
except KeyError:
pass
return place
else:
place_map = {}
for place, coincidences in results:
place_name = place.name
if coincidences > 1:
place_name += f' ({place.type})'
place_map[place_name] = place
context.user_data['place_map'] = place_map
if (place_map):
update.message.reply_text("Elige un sitio",
reply_markup=ReplyKeyboardMarkup(
[[row]
for row in place_map.keys()],
one_time_keyboard=True))
else:
update.message.reply_text(
"No se ha encontrado ningún sitio :(",
reply_markup=ReplyKeyboardRemove())
def populate_places(insert_measures=True):
"""Used for the first time, it will populate the list of places in database.
It will also add insert the first measures for those places if parameter set to True.
"""
measures_json_lists = json_source.get_measures_json_list()
places_list = json_source.get_places(measures_json_lists)
session = Session()
session.bulk_save_objects(places_list)
session.commit()
session.close()
if insert_measures:
pull_measures(measures_json_lists)
def query_cases(update: Updater, context: CallbackContext):
"""Search cases"""
place = search_place(update, context)
if (place):
with Session() as session:
# moto = session.query(Moto).filter_by(id=1).one()
cases = session.query(Measures).filter_by(
place_code=place.code, ).order_by(
Measures.date_reg.desc()).first()
update.message.reply_text(
f'Casos por 100.000 habitantes acumulados en 14 días en {place.name}:\n'
f'{str(cases.pdia_14d_rate)}',
reply_markup=ReplyKeyboardRemove())
def update_user_registration(user: User):
with Session() as session:
session.merge(user)
session.commit()
def user_places(user_id) -> Tuple[Place, UserTracking]:
with Session() as session:
return session.query(Place, UserTracking).\
join(UserTracking, Place.id == UserTracking.place_id).\
filter(UserTracking.user_id == user_id)
def track_place(user_id, place_id):
entity = UserTracking(user_id=user_id, place_id=place_id)
with Session() as session:
session.add(entity)
session.commit()
def retrieve_measures_from_date(date, session=None):
if session is None:
session = Session()
return session.query(Measures).filter_by(date_reg=date).all()