flask-sqlalchemy 两个表之间的多种关系类型

Question

我在设置两个模型之间的多个关系时遇到了问题。这是我现在的两个模型：

class Product(db.Model):
    tablename='product'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(50))
    image_id = db.Column(db.Integer, db.ForeignKey('image.id'))
    image = db.relationship('Image',uselist=False,backref=db.backref('product'))

class Image(db.Model):
    __tablename__='address'
    id = db.Column(db.Integer, primary_key=True)
    normal = db.Column(db.String(200))
    product_id = db.Column(db.Integer, db.ForeignKey('product.id'))
    product = db.relationship('Product', backref='product_images')

产品应该有一个封面图片的单一关系，还有一个与其他图片的多重关系。但是，外键之间存在循环依赖。

我希望只用两个表来实现这些关系。有没有其他方法可以实现这两个关系呢？

目前，上面的代码会抛出以下错误：

sqlalchemy.exc.AmbiguousForeignKeysError

Answer 1

这里有两个循环依赖的问题：

1. 外键相互依赖于每个表的存在。也就是说，必须在相关的表已经存在之后才能创建其中一个外键。可以在其中一个外键上设置 use_alter=True 和 name='some_name' 来解决这个问题。
2. 这两个关系都需要在插入后解决它们目标的主键，但它们又相互依赖，必须在两者都已经提交之后才能进行。可以在其中一个关系上设置 post_update=True 来解决这个问题。

请查看以下文档：

• 循环依赖错误
• 依赖外键
• 依赖关系

下面是一个演示解决方案的工作示例。

from sqlalchemy import create_engine, Column, Integer, String, ForeignKey, Table
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship

engine = create_engine('sqlite:///:memory:', echo=True)
Session = sessionmaker(bind=engine)
session = Session()
Base = declarative_base(bind=engine)


class Product(Base):
    __tablename__ = 'product'

    id = Column(Integer, primary_key=True)
    name = Column(String, nullable=False)

    # cover image foreign key
    # use_alter=True along with name='' adds this foreign key after Image has been created to avoid circular dependency
    cover_id = Column(Integer, ForeignKey('image.id', use_alter=True, name='fk_product_cover_id'))

    # cover image one-to-one relationship
    # set post_update=True to avoid circular dependency during
    cover = relationship('Image', foreign_keys=cover_id, post_update=True)

class Image(Base):
    __tablename__ = 'image'

    id = Column(Integer, primary_key=True)
    path = Column(String, nullable=False)
    product_id = Column(Integer, ForeignKey(Product.id))

    # product gallery many-to-one
    product = relationship(Product, foreign_keys=product_id, backref='images')

    # nothing special was need in Image, all circular dependencies were solved in Product


Base.metadata.create_all()

# create some images
i1 = Image(path='img1')
i2 = Image(path='img2')
i3 = Image(path='img3')
i4 = Image(path='img4')

# create a product with those images, one of which will also be the cover
p1 = Product(name='sample', images=[i1, i2, i3, i4], cover=i2)

session.add(p1)
session.commit()

print 'cover:', p1.cover.path  # prints one cover image path
print 'images:', [i.path for i in p1.images]  # prints 4 gallery image paths

print 'image product:', p1.images[0].product.name  # prints product name from image perspective