flask:用Flask-SQLAlchemy访问mysql

一，安装第三方库

$ pip3 install Flask-SQLAlchemy

二，建立到数据库的连接

import os
from dotenv import load_dotenv

from flask import Flask,jsonify

from flask_sqlalchemy import SQLAlchemy

# 加载变量
dotenv_path = os.path.join(os.path.dirname(__file__), '.env')
load_dotenv(dotenv_path=dotenv_path, verbose=True, override=True)
db_uri = os.getenv("SQLALCHEMY_DATABASE_URI")
print("打印配置项:")
print(db_uri)

app = Flask(__name__)

# 数据库信息
host = '127.0.0.1'
port = '3306'
database = 'mybase'
username = 'root'
password = 'rootpassword'

# 数据库类型+连接数据库的插件，这里使用的pymysql
DB_URI = f'mysql+pymysql://{username}:{password}@{host}:{port}/{database}'
# 配置数据库连接
app.config['SQLALCHEMY_DATABASE_URI'] = DB_URI
# 绑定Flask对象
db = SQLAlchemy()
db.init_app(app)

from controller.admin import admin
from controller.user import user

app.register_blueprint(admin,url_prefix="/admin")
app.register_blueprint(user,url_prefix="/user")

@app.errorhandler(404)
def page_not_found(error):
    data = {
        "status": "failed",
        "code": 500,
        "msg": "错误的路径"
    }
    return jsonify(data), 404

if __name__ == '__main__':
    app.run(debug=True)

三，查询数据

model类

from app import db

# 设计数据库模型
class User(db.Model):
    # 指定表名称
    __tablename__ = 'user'
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(64), nullable=False)
    nickname = db.Column(db.String(256), nullable=False)
    age = db.Column(db.Integer, default=0)
    city = db.Column(db.String(256), nullable=False)

查询:

from flask import Blueprint,jsonify,render_template,request

from models.user import User

user = Blueprint('user', __name__)

@user.route("/list/")
def user_list():
    meta = {
        "title": "用户列表",
        "code": 200,
        "msg": ""
    }

    # 得到数据库中的数据
    users = User.query.all()

    return render_template('user/list.html', meta=meta, users=users)

四，测试 效果