def create_year_info_json(db, year):
'''Creates a json with information about an year.'''
q_total = db.session.query(Execucao).filter(
Execucao.get_year() == year)
num_total = q_total.count()
q_mapped = q_total.filter(Execucao.point_found())
num_mapped = q_mapped.count()
rows = {
'total': num_total,
'mapped': num_mapped,
# TODO: calcular regionalizados...
'region': num_mapped,
}
values = []
fields = [
('orcado', 'sld_orcado_ano'),
('atualizado', 'vl_atualizado'),
('empenhado', 'vl_empenhadoliquido'),
('liquidado', 'vl_liquidado')
]
for name, db_field in fields:
q = (db.session.query(
func.sum(Execucao.data[db_field].cast(db.Float)))
.filter(Execucao.get_year() == year))
total = q.scalar()
mapped = q.filter(Execucao.point_found()).scalar()
if mapped is None:
mapped = 0
values.append({
'name': name,
'total': total,
'mapped': mapped,
# TODO: calcular regionalizados...
'region': mapped,
})
last_update = (db.session.query(Execucao.data['datafinal'])
.filter(Execucao.get_year()==year)
.distinct().all()[-1][0])
return {
'data': {
'rows': rows,
'values': values,
'last_update': last_update if last_update else str(year),
}
}
def update_from_csv(db, csv):
'''Update table using values from CSV. Slower than 'insert_csv' but raises
no error if primary key already exists (just updates values).'''
table = pd.read_csv(csv)
pks = create_pks(table)
counter = ProgressCounter(len(table))
modified_counter = 0
added_counter = 0
for row_i, row in table.iterrows():
code = pks.iloc[row_i]
row_model = db.session.query(Execucao).filter_by(code=code).first()
new_row = prepare_row(code, row)
date = datetime.datetime.strptime(new_row['data']['datafinal'],
'%Y-%m-%d')
if row_model:
modified = {}
# Check if state was modified
if row_model.state != new_row['state'].decode('utf8'):
modified['state'] = (row_model.state, new_row['state'])
row_model.state = new_row['state']
# Check if a field in data was modified
for key, new_value in new_row['data'].items():
old_value = row_model.data.get(key)
# Avoids confusion caused by new_value not been unicode
if type(new_value) is str:
new_value = new_value.decode('utf8')
new_row['data'][key] = new_value
# Avoids data that changed from 0 to None
if (old_value or new_value) and (old_value != new_value):
modified[key] = (old_value, new_value)
# Avoids registering row as modified if only datafinal changend
if len(modified) == 1 and 'datafinal' in modified:
modified = {}
if modified:
db.session.add(
History(event='modified',
code=code,
date=date,
data=modified))
modified_counter += 1
# Updates DB data even if only 'datafinal' changed
row_model.data = new_row['data']
else:
db.session.add(
History(event='created', code=code, date=date, data=new_row))
db.session.add(Execucao(**new_row))
added_counter += 1
counter.update()
counter.end()
db.session.commit()
print('Added/Modified/Total: %s/%s/%s' %
(added_counter, modified_counter, len(table)))
def generate_year(db, year, outfolder):
year_data = db.session.query(
Execucao, Execucao.point.ST_AsGeoJSON(3)).filter(
Execucao.get_year() == year).all()
rows = []
for row, geo in year_data:
data = {
'estado': row.state,
'codigo': row.code,
}
# Add row.data fields taking care of unicode
for k, v in row.data.items():
try:
data[k] = v.encode('utf-8')
except AttributeError:
# For non strings types
data[k] = v
lon, lat = get_lonlat(geo)
data['longitude'] = lon
data['latitude'] = lat
rows.append(data)
# Sort by code
rows.sort(lambda x, y: x['codigo'] < y['codigo'])
filepath = os.path.join(outfolder, year + '.csv')
with open(filepath, 'w') as outfile:
writer = csv.DictWriter(outfile,
fieldnames=sorted(rows[0].keys()),
dialect='unix')
writer.writeheader()
writer.writerows(rows)
def generate_year(db, year, outfolder):
year_data = db.session.query(Execucao,
Execucao.point.ST_AsGeoJSON(3)).filter(
Execucao.get_year() == year).all()
rows = []
for row, geo in year_data:
data = {
'estado': row.state,
'codigo': row.code,
}
# Add row.data fields taking care of unicode
for k, v in row.data.items():
try:
data[k] = v.encode('utf-8')
except AttributeError:
# For non strings types
data[k] = v
lon, lat = get_lonlat(geo)
data['longitude'] = lon
data['latitude'] = lat
rows.append(data)
# Sort by code
rows.sort(lambda x, y: x['codigo'] < y['codigo'])
filepath = os.path.join(outfolder, year + '.csv')
with open(filepath, 'w') as outfile:
writer = csv.DictWriter(outfile,
fieldnames=sorted(rows[0].keys()),
dialect='unix')
writer.writeheader()
writer.writerows(rows)
def generate_all(db, outfolder, years):
# if years is an empty list, calculate for all years in the DB
if not years:
dbyears = db.session.query(Execucao.get_year()).distinct().all()
years = sorted([str(i[0]) for i in dbyears])
for year in years:
print(year)
generate_year(db, year, outfolder)
def generate_all(db, outfolder, years):
# if years is an empty list, calculate for all years in the DB
if not years:
dbyears = db.session.query(Execucao.get_year()).distinct().all()
years = sorted([str(i[0]) for i in dbyears])
for year in years:
print(year)
generate_year(db, year, outfolder)
def update_from_csv(db, csv):
'''Update table using values from CSV. Slower than 'insert_csv' but raises
no error if primary key already exists (just updates values).'''
table = pd.read_csv(csv)
pks = create_pks(table)
counter = ProgressCounter(len(table))
for row_i, row in table.iterrows():
code = pks.iloc[row_i]
row_model = db.session.query(Execucao).filter_by(code=code).first()
new_row = prepare_row(code, row)
if row_model:
for key, new_value in new_row.iteritems():
setattr(row_model, key, new_value)
# old_value = getattr(row_model, key)
# if old_value != new_value:
# print(key, old_value, new_value)
# setattr(row_model, key, new_value)
else:
db.session.add(Execucao(**new_row))
counter.update()
counter.end()
db.session.commit()
def update_all_years_info(db):
dbyears = db.session.query(Execucao.get_year()).distinct().all()
for tup in dbyears:
update_year_info(db, tup[0])
