外键

插入数据

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
import config

app = Flask(__name__)
app.config.from_object(config)
db = SQLAlchemy(app)

class User(db.Model):
    __tablename__ = 'user'
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    username = db.Column(db.String(30), nullable=False)

class Article(db.Model):
    __tablename__ = 'article'
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    title = db.Column(db.String(30), nullable=False)
    content = db.Column(db.Text, nullable=False)
    # 外键列数据类型必须和要引用的列保持一致，表名.列名
    author_id = db.Column(db.Integer, db.ForeignKey('user.id'))
    # 将Article和User关系起来，通过名为author关系，可以让Article模型中的数据结果，直接获取到User模型中对应的数据
    author = db.relationship('User', backref=db.backref('articles'))

db.create_all()

@app.route('/')
def index():
    # 创建用户
    user1 = User(username='LotusChing')
    db.session.add(user1)
    db.session.commit()
    # 创建文章
    article1 = Article(title='a', content='a1', author_id=1)
    db.session.add(article1)
    db.session.commit()
    return 'Index.'

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

通过外键获取引用数据（正向引用）

查找文章标题为a的作者

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
import config

app = Flask(__name__)
app.config.from_object(config)
db = SQLAlchemy(app)

class User(db.Model):
    __tablename__ = 'user'
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    username = db.Column(db.String(30), nullable=False)

class Article(db.Model):
    __tablename__ = 'article'
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    title = db.Column(db.String(30), nullable=False)
    content = db.Column(db.Text, nullable=False)
    author_id = db.Column(db.Integer, db.ForeignKey('user.id'))
    author = db.relationship('User', backref=db.backref('articles'))
db.create_all()

@app.route('/')
def index():
    #   笨办法
    # article1 = Article.query.filter(Article.title == 'a').first()
    # author_id = article1.author_id
    # user1 = User.query.filter(User.id == author_id).first()
    # print(user1.username)

    #   好办法
    article1 = Article.query.filter(Article.title == 'a').first()
    #   创建模型时定义了Article和User的关系，所以通过名为article的关系名，获取到了对应的User数据对象
    print('Username: {}'.format(article1.author.username))

    return 'Index.'

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

反向引用

查找LotusChing写过的所有文章

@app.route('/')
def index():
    user1 = User.query.filter(User.username == 'LotusChing').first()
    user_articles = user1.articles
    print('Article Count: {}'.format(len(user_articles)))
    for a in user_articles:
        print('Title: {} Content: {} Author: {}'.format(a.title, a.content, a.author.username))
    return 'Index.'