Flask-sqlalchemy外键关系映射

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

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = 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 Flask
from flask_sqlalchemy import SQLAlchemy

app = 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()