CODEWITHSUNDEEP
pythonsqlalchemy
user3778914
user3778914

Reputation: 393

Cannot persist many-to-many relationship correctly

Article and Image have many-to-many relationship and their intermediate table is ArticleImage. I expect I'll see 1 rows in Article table, 3 rows in Image table, and 3 rows in ArticleImage table. Article and ArticleImage part are working as expected, however, there is 0 row in Image table and the foreigh-key column 'image_id' in ArticleImage table of that 3 newly-added rows are 'NULL'. What am I missing to persist rows in Image table correctly? (there is no error printed)

My code:

db_session = some_function()  # get connection to database here
try:
    article = Article()
    db_session.add(article)
    article.name = 'new post #1'
    article.author = 'pojan'
    article.images.append(Image('img1.jpg'))
    article.images.append(Image('img2.jpg'))
    article.images.append(Image('img3.jpg'))
    db_session.merge(article)
    db_session.commit()

    print db_session.query(Article).count()       # 1
    print db_session.query(ArticleImage).count()  # 3
    print db_session.query(Image).count()         # 0 <- error here, images are not added 

except Exception as e:
    print e

I have also tried the following code, now the Image would be added to database, but the image_id column in ArticleImage still remain as NULL:

    article = Article()
    db_session.add(article)
    article.name = 'new post #1'
    article.author = 'pojan'

    image1 = Image('img1.jpg')
    image2 = Image('img2.jpg')
    image3 = Image('img3.jpg')
    db_session.add(image1)
    db_session.add(image2)
    db_session.add(image3)

    article.images.append(image1)
    article.images.append(image2)
    article.images.append(image3)
    db_session.merge(article)
    db_session.commit()

My model:

class Image(EntityClass):
    attributes = ['id', 'name', 'path']

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

    def __init__(self, name):
        self.name = name
        self.path = '/root/' + name

class ArticleImage(EntityClass):

    article_id = Column(Integer, ForeignKey('article.id'))
    image_id = Column(Integer, ForeignKey('image.id'))
    image = relationship(Image)
    position = Column(Integer)

class Article(EntityClass):

    attributes = ['id','name','author']

    id = Column(Integer, primary_key=True)

    _images = relationship(ArticleImage,
             order_by=[ArticleImage.position],
             collection_class=ordering_list('position'))
    images = association_proxy('_images', 'image')

    name = Column(String(255), nullable=False)
    author = Column(String(255), nullable=False)

(the design is base on an article I found on internet, and I made some minor changes)

Upvotes: 1

Views: 71

Answers (2)

Paul Lo
Paul Lo

Reputation: 6148

According to the article you refer to, you should also add a constructor in ArticleImage to associate ArticleImage with Image when an ArticleImage oject is created, otherwise ArticleImage would never know which image row it should refer to.

class ArticleImage(EntityClass):

    article_id = Column(Integer, ForeignKey('article.id'))
    image_id = Column(Integer, ForeignKey('image.id'))
    image = relationship(Image)
    position = Column(Integer)

    def __init__(self, image=None):
        self.image = image

Upvotes: 1

gy8409
gy8409

Reputation: 71

You missed primary key in ArticleImage compared to the example you give.

I would get

sqlalchemy.exc.ArgumentError: Mapper Mapper|ArticleImage|article_image could not assemble any primary key columns for mapped table 'article_image'

with your ArticleImage code.

Upvotes: 0

Related Questions