本文中,为了测试方便,所以使用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()