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?
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))
)
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)
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)
精彩评论