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 delete_all():
model.Base.metadata.drop_all()
model.Base.metadata.create_all()
es = Elastic()
es.indices.delete('*')
work_folder = os.path.join(os.path.dirname(__file__), '..', 'work')
shutil.rmtree(work_folder, ignore_errors=True)
if not os.path.exists(work_folder):
os.makedirs(work_folder)
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_cases_recovered_death():
es = Elastic()
sex = {'M': 'm', 'Z': 'f'}
def _init_data(nosql_index, ages_index, township_code, ages, remaining_rec):
body = {
"query": {
"bool": {
"filter": [
{ "match": { "okres_lau_kod": township_code }}
]
}
},
"sort": [
{ "datum": { "order": "asc" }}
],
'_source': ['pohlavi', 'vek', 'datum']
}
data = [i['_source'] for i in helpers.scan(
es,
index = nosql_index,
scroll = '3m',
size = 1000,
query=body
)]
for i, rec in enumerate(data):
rec['pohlavi'] = sex[rec['pohlavi']]
rec['datum'] = date.fromisoformat(rec['datum'])
if len(remaining_rec) > 0:
found = (rec['vek'], rec['pohlavi'])
if found in remaining_rec:
ages[found[0]][found[1]][ages_index] = i
remaining_rec.remove(found)
return data
def _find_next_match(index, to_search, case, check_date=False):
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()