#!/usr/bin/python3
# encoding:utf-8
from flask import Flask, url_for
import flask
# url_for 接受两个参数(endpoint,**value)endpoint没有指定就是默认的函数名,根据 view_func.__name__
import pymysql
import json
import requests
app = Flask(__name__)
import decimal
# 规避TypeError: Object of type ‘Decimal‘ is not JSON serializable报错
class DecimalEncoder(json.JSONEncoder):
def default(self, o):
if isinstance(o, decimal.Decimal):
return float(o)
super(DecimalEncoder, self).default(o)
@app.route('/API/query/ChineseLessThanMath', endpoint='test112',methods=['GET'])
def ChineseLessThanMath():
db = pymysql.connect(host='localhost',
user='root',
password='000000',
database='test')
# 使用 cursor() 方法创建一个游标对象 cursor
cursor = db.cursor()
# 带字段名称的游标
cursor = db.cursor(cursor=pymysql.cursors.DictCursor)
# 使用 execute() 方法执行 SQL 查询
sql = "select t1.s_score as '语文',t2.s_score as '数学',t1.s_id,t3.* from\
(select s_score,s_id from score where c_id ='01') t1,\
(select s_score,s_id from score where c_id ='02') t2, student t3 where t1.s_id=t2.s_id and t1.s_id=t3.s_id and t1.s_score <t2.s_score"
cursor.execute(sql)
# 使用 fetchone() 方法获取单条数据.
data1 = cursor.fetchone()
data = cursor.fetchall()
db.close()
print('data:', data, '\n', 'data1:', data1)
# return str(data)
return json.dumps(data, ensure_ascii=False,cls=DecimalEncoder)
# return data[0][4]
# 关闭数据库连接
# app.add_url_rule('/list/1111', endpoint='test112', view_func=test112,
# methods=['GET']) # 这里endpoint可以不填 ,view_func 一定要是函数名:具体看下面源码解释
@app.route('/API/query/TotalScore', methods=['get'])
def TotalScore():
# from-data格式参数
db = pymysql.connect(host='localhost',
user='root',
password='000000',
database='test')
# 使用 cursor() 方法创建一个游标对象 cursor
cursor = db.cursor()
# 带字段名称的游标
cursor = db.cursor(cursor=pymysql.cursors.DictCursor)
# 使用 execute() 方法执行 SQL 查询
sql = "select sum(t1.s_score),t1.s_id,count(t1.c_id),t2.* from `score` t1 ,student t2 where t1.s_id=t2.s_id group by t1.s_id;"
cursor.execute(sql)
data = cursor.fetchall()
lista = ['msg', 'msg_code', 'data']
listb = ['请求成功', 200, data]
ren1 = dict(zip(lista, listb))
if ren1:
ren = ren1
else:
ren = {'msg': '用户名或密码错误', 'msg_code': -1}
return json.dumps(ren, ensure_ascii=False,cls=DecimalEncoder)
@app.route('/API/query/AvgLessThanSixth', methods=['post'])
def AvgLessThanSixth():
# from-data格式参数
usrname = flask.request.json.get('usrname')
Avgsroce = flask.request.json.get('Avgsroce')
db = pymysql.connect(host='localhost',
user='root',
password='000000',
database='test')
# 使用 cursor() 方法创建一个游标对象 cursor
cursor = db.cursor()
# 带字段名称的游标
cursor = db.cursor(cursor=pymysql.cursors.DictCursor)
# 使用 execute() 方法执行 SQL 查询
sql = "select avg(s_score),t1.s_id,t3.s_name from `score` t1,student t3 where t1.s_id=t3.s_id group by t1.s_id having avg(s_score)>{0};".format(Avgsroce)
cursor.execute(sql)
# 使用 fetchone() 方法获取单条数据.
data1 = cursor.fetchone()
data = cursor.fetchall()
lista = ['msg', 'msg_code', 'data']
listb = ['请求成功', 200, data]
ren1 = dict(zip(lista, listb))
if usrname and Avgsroce:
if usrname == 'test':
ren = ren1
else:
ren = {'msg': '用户名或密码错误', 'msg_code': -1}
else:
ren = {'msg': '参数错误', 'msg_code': 1001}
return json.dumps(ren, ensure_ascii=False,cls=DecimalEncoder)
@app.route('/API/Add/Student', methods=['post'])
def AddStudent():
# from-data格式参数
name = flask.request.json.get('name')
birth = flask.request.json.get('birth')
sex = flask.request.json.get('sex')
db = pymysql.connect(host='localhost',
user='root',
password='000000',
database='test')
# 使用 cursor() 方法创建一个游标对象 cursor
cursor = db.cursor()
# 带字段名称的游标
cursor = db.cursor(cursor=pymysql.cursors.DictCursor)
# 使用 execute() 方法执行 SQL 查询
sql = "insert into student(s_name,s_birth,s_sex) values('{0}','{1}','{2}');".format(name,birth,sex)
print(sql)
cursor.execute(sql)
db.commit()
# 使用 fetchone() 方法获取单条数据.
data1 = cursor.fetchone()
data = cursor.fetchall()
lista = ['msg', 'msg_code', 'data']
listb = ['请求成功', 200, data]
ren1 = dict(zip(lista, listb))
if name and birth and sex:
ren = ren1
else:
ren = {'msg': '参数错误', 'msg_code': 1001}
return json.dumps(ren, ensure_ascii=False,cls=DecimalEncoder)
@app.route('/API/Add/Score', methods=['post'])
def AddScore():
# from-data格式参数
sid = flask.request.json.get('sid')
cid = flask.request.json.get('cid')
score = flask.request.json.get('score')
db = pymysql.connect(host='localhost',
user='root',
password='000000',
database='test')
# 使用 cursor() 方法创建一个游标对象 cursor
cursor = db.cursor()
# 带字段名称的游标
cursor = db.cursor(cursor=pymysql.cursors.DictCursor)
# 使用 execute() 方法执行 SQL 查询
sql = "insert into score(s_id,c_id,s_score) values('{0}','{1}',{2});".format(sid,cid,score)
print(sql)
cursor.execute(sql)
db.commit()
# 使用 fetchone() 方法获取单条数据.
data1 = cursor.fetchone()
data = cursor.fetchall()
lista = ['msg', 'msg_code', 'data']
listb = ['请求成功', 200, data]
ren1 = dict(zip(lista, listb))
if sid and cid and score:
ren = ren1
else:
ren = {'msg': '参数错误', 'msg_code': 1001}
return json.dumps(ren, ensure_ascii=False,cls=DecimalEncoder)
# #请求上下文
# with app.test_request_context():
# pass
if __name__ == '__main__':
app.run(debug=True,host='127.0.0.1', port=9000)
# app.run(debug=True)
flask接口服务搭建
最新推荐文章于 2024-07-08 17:52:59 发布