4.5 团队作业1(后台数据操作)
经过将近一周的时间,弄这个,一直在尝试各种方法,写了删、删了写,反复修改。终于可以正常的返回数据
看后台代码
app.py
import utils from flask import Flask from flask import request from flask import render_template from flask import jsonify import string app = Flask(__name__) @app.route('/') def hello_world(): return render_template("main.html") @app.route("/c1") def get_c1_data(): data = utils.get_c1_data() return jsonify({"confirm":data[0],"suspect":data[1],"heal":data[2],"dead":data[3]}) @app.route("/c2") def get_c2_data(): res = [] for tup in utils.get_c2_data(): # print(tup) res.append({"name":tup[0],"value":int(tup[1])}) return jsonify({"data":res}) @app.route("/l1") def get_l1_data(): data = utils.get_l1_data() day,confirm,suspect,heal,dead = [],[],[],[],[] for a,b,c,d,e in data[7:]: day.append(a.strftime("%m-%d")) # a是datatime类型 confirm.append(b) suspect.append(c) heal.append(d) dead.append(e) return jsonify({"day": day, "confirm": confirm, "suspect": suspect, "heal": heal, "dead": dead}) @app.route("/l2") def get_l2_data(): data = utils.get_l2_data() day, confirm_add, suspect_add = [], [], [] for a, b, c in data[7:]: day.append(a.strftime("%m-%d")) # a是datatime类型 confirm_add.append(b) suspect_add.append(c) return jsonify({"day": day, "confirm_add": confirm_add, "suspect_add": suspect_add}) @app.route("/r1") def get_r1_data(): data = utils.get_r1_data() city = [] confirm = [] for k,v in data: city.append(k) confirm.append(int(v)) return jsonify({"city": city, "confirm": confirm}) @app.route("/r2") def get_r2_data(): res = [] for tup in utils.get_r2_data(): # print(tup) res.append({"name": tup[0], "value": int(tup[1])}) return jsonify({"data": res}) @app.route("/time") def get_time(): return utils.get_time() if __name__ == '__main__': app.run(host='127.0.0.1', port=5000)
utils.py
import time import pymysql from decimal import Decimal import json def get_time(): time_str = time.strftime("%Y{}%m{}%d{} %X") return time_str.format("年", "月", "日") def get_conn(): """ :return: 连接,游标 """ # 创建连接 conn = pymysql.connect(host="localhost", user="root", password="root1", db="cov", charset="utf8") # 创建游标 cursor = conn.cursor() # 执行完毕返回的结果集默认以元组显示 return conn, cursor def close_conn(conn, cursor): cursor.close() conn.close() def query(sql, *args): """ 封装通用查询 :param sql: :param args: :return: 返回查询到的结果,((),(),)的形式 """ conn, cursor = get_conn() cursor.execute(sql, args) res = cursor.fetchall() close_conn(conn, cursor) return res def get_c1_data(): """ :return: 返回大屏div id=c1 的数据 """ # 因为会更新多次数据,取时间戳最新的那组数据 sql = "select sum(confirm)," \ "(select suspect from history order by ds desc limit 1)," \ "sum(heal)," \ "sum(dead) " \ "from details " \ "where update_time=(select update_time from details order by update_time desc limit 1) " res = query(sql) res_list = [str(i) for i in res[0]] res_tuple=tuple(res_list) return res_tuple def get_c2_data(): """ :return: 返回各省数据 """ # 因为会更新多次数据,取时间戳最新的那组数据 sql = "select province,sum(confirm) from details " \ "where update_time=(select update_time from details " \ "order by update_time desc limit 1) " \ "group by province" res = query(sql) return res def get_l1_data(): sql = "select ds,confirm,suspect,heal,dead from history" res = query(sql) return res def get_l2_data(): sql = "select ds,confirm_add,suspect_add from history" res = query(sql) return res def get_r1_data(): """ :return: 返回非湖北地区城市确诊人数前5名 """ sql = 'SELECT province,confirm_add FROM ' \ '(select province,sum(confirm_add) as confirm_add from details ' \ 'where update_time=(select update_time from details order by update_time desc limit 1) ' \ 'group by province) as a ' \ 'ORDER BY confirm_add DESC LIMIT 5' res = query(sql) return res def get_r2_data(): ''' 获取世界各国的疫情数据 :return: ''' # 因为会更新多次数据,取时间戳最新的那组数据 sql = "select province,sum(confirm_add) from details " \ "where update_time=(select update_time from details " \ "order by update_time desc limit 1) " \ "group by province" res = query(sql) return res if __name__ == "__main__": print(get_c1_data())