db.backref()是你需要对放置 backref的那一边的参数，

(在上例中为 Address类的 .user属性)指定参数时, 使用 backref()函数代替字符串, 常见的有 lazy='dynamic'(禁止自动查询, 用于添加过滤器)。

 backref用于在关系另一端的类中快捷地创建一个指向当前类对象的属性, 而当需要对那个属性指定参数时使用 db.backref()。

Flask-SQLAlchemy(MySQL)之一对多、一对一、多对多关系

一对多

1. 创建两个模型

class Person(db.Model):
    __tablename__ = 'person'
    name = db.Column(db.String(20), primary_key=True)
    age = db.Column(db.Integer)
    birth = db.Column(db.Date)
    phone = db.Column(db.String(11), unique=True)

    # 使用关系函数定义关系属性
    cars = db.relationship('Car')

    def __repr__(self):
        return '姓名：{name} 年龄：{age} 生日：{birth} 电话：{phone}'.format(name=self.name, age=self.age, birth=self.birth,
                                                                 phone=self.phone)


class Car(db.Model):
    name = db.Column(db.String(10), primary_key=True)
    price = db.Column(db.Float)
    # 定义外键(表明.字段名)
    course_phone = db.Column(db.String(11), db.ForeignKey('person.phone'))

    def __repr__(self):
        return '汽车类型：{name} 总价：{price}'.format(name=self.name, price=self.price)

2. 通过设置外键建立关系

@app.cli.command()
def insertpc():
    person = Person(name='老赵', age=27, birth=datetime.datetime.now(), phone='17777777777')
    car1 = Car(name='五菱宏光', price=55000.00, course_phone='17777777777')
    car2 = Car(name='吉利*舰', price=43000.00, course_phone='17777777777')
    db.session.add(person)
    db.session.add(car1)
    db.session.add(car2)
    db.session.commit()
    click.echo('insert')

或者通过关系属性cars调用append建立关系

@app.cli.command()
def insertpc():
    person = Person(name='老赵', age=27, birth=datetime.datetime.now(), phone='17777777777')
    car1 = Car(name='五菱宏光', price=55000.00)
    car2 = Car(name='吉利*舰', price=43000.00)
    db.session.add(person)
    person.cars.append(car1)
    person.cars.append(car2)
    db.session.commit()
    click.echo('insert')

通过remove解绑关系

person.cars.remove(car1)
db.session.commit()

3. 查询

@app.cli.command()
def querypc():
    person = Person.query.first()
    click.echo(person.cars)

查询结果如下：

[汽车类型：五菱宏光 总价：55000.0, 汽车类型：吉利*舰 总价：43000.0]

建立双向关系

1. 创建两个模型

class Person(db.Model):
    __tablename__ = 'person'
    name = db.Column(db.String(20), primary_key=True)
    age = db.Column(db.Integer)
    birth = db.Column(db.Date)
    phone = db.Column(db.String(11), unique=True)

    # 使用关系函数定义关系属性
    cars = db.relationship('Car', back_populates='person')

    def __repr__(self):
        return '姓名：{name} 年龄：{age} 生日：{birth} 电话：{phone}'.format(name=self.name, age=self.age, birth=self.birth,
                                                                 phone=self.phone)


class Car(db.Model):
    name = db.Column(db.String(10), primary_key=True)
    price = db.Column(db.Float)
    # 定义外键(表明.字段名)
    course_phone = db.Column(db.String(11), db.ForeignKey('person.phone'))

    person = db.relationship('Person', back_populates='cars')

    def __repr__(self):
        return '汽车类型：{name} 总价：{price}'.format(name=self.name, price=self.price)

2. 建立关系

@app.cli.command()
def insertpc():
    person = Person(name='老赵', age=27, birth=datetime.datetime.now(), phone='17777777777')
    car1 = Car(name='五菱宏光', price=55000.00)
    car2 = Car(name='吉利*舰', price=43000.00)
    db.session.add(person)
    person.cars.append(car1)
    person.cars.append(car2)
    db.session.commit()
    click.echo('insert')

3. 查询

@app.cli.command()
def queryc():
    car = Car.query.first()
    click.echo('{person} {car} '.format(person=car.person, car=car))

查询结果如下：

姓名：老赵 年龄：27 生日：2018-10-24 电话：17777777777 汽车类型：五菱宏光 总价：55000.0

一对一关系

1. 创建两个模型，注意：创建一对一关系是通过将uselist设为False

class Husband(db.Model):
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    name = db.Column(db.String(10))
    age = db.Column(db.Integer)

    wife = db.relationship('Wife', uselist=False)

    def __repr__(self):
        return '老公：{name} 年齡：{age}'.format(name=self.name, age=self.age)


class Wife(db.Model):
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    name = db.Column(db.String(10))
    age = db.Column(db.Integer)
    husband_id = db.Column(db.Integer, db.ForeignKey('husband.id'))

    husband = db.relationship('Husband')

    def __repr__(self):
        return '老婆：{name} 年齡：{age}'.format(name=self.name, age=self.age)

