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 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])