def run():
with db.common_db() as con:
cur = con.cursor()
drop_table = "DROP TABLE to_survive"
try:
cur.execute(drop_table)
except:
pass
create_table = """
CREATE TABLE to_survive (
region_id INT,
district_id INT,
year_code VARCHAR,
sex INT,
value_ FLOAT,
PRIMARY KEY (region_id, district_id, year_code, sex, value_)
);
"""
cur.execute(create_table)
con.commit()
for fname in os.listdir(DATA_DIR):
if fname not in (".", ".."):
parse(DATA_DIR + fname)
with db.common_db() as con:
cur = con.cursor()
cur.execute("VACUUM")
def run():
with db.common_db() as con:
cur = con.cursor()
drop_table = "DROP TABLE unemployed"
try:
cur.execute(drop_table)
except:
pass
create_table = """
CREATE TABLE unemployed (
municipality_id INT,
year_ INT,
month_ INT,
type_ VARCHAR,
value_ FLOAT,
PRIMARY KEY (municipality_id, year_, month_, type_, value_)
);
"""
cur.execute(create_table)
con.commit()
for fname in os.listdir(DATA_DIR):
if fname not in (".", ".."):
parse(DATA_DIR + fname)
with db.common_db() as con:
cur = con.cursor()
cur.execute("VACUUM")
def parse(path: str):
with db.common_db() as con:
cur = con.cursor()
insert_query = "INSERT INTO unemployed VALUES (?, ?, ?, ?, ?)"
values = []
i = 0
with open(path, 'r') as csvfile:
reader = csv.reader(csvfile, delimiter=',', quotechar='"')
next(reader)
for row in reader:
i += 1
values.append((
row[8], # municipality_id
row[5], # year_
row[6], # month_
row[2], # type_
row[1].replace(",", "."), # value_
))
if i > 10000:
cur.executemany(insert_query, values)
con.commit()
i = 0
values = []
cur.executemany(insert_query, values)
con.commit()
def get_death_causes():
"""
Vrati nejcastejsi umrti.
"""
args = json.loads(request.data)
district_code = int(args['district_code'])
with db.common_db(cursor=True) as cursor:
query = """
select
dc.year as year,
dc.cause_id as cause_id,
sum(dc.value) as val,
d.name as disease_name
FROM death_cause dc, disease d
where district_id=? and dc.value > 0 and d.code=dc.cause_id
GROUP BY dc.year, dc.cause_id, d.name
ORDER BY val DESC
LIMIT 8
"""
cursor.execute(query, (district_code, ))
data = [{
'x': item['disease_name'],
'y': item['val'],
'color': color
} for item, color in zip(cursor.fetchall(), COLORS) if item]
return dict(result=True,
data={
'data': data,
'title': "Nemoci na které se v okresu nejčastěji umírá"
})
def prepare_data(request_args: Dict[str, str],
data_type: str) -> Tuple[List[Dict[str, Any]], str]:
"""
Jelikoz vsechny 3 datove sady maji stejny format, lze se na ne dotazovat stejnou funkci a
staci jen menit nazvy tabulek.
"""
municipality_code = request_args.get('municipality_code')
response_data = []
with db.common_db() as connection:
cursor = connection.cursor()
# Obec
if municipality_code:
data = cursor.execute(
'''SELECT metric_id, metric, value FROM {}_obec WHERE municipality_id=?'''
.format(data_type), (municipality_code, ))
else:
data = []
# Pripravim data pro view
data = list(data)
data_sum = sum([x[2] for x in data
if x[0] != 'voters']) # Pocet volicu celkem
data_voters = sum([x[2] for x in data
if x[0] == 'voters']) # Pocet potencialnich volicu
label = 'Volby do Poslanecké sněmovny 2013 (Účast {:.2f} % ({}/{}))'.format(
((data_sum / data_voters) * 100) if data_voters else 0,
'{:,}'.format(int(data_sum)).replace(',', ' '),
'{:,}'.format(int(data_voters)).replace(',', ' '))
for item in data:
if item[0] == 'voters':
# Celkovy pocet volicu nechceme
continue
# Data pro celkovy pocet volicu muze byt 0 a v tom pripade bych delil 0
if data_sum:
percent = (item[2] / data_sum) * 100
else:
percent = 0
response_data.append({
'x': '{} {:.2f} %'.format(item[1], percent),
'y': percent,
})
# Zajima nas prvnich 9 a 10. je soucet vsech ostatnich
response_data = sorted(response_data, key=lambda x: x['y'], reverse=True)
new_response_data = response_data[:9]
others_sum = sum([x['y'] for x in response_data[9:]])
percents = others_sum
new_response_data.append({
'x': 'Ostatní {:.2f} %'.format(percents),
'y': percents
})
return new_response_data, label
def parse(path):
"""
Naliti dat z
:param path:
:return:
"""
region_district_query = "SELECT vusc_kod, kod FROM okresy WHERE nazev = ?"
insert_query = "INSERT INTO to_survive VALUES (?, ?, ?, ?, ?)"
with db.ruian_db(True) as ruian_cur:
with db.common_db() as con:
cur = con.cursor()
values = []
i = 0
with open(path, 'r') as csvfile:
reader = csv.reader(csvfile, delimiter=',', quotechar='"')
next(reader)
for row in reader:
i += 1
if int(row[7]) == 101:
# hnusny hack :-)
if row[14] == "Praha":
row[14] = "Hlavní město Praha"
ruian_cur.execute(region_district_query, (row[14], ))
ruian_response = [
dict(item) for item in ruian_cur.fetchall()
]
ruian_row = ruian_response[0]
values.append((
ruian_row['vusc_kod'], # region_id
ruian_row['kod'], # district_id
row[6], # year_code
row[4], # sex
row[1].replace(",", "."), # value_
))
if i > 10000:
cur.executemany(insert_query, values)
con.commit()
i = 0
values = []
cur.executemany(insert_query, values)
con.commit()
def prepare_data(request_args, data_type):
"""
Jelikoz vsechny 3 datove sady maji stejny format, lze se na ne dotazovat stejnou funkci a
staci jen menit nazvy tabulek.
"""
municipality_code = request_args.get('municipality_code')
district_code = request_args.get('district_code')
region_code = request_args.get('region_id')
response_data = []
with db.common_db() as connection:
cursor = connection.cursor()
# Obec
if municipality_code:
data = cursor.execute(
'''SELECT metric, value FROM {}_obec WHERE city_id=?'''.format(
data_type), (municipality_code, ))
# Okres
elif district_code:
data = cursor.execute(
'''SELECT metric, value FROM {}_okres WHERE district_id=?'''.
format(data_type), (district_code, ))
# Kraj
elif region_code:
data = cursor.execute(
'''SELECT metric, value FROM {}_kraj WHERE region_id=?'''.
format(data_type), (region_code, ))
else:
data = []
# Pripravim data pro view
data = list(data)
data_sum = sum([x[1] for x in data])
for item in data:
percent = (item[1] / data_sum) * 100
response_data.append({
'key': '{} {:.2f} %'.format(item[0], percent),
'value': item[1],
})
return sorted(response_data, key=lambda x: x['value'], reverse=True)
def get_data_from_query(region_id, district_code):
# type: (str, str) -> list(dict)
"""
Vraci stredni delka zivota a prumer nad celym statem.
Metoda radi dle pohlavi (muzi/zeny)
"""
with db.common_db(cursor=True) as cur:
query = """
SELECT
value_ AS value,
sex
FROM to_survive
WHERE
region_id = ? AND
district_id = ? AND
year_code = ?
GROUP BY sex
ORDER BY sex ASC
"""
cur.execute(query, (region_id, district_code, CHILD_YEAR_CODE))
return [dict(x) for x in cur.fetchall()]
def get_data_from_query(municipality_code, type_):
# type: (str, str) -> list(dict)
"""
Vytazeni poslednich 10ti zaznamu z tabulky nezamestnanosti
:param municipality_code: ...
:param type_: ...
:return: ...
"""
with db.common_db(cursor=True) as cur:
query = """
SELECT
value_ AS y,
month_ || '/ ' || year_ AS x,
'#1f77b4' as color
FROM unemployed
WHERE
municipality_id = ? AND
type_ = ?
ORDER BY year_ DESC, month_ DESC
LIMIT ?
"""
cur.execute(query, (municipality_code, type_, LIMIT))
return [dict(x) for x in cur.fetchall()]