def agriculture_facts():
if request.method == "GET":
# if not current_user.can(Permission.QUANTIFY_R):
# return jsonify(status="fail", data=[], reason="no permission")
args = std_json(request.args)
if not check_args(ALLOW_ARGS, args.keys()):
return jsonify(status="fail", reason="error args", data=[])
start_time = args.get('start_time')
end_time = args.get('end_time')
table = AgricultureTable.r_query().filter(AgricultureTable.id==args.get('table_id')).first()
if table is None:
return jsonify(status="fail", data=[], reason="no such table")
log_id = args.get('log_id')
if log_id is None:
log_id = table.cur_log_id
result = []
for country_id in args.get("country_ids"):
for index_id in args.get("index_ids"):
for kind_id in args.get('kind_ids'):
facts = AgricultureFacts.find(table_id=table.id, index_ids=[index_id], kind_ids=[kind_id],
country_ids=[country_id], start_time=int(start_time),
end_time=int(end_time), log_id=log_id)
index = AgricultureIndexes.r_query().filter(AgricultureIndexes.id==index_id).first()
country = Country.r_query().filter(Country.id==country_id).first()
kind = AgricultureKind.r_query().filter(AgricultureKind.id==kind_id).first()
result.append(
{"country": country.to_json(),
"index": index.to_json(),
"kind": kind.to_json(),
"data": [fact.to_json() for fact in facts]})
# facts = AgricultureFacts.find(table_id=args.get("table_id"), index=args.get("index"),
# country=args.get("country"), start_time=args.get("start_time"))
#
# result = list()
#
# for fact in facts:
# _tmp_has_index = find_in_list(result, fact.index_id, lambda x, y: x.get("index_id") == y)
# if _tmp_has_index is None:
# result.append({"index_id": fact.index_id,
# "data": [{"country_id": fact.country_id,
# "data": [fact.to_json()]}]})
# else:
# _tmp_has_country = find_in_list(result, fact.country_id, lambda x, y: x.get("country_id") == y)
# if _tmp_has_country is None:
# _tmp_has_index['data'].append({"country_id": fact.country_id,
# "data": [fact.to_json()]})
# else:
# _tmp_has_country["data"].append(fact.to_json)
return jsonify(status="success", reason="", data=result)
def socioeconomic_facts_graph():
if request.method == "GET":
# if not current_user.can(Permission.QUANTIFY_R):
# return jsonify(status="fail", data=[], reason="no permission")
args = std_json(request.args)
if not check_args(ALLOW_ARGS, args.keys()):
return jsonify(status="fail", reason="error args", data=[])
table = SocioeconomicTable.r_query().filter(
SocioeconomicTable.id==args.get("table_id")).first()
if table is None:
return jsonify(status="fail", data=[], reason="no such table")
log_id = args.get("log_id")
if log_id is None:
log_id = table.cur_log_id
start_time = args.get("start_time")
# if start_time is not None:
# start_time = datetime.strptime(str(start_time), "%Y")
end_time = args.get("end_time")
# if end_time is not None:
# end_time = datetime.strptime(str(end_time), "%Y")
datas = []
for index_id in args.get("index_ids"):
for country_id in args.get("country_ids"):
index = SocioeconomicIndexes.r_query().filter(
SocioeconomicIndexes.id==index_id).first()
country = Country.r_query().filter_by(Country.id==country_id).first()
facts = SocioeconomicFacts.find(
table_id=args.get("table_id"),
index_ids=[index_id],
country_ids=[country_id],
start_time=int(start_time),
end_time=int(end_time),
log_id=log_id)
if index is None or country is None or facts is None:
break
fact_series = []
for fact in facts:
fact_serie = {'x': fact.time, 'y': fact.value}
fact_series.append(fact_serie)
data = {
'index': index.to_json_by_fact(),
'country': country.to_json(),
'series': fact_series
}
datas.append(data)
return jsonify({
'status': 'success',
'reason': '',
'data': datas
})
def route_country():
if request.method == "GET":
country = Country.r_query().all()
return jsonify(status="success",
reason="",
data=[t.to_json() for t in country])
if request.method == "POST":
country = Country(name=request.form.get("name"),
cn_alias=request.form.get("cn_alis"),
en_alias=request.form.get("en_alis"))
db.session.add(country)
db.session.commit()
return jsonify(status="success", reason="", data=[country.to_json()])
if request.method == "DELETE":
country = Country.query.filter_by(id=request.args.get("id")).first()
db.session.delete(country)
db.session.commit()
return jsonify(status="success", reason="", data=[country.to_json()])
def agriculture_excel():
if 'filename' in request.json:
filename = current_app.config['UPLOAD_FOLDER']+'/'+request.json['filename']
#filename = request.json['filename']
df = pandas.read_csv(filename)
else:
return jsonify({
'status':'fail',
'reason':'there is no filename'
})
if 'table_id' in request.json:
table_id = request.json['table_id']
table = AgricultureTable.r_query().filter(id=table_id).first()
if table is None:
return jsonify({
'status':'fail',
'reason':'the table does not exist'
})
else:
return jsonify({
'status':'fail',
'reason':'there is no table id'
})
if 'field' in request.json:
field = request.json['field']
else:
return jsonify({
'status':'fail',
'reason':'there is no field'
})
table_id = request.json['table_id']
years = []
note = request.json['note']
old_log = table.cur_log
new_log = ArgLog(note=note, user_id=current_user.id,
table_id=table_id, pre_log_id=old_log.id, timestamp=datetime.now())
db.session.add(new_log)
try:
db.session.commit()
except IntegrityError:
print("EXCEPETION")
print(1)
db.session.rollback()
table.cur_log_id = new_log.id
db.session.add(table)
try:
db.session.commit()
except IntegrityError:
print("EXCEPETION")
db.session.rollback()
log_id = table.cur_log_id
for item in df.columns.tolist():
if item not in ['Country', 'Indicator', 'Product']:
years.append(item)
print(years)
for i in range(0, df.shape[0]):
country_l = getattr(Country, field.strip())
country_name = df.iloc[i]['Country']
country = Country.r_query().filter(country_l==country_name).first()
country_id = country.id
index_l = getattr(AgricultureIndexes, field.strip())
index_name = df.iloc[i]['Indicator']
index = AgricultureIndexes.r_query().filter(AgricultureIndexes.table_id==table_id).filter(index_l==index_name).first()
index_id = index.id
kind_l = getattr(AgricultureKind, field.strip())
kind_name = df.iloc[i]['Product']
kind = AgricultureKind.r_query().filter(kind_l==kind_name).first()
kind_id = kind.id
for year in years:
value = df.iloc[i][year]
print(year)
if value != 'undefined':
fact = AgricultureFacts(time=int(year), index_id=index_id, country_id=country_id, value=int(value), log_id=log_id, kind_id=kind_id)
db.session.add(fact)
try:
db.session.commit()
except IntegrityError:
print("EXCEPETION")
db.session.rollback()
db.session.commit()
return jsonify({
'status':'success',
'reason':''
})
from flask import current_app
from app import db, create_app
from app.model.user import User,Role
from app.model.quantify import Country
from app.model.qualitative.information import Kind
if __name__ == '__main__':
app = create_app('development')
app_ctx = app.app_context()
app_ctx.push()
print("建立数据表")
db.create_all()
print("新增国家")
cn = Country(name="China", en_alis="china", cn_alis="中国")
db.session.add(cn)
br = Country(name="Brazil", en_alis="brazil", cn_alis="巴西")
db.session.add(br)
ind = Country(name="India", en_alis="india", cn_alis="印度")
db.session.add(ind)
ru = Country(name="Russia", en_alis="russia", cn_alis="俄罗斯")
db.session.add(ru)
sa = Country(name="SouthAfrica", en_alis="south africa", cn_alis="南非")
db.session.add(sa)
db.session.commit()
print("新增类别")
adp = Kind(id=1, name="adp", en_alis="Agricultural Development Policies", cn_alis="农业发展政策")
db.session.add(adp)
atp = Kind(id=2, name="atp", en_alis="Agricultural Trade Policies", cn_alis="农业贸易政策")
def socioeconomic_facts():
if request.method == "GET":
# if not current_user.can(Permission.QUANTIFY_R):
# return jsonify(status="fail", data=[], reason="no permission")
args = std_json(request.args)
if not check_args(ALLOW_ARGS, args.keys()):
return jsonify(status="fail", reason="error args", data=[])
start_time = args.get("start_time")
# if start_time is not None:
# start_time = datetime.strptime(str(start_time), "%Y")
end_time = args.get("end_time")
# if end_time is not None:
# end_time = datetime.strptime(str(end_time), "%Y")
table = SocioeconomicTable.r_query().filter(
SocioeconomicTable.id==args.get("table_id")).first()
if table is None:
return jsonify(status="fail", data=[], reason="no such table")
log_id = args.get("log_id")
if log_id is None:
log_id = table.cur_log_id
result = []
for country_id in args.get("country_ids"):
for index_id in args.get("index_ids"):
facts = SocioeconomicFacts.find(
table_id=table.id,
index_ids=[index_id],
country_ids=[country_id],
start_time=int(start_time),
end_time=int(end_time),
log_id=log_id)
index = SocioeconomicIndexes.r_query().filter(
SocioeconomicIndexes.id==index_id).first()
country = Country.r_query().filter(Country.id==country_id).first()
result.append(
{"country": country.to_json(),
"index": index.to_json(),
"data": [fact.to_json() for fact in facts]})
# tmp_index = {}
# tmp_country = {}
# for fact in facts:
# if tmp_index.get(fact.index_id) is None:
# tmp_index[fact.index_id] = [fact]
# else:
# tmp_index[fact.index_id].append(fact)
#
# for index_id,same_index_facts in tmp_index.items():
#
# for fact in same_index_facts:
# if tmp_country.get(fact.country_id) is None:
# tmp_country[fact.country_id] = [fact]
# else:
# tmp_country[fact.country_id].append(fact)
#
# index = SocioeconomicIndexes.query.filter_by(id=index_id).first()
# final_data = []
# for country_id, same_country_facts in tmp_country.items():
# country = Country.query.filter_by(id=country_id).first()
# final_data.append({"country": country.to_json(), "data":[fact.to_json() for fact in same_country_facts]})
# result.append({"index":index.to_json(), "data":list(final_data)})
return jsonify(status="success", reason="", data=result)