3个.py文件
app.py #转发器
create_html.py # 利用pandas 将result生成 表格形式
mysql.py #连接数据库获取数据.
app.py:
from flask import Flask
from flask import request
from create_html import convert_to_html
from mysql import get_result
app = Flask(__name__)
@app.route('/',methods=['GET','POST'])
def home():
return '<h1>Home<h1>'
@app.route('/getdata',methods=['GET'])
def signig_from():
result = get_result()#获取数据
title = ['监护人','学校','年级','班级','学生姓名','托管时间','签订时间']
h=convert_to_html(result,title)
return h
#不用管下面
@app.route('/signin',methods=['POST'])
def signin():
if request.form['username']=='admin' and request.form['password']=='password':
return '<h3>Hello,admin!<h3>'
return '<h3>Bad username or password.<h3/>'
if __name__ == '__main__':
#指定ip和端口
app.run(host='0.0.0.0', port=80)
mysql.py
import pymysql
def get_result():
conn = pymysql.connect(host='xxxx',user='root',passwd='xxx',db='ygxy-sys')
cur = conn.cursor()
sql = '''SELECT
a.guardian_name ,
e.s_school ,
e.s_session ,
e.s_class ,
d.NAME,
case param->'$[0]'
when '["0", "1", "0"]' then '中午'
when '["0", "1", "1"]' then '中午/晚上'
when '["0", "0", "1"]' then '晚上'
else '其他' end ,
b.create_time
FROM
guardian_data AS a,
student_signed_protocol AS b,
guardian_ship AS c,
student_data AS d,
student_school AS e
WHERE
c.main_guardian_id = a.guardian_phone
AND b.id = c.id
AND b.sid = d.sid
AND d.sid = e.sid
'''
print(sql)
result = [[],[],[],[],[],[],[]]
try:
cur.execute(sql)
results = cur.fetchall()
for row in results:
for i in range(0,7):
result[i].append(row[i])
except Exception as e:
print(e)
finally:
return result
conn.close()
注:pandas中DataFrame 方法,接收数据的格式如下:
-
result = [[u'2016-08-25',u'2016-08-26',u'2016-08-27'],[u'张三',u'李四',u'王二']]
-
title = [u'日期',u'姓名']
从数据库里取出的值格式不是这样,所以需要做如下操作,把同一列值都放到一个list中
result = [[],[],[],[],[],[],[]]
try:
cur.execute(sql)
results = cur.fetchall()
for row in results:
for i in range(0,7):
result[i].append(row[i])
create_html.py
import pandas as pd
from mysql import get_result
def convert_to_html(result,title):
d = {}
index = 0
#for row in result:
for t in title:
d[t] = result[index]
index+=1
df = pd.DataFrame(d)
#如数据过长,可能在表格中无法显示,加上pd.set_option语句可以避免这一情况
pd.set_option('max_colwidth',200)
df = df [title]
h =df.to_html(index=True)
with open('11.html','w',encoding='utf-8') as f:
for eachline in h:
f.write(eachline)
return h
if __name__=="__main__":
result = get_result()
title = ['监护人','学校','年级','班级','学生姓名','托管时间','签订时间']
convert_to_html(result,title)