from flask import Flask
import pandas as pd
import pymysql
import sys
def read_mysql_and_insert():
# pymysql for df read_sql
try:
conn = pymysql.connect(host='x.x.x.x', user='xxx', password='xxx', db='xxx', charset='utf8')
except pymysql.err.OperationalError as e:
print('Error is ' + str(e))
sys.exit()
try:
# pymysql 读取数据库，返回df类型数据
sql = '''
SELECT AVG(time2.`PTP sync time BETWEEN RU AND DU`) AS PTP_sync
,AVG(time2.`Radio data path sync time`) AS path_sync
,AVG(time2.`Tx Carrier setup time IN radio`) AS Tx_setup
,AVG(time2.`Rx Carrier setup time IN radio`) AS Rx_setup
,AVG(time2.`Tx Carrier activate time IN radio`) AS Tx_activate
,AVG(time2.`Rx Carrier activate time IN radio`) AS Rx_activate
,AVG(time2.`Tx Carrier deactivate time IN radio`) AS Tx_deactivate
,AVG(time2.`Rx Carrier deactivate time IN radio`) AS Rx_deactivate
,AVG(time1.rru_reset_detected) AS reset_detected
,AVG(time1.rru_reset_enable) AS reset_enable
,AVG(time1.rru_reset_onair) AS reset_onair
,AVG(time1.rru_reset_detected)+AVG(time1.rru_reset_enable)+AVG(time1.rru_reset_onair) AS totol
,left(version1.rru_vesion,25)
FROM report_report_reportperiod AS time1
LEFT JOIN report_parse_soap_result AS time2
ON time1.fk_report_uuid_id = time2.fk_report_uuid_id
LEFT JOIN report_reportinfo AS info1
ON time1.fk_report_uuid_id = info1.report_uuid
LEFT JOIN report_reportdetail AS version1
ON time1.fk_report_uuid_id = version1.fk_report_uuid_id
WHERE time1.rru_reset_detected >0
AND info1.platform_name='AEQE'
GROUP BY version1.rru_vesion;
'''
df = pd.read_sql(sql, con=conn)
except pymysql.err.ProgrammingError as e:
print('Error is ' + str(e))
sys.exit()
print(df.head())
return df
app = Flask(__name__)
@app.route('/')
def show_excel():
df = read_mysql_and_insert()
table_html = df.to_html()
return f"""
<html>
<body>
<h1>Reset time</h1>
<div>{table_html}</div>
</body>
</html>
"""
if __name__ == '__main__':
app.run(host="0.0.0.0")