python3搭建一个简单的后端服务

1.目的：
通过python3+flask+mysql搭建一个简单的后端服务，实现对数据库的增删改查操作。
2.代码结构：
代码下载地址:https://github.com/songteng2012/python3-flask-mysql

2.1 .创建学生表结构st_table.sql

-- 创建学生表结构
DROP TABLE IF EXISTS students;

CREATE TABLE students (
  sno VARCHAR(20) not null,
  sname VARCHAR(20) not null,
  ssex VARCHAR(20) not null DEFAULT 'female',
  sbirthday DATE,
  sclass VARCHAR(20)
  );

2.2.连接mysql数据库st_conn.py

import pymysql

# 1.连接本地mysql数据库
conn = pymysql.connect(
    host = 'localhost',
    port = 3306,
    user = 'root',
    password = 'yaoll100.',
    database = 'mysql',
    charset = 'utf8'
)

2.3.对数据库的增删改查接口st.py
注意:其实一般不会直接裸写sql,不同框架都有成熟的orm(比如sqlalchemy)，大家可以了解一下。

from flask import Flask
from flask import request
from st_conn import conn


app = Flask(__name__)
# 学生信息students
# R: Read   读取students /GET
# C: Create 创建students /POST
# U: Update 更新students /PUT
# D: Delete 删除students /DELETE
@app.route('/students', methods=['GET', 'POST','PUT','DELETE'])
def students():
    if request.method == 'GET':
        cursor = conn.cursor()
        try:
            query = "select * from students"
            cursor.execute(query)
            conn.commit()
            results = cursor.fetchall()
            print(results)
        except Exception as e:
            print(e)
        sno = request.args.get('sno',type=int,default=None)
        if sno == 1:
            return dict(sno='sno from get')
        else:
            return dict(sno='no exit')

    elif request.method == 'POST':
        # 1.获取游标
        cursor = conn.cursor()
        try:
            #2.写sql语句
            query = "insert into students (sno,sname,ssex,sbirthday,sclass) values('003','王丽','女','1976-01-23','三班')"
            # 3.执行sql
            cursor.execute(query)
            # 4.提交事务
            conn.commit()
            # 5.接受全部的返回结果行
            results = cursor.fetchall()
            print(results)
        # 6.抛出异常
        except Exception as e:
            print(e)
        #7.post接口返回值
        print(request.form)
        print(request.data)
        print(request.json)
        sno = request.json.get('sno')
        if sno == 2:
            return dict(sno='sno from post')
        else:
            return dict(sno='no exit')

    elif request.method == 'PUT':
        cursor = conn.cursor()
        try:
            query = "update students set sname='张三' where sno='3';"
            cursor.execute(query)
            conn.commit()
            results = cursor.fetchall()
            print(results)
        except Exception as e:
            print(e)
        sno = request.args.get('sno',type=int,default=None)
        if sno == 3:
            return dict(sno='sno from put')
        else:
            return dict(sno='no exit')

    elif request.method == 'DELETE':
        cursor = conn.cursor()
        try:
            query = "DELETE FROM students WHERE sno=3;"
            cursor.execute(query)
            conn.commit()
            results = cursor.fetchall()
            print(results)
        except Exception as e:
            print(e)
        sno = request.args.get('sno',type=int,default=None)
        if sno == 4:
            return dict(sno='sno from delete')
        else:
            return dict(sno='no exit')

3.运行脚本
3.1
myproject目录下cmd下执行> venv\Scripts\activate
3.2
$ set FLASK_APP=st.py
$ set FLASK_ENV=development
$ flask run

(venv) D:\资料\2020_Java\myproject>flask run
 * Serving Flask app "st.py" (lazy loading)
 * Environment: development
 * Debug mode: on
 * Restarting with stat
 * Debugger is active!
 * Debugger PIN: 871-018-045
 * Running on http://127.0.0.1:5000/ (Press CTRL+C to quit)

4.验证效果
4.1查询
postman:

浏览器

数据库

4.2增加
postman

数据库

4.3修改
postman

数据库

4.4删除
postman

数据库