SQLAlchemy: add a relationship using id instead of object?

Question

Is it possible to add to a SQLAlchemy relationship using ids rather than objects?

For example, consider two declarative SQLAlchemy classes, Review and Artist, with a relationship between them:

class Review(Base):
    artist_id = Column(Integer, ForeignKey('artist.id'))
    artist = relationship(Artist, backref=backref('reviews', order_by=id))
    # etc.

class Artist(Base):
    # etc.

With a list of review ids to add to an artist, I seem to need to look up the artist from the id, then add the artist object to the review, like this:

for review_id in review_ids:
    review = session.query(Review).filter(Review.id==review_id).first()
    artist.reviews.append(review)

I'm sure it would be more efficient to skip the lookup and just add the ids, but is this possible?

I was able to accomplish this by following [this answer](https://stackoverflow.com/a/21670302/859277). — akindofyoga, Sep 12 '18 at 23:11

score 9 · Accepted Answer · answered May 17 '11 at 02:44

Your best bet is probably to compose an update expression against the backing tables. Otherwise, you can't really modify a Review without actually querying for it (and that's what your doing; you aren't actually modifying the Artist at all).

Assuming Review.id is the primary key, That would roughly be:

conn = session.connection()
conn.execute(Review.__table__
                .update()
                .values(artist_id=artist_id)
                .where(Review.id.in_(review_ids))
            )

score 8 · Answer 2 · answered May 17 '11 at 11:38

I think if you want to stick with the pure ORM solution, you'll have to live with the somewhat inefficient querying pattern.

@TokenMacGuy has provided a good alternative. You could integrate that approach by adding add_reviews() to the Artist() class. This would augment the 'standard' orm api, so you could use either depending on the situation.

from sqlalchemy.orm.session import object_session

class Artist(Base):
    def add_reviews(self, review_ids):
        sess = object_session(self)
        if isinstance(review_ids, int): review_ids = [review_ids]
        sess.execute(
            Review.__table__
            .update()
            .values(artist_id=self.artist_id)
            .where(Review.id.in_(review_ids))
        )
        sess.refresh(self)

review_ids = [123, 556, 998, 667, 111]
artist.add_reviews(review_ids)
review_id = 333
artist.add_reviews(review_id)

score 3 · Answer 3 · answered May 17 '11 at 00:14

3

I'm not sure if I understood. I guess you want something like this:

reviews = session.query(Review).filter(Review.id.in_(review_ids)).all()

artist.reviews.extend(reviews)

answered May 17 '11 at 00:14

snahor

1,162
1
10
16

Actually that's what I'm doing! I wrote the code above to provide a simpler example. It's still looking up a Review objects for each insert, which I'm trying to avoid. Thanks for your suggestion though. – Ollie Glass May 17 '11 at 08:56

SQLAlchemy: add a relationship using id instead of object?

3 Answers3