3.3.6-PythonWeb框架 - 使用Flask框架操作数据库

3.3.6-PythonWeb框架 - 使用Flask框架操作数据库【多选一：Flask系列】

原文链接： https://blog.csdn.net/m0_52680439/article/details/134391959

第1关：查询操作

#config.py
class Config(object):
    #连接数据库
    # ********* Begin *********#
    SQLALCHEMY_DATABASE_URI='mysql+pymysql://root:123123@localhost:3306/web?charset=utf8'
    SQLALCHEMY_TRACK_MODIFICATIONS=True
    # ********* End *********#

#models.py
from app import db
 
class Message(db.Model):
    #表模型
    #********* Begin *********#
    id=db.Column(db.Integer,primary_key=True,autoincrement=True)
    provincename=db.Column(db.String(255))
    cityname=db.Column(db.String(255))
    ct=db.Column(db.Integer)
    #********* End *********#

#test.py
from app import db,app
from models import Message
from flask import render_template
 
@app.route("/select")
def select():
    '''
    首先根据ct这一列进行序排序
    然后将每一列的数据转换成一个列表
    '''
    # ********* Begin *********#
    result = Message.query.order_by(Message.ct.desc()).all()
    province = list(map(lambda x:x.provincename,result))
    city = list(map(lambda x:x.cityname,result))
    count = list(map(lambda x:x.ct,result))
    # ********* End *********#
    return render_template("index.html",city=city,count=count, province=province)
@app.route("/")
def home():
    return render_template("home.html")
 
if __name__ == "__main__":
    app.run(debug = True,host="0.0.0.0",port=8080)

第2关：增加操作

#config.py
class Config(object):
    #连接数据库
    # ********* Begin *********#
    SQLALCHEMY_DATABASE_URI='mysql+pymysql://root:123123@localhost:3306/web?charset=utf8'
    SQLALCHEMY_TRACK_MODIFICATIONS=True
    # ********* End *********#

#models.py
from app import db
 
class Message(db.Model):
    #表模型
    # ********* Begin *********#
    id=db.Column(db.Integer,primary_key=True,autoincrement=True)
    provincename=db.Column(db.String(255))
    cityname=db.Column(db.String(255))
    ct=db.Column(db.Integer)  
    # ********* End *********#

#test.py
from app import app,db
from models import Message
from flask import Flask,render_template,request,redirect
 
@app.route('/insert',methods=['GET','POST'])
def insert():
    #进行添加操作
    # ********* Begin ********* #
    newMessage = Message(provincename='纽约州',cityname="纽约市",ct=1205)
    db.session.add(newMessage)
    db.session.commit()
    # ********* End ********* #
    return redirect('/')
 
@app.route("/insert_page")
def insert_page():
    #跳转至添加页面
    return render_template("insert.html")
 
@app.route("/")
def home():
    listCity = Message.query.order_by(Message.id.desc()).all()
    return render_template("home.html",city_list = listCity)
 
if __name__ == "__main__":
    app.run(debug=True,host="0.0.0.0", port=8080)

第3关：删除操作

#config.py
class Config(object):
    #连接数据库
    # ********* Begin *********#
    SQLALCHEMY_DATABASE_URI = "mysql+pymysql://root:123123@localhost:3306/web?charset=utf8" 
    SQLALCHEMY_TRACK_MODIFICATIONS = True
    # ********* End *********#

#models.py
from app import db
 
class Message(db.Model):
    #表模型  定义四个字段 id ct provincename cityname
    # ********* Begin *********#
    id=db.Column(db.Integer, primary_key=True)  
    provincename = db.Column(db.String(255))
    cityname = db.Column(db.String(255))
    ct = db.Column(db.Integer)
    # ********* End *********#

#test.py
from app import db,app
from models import Message
from flask import render_template,redirect,request
 
@app.route("/delete",methods=['GET'])
def delete():
    #操作数据库删除目标数据   前台传入的参数为  id
    # ********* Begin *********#
    u=Message.query.filter_by(id=1).first()
    db.session.delete(u)
    db.session.commit()
    # ********* End *********#
    #删除完重定向到主页
    return redirect('/')
 
@app.route("/")
def home():
    listCity = Message.query.order_by(Message.id.desc()).all()
    return render_template("home.html",city_list = listCity)
 
if __name__ == "__main__":
    app.run(debug = True,host="0.0.0.0",port=8080)

第4关：修改操作

from app import app,db
from models import Message
from flask import render_template
from flask import redirect,request
import pymysql
 
 
@app.route("/alter",methods=['POST'])
def alter():
    #接收参数，修改数据
    # ********* Begin *********#
    u=Message.query.filter_by(id=1).first()
    u.ct=100000
    db.session.commit()
    # ********* End *********#
    return redirect('/')
 
#修改页面
@app.route("/alter_page",methods=['GET'])
def alter_page():
    id = request.args.get("id")
    province = request.args.get("provincename")
    cityname = request.args.get("cityname")
    ct = request.args.get("ct")
    message = Message(id = id,provincename=province,cityname=cityname,ct=ct)
    print(message)
    return render_template("alter.html",message = message)
 
@app.route("/")
def home():
    listCity = Message.query.order_by(Message.id.desc()).all()
    return render_template("home.html",city_list = listCity)
 
if __name__ == "__main__":
    app.run(debug=True, host="0.0.0.0", port=8080)