CODEWITHSUNDEEP
pythonsqlalchemy
shay te
shay te

Reputation: 1068

sqlalchemy update one to many without selecting first

Hello Dear All.

I know how to update One to Many values when selecting the parent first and after that attach the many values to it. (here is the working version)

session = sessionmaker(bind=engine)()
some_values_ids = [1,2,....]
user = session.query(User).get(user_id)
user.some_values = session.query(SomeValue).filter(SomeValue.id.in_(some_values_ids)).all()}

my question is: Is there a way to update without selecting the user first?
For example, is it possible to do something like that?

{'some_values': session.query(SomeValue).filter(SomeValue.id.in_(some_values_ids)).all()}
session.query(User).filter(User.id == user_id).update(update)

The schemas are: 

class User(Base):
    __tablename__ = 'user'

    ....
    some_values = relationship(SomeValue, secondary=UserSomeValue.__tablename__)

class UserSomeValue(Base):

    __tablename__ = "user_some_value"

    id = Column(Integer, primary_key=True, nullable=False)
    user_id = Column(Integer, ForeignKey("user.id"), nullable=False)
    some_value_id = Column(Integer, ForeignKey("some_value.id"), nullable=False)

    __table_args__ = (Index("user_to_some_value", "user_id", "some_value_id", unique=True), )


class SomeValue(Base):

    __tablename__ = 'some_value'

    id = Column(Integer, primary_key=True, nullable=False)
    name = Column(VARCHAR(length=255), nullable=False, unique=True)

Thank you very much shay

Upvotes: 0

Views: 162

Answers (1)

snakecharmerb
snakecharmerb

Reputation: 55799

You can't do this with .update, but you can overwrite the user's collection, assuming that you know the user's id or have some other way to uniquely identify it in a query:

some_vals = session.query(SomeValue).filter(SomeValue.id.in_(some_values_ids)).all()
user = session.query(User).get(user_id).some_values = some_vals

or 

some_vals = session.query(SomeValue).filter(SomeValue.id.in_(some_values_ids)).all()
user = session.query(User).filter(User.name == 'foo').one().some_values = some_vals

Upvotes: 1

Related Questions