python实战之flask操作mysql数据库实战代码

from flask import Flask, request, jsonify
import pymysql
from flask_cors import CORS

db = pymysql.connect("127.0.0.1", "root", "root", "ccflow")
cursor = db.cursor()  # 数据库指针

app = Flask(__name__)
CORS(app, resources=r'/*')  # 解决跨域问题


@app.route("/list", methods=['POST', 'GET'])
def list():
    if request.method == "POST":  # 判断是否为post模式
        cursor.execute("select name,pass,email,tel from port_emp")
        data = cursor.fetchall();
        temp = {}
        result = []
        if (data != None):
            for i in data:
                temp["name"] = i[0]
                temp["pass"] = i[1]
                temp["email"] = i[2]
                temp["tel"] = i[3]
                result.append(temp.copy())
            print(len(data))
            return jsonify(result)
        else:
            print("result:NULL取到空")
            return jsonify([])
        # username = request.form.get("username")
        # print("!!!!!!!!" + str(username))
    else:
        return "get模式"


@app.route("/add", methods=['POST'])
def add():
    if request.method == "POST":  # 判断是否为post模式
        no = request.form.get("no")
        name = request.form.get("name")
        vpass = request.form.get("pass")
        email = request.form.get("email")
        tel = request.form.get("tel")
        try:
            cursor.execute(
                "insert into port_emp(no,name,pass,email,tel) values (\"" + str(no)
                + "\",\"" + str(name) + "\",\"" + str(vpass) + "\",\""
                + str(email) + "\",\"" + str(tel) + "\")")
            db.commit()
            print("ok!!!!!!!!!!!!!!!!")
            return "1"
        except Exception as e:
            print("add---failed", e)
            db.rollback()
            return "-1"


@app.route("/del", methods=['POST'])
def ddel():
    if request.method == "POST":  # 判断是否为post模式
        no = request.form.get("no")
        try:
            cursor.execute("Delete from port_emp where no =" + str(no))
            db.commit()
            print("del---ok!!!!!!!!!!!!!!!!")
            return "1"
        except Exception as e:
            print("del---failed", e)
            db.rollback()
            return "-1"


@app.route("/update", methods=['POST'])
def update():
    if request.method == "POST":  # 判断是否为post模式
        no = request.form.get("no")
        tel = request.form.get("tel")
        try:
            cursor.execute("update port_emp set tel=\"" + str(tel) + "\" where no=\"" + str(no) + "\"")
            db.commit()
            print("update---ok!!!!!!!!!!!!!!!!")
            return "1"
        except Exception as e:
            print("update---failed", e)
            db.rollback()
            return "-1"


@app.route("/vlogin", methods=['POST'])
def vlogin():
    if request.method == "POST":  # 判断是否为post模式
        username = request.form.get("username")
        password = request.form.get("password")
        cursor.execute(
            "select no,name,tel,pass from port_emp where name=\"" + str(username) + "\" and pass=\"" + str(
                password) + "\"")
        data = cursor.fetchone()
        if (data != None):
            print("result:", data)
            jsondata = {"name": str(data[0]), "tel": str(data[2]), "pass": str(data[3])}
            return jsonify(jsondata)
        else:
            print("result:NULL")
            jsondata = {}
            return jsonify(jsondata)


@app.route("/count", methods=['POST'])
def count():
    if request.method == "POST":  # 判断是否为post模式
        name = request.form.get("name")
        try:
            cursor.execute("update port_emp set idx=idx+1 where name=\""+str(name)+"\"")
            db.commit()
            print("count---ok!!!!!!!!!!!!!!!!")
            return "1"
        except Exception as e:
            print("count---failed", e)
            db.rollback()
            return "-1"

@app.route('/')
def hello_world():
    return 'Hello World!'

if __name__ == '__main__':
    app.run(host="0.0.0.0", port=5000, debug=True)
    db.close()
    print("bye!!!!!!!!!!!!!")