def township_influence_neighbours():
neighbours = []
with db_session() as db:
for tn in db.query(NeighbourTownship.code1,
NeighbourTownship.code2).all():
neighbours.append([tn.code1, tn.code2])
return neighbours
def import_township_neighbours():
es = Elastic()
with db_session() as db:
resp = helpers.scan(
es,
index = 'neighbours',
scroll = '3m',
size = 10,
query={'_source': ['HODNOTA1', 'HODNOTA2']}
)
townships = db.query(Township.code, Township.name)
for doc in resp:
name_1 = doc['_source']['HODNOTA1']
name_2 = doc['_source']['HODNOTA2']
if name_1 is None or len(name_1) == 0 or name_2 is None or len(name_2) == 0:
logger.error("Invalid data in 'sousedni_okresy'")
continue
ts_1 = townships.filter(Township.name == name_1)
ts_2 = townships.filter(Township.name == name_2)
if ts_1.first() is None or ts_2.first() is None:
logger.error("Invalid data in 'sousedni_okresy', not existing township")
continue
db.add(NeighbourTownship(code1=ts_1.first().code, code2=ts_2.first().code))
db.commit()
def import_countries():
es = Elastic()
with db_session() as db:
codes = set(i[0] for i in db.query(Country.code).all())
resp = helpers.scan(
es,
index = 'states',
scroll = '3m',
size = 10,
query={'_source': ['CHODNOTA', 'ZKRTEXT']}
)
for doc in resp:
code = doc['_source']['CHODNOTA']
name = doc['_source']['ZKRTEXT']
if code is None or len(code) == 0 \
or name is None or len(name) == 0 \
or code in codes:
continue
codes.add(code)
db.add(Country(code=code, name=name))
db.commit()
def main():
parser = argparse.ArgumentParser()
parser.add_argument('-w', '--web', action='store_true', help='Run Web')
parser.add_argument('-f', '--fill', action='store_true', help='Fill data into databases')
parser.add_argument('-m', '--move', action='store_true', help='Imports data from NoSQL to MySQL')
parser.add_argument('-q', '--queries', action='store_true', help='Answer all queries')
parser.add_argument('-e', '--erase', action='store_true', help='Deletes both dbs data')
args = parser.parse_args()
logger.info("Started: {0}".format(datetime.datetime.now().strftime("%d/%m/%Y %H:%M:%S")))
work_folder_path = os.path.join(os.path.dirname(__file__), '..', 'work')
if not os.path.exists(work_folder_path):
os.mkdir(work_folder_path)
if args.web:
app = create_app()
app.run(host='0.0.0.0', port='80', debug=True)
elif args.fill:
files = ['states', 'regions', 'neighbours', 'infected', 'recovered', 'dead']
es = Elastic()
es.indices.delete('*')
for f in files:
copy_data_file(f)
path = os.path.join(work_folder_path, f'{f}.csv')
fill_data(path)
logger.info("Finished fill: {0}".format(datetime.datetime.now().strftime("%d/%m/%Y %H:%M:%S")))
elif args.move:
models.Base.metadata.drop_all()
models.Base.metadata.create_all()
sql_helpers.import_all()
with db_session() as db:
db.add(models.DataConsistency(code='valid'))
db.commit()
logger.info("Finished move: {0}".format(datetime.datetime.now().strftime("%d/%m/%Y %H:%M:%S")))
elif args.queries:
generate()
generate_townships()
generate_custom_query()
logger.info("Finished queries: {0}".format(datetime.datetime.now().strftime("%d/%m/%Y %H:%M:%S")))
elif args.erase:
delete_all()
logger.info("Finished erase: {0}".format(datetime.datetime.now().strftime("%d/%m/%Y %H:%M:%S")))
else:
app = create_app()
app.run(host='0.0.0.0', port='80', debug=True)
logger.info("Finished: {0}".format(datetime.datetime.now().strftime("%d/%m/%Y %H:%M:%S")))
def import_all(progress_print = print):
sqlim.import_countries()
progress_print(f'Země byly importovány do MySQL databáze.')
sqlim.import_townships_and_regions()
progress_print(f'Kraje a okresy byly importovány do MySQL databáze.')
sqlim.import_township_neighbours()
progress_print(f'Sousedící okresy byly importovány do MySQL databáze.')
sqlim.import_covid_cases()
progress_print(f'Záznamy infikovaných osob byly importovány do MySQL databáze.')
with db_session() as db:
township_count = db.query(func.count(Township.code)).first()[0]
for township in sqlim.import_cases_recovered_death():
township_count = township_count - 1
progress_print(f'Infikovaným v kraji {township} byly přiřazeny datumy vyléčení/úmrtí. Zbývá {township_count} krajů.')
year = datetime.date.today().year
for month in range(1, 13):
month_date = datetime.date(year, month, 1)
townships_infl = township_influence_townships(month_date)
rows = []
for code, ts_infl in townships_infl.items():
rows.append(TownshipReproductionRateCache(code=code, month=month_date, reproduction_rate=round(ts_infl.get_rep_number(), 1)))
with db_session() as db:
db.bulk_save_objects(rows)
db.commit()
progress_print(f'Spočteny reprodukční čísla pro {month}. měsíc.')
def township_influence_townships(month_date='2020-01-01'):
if type(month_date) is str:
month_date = date.fromisoformat(month_date)
elif type(month_date) is not date:
raise Exception('invalid date')
last_day = calendar.monthrange(month_date.year, month_date.month)
if month_date.month == date.today().month: # v ramci mesice
day1 = date.today() + timedelta(days=-12)
day2 = date.today() + timedelta(days=-6)
day3 = date.today() + timedelta(days=-7)
day4 = date.today() + timedelta(days=-1)
else:
day1 = date(month_date.year, month_date.month, 1)
day2 = date(month_date.year, month_date.month, 15)
day3 = day2
day4 = date(month_date.year, month_date.month, last_day[1])
townships = {}
with db_session() as db:
for ts in db.query(Township.code, Township.name).all():
townships[ts.code] = TSInfluence(ts.code, ts.name)
logger.info("Townships count: {0}".format(len(townships)))
cases_1 = db.query(CovidCase.township_code,
func.count(CovidCase.id).label('count')).filter(
and_(CovidCase.infected_date >= day1,
CovidCase.infected_date <= day2)).group_by(
CovidCase.township_code)
logger.info(cases_1)
for c1 in cases_1:
townships[c1.township_code].cases1 = c1.count
cases_2 = db.query(CovidCase.township_code,
func.count(CovidCase.id).label('count')).filter(
and_(CovidCase.infected_date > day3,
CovidCase.infected_date <= day4)).group_by(
CovidCase.township_code)
logger.info(cases_2)
for c1 in cases_2:
townships[c1.township_code].cases2 = c1.count
return townships
def import_townships_and_regions():
es = Elastic()
with db_session() as db:
region_codes = set(i[0] for i in db.query(Region.code).all())
township_codes = set(i[0] for i in db.query(Township.code).all())
resp = helpers.scan(
es,
index = 'regions',
scroll = '3m',
size = 10,
query={'_source': ['CHODNOTA1', 'TEXT1', 'CHODNOTA2', 'TEXT2']}
)
for doc in resp:
reg_code = re.search(r'CZ0\d\d', doc['_source']['CHODNOTA1'])
reg_name = doc['_source']['TEXT1']
if reg_code is None or reg_name is None or len(reg_name) == 0:
continue
reg_code = reg_code.group()
if reg_code not in region_codes:
region_codes.add(reg_code)
db.add(Region(code=reg_code, name=reg_name))
ts_code = re.search(r'CZ0\d\d[\d|A-Z]', doc['_source']['CHODNOTA2'])
ts_name = doc['_source']['TEXT2']
if ts_code is None or ts_name is None or len(ts_name) == 0:
continue
ts_code = ts_code.group()
if ts_code not in township_codes:
township_codes.add(ts_code)
db.add(Township(code=ts_code, name=ts_name, region_code=reg_code))
db.commit()
def import_covid_cases():
es = Elastic()
# if 'CZ099Y' not in township_codes: # mimo uzemi cr
gender_map = { 'M': 'm', 'Z': 'f' }
with db_session() as db:
resp = helpers.scan(
es,
index = 'infected',
scroll = '3m',
size = 1000
)
count = 0
for doc in resp:
case = doc['_source']
sex = gender_map.get(case['pohlavi'], None)
if sex is None:
continue
country = 'CZ' if case['nakaza_v_zahranici'] is None else case['nakaza_zeme_csu_kod']
if case['okres_lau_kod'] == 'CZ099Y' or country is None or len(country) == 0:
continue
db.add(CovidCase(age=case['vek'], gender=sex, infected_date=case['datum'], township_code=case['okres_lau_kod'], country_code=country))
count = count + 1
if count == 5000:
count = 0
db.commit()
db.commit()
else:
app = create_app()
app.run(host='0.0.0.0', port='80', debug=True)
logger.info("Finished: {0}".format(datetime.datetime.now().strftime("%d/%m/%Y %H:%M:%S")))
def create_app():
# create and configure the app
app = Flask(__name__, template_folder='web/templates', static_folder='web/static')
app.config.from_mapping(
SECRET_KEY='UPA',
UPLOAD_FOLDER='work/'
)
init_views(app)
return app
if __name__ == '__main__':
models.Base.metadata.create_all()
with db_session() as db:
imp_row = db.query(models.DataConsistency).filter(models.DataConsistency.code == 'import').first()
if imp_row is not None:
delete_all()
sys.exit(main())