CODEWITHSUNDEEP
pythonsqlalchemyrelationship
cyberra
cyberra

Reputation: 599

flask-sqlalchemy multiple relationship types between two tables

I am having trouble setting up multiple relationships between two models. These are my two models as I have them now:

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')

Product should have a one-to-one with a cover image, and a one to many with a gallery of other images. However, there is a circular dependency with the foreign keys.

I would like to do this in only two tables. Is there another way to implement these two relationships?

At this point code above throws:

sqlalchemy.exc.AmbiguousForeignKeysError

Upvotes: 3

Views: 3508

Answers (1)

davidism
davidism

Reputation: 127410

There are two circular dependencies here:

  1. The foreign keys are mutually dependent on the existence of each table. One of the fks must be created after the dependent table already exists. Set use_alter=True and name='some_name on one to resolve this.
  2. The relationships both need to resolve the primary_key of their target after insert, but are mutually dependent on both having already been commited. Set post_update=True on one to resolve this.

See the following documentation:

Here is a working example demonstrating the solution.

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

Upvotes: 2

Related Questions