def df_variety_p(kind):
start_date = request.form.get('start')
end_date = request.form.get('end')
variety = request.form.get('variety')
if kind == 'retail':
sql_cmd = "SELECT a.TYPE, a.CREATE_DATE, b.* FROM bd_fruit_price a RIGHT join bd_fruit_price_detail b ON a.ID=b.PRICE_ID WHERE a.TYPE = 'market' \
and CREATE_DATE between '{}' AND '{}'".format(start_date, end_date)
elif kind == 'from':
sql_cmd = "SELECT a.TYPE,a.CREATE_DATE, b.* FROM bd_fruit_price a RIGHT join bd_fruit_price_detail b ON a.ID=b.PRICE_ID WHERE a.TYPE = 'from'\
and CREATE_DATE between '{}' AND '{}'".format(start_date, end_date)
elif kind == 'market':
sql_cmd = "SELECT * FROM abd_market_price WHERE TYPE = 'market' \
and CREATE_DATE between '{}' AND '{}'".format(
start_date, end_date)
df = get_data_from_sql(sql_cmd)
if df.empty == False:
pi = Price_index(df)
df_svpi = pi.variety_pi(variety)
# print(df_svpi.head())
df_svpi.rename(columns={df_svpi.iloc[:, 1].name: 'svpi'}, inplace=True)
result = df_svpi.to_dict()
else:
result = None
return jsonify(result)
def get_sentiments_report():
try:
start = request.form.get('start')
end = request.form.get('end')
# print( "前端传入的数据", request.form,type(start),start, type(end),end )
table_name = "2020_seg_sentiment_content"
# test_data
# df = pd.read_excel(os.path.join(os.getcwd(), 'test/sentiment_test/sentiment_data_source/input/seg_tea_hotspot.xlsx'))
sql_cmd ='''select * from {} where pubtime between "{}" and "{}" '''.format(table_name, start, end)
df = get_data_from_sql(sql_cmd)
if not df.empty:
result = sentiment_report(df, start, end)
else:
result = None
# with open(r'test\sentiment_test\sentiment_data_source\output\sentiment_report.json', 'w') as f:
# json.dump(result, f, indent=2, ensure_ascii=False)
except Exception as e:
error_line = e.__traceback__.tb_lineno
log.info(f'第{error_line}行发生error为: {e}')
result = f'第{error_line}行发生error为: {e}'
return jsonify(result)
def per_month_report():
try:
now = datetime.datetime.now()
start = (now - relativedelta(months=1)).strftime("%Y-%m-%d %H:%M:%S")
end = now.strftime("%Y-%m-%d %H:%M:%S")
table_name = "2020_seg_sentiment_content"
sql_cmd = '''select * from {} where pubtime between "{}" and "{}" '''.format(
table_name, start, end)
df = get_data_from_sql(sql_cmd)
if not df.empty:
df.sort_values('pubtime', inplace=True, ascending=False)
df.reset_index(drop=True, inplace=True)
result = sentiment_report(df, start, end)
with open('app/result/per_month_report.json', 'w') as f:
json.dump(result, f, indent=2, ensure_ascii=False)
log.info(f"{df.loc[0,'pubtime']} 时间前的数据已更新")
else:
result = None
except Exception as e:
error_line = e.__traceback__.tb_lineno
log.info(f'第{error_line}行发生error为: {e}')
def get_varietys(kind):
start_date = request.form.get('start')
end_date = request.form.get('end')
if kind == 'retail':
sql_cmd = "SELECT a.TYPE, a.CREATE_DATE, b.* FROM bd_fruit_price a RIGHT join bd_fruit_price_detail b ON a.ID=b.PRICE_ID WHERE a.TYPE = 'market' \
and CREATE_DATE between '{}' AND '{}'".format(start_date, end_date)
elif kind == 'from':
sql_cmd = "SELECT a.TYPE,a.CREATE_DATE, b.* FROM bd_fruit_price a RIGHT join bd_fruit_price_detail b ON a.ID=b.PRICE_ID WHERE a.TYPE = 'from'\
and CREATE_DATE between '{}' AND '{}'".format(start_date, end_date)
elif kind == 'market':
sql_cmd = "SELECT * FROM abd_market_price WHERE TYPE = 'market' \
and CREATE_DATE between '{}' AND '{}'".format(
start_date, end_date)
df = get_data_from_sql(sql_cmd)
if df.empty == False:
pi = Price_index(df)
varietys = pi.get_varietys()
# print(varietys[:5])
result = varietys
else:
result = None
return jsonify(result)
return total_dict
def sentiments_pre_warning(df, start, end):
parameter = Parameter(df, start, end)
warning_df, weibo_warning_df = pre_warning(df, parameter)
total_dict = {}
total_dict['总体'] = warning_df.to_dict(orient='list')
if not weibo_warning_df.empty:
total_dict['微博'] = weibo_warning_df.to_dict(orient='list')
return total_dict
if __name__ == '__main__':
table_name = "search_apple_news"
new_table_name = 'seg' + '_' + table_name
start = '2020-06-30 11:25:00'
end = '2020-06-23 14:34:00'
sql_cmd = '''select * from {} where pubtime between "{}" and "{}" '''.format(
table_name, start, end)
df = get_data_from_sql(sql_cmd)
try:
if df.empty == True:
raise Exception("没有数据")
except Exception as e:
print(e)
df_after = main(df)
to_database(df_after, new_table_name)