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)
来源: https://blog.csdn.net/LJXZDN/article/details/82291478