2. 建立关系，注意：一对一关系不能使用append，因为是单个记录，所以使用=

@app.cli.command()
def inserthw():
    husband = Husband(name='老王', age=24)
    wife = Wife(name='小红', age=18)
    db.session.add(husband)
    husband.wife = wife
    db.session.commit()
    click.echo('insert')

3. 查询

@app.cli.command()
def queryhw():
    husband = Husband.query.first()
    click.echo('{husband} {wife}'.format(husband=husband, wife=husband.wife))

查询结果如下：

老公：老王 年齡：24 老婆：小红 年齡：18

多对多关系

1. 建立存储多对多模型的外键对应关系的关联表

association_table = db.Table('association', db.Column('customer_id', db.Integer, db.ForeignKey('customer.id')),
                             db.Column('product_id', db.Integer, db.ForeignKey('product.id')))

2. 建立两个模型，secondary设为关联表的名称，具体可查看relationship

class Customer(db.Model):
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    name = db.Column(db.String(10))
    work = db.Column(db.String(20))
    products = db.relationship('Product', secondary=association_table, back_populates='customers')

    def __repr__(self):
        return '姓名：{name} 公司：{work}'.format(name=self.name, work=self.work)


class Product(db.Model):
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    name = db.Column(db.String(10))
    price = db.Column(db.Float)
    customers = db.relationship('Customer', secondary=association_table, back_populates='products')

    def __repr__(self):
        return '产品类型：{name} 单价：{price}'.format(name=self.name, price=self.price)

3. 建立关系

@app.cli.command()
def insertcp():
    customer1 = Customer(name='程老板', work='大兴有限公司')
    customer2 = Customer(name='李老板', work='弘成科技')
    customer3 = Customer(name='司马老板', work='小马加油有限公司')
    product1 = Product(name='丝绸', price=35.12)
    product2 = Product(name='铝合金', price=54.45)
    product3 = Product(name='盐', price=3.00)
    db.session.add(customer1)
    customer1.products.append(product1)
    customer1.products.append(product2)
    customer2.products.append(product2)
    customer3.products.append(product1)
    customer3.products.append(product3)
    product1.customers.append(customer1)
    product1.customers.append(customer3)
    product2.customers.append(customer2)
    product2.customers.append(customer1)
    product3.customers.append(customer3)
    db.session.commit()
    click.echo('insert')

4. 查询

@app.cli.command()
def querycp():
    customer = Customer.query.first()
    click.echo('{customer}  购买了  {products}'.format(customer=customer, products=customer.products))

查询结果：

姓名：程老板 公司：大兴有限公司  购买了  [产品类型：铝合金 单价：54.45, 产品类型：丝绸 单价：35.12]

参考

• https://github.com/greyli/helloflask/blob/master/demos/database/app.py
• SQLAlchemy一对多
• SQLAlchemy多对一
• SQLAlchemy一对一
• SQLAlchemy多对多

from flask_sqlalchemy import SQLAlchemy
from flask import FLask
app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 
    "mysql://root:12345@localhost/test"
db = SQLAlchemy(app)

from flask_sqlalchemy import SQLAlchemy
from flask import FLask
db = SQLAlchemy()

def create_app():
    app = Flask(__name__)
    db.init_app(app)
    return app

class Student(db.Model):
    __tablename__ = 'students' #指定表名
    sno = db.Column(db.String(10), primary_key=True)
    sname = db.Column(db.String(10))
    sage = db.Column(db.Integer)

time = db.Column(db.Date, default=datetime.utcnow)

class Deptment(db.Model):
    __tablename__ = 'deptments'
    dno = db.Column(db.Integer, primary_key=True)
    dname = Sname = db.Column(db.String(10),index=True)

class Deptment(db.Model):
    ...
    students = db.relationship('Student', backref='dept')
    
    
class Student(db.Model):
    ...
    dept_no = db.Column(db.Integer, db.ForeignKey('deptments.dno'))

sc = db.Table('sc',
    db.Column('sno', db.String(10), db.ForeignKey('students.sno'))
    db.Column('cno',db.String(10), db.ForeignKey('courses.cno'))
    )
    
Class Course(db.Model):
    __tablename__ = 'courses'
    cno = db.Column(db.String(10), primary_key=True)
    cname = db.Column(db.String(10), index=True)
    students = db.relationship('Student',
         secondary=sc,
         backref=db.backref('course',lazy='dynamic'),
         lazy='dynamic'
         )

flask_sqlalchemy\__init__.py:800: UserWarning: SQLALCHEMY_TRACK_MODIFICATIONS adds significant overhead and will be disabled by default in the future.  Set it to True to suppress this warning.
  warnings.warn('SQLALCHEMY_TRACK_MODIFICATIONS adds significant overhead and will be disabled by default in the future.  Set it to True to suppress this warning.')

app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = True

track_modifications = app.config.setdefault('SQLALCHEMY_TRACK_MODIFICATIONS', True)