Flask-sqlalchemy外键关系映射

本文中,为了测试方便,所以使用sqlite,定义两张表User Article
Article中author_id引用User表中的id,也就是author_id作为User表中id的外键

from flask import Flaskfrom flask_sqlalchemy import SQLAlchemyapp = Flask(__name__)app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///./Article.db'db = SQLAlchemy(app)class User(db.Model):    __tablename__ = 'user'    id = db.Column(db.Integer,primary_key = True)    username = db.Column(db.String(100),nullable = False)class Article(db.Model):    __tablename__ = 'artivle'    id = db.Column(db.Integer,primary_key = True)    title = db.Column(db.String(100),nullable = False)    content = db.Column(db.String(100),nullable = True)    #author_id引用User表中的id    author_id = db.Column(db.Integer,db.ForeignKey('user.id'))# db.create_all()@app.route('/')def index():    #想要添加文章,由于依赖于用户,所以先添加用户    # user1 = User(username='zmy')    # db.session.add(user1)    # db.session.commit()    #然后添加一篇文章    article = Article(title = 'today',content = 'hello',author_id = 1)    db.session.add(article)    db.session.commit()    return 'index'if __name__ == '__main__':    app.run()

当我们想寻找文章标题为today的作者可以用一下方式

article = Article.query.filter(Article.title =='today').first()    user = User.query.filter(User.id == article.author_id).first()    print(user.username)

但这种方式太过于繁琐,作为sqlalchemy可以用一下方法

class Article(db.Model):    __tablename__ = 'article'    id = db.Column(db.Integer,primary_key = True)    title = db.Column(db.String(100),nullable = False)    content = db.Column(db.String(100),nullable = True)    #author_id引用User表中的id    author_id = db.Column(db.Integer,db.ForeignKey('user.id'))    #第一个参数为你要关系到哪个模型的名字,也就是类名    #db.backref('articles')第一个参数articles为要反向引用的名字,也可以用其他名字    #正向引用是Article访问author,反向引用是从User访问表Article    author = db.relationship('User',backref=db.backref('articles'))

然后在视图函数中

article = Article.query.filter(Article.title == 'today').first()    print(article.author.username)

与刚才比较繁琐的方法对比

article = Article.query.filter(Article.title =='today').first()    user = User.query.filter(User.id == article.author_id).first()    print(user.username)

这样我们就可以获取某个用户的所有文章

user = User.query.filter(User.username == 'zmy').first()    result = user.articles    for article in result:        print('-'*10)        print(article.title)

完整代码

from flask import Flaskfrom flask_sqlalchemy import SQLAlchemyapp = Flask(__name__)app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///./Article.db'db = SQLAlchemy(app)class User(db.Model):    __tablename__ = 'user'    id = db.Column(db.Integer,primary_key = True)    username = db.Column(db.String(100),nullable = False)class Article(db.Model):    __tablename__ = 'article'    id = db.Column(db.Integer,primary_key = True)    title = db.Column(db.String(100),nullable = False)    content = db.Column(db.String(100),nullable = True)    #author_id引用User表中的id    author_id = db.Column(db.Integer,db.ForeignKey('user.id'))    #第一个参数为你要关系到哪个模型的名字,也就是类名    #db.backref('articles')第一个参数articles为要反向引用的名字,也可以用其他名字    #正向引用是Article访问author,反向引用是从User访问表Article    author = db.relationship('User',backref=db.backref('articles'))#如果想新增字段,这里是不会做第二次映射的#可以删除数据库重建,或者手动更改db.create_all()@app.route('/')def index():    #想要添加文章,由于依赖于用户,所以先添加用户    # user1 = User(username='zmy')    # db.session.add(user1)    # db.session.commit()    #然后添加一篇文章    # article = Article(title = 'tomorrow',content = 'Hi',author_id = 3)    # db.session.add(article)    # db.session.commit()    #想要找文章标题为today的作者    # article = Article.query.filter(Article.title =='today').first()    # user = User.query.filter(User.id == article.author_id).first()    # print(user.username)    #想找zmy写过哪些文章    article = Article.query.filter(Article.title == 'today').first()    # print(article.author.username)    #希望用author.articles的方式就能获得zmy写过的所有文章    # author.articles    # article = Article(title = 'yesterday',content = 'world')    # article.author = User.query.filter(User.id == 1).first()    user = User.query.filter(User.username == 'zmy').first()    result = user.articles    for article in result:        print('-'*10)        print(article.title)    return 'index'if __name__ == '__main__':    app.run()