Flask框架 之Flask-SQLAlchemy操作数据库

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy import desc

class Config(object):
    ''' sqlalchemy 参数配置 '''

    # 配置数据库
    SQLALCHEMY_DATABASE_URI = 'mysql://test01:123456@127.0.0.1:3306/test01?charset=utf8'
    # 自动会追踪对象的修改并且发送信号
    SQLALCHEMY_TRACK_MODIFICATIONS = True
    # 输出sql语句
    SQLALCHEMY_ECHO = True

app = Flask(__name__)
# 载入数据库配置
app.config.from_object(Config)

# 创建sqlalchemy工具对象
db = SQLAlchemy(app)

class User(db.Model):
    __tablename__ = 'user'
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(32), unique = True)
    email = db.Column(db.String(255), unique = True)
    role_id = db.Column(db.Integer, db.ForeignKey('role.id'))

class Role(db.Model):
    __tablename__ = 'role'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(32), unique = True)
    user = db.relationship('User')

if __name__ == '__main__':

    # 删除所有表
    db.drop_all()

    # 创建所有表
    db.create_all()

    # 创建数据
    role1 = Role(name = '管理员')
    db.session.add(role1)
    db.session.commit()

    role2 = Role(name = '普通用户')
    db.session.add(role2)
    db.session.commit()

    u1 = User(username='admin', email='admin@example.com', role_id=role1.id)
    u2 = User(username='peter', email='peter@example.com', role_id=role2.id)
    db.session.add_all([u1, u2])
    db.session.commit()

    # 查询数据
    for r in db.session.query(Role).order_by(desc(Role.id)).all():
        print(r.name)
    
    u = User.query.filter_by(id=1).first()
    print(u)

    app.run()