Re: [sqlalchemy] Controlling table dependency for flushing
On Wed, Oct 10, 2018 at 7:54 PM Alex Rothberg wrote:
>
> I'm not totally sure how "overlaps" are used in that example, but yes that
> might be fine to have viewonly=False (ie default) and then mark what is and
> isn't overlapped.
>
> So here is the full model with some color:
>
> Employee (all nullable [slight change from example above]):
> - department_id
> - title_id
> - fund_id
>
> with the fks as:
> department_id -> Department
> fund_id -> Fund
> (department_id, title_id) -> Title
> (department_id, fund_id) -> FundDepartment # not shown in code snipped
> earlier, but I also have this too ;-)
> (department_id, title_id, fund_id) -> FundTitle
>
> relationships setup the best I can to avoid overlaps, etc.
>
>
> An employee may have just a fund assigned, just a department, a department
> and a title, a department and fund or a department, title and a fund.
so...the columns are all nullable and that means the Employee should
be flushable before the FundTitle?
> Further I want to keep track of the department_id on the title (ie a title
> belongs to a department). I want to make sure that the department_id on the
> employee matches the department_id on the title, hence the potentially
> extraneous composite fk (ie I could just fk from Employee to title but then
> there is no constraint that the department matches; an fk from the title to
> department does not ensure that). I actually use this pattern quite a bit
> with tenancy throughout my models (ie where I use a composite fk of the
> standard pk + the tenent to ensure at the db level that the tenant matches
> between the two models).>
> Let met know if something seems totally silly here!
given how this model is, I would think you would want just all normal
relationships and whichever one you happen to mutate is the one that
sets the foreign keys. because you might want to set
Employee.department alone or Employee.title which gives you department
also. "overlaps" here might want to actually assert the two FK
settings aren't conflicting. Otherwise if you set
Employee.department = d1 and Employee.title =Title(department=d2),
it's random which one "wins".
this is not a use case that's ever been considered.
>
> On Wednesday, October 10, 2018 at 6:12:59 PM UTC-4, Mike Bayer wrote:
>>
>> for example why don't we like just using plain relationship() without
>> the viewonly=True? Shouldn't you be explicitly associating FundTitle
>> with Employee in any case?that is:
>>
>> class Employee(Base):
>> __tablename__ = 'employee'
>> id = Column(Integer, primary_key=True)
>> title_id = Column(ForeignKey('title.id'), nullable=False)
>> department_id = Column(ForeignKey('department.id'), nullable=False)
>> fund_id = Column(ForeignKey('fund.id'), nullable=False)
>>
>> department = relationship(lambda: Department)
>> title = relationship("Title")
>> fund = relationship("Fund")
>>
>> fund_title = relationship(FundTitle)
>>
>> __table_args__ = (
>> ForeignKeyConstraint(
>> (title_id, department_id, fund_id),
>> (FundTitle.title_id, FundTitle.department_id, FundTitle.fund_id)
>> ),
>> )
>>
>>
>> and then:
>>
>> for i in range(5):
>> d1 = Department()
>> t1 = Title(department=d1)
>> f1 = Fund(department=d1, title=t1)
>> ft1 = FundTitle(title=t1, department=d1, fund=f1)
>>
>> s.add_all([d1, t1, f1, ft1])
>>
>> e1 = Employee(title=t1, department=d1, fund=f1, fund_title=ft1)
>>
>> there's still the warning you don't like, but then at least we can
>> make an optoin that is narrower in scope:
>>
>> fund_title = relationship(
>> FundTitle, overlaps=('department', 'title', 'fund'))
>>
>> e.g. we aren't saying viewonly=True but then still having the
>> relationship be related to the flush, nor are we making the claim that
>> fund_title doesn't populate the department_id, title_id, fund_id
>> columns because that seems to contradict what the relationship is
>> supposed to do. at least with "overlaps" the intent of what you are
>> trying to do is clearer. but im not really sure, because I'm still
>> not feeling like I fully understand the model you have. normally
>> you'd have employee->fundtitle as the FK, and you would *not* have a
>> foreign key from Employee to Department, Title, Fund individually.
>> it would be like this:
>>
>> class Employee(Base):
>> __tablename__ = 'employee'
>> id = Column(Integer, primary_key=True)
>> title_id = Column(nullable=False)
>> department_id = Column(nullable=False)
>> fund_id = Column(nullable=False)
>>
>> department = association_proxy("fund_title", "department")
>> title = association_proxy("fund_title", "title")
>> fund = association_proxy("fund_title", "fund")
>>
>> fund_title = relationship(FundTitle)
>>
>> __table_args__ = (
>> ForeignKeyConstraint(
>> (title_id, department_id, fund_id),
>> (FundTitle.title_id, Fu
Re: [sqlalchemy] Controlling table dependency for flushing
I'm not totally sure how "overlaps" are used in that example, but yes that
might be fine to have viewonly=False (ie default) and then mark what is and
isn't overlapped.
So here is the full model with some color:
Employee (all nullable [slight change from example above]):
- department_id
- title_id
- fund_id
with the fks as:
department_id -> Department
fund_id -> Fund
(department_id, title_id) -> Title
(department_id, fund_id) -> FundDepartment # not shown in code snipped
earlier, but I also have this too ;-)
(department_id, title_id, fund_id) -> FundTitle
relationships setup the best I can to avoid overlaps, etc.
An employee may have just a fund assigned, just a department, a department
and a title, a department and fund or a department, title and a fund.
Further I want to keep track of the department_id on the title (ie a title
belongs to a department). I want to make sure that the department_id on the
employee matches the department_id on the title, hence the potentially
extraneous composite fk (ie I could just fk from Employee to title but then
there is no constraint that the department matches; an fk from the title to
department does not ensure that). I actually use this pattern quite a bit
with tenancy throughout my models (ie where I use a composite fk of the
standard pk + the tenent to ensure at the db level that the tenant matches
between the two models).
Let met know if something seems totally silly here!
On Wednesday, October 10, 2018 at 6:12:59 PM UTC-4, Mike Bayer wrote:
>
> for example why don't we like just using plain relationship() without
> the viewonly=True? Shouldn't you be explicitly associating FundTitle
> with Employee in any case?that is:
>
> class Employee(Base):
> __tablename__ = 'employee'
> id = Column(Integer, primary_key=True)
> title_id = Column(ForeignKey('title.id'), nullable=False)
> department_id = Column(ForeignKey('department.id'), nullable=False)
> fund_id = Column(ForeignKey('fund.id'), nullable=False)
>
> department = relationship(lambda: Department)
> title = relationship("Title")
> fund = relationship("Fund")
>
> fund_title = relationship(FundTitle)
>
> __table_args__ = (
> ForeignKeyConstraint(
> (title_id, department_id, fund_id),
> (FundTitle.title_id, FundTitle.department_id,
> FundTitle.fund_id)
> ),
> )
>
>
> and then:
>
> for i in range(5):
> d1 = Department()
> t1 = Title(department=d1)
> f1 = Fund(department=d1, title=t1)
> ft1 = FundTitle(title=t1, department=d1, fund=f1)
>
> s.add_all([d1, t1, f1, ft1])
>
> e1 = Employee(title=t1, department=d1, fund=f1, fund_title=ft1)
>
> there's still the warning you don't like, but then at least we can
> make an optoin that is narrower in scope:
>
> fund_title = relationship(
> FundTitle, overlaps=('department', 'title', 'fund'))
>
> e.g. we aren't saying viewonly=True but then still having the
> relationship be related to the flush, nor are we making the claim that
> fund_title doesn't populate the department_id, title_id, fund_id
> columns because that seems to contradict what the relationship is
> supposed to do. at least with "overlaps" the intent of what you are
> trying to do is clearer. but im not really sure, because I'm still
> not feeling like I fully understand the model you have. normally
> you'd have employee->fundtitle as the FK, and you would *not* have a
> foreign key from Employee to Department, Title, Fund individually.
> it would be like this:
>
> class Employee(Base):
> __tablename__ = 'employee'
> id = Column(Integer, primary_key=True)
> title_id = Column(nullable=False)
> department_id = Column(nullable=False)
> fund_id = Column(nullable=False)
>
> department = association_proxy("fund_title", "department")
> title = association_proxy("fund_title", "title")
> fund = association_proxy("fund_title", "fund")
>
> fund_title = relationship(FundTitle)
>
> __table_args__ = (
> ForeignKeyConstraint(
> (title_id, department_id, fund_id),
> (FundTitle.title_id, FundTitle.department_id,
> FundTitle.fund_id)
> ),
> )
>
>
> ft1 = FundTitle(title=t1, department=d1, fund=f1)
> e1 = Employee(fund_title=ft1)
>
> e.g. a simple association object pattern. I don't see what the
> redundant foreign keys solves.
>
>
>
>
> On Wed, Oct 10, 2018 at 5:48 PM Mike Bayer > wrote:
> >
> > On Wed, Oct 10, 2018 at 5:22 PM Alex Rothberg > wrote:
> > >
> > > I think so, yes.
> > >
> > > I am open to other ideas, but that seems like the cleanest way to
> model the dep without having to add dummy stuff to the model.
> >
> >
> > OK so like some other issues you've raised in the past few days, this
> > is a nice to have but isn't high priority for me right now, since the
> > ORM can already do what you need and des
Re: [sqlalchemy] Controlling table dependency for flushing
for example why don't we like just using plain relationship() without
the viewonly=True? Shouldn't you be explicitly associating FundTitle
with Employee in any case?that is:
class Employee(Base):
__tablename__ = 'employee'
id = Column(Integer, primary_key=True)
title_id = Column(ForeignKey('title.id'), nullable=False)
department_id = Column(ForeignKey('department.id'), nullable=False)
fund_id = Column(ForeignKey('fund.id'), nullable=False)
department = relationship(lambda: Department)
title = relationship("Title")
fund = relationship("Fund")
fund_title = relationship(FundTitle)
__table_args__ = (
ForeignKeyConstraint(
(title_id, department_id, fund_id),
(FundTitle.title_id, FundTitle.department_id, FundTitle.fund_id)
),
)
and then:
for i in range(5):
d1 = Department()
t1 = Title(department=d1)
f1 = Fund(department=d1, title=t1)
ft1 = FundTitle(title=t1, department=d1, fund=f1)
s.add_all([d1, t1, f1, ft1])
e1 = Employee(title=t1, department=d1, fund=f1, fund_title=ft1)
there's still the warning you don't like, but then at least we can
make an optoin that is narrower in scope:
fund_title = relationship(
FundTitle, overlaps=('department', 'title', 'fund'))
e.g. we aren't saying viewonly=True but then still having the
relationship be related to the flush, nor are we making the claim that
fund_title doesn't populate the department_id, title_id, fund_id
columns because that seems to contradict what the relationship is
supposed to do. at least with "overlaps" the intent of what you are
trying to do is clearer. but im not really sure, because I'm still
not feeling like I fully understand the model you have. normally
you'd have employee->fundtitle as the FK, and you would *not* have a
foreign key from Employee to Department, Title, Fund individually.
it would be like this:
class Employee(Base):
__tablename__ = 'employee'
id = Column(Integer, primary_key=True)
title_id = Column(nullable=False)
department_id = Column(nullable=False)
fund_id = Column(nullable=False)
department = association_proxy("fund_title", "department")
title = association_proxy("fund_title", "title")
fund = association_proxy("fund_title", "fund")
fund_title = relationship(FundTitle)
__table_args__ = (
ForeignKeyConstraint(
(title_id, department_id, fund_id),
(FundTitle.title_id, FundTitle.department_id, FundTitle.fund_id)
),
)
ft1 = FundTitle(title=t1, department=d1, fund=f1)
e1 = Employee(fund_title=ft1)
e.g. a simple association object pattern. I don't see what the
redundant foreign keys solves.
On Wed, Oct 10, 2018 at 5:48 PM Mike Bayer wrote:
>
> On Wed, Oct 10, 2018 at 5:22 PM Alex Rothberg wrote:
> >
> > I think so, yes.
> >
> > I am open to other ideas, but that seems like the cleanest way to model the
> > dep without having to add dummy stuff to the model.
>
>
> OK so like some other issues you've raised in the past few days, this
> is a nice to have but isn't high priority for me right now, since the
> ORM can already do what you need and designing a good API for this new
> feature may be difficult to get right. It's often very tough to
> add support for a new use case without it being too arbitrary and
> specific to exactly what someone needs at the moment.So the bigger
> pattern of how you have these foreign keys set up and what it means to
> be overlapping them like that needs to be understood more canonically.
>
>
>
> >
> > On Wednesday, October 10, 2018 at 5:20:51 PM UTC-4, Mike Bayer wrote:
> >>
> >> so you want some kind of flag on relationship() that explicitly states
> >> this purpose of the relationship(), which can co-exist with
> >> viewonly=True such that it doesn't write data, but still represents a
> >> record that is understood to be created in some other part of the
> >> transaction. like enforce_dependency=True.
> >> On Wed, Oct 10, 2018 at 3:50 PM Alex Rothberg wrote:
> >> >
> >> > Yes, I am suggesting modifying the library such that I can specify a
> >> > flush dependency ideally without needing to add dummy relationships or
> >> > use what I am guessing is a pretty low level feature of the ORM
> >> > (@event.listens_for(Session, "before_flush")). I agree that I can ignore
> >> > the warnings.
> >> >
> >> > On Wednesday, October 10, 2018 at 3:07:24 PM UTC-4, Mike Bayer wrote:
> >> >>
> >> >> On Wed, Oct 10, 2018 at 2:57 PM Alex Rothberg
> >> >> wrote:
> >> >> >
> >> >> > I actually have that newest warning about "'passive_deletes' is
> >> >> > normally configured on..." coming up in quite a few places in my
> >> >> > codebase, In those cases I had added passive_deletes for the same
> >> >> > reason as here: to avoid a load in the case of a delete. In some /
> >> >> > many of those other places, I don't have overlapping fks.
> >> >> >
> >> >> > I gues
Re: [sqlalchemy] Controlling table dependency for flushing
On Wed, Oct 10, 2018 at 5:22 PM Alex Rothberg wrote: > > I think so, yes. > > I am open to other ideas, but that seems like the cleanest way to model the > dep without having to add dummy stuff to the model. OK so like some other issues you've raised in the past few days, this is a nice to have but isn't high priority for me right now, since the ORM can already do what you need and designing a good API for this new feature may be difficult to get right. It's often very tough to add support for a new use case without it being too arbitrary and specific to exactly what someone needs at the moment.So the bigger pattern of how you have these foreign keys set up and what it means to be overlapping them like that needs to be understood more canonically. > > On Wednesday, October 10, 2018 at 5:20:51 PM UTC-4, Mike Bayer wrote: >> >> so you want some kind of flag on relationship() that explicitly states >> this purpose of the relationship(), which can co-exist with >> viewonly=True such that it doesn't write data, but still represents a >> record that is understood to be created in some other part of the >> transaction. like enforce_dependency=True. >> On Wed, Oct 10, 2018 at 3:50 PM Alex Rothberg wrote: >> > >> > Yes, I am suggesting modifying the library such that I can specify a flush >> > dependency ideally without needing to add dummy relationships or use what >> > I am guessing is a pretty low level feature of the ORM >> > (@event.listens_for(Session, "before_flush")). I agree that I can ignore >> > the warnings. >> > >> > On Wednesday, October 10, 2018 at 3:07:24 PM UTC-4, Mike Bayer wrote: >> >> >> >> On Wed, Oct 10, 2018 at 2:57 PM Alex Rothberg wrote: >> >> > >> >> > I actually have that newest warning about "'passive_deletes' is >> >> > normally configured on..." coming up in quite a few places in my >> >> > codebase, In those cases I had added passive_deletes for the same >> >> > reason as here: to avoid a load in the case of a delete. In some / many >> >> > of those other places, I don't have overlapping fks. >> >> > >> >> > I guess to make my earlier point more clear: is there any chance we >> >> > could add a flag, etc allowing a viewonly=False but where all of the >> >> > fks are not set by relationship? >> >> >> >> If we are talking about changing the library, then we should work on >> >> solving use cases, not allowing for hacks to not raise warnings. >> >> >> >> >> >> > >> >> > On Wednesday, October 10, 2018 at 2:54:18 PM UTC-4, Mike Bayer wrote: >> >> >> >> >> >> the warnings here are really just to prevent a whole set of very >> >> >> common mistakes - if the usage was never valid, then it would be >> >> >> raising an error. You're doing something that nobody ever does which >> >> >> is have two foreign keys on the same columns, so none of these >> >> >> warnings apply. i dont know what other approach SQLAlchemy could >> >> >> have for this kind of thing. >> >> >> >> >> >> >> >> >> >> >> >> On Wed, Oct 10, 2018 at 2:08 PM Alex Rothberg >> >> >> wrote: >> >> >> > >> >> >> > Adding the passive delete fixes the raise load but adds yet another >> >> >> > warning from sqla: >> >> >> > >> >> >> > sqlalchemy/orm/relationships.py:1790: SAWarning: On >> >> >> > Employee._ft_for_dependency, 'passive_deletes' is normally >> >> >> > configured on one-to-many, one-to-one, many-to-many relationships >> >> >> > only. >> >> >> > >> >> >> > Looking at this: >> >> >> > @event.listens_for(Session, "before_flush") >> >> >> > def _add_dep(session, context, objects): >> >> >> > context.dependencies.update([ >> >> >> > ( >> >> >> > unitofwork.SaveUpdateAll(context, inspect(FundTitle)), >> >> >> > unitofwork.SaveUpdateAll(context, inspect(Employee)) >> >> >> > ) >> >> >> > ]) >> >> >> > >> >> >> > do I not have to mark one Model as dependent on the other? Or is >> >> >> > that implied by the order of the list? >> >> >> > >> >> >> > On Wednesday, October 10, 2018 at 1:36:09 PM UTC-4, Mike Bayer wrote: >> >> >> >> >> >> >> >> On Wed, Oct 10, 2018 at 1:32 PM Alex Rothberg >> >> >> >> wrote: >> >> >> >> > >> >> >> >> > Well the other way doesn't quite work as if I mark none of the >> >> >> >> > columns as foreign in the primary join, sqla then assumes / >> >> >> >> > guesses all of them are. >> >> >> >> >> >> >> >> that is the case, that is code that has changed a lot over the years >> >> >> >> so it has a lot of baggage. >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> > >> >> >> >> > Let me test with passive. >> >> >> >> > >> >> >> >> > On Wed, Oct 10, 2018, 13:30 Mike Bayer >> >> >> >> > wrote: >> >> >> >> >> >> >> >> >> >> On Wed, Oct 10, 2018 at 1:27 PM Alex Rothberg >> >> >> >> >> wrote: >> >> >> >> >> > >> >> >> >> >> > And I'll reiterate, not worth doing it all from the original >> >> >> >> >> > single relationship (ie not needing to either add more >> >> >> >> >> > relationships, have warnings or use the more obscure
Re: [sqlalchemy] Forcing relationships to load before after_commit
On Wed, Oct 10, 2018 at 4:40 PM Benjamin Bertrand wrote:
>
> Hi,
>
> I'm trying to index some data from my postgres database to elasticsearch. I
> do something similar to what is described in this blog post:
> https://blog.miguelgrinberg.com/post/the-flask-mega-tutorial-part-xvi-full-text-search
> I store the new or modified objects in the before_flush event and want to
> store a representation of the object in the after_commit event.
>
> My issue is that I have some relationships in my objects and the following
> exception is raised by sqlalchemy:
> sqlalchemy.exc.InvalidRequestError: This session is in 'committed' state; no
> further SQL can be emitted within this transaction.
so you need to disable expire_on_commit, since you want the data there
inside of after_commit there is no way around this:
session = Session(engine, expire_on_commit=False)
it's likely you might want to use a different event hook for what
you're trying to do as the above is not ideal. after_flush_postexec()
might be a better choice as that is prior to the expiration.
then you need to load all the attributes somewhere ahead of time,
here's doing it inside of before_flush:
@event.listens_for(Session, "before_flush")
def before_flush(session, flush_context, instances):
# ...
if isinstance(obj, (Device, Host)):
# ...
for attr in inspect(obj).attrs:
getattr(obj, attr.key)
where again, you can query the database inside of
after_flush_postexec() without issue and no pre-load is needed. If
you are trying to only print at the after_commit boundary, then place
the state you want into your session._changes, like the actual strings
you're looking to print, then just print those.
>
> Here is a short example to illustrate the issue:
>
> import itertools
> from sqlalchemy import event, Column, Integer, String, ForeignKey,
> create_engine
> from sqlalchemy.orm import Session, relationship
> from sqlalchemy.ext.declarative import declarative_base
>
>
> Base = declarative_base()
>
>
> @event.listens_for(Session, "before_flush")
> def before_flush(session, flush_context, instances):
> session._changes = []
> for obj in itertools.chain(session.new, session.dirty):
> print(f"before_flush: {obj}")
> if isinstance(obj, (Device, Host)):
> session._changes.append(obj)
>
>
> @event.listens_for(Session, "after_commit")
> def after_commit(session):
> print("after_commit")
> for obj in session._changes:
> print(obj.to_dict())
>
>
> class User(Base):
> __tablename__ = "user_account"
> id = Column(Integer, primary_key=True)
> username = Column(String, nullable=False, unique=True)
>
> def __str__(self):
> return self.username
>
>
> class Device(Base):
> __tablename__ = "device"
> id = Column(Integer, primary_key=True)
> name = Column(String)
> user_id = Column(ForeignKey("user_account.id"), nullable=False, default=1)
> host_id = Column(Integer, ForeignKey("host.id"))
> user = relationship("User")
>
> def __str__(self):
> return self.name
>
> def to_dict(self):
> return {
> "id": self.id,
> "name": self.name,
> "user": str(self.user),
> "host": str(self.host),
> }
>
>
> class Host(Base):
> __tablename__ = "host"
> id = Column(Integer, primary_key=True)
> name = Column(String)
> devices = relationship("Device", backref="host")
>
> def __str__(self):
> return self.name
>
> def to_dict(self):
> return {
> "id": self.id,
> "name": self.name,
> "devices": [device.to_dict() for device in self.devices],
> }
>
>
> # engine = create_engine("postgresql://postgres:secret@localhost/test",
> echo=True)
> engine = create_engine("postgresql://postgres:secret@localhost/test")
> Base.metadata.drop_all(engine)
> Base.metadata.create_all(engine)
> session = Session(engine)
>
> user = User(id=1, username="John")
> session.add(user)
> session.commit()
>
> # If I don't query the user to add it to the session, I get:
> # sqlalchemy.exc.InvalidRequestError:
> # This session is in 'committed' state; no further SQL can be emitted within
> this transaction.
> myuser = session.query(User).get(1)
> device1 = Device(name="mydevice")
> session.add(device1)
> session.commit()
>
> host1 = Host(name="test")
> session.add(host1)
> # This raises sqlalchemy.exc.InvalidRequestError when trying to access
> host1.devices
> session.commit()
>
>
> How can I make sure that all relationships are in the session before the
> after_commit event?
>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> You received this message because you are subscribed to
Re: [sqlalchemy] Controlling table dependency for flushing
so you want some kind of flag on relationship() that explicitly states this purpose of the relationship(), which can co-exist with viewonly=True such that it doesn't write data, but still represents a record that is understood to be created in some other part of the transaction. like enforce_dependency=True. On Wed, Oct 10, 2018 at 3:50 PM Alex Rothberg wrote: > > Yes, I am suggesting modifying the library such that I can specify a flush > dependency ideally without needing to add dummy relationships or use what I > am guessing is a pretty low level feature of the ORM > (@event.listens_for(Session, "before_flush")). I agree that I can ignore the > warnings. > > On Wednesday, October 10, 2018 at 3:07:24 PM UTC-4, Mike Bayer wrote: >> >> On Wed, Oct 10, 2018 at 2:57 PM Alex Rothberg wrote: >> > >> > I actually have that newest warning about "'passive_deletes' is normally >> > configured on..." coming up in quite a few places in my codebase, In those >> > cases I had added passive_deletes for the same reason as here: to avoid a >> > load in the case of a delete. In some / many of those other places, I >> > don't have overlapping fks. >> > >> > I guess to make my earlier point more clear: is there any chance we could >> > add a flag, etc allowing a viewonly=False but where all of the fks are not >> > set by relationship? >> >> If we are talking about changing the library, then we should work on >> solving use cases, not allowing for hacks to not raise warnings. >> >> >> > >> > On Wednesday, October 10, 2018 at 2:54:18 PM UTC-4, Mike Bayer wrote: >> >> >> >> the warnings here are really just to prevent a whole set of very >> >> common mistakes - if the usage was never valid, then it would be >> >> raising an error. You're doing something that nobody ever does which >> >> is have two foreign keys on the same columns, so none of these >> >> warnings apply. i dont know what other approach SQLAlchemy could >> >> have for this kind of thing. >> >> >> >> >> >> >> >> On Wed, Oct 10, 2018 at 2:08 PM Alex Rothberg wrote: >> >> > >> >> > Adding the passive delete fixes the raise load but adds yet another >> >> > warning from sqla: >> >> > >> >> > sqlalchemy/orm/relationships.py:1790: SAWarning: On >> >> > Employee._ft_for_dependency, 'passive_deletes' is normally configured >> >> > on one-to-many, one-to-one, many-to-many relationships only. >> >> > >> >> > Looking at this: >> >> > @event.listens_for(Session, "before_flush") >> >> > def _add_dep(session, context, objects): >> >> > context.dependencies.update([ >> >> > ( >> >> > unitofwork.SaveUpdateAll(context, inspect(FundTitle)), >> >> > unitofwork.SaveUpdateAll(context, inspect(Employee)) >> >> > ) >> >> > ]) >> >> > >> >> > do I not have to mark one Model as dependent on the other? Or is that >> >> > implied by the order of the list? >> >> > >> >> > On Wednesday, October 10, 2018 at 1:36:09 PM UTC-4, Mike Bayer wrote: >> >> >> >> >> >> On Wed, Oct 10, 2018 at 1:32 PM Alex Rothberg >> >> >> wrote: >> >> >> > >> >> >> > Well the other way doesn't quite work as if I mark none of the >> >> >> > columns as foreign in the primary join, sqla then assumes / guesses >> >> >> > all of them are. >> >> >> >> >> >> that is the case, that is code that has changed a lot over the years >> >> >> so it has a lot of baggage. >> >> >> >> >> >> >> >> >> >> >> >> > >> >> >> > Let me test with passive. >> >> >> > >> >> >> > On Wed, Oct 10, 2018, 13:30 Mike Bayer >> >> >> > wrote: >> >> >> >> >> >> >> >> On Wed, Oct 10, 2018 at 1:27 PM Alex Rothberg >> >> >> >> wrote: >> >> >> >> > >> >> >> >> > And I'll reiterate, not worth doing it all from the original >> >> >> >> > single relationship (ie not needing to either add more >> >> >> >> > relationships, have warnings or use the more obscure feature you >> >> >> >> > outlined)? Seems like that would be cleaner in code. >> >> >> >> >> >> >> >> you mean take the viewonly=True off the existing relationship? sure >> >> >> >> you can do that. but if you mutate the elements in that collection, >> >> >> >> you can incur a change that is conflicting with the other objects. >> >> >> >> that's why I suggested making the non-viewonly a private member, but >> >> >> >> either way works. >> >> >> >> >> >> >> >> >> >> >> >> > >> >> >> >> > On Wed, Oct 10, 2018, 13:17 Mike Bayer >> >> >> >> > wrote: >> >> >> >> >> >> >> >> >> >> the raise load issue is because without passive_deletes, it has >> >> >> >> >> to >> >> >> >> >> load the collection to make sure those objects are all updated. >> >> >> >> >> passive_deletes fixes, now you just have a warning. or use the >> >> >> >> >> unit >> >> >> >> >> of work recipe which is more direct. >> >> >> >> >> On Wed, Oct 10, 2018 at 1:15 PM Alex Rothberg >> >> >> >> >> wrote: >> >> >> >> >> > >> >> >> >> >> > Not just for warning. Also this raise load issue. yes, i see >> >> >> >> >> > that I can't mark none. If I could though, th
[sqlalchemy] Forcing relationships to load before after_commit
Hi,
I'm trying to index some data from my postgres database to elasticsearch. I
do something similar to what is described in this blog
post:
https://blog.miguelgrinberg.com/post/the-flask-mega-tutorial-part-xvi-full-text-search
I store the new or modified objects in the before_flush event and want to
store a representation of the object in the after_commit event.
My issue is that I have some relationships in my objects and the following
exception is raised by sqlalchemy:
sqlalchemy.exc.InvalidRequestError: This session is in 'committed' state; no
further SQL can be emitted within this transaction.
Here is a short example to illustrate the issue:
import itertools
from sqlalchemy import event, Column, Integer, String, ForeignKey,
create_engine
from sqlalchemy.orm import Session, relationship
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
@event.listens_for(Session, "before_flush")
def before_flush(session, flush_context, instances):
session._changes = []
for obj in itertools.chain(session.new, session.dirty):
print(f"before_flush: {obj}")
if isinstance(obj, (Device, Host)):
session._changes.append(obj)
@event.listens_for(Session, "after_commit")
def after_commit(session):
print("after_commit")
for obj in session._changes:
print(obj.to_dict())
class User(Base):
__tablename__ = "user_account"
id = Column(Integer, primary_key=True)
username = Column(String, nullable=False, unique=True)
def __str__(self):
return self.username
class Device(Base):
__tablename__ = "device"
id = Column(Integer, primary_key=True)
name = Column(String)
user_id = Column(ForeignKey("user_account.id"), nullable=False, default=
1)
host_id = Column(Integer, ForeignKey("host.id"))
user = relationship("User")
def __str__(self):
return self.name
def to_dict(self):
return {
"id": self.id,
"name": self.name,
"user": str(self.user),
"host": str(self.host),
}
class Host(Base):
__tablename__ = "host"
id = Column(Integer, primary_key=True)
name = Column(String)
devices = relationship("Device", backref="host")
def __str__(self):
return self.name
def to_dict(self):
return {
"id": self.id,
"name": self.name,
"devices": [device.to_dict() for device in self.devices],
}
# engine = create_engine("postgresql://postgres:secret@localhost/test",
echo=True)
engine = create_engine("postgresql://postgres:secret@localhost/test")
Base.metadata.drop_all(engine)
Base.metadata.create_all(engine)
session = Session(engine)
user = User(id=1, username="John")
session.add(user)
session.commit()
# If I don't query the user to add it to the session, I get:
# sqlalchemy.exc.InvalidRequestError:
# This session is in 'committed' state; no further SQL can be emitted
within this transaction.
myuser = session.query(User).get(1)
device1 = Device(name="mydevice")
session.add(device1)
session.commit()
host1 = Host(name="test")
session.add(host1)
# This raises sqlalchemy.exc.InvalidRequestError when trying to access
host1.devices
session.commit()
How can I make sure that all relationships are in the session before the
after_commit event?
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Controlling table dependency for flushing
On Wed, Oct 10, 2018 at 2:57 PM Alex Rothberg wrote: > > I actually have that newest warning about "'passive_deletes' is normally > configured on..." coming up in quite a few places in my codebase, In those > cases I had added passive_deletes for the same reason as here: to avoid a > load in the case of a delete. In some / many of those other places, I don't > have overlapping fks. > > I guess to make my earlier point more clear: is there any chance we could add > a flag, etc allowing a viewonly=False but where all of the fks are not set by > relationship? If we are talking about changing the library, then we should work on solving use cases, not allowing for hacks to not raise warnings. > > On Wednesday, October 10, 2018 at 2:54:18 PM UTC-4, Mike Bayer wrote: >> >> the warnings here are really just to prevent a whole set of very >> common mistakes - if the usage was never valid, then it would be >> raising an error. You're doing something that nobody ever does which >> is have two foreign keys on the same columns, so none of these >> warnings apply. i dont know what other approach SQLAlchemy could >> have for this kind of thing. >> >> >> >> On Wed, Oct 10, 2018 at 2:08 PM Alex Rothberg wrote: >> > >> > Adding the passive delete fixes the raise load but adds yet another >> > warning from sqla: >> > >> > sqlalchemy/orm/relationships.py:1790: SAWarning: On >> > Employee._ft_for_dependency, 'passive_deletes' is normally configured on >> > one-to-many, one-to-one, many-to-many relationships only. >> > >> > Looking at this: >> > @event.listens_for(Session, "before_flush") >> > def _add_dep(session, context, objects): >> > context.dependencies.update([ >> > ( >> > unitofwork.SaveUpdateAll(context, inspect(FundTitle)), >> > unitofwork.SaveUpdateAll(context, inspect(Employee)) >> > ) >> > ]) >> > >> > do I not have to mark one Model as dependent on the other? Or is that >> > implied by the order of the list? >> > >> > On Wednesday, October 10, 2018 at 1:36:09 PM UTC-4, Mike Bayer wrote: >> >> >> >> On Wed, Oct 10, 2018 at 1:32 PM Alex Rothberg wrote: >> >> > >> >> > Well the other way doesn't quite work as if I mark none of the columns >> >> > as foreign in the primary join, sqla then assumes / guesses all of them >> >> > are. >> >> >> >> that is the case, that is code that has changed a lot over the years >> >> so it has a lot of baggage. >> >> >> >> >> >> >> >> > >> >> > Let me test with passive. >> >> > >> >> > On Wed, Oct 10, 2018, 13:30 Mike Bayer wrote: >> >> >> >> >> >> On Wed, Oct 10, 2018 at 1:27 PM Alex Rothberg >> >> >> wrote: >> >> >> > >> >> >> > And I'll reiterate, not worth doing it all from the original single >> >> >> > relationship (ie not needing to either add more relationships, have >> >> >> > warnings or use the more obscure feature you outlined)? Seems like >> >> >> > that would be cleaner in code. >> >> >> >> >> >> you mean take the viewonly=True off the existing relationship? sure >> >> >> you can do that. but if you mutate the elements in that collection, >> >> >> you can incur a change that is conflicting with the other objects. >> >> >> that's why I suggested making the non-viewonly a private member, but >> >> >> either way works. >> >> >> >> >> >> >> >> >> > >> >> >> > On Wed, Oct 10, 2018, 13:17 Mike Bayer >> >> >> > wrote: >> >> >> >> >> >> >> >> the raise load issue is because without passive_deletes, it has to >> >> >> >> load the collection to make sure those objects are all updated. >> >> >> >> passive_deletes fixes, now you just have a warning. or use the unit >> >> >> >> of work recipe which is more direct. >> >> >> >> On Wed, Oct 10, 2018 at 1:15 PM Alex Rothberg >> >> >> >> wrote: >> >> >> >> > >> >> >> >> > Not just for warning. Also this raise load issue. yes, i see that >> >> >> >> > I can't mark none. If I could though, that would be awesome since >> >> >> >> > I think it would solve this problem? I can test by setting one >> >> >> >> > foreign and seeing if that works. >> >> >> >> > >> >> >> >> > On Wednesday, October 10, 2018 at 1:13:32 PM UTC-4, Mike Bayer >> >> >> >> > wrote: >> >> >> >> >> >> >> >> >> >> On Wed, Oct 10, 2018 at 12:56 PM Alex Rothberg >> >> >> >> >> wrote: >> >> >> >> >> > >> >> >> >> >> > let me get that. in the meantime, what are your thoughts on >> >> >> >> >> > just removing the view only from the original relationship and >> >> >> >> >> > then using an explicit primary join where none of the columns >> >> >> >> >> > are marked foreign? Theoretically that should solve this >> >> >> >> >> > problem, no? >> >> >> >> >> >> >> >> >> >> is this just for the warning?I don't think the >> >> >> >> >> relationship() can >> >> >> >> >> be set up with no columns marked as foreign, it takes that as a >> >> >> >> >> cue >> >> >> >> >> that it should figure out the "foreign" columns on its own. >> >> >> >> >> >> >> >> >> >> There's another way to make sure Employee
Re: [sqlalchemy] Need Hybris Developer role in Long Beach, CA!!
is it really worth getting your email address marked as a spammer within Google? probably not. On Wed, Oct 10, 2018 at 2:09 PM Ankit Khanna wrote: > > Please reply me at: an...@holistic-partners.com > > Hello, > Hope you are doing well, > > We have an urgent requirement for Hybris Developer role in CA. If you are > interested for this role then please revert me back ASAP. Please find the > below JD. > > > > Location- Long Beach, CA > > Duration: 12-18+ months > Phone and Skype > > > > JD: > > > > The candidate will be responsible for day-day production support, maintenance > activities and new development for CLIENT’s North and Latin American > e-commerce systems operating on a SAP Hybris platform under Linux. The > successful candidate must have excellent written and communication skills and > demonstrated senior-level Java development experience along with strong > analytical and troubleshooting skills. Candidate must be able to work well > independently as well as interacting with end users and project leads to > provide technical recommendations, development scoping (e.g. level-of-effort > analysis) and assist in producing technical and business requirements. > > > > Primary responsibilities include creating Hybris extensions for Product > Content Management (PCM), Web Content Management (WCMS), and Backoffice > cockpits. Strong knowledge of Spring and Spring MVC is required. > > > > This is a fast-paced, multiple project environment; the qualified candidate > must be able to balance multiple projects and tasks, manage to project > deadlines and effectively and appropriately escalate and follow-up on issues > and assignments. The successful candidate must be a demonstrated self-starter > with an aptitude for learning and a desire to take on new responsibilities > and challenges. Good communication skills are required for this role. > > > > Duties and Responsibilities: > > Participates in gathering and organizing of business/user requirements and > feasibility analysis > Participates in performing application design and review > Develops code that meets business/user requirements, IS quality standards and > secure web development guidelines > Develops and conducts system/unit test plans including user acceptance test > planning and execution > Participates in development of implementation schedules, system > implementation planning and execution > Meets project objectives within specified time frame parameters and defined > requirements > Ensures that project success criteria are met; performs day-to-day > maintenance of production software systems > Creates technical/functional documentation > Actively evaluates and resolves daily business problems by performing > independent analysis > Participates in on-call rotation supporting production systems and user > trouble reports > Communicates task status/issues to IS management and project leaders > Participates in research and evaluation of appropriate software products > being considered for acquisition > Keeps current with technology trends and their impact to the organization > Participates in Disaster Recovery testing to help ensure business continuance > > > > Requirements for the Role: > > Minimum 5 years of extensive experience with J2EE, Java and web applications > (Spring all layers, Spring MVC, Velocity, jstl, jsp) > SAP Hybris 6.2.x experience > Experience with JavaScript, jQuery, json, ajax, REST web services. > Good working knowledge of change control and version control processes using > source code control tools, specifically Subversion. > Working knowledge of build tools such as Ant and Maven. > Strong experience with web applications > Ability to participate in quality assurance activities including unit > testing, QA testing and documentation of test scripts and testing > instructions for UAT testing. In addition, candidate must be able to perform > code reviews and vulnerability testing as required. > Have excellent interpersonal, listening, verbal, and written communication > skills > Enjoy working with a team, while possessing a strong independent work ethic > Bachelor's degree (B.S. or B.A.) or equivalent experience in a web > development related field > > > > Required Hybris Experience > > Customizing the Type System > Experience writing FlexibleSearch Queries and Restrictions > Creating Impex scripts for import and export > Familiar with Catalog Synchronizations > Familiar with SolrFacetSearch > Customization of PCM and WCMS cockpit > Creating CronJobs > Working with Europe1 Pricing (Prices, Taxes, and Discounts) > Experience with all Commerce features including Account Management, Shopping > Cart, Checkout/Payment, and Order Processing > Working knowledge of Promotions and Vouchers > Creating Email Templates > Developing with the ServiceLayer (DTOs, Populators, Validators, Facades, and > Service components) > Creating/extending Spring Beans > > o Minimum 1 full implementation experience > >
Re: [sqlalchemy] Controlling table dependency for flushing
the warnings here are really just to prevent a whole set of very common mistakes - if the usage was never valid, then it would be raising an error. You're doing something that nobody ever does which is have two foreign keys on the same columns, so none of these warnings apply. i dont know what other approach SQLAlchemy could have for this kind of thing. On Wed, Oct 10, 2018 at 2:08 PM Alex Rothberg wrote: > > Adding the passive delete fixes the raise load but adds yet another warning > from sqla: > > sqlalchemy/orm/relationships.py:1790: SAWarning: On > Employee._ft_for_dependency, 'passive_deletes' is normally configured on > one-to-many, one-to-one, many-to-many relationships only. > > Looking at this: > @event.listens_for(Session, "before_flush") > def _add_dep(session, context, objects): > context.dependencies.update([ > ( > unitofwork.SaveUpdateAll(context, inspect(FundTitle)), > unitofwork.SaveUpdateAll(context, inspect(Employee)) > ) > ]) > > do I not have to mark one Model as dependent on the other? Or is that implied > by the order of the list? > > On Wednesday, October 10, 2018 at 1:36:09 PM UTC-4, Mike Bayer wrote: >> >> On Wed, Oct 10, 2018 at 1:32 PM Alex Rothberg wrote: >> > >> > Well the other way doesn't quite work as if I mark none of the columns as >> > foreign in the primary join, sqla then assumes / guesses all of them are. >> >> that is the case, that is code that has changed a lot over the years >> so it has a lot of baggage. >> >> >> >> > >> > Let me test with passive. >> > >> > On Wed, Oct 10, 2018, 13:30 Mike Bayer wrote: >> >> >> >> On Wed, Oct 10, 2018 at 1:27 PM Alex Rothberg wrote: >> >> > >> >> > And I'll reiterate, not worth doing it all from the original single >> >> > relationship (ie not needing to either add more relationships, have >> >> > warnings or use the more obscure feature you outlined)? Seems like that >> >> > would be cleaner in code. >> >> >> >> you mean take the viewonly=True off the existing relationship? sure >> >> you can do that. but if you mutate the elements in that collection, >> >> you can incur a change that is conflicting with the other objects. >> >> that's why I suggested making the non-viewonly a private member, but >> >> either way works. >> >> >> >> >> >> > >> >> > On Wed, Oct 10, 2018, 13:17 Mike Bayer wrote: >> >> >> >> >> >> the raise load issue is because without passive_deletes, it has to >> >> >> load the collection to make sure those objects are all updated. >> >> >> passive_deletes fixes, now you just have a warning. or use the unit >> >> >> of work recipe which is more direct. >> >> >> On Wed, Oct 10, 2018 at 1:15 PM Alex Rothberg >> >> >> wrote: >> >> >> > >> >> >> > Not just for warning. Also this raise load issue. yes, i see that I >> >> >> > can't mark none. If I could though, that would be awesome since I >> >> >> > think it would solve this problem? I can test by setting one foreign >> >> >> > and seeing if that works. >> >> >> > >> >> >> > On Wednesday, October 10, 2018 at 1:13:32 PM UTC-4, Mike Bayer wrote: >> >> >> >> >> >> >> >> On Wed, Oct 10, 2018 at 12:56 PM Alex Rothberg >> >> >> >> wrote: >> >> >> >> > >> >> >> >> > let me get that. in the meantime, what are your thoughts on just >> >> >> >> > removing the view only from the original relationship and then >> >> >> >> > using an explicit primary join where none of the columns are >> >> >> >> > marked foreign? Theoretically that should solve this problem, no? >> >> >> >> >> >> >> >> is this just for the warning?I don't think the relationship() >> >> >> >> can >> >> >> >> be set up with no columns marked as foreign, it takes that as a cue >> >> >> >> that it should figure out the "foreign" columns on its own. >> >> >> >> >> >> >> >> There's another way to make sure Employee is always dependent on >> >> >> >> FundTitle but it's a little bit off-label. Add the dependency >> >> >> >> you >> >> >> >> want directly into the unit of work: >> >> >> >> >> >> >> >> from sqlalchemy.orm import unitofwork >> >> >> >> from sqlalchemy import event >> >> >> >> >> >> >> >> >> >> >> >> @event.listens_for(Session, "before_flush") >> >> >> >> def _add_dep(session, context, objects): >> >> >> >> context.dependencies.update([ >> >> >> >> ( >> >> >> >> unitofwork.SaveUpdateAll(context, inspect(FundTitle)), >> >> >> >> unitofwork.SaveUpdateAll(context, inspect(Employee)) >> >> >> >> ) >> >> >> >> ]) >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> > >> >> >> >> > On Wednesday, October 10, 2018 at 12:41:25 PM UTC-4, Alex >> >> >> >> > Rothberg wrote: >> >> >> >> >> >> >> >> >> >> Is it possible to specific a non viewonly relationship in which >> >> >> >> >> I have a primary join specified in which none of the fk's are >> >> >> >> >> marked "foreign"? ie where I can mark the relationship >> >> >> >> >> dependancy but it wont set any colu
[sqlalchemy] Need Hybris Developer role in Long Beach, CA!!
*Please reply me at: an...@holistic-partners.com * *Hello,* *Hope you are doing well,* *We have an urgent requirement for Hybris Developer role in CA. If you are interested for this role then please revert me back ASAP. Please find the below JD.* *Location- Long Beach, CA* *Duration: 12-18+ months Phone and Skype* *JD:* The candidate will be responsible for day-day production support, maintenance activities and new development for CLIENT’s North and Latin American e-commerce systems operating on a SAP Hybris platform under Linux. The successful candidate must have excellent written and communication skills and demonstrated senior-level Java development experience along with strong analytical and troubleshooting skills. Candidate must be able to work well independently as well as interacting with end users and project leads to provide technical recommendations, development scoping (e.g. level-of-effort analysis) and assist in producing technical and business requirements. Primary responsibilities include creating Hybris extensions for Product Content Management (PCM), Web Content Management (WCMS), and Backoffice cockpits. Strong knowledge of Spring and Spring MVC is required. This is a fast-paced, multiple project environment; the qualified candidate must be able to balance multiple projects and tasks, manage to project deadlines and effectively and appropriately escalate and follow-up on issues and assignments. The successful candidate must be a demonstrated self-starter with an aptitude for learning and a desire to take on new responsibilities and challenges. Good communication skills are required for this role. *Duties and Responsibilities:* - Participates in gathering and organizing of business/user requirements and feasibility analysis - Participates in performing application design and review - Develops code that meets business/user requirements, IS quality standards and secure web development guidelines - Develops and conducts system/unit test plans including user acceptance test planning and execution - Participates in development of implementation schedules, system implementation planning and execution - Meets project objectives within specified time frame parameters and defined requirements - Ensures that project success criteria are met; performs day-to-day maintenance of production software systems - Creates technical/functional documentation - Actively evaluates and resolves daily business problems by performing independent analysis - Participates in on-call rotation supporting production systems and user trouble reports - Communicates task status/issues to IS management and project leaders - Participates in research and evaluation of appropriate software products being considered for acquisition - Keeps current with technology trends and their impact to the organization - Participates in Disaster Recovery testing to help ensure business continuance *Requirements for the Role:* - Minimum 5 years of extensive experience with J2EE, Java and web applications (Spring all layers, Spring MVC, Velocity, jstl, jsp) - SAP Hybris 6.2.x experience - Experience with JavaScript, jQuery, json, ajax, REST web services. - Good working knowledge of change control and version control processes using source code control tools, specifically Subversion. - Working knowledge of build tools such as Ant and Maven. - Strong experience with web applications - Ability to participate in quality assurance activities including unit testing, QA testing and documentation of test scripts and testing instructions for UAT testing. In addition, candidate must be able to perform code reviews and vulnerability testing as required. - Have excellent interpersonal, listening, verbal, and written communication skills - Enjoy working with a team, while possessing a strong independent work ethic - Bachelor's degree (B.S. or B.A.) or equivalent experience in a web development related field *Required Hybris Experience* - Customizing the Type System - Experience writing FlexibleSearch Queries and Restrictions - Creating Impex scripts for import and export - Familiar with Catalog Synchronizations - Familiar with SolrFacetSearch - Customization of PCM and WCMS cockpit - Creating CronJobs - Working with Europe1 Pricing (Prices, Taxes, and Discounts) - Experience with all Commerce features including Account Management, Shopping Cart, Checkout/Payment, and Order Processing - Working knowledge of Promotions and Vouchers - Creating Email Templates - Developing with the ServiceLayer (DTOs, Populators, Validators, Facades, and Service components) - Creating/extending Spring Beans o Minimum 1 full implementation experience *CLIENT Technologies* - SAP hybris 6.2.x - Bazaarvoice - Omniture - Google Analytics - Apache Solr - Ad
Re: [sqlalchemy] Controlling table dependency for flushing
Adding the passive delete fixes the raise load but adds yet another warning
from sqla:
sqlalchemy/orm/relationships.py:1790: SAWarning: On Employee.
_ft_for_dependency, 'passive_deletes' is normally configured on one-to-many,
one-to-one, many-to-many relationships only.
Looking at this:
@event.listens_for(Session, "before_flush")
def _add_dep(session, context, objects):
context.dependencies.update([
(
unitofwork.SaveUpdateAll(context, inspect(FundTitle)),
unitofwork.SaveUpdateAll(context, inspect(Employee))
)
])
do I not have to mark one Model as dependent on the other? Or is that
implied by the order of the list?
On Wednesday, October 10, 2018 at 1:36:09 PM UTC-4, Mike Bayer wrote:
>
> On Wed, Oct 10, 2018 at 1:32 PM Alex Rothberg > wrote:
> >
> > Well the other way doesn't quite work as if I mark none of the columns
> as foreign in the primary join, sqla then assumes / guesses all of them
> are.
>
> that is the case, that is code that has changed a lot over the years
> so it has a lot of baggage.
>
>
>
> >
> > Let me test with passive.
> >
> > On Wed, Oct 10, 2018, 13:30 Mike Bayer > wrote:
> >>
> >> On Wed, Oct 10, 2018 at 1:27 PM Alex Rothberg > wrote:
> >> >
> >> > And I'll reiterate, not worth doing it all from the original single
> relationship (ie not needing to either add more relationships, have
> warnings or use the more obscure feature you outlined)? Seems like that
> would be cleaner in code.
> >>
> >> you mean take the viewonly=True off the existing relationship? sure
> >> you can do that. but if you mutate the elements in that collection,
> >> you can incur a change that is conflicting with the other objects.
> >> that's why I suggested making the non-viewonly a private member, but
> >> either way works.
> >>
> >>
> >> >
> >> > On Wed, Oct 10, 2018, 13:17 Mike Bayer > wrote:
> >> >>
> >> >> the raise load issue is because without passive_deletes, it has to
> >> >> load the collection to make sure those objects are all updated.
> >> >> passive_deletes fixes, now you just have a warning. or use the unit
> >> >> of work recipe which is more direct.
> >> >> On Wed, Oct 10, 2018 at 1:15 PM Alex Rothberg > wrote:
> >> >> >
> >> >> > Not just for warning. Also this raise load issue. yes, i see that
> I can't mark none. If I could though, that would be awesome since I think
> it would solve this problem? I can test by setting one foreign and seeing
> if that works.
> >> >> >
> >> >> > On Wednesday, October 10, 2018 at 1:13:32 PM UTC-4, Mike Bayer
> wrote:
> >> >> >>
> >> >> >> On Wed, Oct 10, 2018 at 12:56 PM Alex Rothberg <
> agrot...@gmail.com> wrote:
> >> >> >> >
> >> >> >> > let me get that. in the meantime, what are your thoughts on
> just removing the view only from the original relationship and then using
> an explicit primary join where none of the columns are marked foreign?
> Theoretically that should solve this problem, no?
> >> >> >>
> >> >> >> is this just for the warning?I don't think the relationship()
> can
> >> >> >> be set up with no columns marked as foreign, it takes that as a
> cue
> >> >> >> that it should figure out the "foreign" columns on its own.
> >> >> >>
> >> >> >> There's another way to make sure Employee is always dependent on
> >> >> >> FundTitle but it's a little bit off-label. Add the dependency
> you
> >> >> >> want directly into the unit of work:
> >> >> >>
> >> >> >> from sqlalchemy.orm import unitofwork
> >> >> >> from sqlalchemy import event
> >> >> >>
> >> >> >>
> >> >> >> @event.listens_for(Session, "before_flush")
> >> >> >> def _add_dep(session, context, objects):
> >> >> >> context.dependencies.update([
> >> >> >> (
> >> >> >> unitofwork.SaveUpdateAll(context,
> inspect(FundTitle)),
> >> >> >> unitofwork.SaveUpdateAll(context, inspect(Employee))
> >> >> >> )
> >> >> >> ])
> >> >> >>
> >> >> >>
> >> >> >>
> >> >> >>
> >> >> >>
> >> >> >> >
> >> >> >> > On Wednesday, October 10, 2018 at 12:41:25 PM UTC-4, Alex
> Rothberg wrote:
> >> >> >> >>
> >> >> >> >> Is it possible to specific a non viewonly relationship in
> which I have a primary join specified in which none of the fk's are marked
> "foreign"? ie where I can mark the relationship dependancy but it wont set
> any columns? It looks like there may be some logic in sqla that assume all
> columns are fk if none are specified as foreign?
> >> >> >> >>
> >> >> >> >> On Wednesday, October 10, 2018 at 11:56:49 AM UTC-4, Alex
> Rothberg wrote:
> >> >> >> >>>
> >> >> >> >>> So one minor issue and one big issue with that solution:
> >> >> >> >>> 1. minor issue, I now get these: SAWarning: relationship
> '' will copy column to column , which conflicts with
> relationship(s): '
> >> >> >> >>> 2. major issue, I use raiseload("*") and now I start seeing:
> sqlalchemy.exc.InvalidReq
Re: [sqlalchemy] Controlling table dependency for flushing
On Wed, Oct 10, 2018 at 1:32 PM Alex Rothberg wrote:
>
> Well the other way doesn't quite work as if I mark none of the columns as
> foreign in the primary join, sqla then assumes / guesses all of them are.
that is the case, that is code that has changed a lot over the years
so it has a lot of baggage.
>
> Let me test with passive.
>
> On Wed, Oct 10, 2018, 13:30 Mike Bayer wrote:
>>
>> On Wed, Oct 10, 2018 at 1:27 PM Alex Rothberg wrote:
>> >
>> > And I'll reiterate, not worth doing it all from the original single
>> > relationship (ie not needing to either add more relationships, have
>> > warnings or use the more obscure feature you outlined)? Seems like that
>> > would be cleaner in code.
>>
>> you mean take the viewonly=True off the existing relationship? sure
>> you can do that. but if you mutate the elements in that collection,
>> you can incur a change that is conflicting with the other objects.
>> that's why I suggested making the non-viewonly a private member, but
>> either way works.
>>
>>
>> >
>> > On Wed, Oct 10, 2018, 13:17 Mike Bayer wrote:
>> >>
>> >> the raise load issue is because without passive_deletes, it has to
>> >> load the collection to make sure those objects are all updated.
>> >> passive_deletes fixes, now you just have a warning. or use the unit
>> >> of work recipe which is more direct.
>> >> On Wed, Oct 10, 2018 at 1:15 PM Alex Rothberg
>> >> wrote:
>> >> >
>> >> > Not just for warning. Also this raise load issue. yes, i see that I
>> >> > can't mark none. If I could though, that would be awesome since I think
>> >> > it would solve this problem? I can test by setting one foreign and
>> >> > seeing if that works.
>> >> >
>> >> > On Wednesday, October 10, 2018 at 1:13:32 PM UTC-4, Mike Bayer wrote:
>> >> >>
>> >> >> On Wed, Oct 10, 2018 at 12:56 PM Alex Rothberg
>> >> >> wrote:
>> >> >> >
>> >> >> > let me get that. in the meantime, what are your thoughts on just
>> >> >> > removing the view only from the original relationship and then using
>> >> >> > an explicit primary join where none of the columns are marked
>> >> >> > foreign? Theoretically that should solve this problem, no?
>> >> >>
>> >> >> is this just for the warning?I don't think the relationship() can
>> >> >> be set up with no columns marked as foreign, it takes that as a cue
>> >> >> that it should figure out the "foreign" columns on its own.
>> >> >>
>> >> >> There's another way to make sure Employee is always dependent on
>> >> >> FundTitle but it's a little bit off-label. Add the dependency you
>> >> >> want directly into the unit of work:
>> >> >>
>> >> >> from sqlalchemy.orm import unitofwork
>> >> >> from sqlalchemy import event
>> >> >>
>> >> >>
>> >> >> @event.listens_for(Session, "before_flush")
>> >> >> def _add_dep(session, context, objects):
>> >> >> context.dependencies.update([
>> >> >> (
>> >> >> unitofwork.SaveUpdateAll(context, inspect(FundTitle)),
>> >> >> unitofwork.SaveUpdateAll(context, inspect(Employee))
>> >> >> )
>> >> >> ])
>> >> >>
>> >> >>
>> >> >>
>> >> >>
>> >> >>
>> >> >> >
>> >> >> > On Wednesday, October 10, 2018 at 12:41:25 PM UTC-4, Alex Rothberg
>> >> >> > wrote:
>> >> >> >>
>> >> >> >> Is it possible to specific a non viewonly relationship in which I
>> >> >> >> have a primary join specified in which none of the fk's are marked
>> >> >> >> "foreign"? ie where I can mark the relationship dependancy but it
>> >> >> >> wont set any columns? It looks like there may be some logic in sqla
>> >> >> >> that assume all columns are fk if none are specified as foreign?
>> >> >> >>
>> >> >> >> On Wednesday, October 10, 2018 at 11:56:49 AM UTC-4, Alex Rothberg
>> >> >> >> wrote:
>> >> >> >>>
>> >> >> >>> So one minor issue and one big issue with that solution:
>> >> >> >>> 1. minor issue, I now get these: SAWarning: relationship ''
>> >> >> >>> will copy column to column , which conflicts with
>> >> >> >>> relationship(s): '
>> >> >> >>> 2. major issue, I use raiseload("*") and now I start seeing:
>> >> >> >>> sqlalchemy.exc.InvalidRequestError: 'Employee._ft_for_dependency'
>> >> >> >>> is not available due to lazy='raise'
>> >> >> >>>
>> >> >> >>> On Wednesday, October 10, 2018 at 9:57:55 AM UTC-4, Mike Bayer
>> >> >> >>> wrote:
>> >> >>
>> >> >> On Tue, Oct 9, 2018 at 6:45 PM Alex Rothberg
>> >> >> wrote:
>> >> >> >
>> >> >> > Okay with some small tweaks to your original code, I am able to
>> >> >> > show the issue I am having. comment out flush to see issue:
>> >> >>
>> >> >> so what you're doing here is making Employee dependent on
>> >> >> FundTitle,
>> >> >> which makes this a little out of the ordinary but this is fine.
>> >> >> You
>> >> >> need to give the ORM a clue that this dependency exists, since it
>> >> >> never looks at foreign key constraints unless you tell it to.
>> >> >> Adding a relati
Re: [sqlalchemy] Controlling table dependency for flushing
Well the other way doesn't quite work as if I mark none of the columns as
foreign in the primary join, sqla then assumes / guesses all of them are.
Let me test with passive.
On Wed, Oct 10, 2018, 13:30 Mike Bayer wrote:
> On Wed, Oct 10, 2018 at 1:27 PM Alex Rothberg
> wrote:
> >
> > And I'll reiterate, not worth doing it all from the original single
> relationship (ie not needing to either add more relationships, have
> warnings or use the more obscure feature you outlined)? Seems like that
> would be cleaner in code.
>
> you mean take the viewonly=True off the existing relationship? sure
> you can do that. but if you mutate the elements in that collection,
> you can incur a change that is conflicting with the other objects.
> that's why I suggested making the non-viewonly a private member, but
> either way works.
>
>
> >
> > On Wed, Oct 10, 2018, 13:17 Mike Bayer wrote:
> >>
> >> the raise load issue is because without passive_deletes, it has to
> >> load the collection to make sure those objects are all updated.
> >> passive_deletes fixes, now you just have a warning. or use the unit
> >> of work recipe which is more direct.
> >> On Wed, Oct 10, 2018 at 1:15 PM Alex Rothberg
> wrote:
> >> >
> >> > Not just for warning. Also this raise load issue. yes, i see that I
> can't mark none. If I could though, that would be awesome since I think it
> would solve this problem? I can test by setting one foreign and seeing if
> that works.
> >> >
> >> > On Wednesday, October 10, 2018 at 1:13:32 PM UTC-4, Mike Bayer wrote:
> >> >>
> >> >> On Wed, Oct 10, 2018 at 12:56 PM Alex Rothberg
> wrote:
> >> >> >
> >> >> > let me get that. in the meantime, what are your thoughts on just
> removing the view only from the original relationship and then using an
> explicit primary join where none of the columns are marked foreign?
> Theoretically that should solve this problem, no?
> >> >>
> >> >> is this just for the warning?I don't think the relationship() can
> >> >> be set up with no columns marked as foreign, it takes that as a cue
> >> >> that it should figure out the "foreign" columns on its own.
> >> >>
> >> >> There's another way to make sure Employee is always dependent on
> >> >> FundTitle but it's a little bit off-label. Add the dependency you
> >> >> want directly into the unit of work:
> >> >>
> >> >> from sqlalchemy.orm import unitofwork
> >> >> from sqlalchemy import event
> >> >>
> >> >>
> >> >> @event.listens_for(Session, "before_flush")
> >> >> def _add_dep(session, context, objects):
> >> >> context.dependencies.update([
> >> >> (
> >> >> unitofwork.SaveUpdateAll(context, inspect(FundTitle)),
> >> >> unitofwork.SaveUpdateAll(context, inspect(Employee))
> >> >> )
> >> >> ])
> >> >>
> >> >>
> >> >>
> >> >>
> >> >>
> >> >> >
> >> >> > On Wednesday, October 10, 2018 at 12:41:25 PM UTC-4, Alex Rothberg
> wrote:
> >> >> >>
> >> >> >> Is it possible to specific a non viewonly relationship in which I
> have a primary join specified in which none of the fk's are marked
> "foreign"? ie where I can mark the relationship dependancy but it wont set
> any columns? It looks like there may be some logic in sqla that assume all
> columns are fk if none are specified as foreign?
> >> >> >>
> >> >> >> On Wednesday, October 10, 2018 at 11:56:49 AM UTC-4, Alex
> Rothberg wrote:
> >> >> >>>
> >> >> >>> So one minor issue and one big issue with that solution:
> >> >> >>> 1. minor issue, I now get these: SAWarning: relationship ''
> will copy column to column , which conflicts with relationship(s):
> '
> >> >> >>> 2. major issue, I use raiseload("*") and now I start seeing:
> sqlalchemy.exc.InvalidRequestError: 'Employee._ft_for_dependency' is not
> available due to lazy='raise'
> >> >> >>>
> >> >> >>> On Wednesday, October 10, 2018 at 9:57:55 AM UTC-4, Mike Bayer
> wrote:
> >> >>
> >> >> On Tue, Oct 9, 2018 at 6:45 PM Alex Rothberg <
> agrot...@gmail.com> wrote:
> >> >> >
> >> >> > Okay with some small tweaks to your original code, I am able
> to show the issue I am having. comment out flush to see issue:
> >> >>
> >> >> so what you're doing here is making Employee dependent on
> FundTitle,
> >> >> which makes this a little out of the ordinary but this is
> fine. You
> >> >> need to give the ORM a clue that this dependency exists, since
> it
> >> >> never looks at foreign key constraints unless you tell it to.
> >> >> Adding a relationship to FundTitle that doesn't have
> viewonly=True is
> >> >> an easy way to do this, there's no need to ever make use of the
> >> >> relationship otherwise:
> >> >>
> >> >> class Employee(Base):
> >> >> __tablename__ = 'employee'
> >> >>
> >> >> # ...
> >> >> fund_title = relationship(FundTitle, viewonly=True)
> >> >>
> >> >> _ft_for_dependency = relationship(FundTitle)
> >> >>
> >> >> __
Re: [sqlalchemy] Controlling table dependency for flushing
On Wed, Oct 10, 2018 at 1:27 PM Alex Rothberg wrote:
>
> And I'll reiterate, not worth doing it all from the original single
> relationship (ie not needing to either add more relationships, have warnings
> or use the more obscure feature you outlined)? Seems like that would be
> cleaner in code.
you mean take the viewonly=True off the existing relationship? sure
you can do that. but if you mutate the elements in that collection,
you can incur a change that is conflicting with the other objects.
that's why I suggested making the non-viewonly a private member, but
either way works.
>
> On Wed, Oct 10, 2018, 13:17 Mike Bayer wrote:
>>
>> the raise load issue is because without passive_deletes, it has to
>> load the collection to make sure those objects are all updated.
>> passive_deletes fixes, now you just have a warning. or use the unit
>> of work recipe which is more direct.
>> On Wed, Oct 10, 2018 at 1:15 PM Alex Rothberg wrote:
>> >
>> > Not just for warning. Also this raise load issue. yes, i see that I can't
>> > mark none. If I could though, that would be awesome since I think it would
>> > solve this problem? I can test by setting one foreign and seeing if that
>> > works.
>> >
>> > On Wednesday, October 10, 2018 at 1:13:32 PM UTC-4, Mike Bayer wrote:
>> >>
>> >> On Wed, Oct 10, 2018 at 12:56 PM Alex Rothberg wrote:
>> >> >
>> >> > let me get that. in the meantime, what are your thoughts on just
>> >> > removing the view only from the original relationship and then using an
>> >> > explicit primary join where none of the columns are marked foreign?
>> >> > Theoretically that should solve this problem, no?
>> >>
>> >> is this just for the warning?I don't think the relationship() can
>> >> be set up with no columns marked as foreign, it takes that as a cue
>> >> that it should figure out the "foreign" columns on its own.
>> >>
>> >> There's another way to make sure Employee is always dependent on
>> >> FundTitle but it's a little bit off-label. Add the dependency you
>> >> want directly into the unit of work:
>> >>
>> >> from sqlalchemy.orm import unitofwork
>> >> from sqlalchemy import event
>> >>
>> >>
>> >> @event.listens_for(Session, "before_flush")
>> >> def _add_dep(session, context, objects):
>> >> context.dependencies.update([
>> >> (
>> >> unitofwork.SaveUpdateAll(context, inspect(FundTitle)),
>> >> unitofwork.SaveUpdateAll(context, inspect(Employee))
>> >> )
>> >> ])
>> >>
>> >>
>> >>
>> >>
>> >>
>> >> >
>> >> > On Wednesday, October 10, 2018 at 12:41:25 PM UTC-4, Alex Rothberg
>> >> > wrote:
>> >> >>
>> >> >> Is it possible to specific a non viewonly relationship in which I have
>> >> >> a primary join specified in which none of the fk's are marked
>> >> >> "foreign"? ie where I can mark the relationship dependancy but it wont
>> >> >> set any columns? It looks like there may be some logic in sqla that
>> >> >> assume all columns are fk if none are specified as foreign?
>> >> >>
>> >> >> On Wednesday, October 10, 2018 at 11:56:49 AM UTC-4, Alex Rothberg
>> >> >> wrote:
>> >> >>>
>> >> >>> So one minor issue and one big issue with that solution:
>> >> >>> 1. minor issue, I now get these: SAWarning: relationship '' will
>> >> >>> copy column to column , which conflicts with relationship(s):
>> >> >>> '
>> >> >>> 2. major issue, I use raiseload("*") and now I start seeing:
>> >> >>> sqlalchemy.exc.InvalidRequestError: 'Employee._ft_for_dependency' is
>> >> >>> not available due to lazy='raise'
>> >> >>>
>> >> >>> On Wednesday, October 10, 2018 at 9:57:55 AM UTC-4, Mike Bayer wrote:
>> >>
>> >> On Tue, Oct 9, 2018 at 6:45 PM Alex Rothberg
>> >> wrote:
>> >> >
>> >> > Okay with some small tweaks to your original code, I am able to
>> >> > show the issue I am having. comment out flush to see issue:
>> >>
>> >> so what you're doing here is making Employee dependent on FundTitle,
>> >> which makes this a little out of the ordinary but this is fine. You
>> >> need to give the ORM a clue that this dependency exists, since it
>> >> never looks at foreign key constraints unless you tell it to.
>> >> Adding a relationship to FundTitle that doesn't have viewonly=True is
>> >> an easy way to do this, there's no need to ever make use of the
>> >> relationship otherwise:
>> >>
>> >> class Employee(Base):
>> >> __tablename__ = 'employee'
>> >>
>> >> # ...
>> >> fund_title = relationship(FundTitle, viewonly=True)
>> >>
>> >> _ft_for_dependency = relationship(FundTitle)
>> >>
>> >> __table_args__ = (
>> >> ForeignKeyConstraint(
>> >> (title_id, department_id, fund_id),
>> >> (FundTitle.title_id, FundTitle.department_id,
>> >> FundTitle.fund_id)
>> >> ),
>> >> )
>> >>
>> >> then you can
Re: [sqlalchemy] Controlling table dependency for flushing
And I'll reiterate, not worth doing it all from the original single
relationship (ie not needing to either add more relationships, have
warnings or use the more obscure feature you outlined)? Seems like that
would be cleaner in code.
On Wed, Oct 10, 2018, 13:17 Mike Bayer wrote:
> the raise load issue is because without passive_deletes, it has to
> load the collection to make sure those objects are all updated.
> passive_deletes fixes, now you just have a warning. or use the unit
> of work recipe which is more direct.
> On Wed, Oct 10, 2018 at 1:15 PM Alex Rothberg
> wrote:
> >
> > Not just for warning. Also this raise load issue. yes, i see that I
> can't mark none. If I could though, that would be awesome since I think it
> would solve this problem? I can test by setting one foreign and seeing if
> that works.
> >
> > On Wednesday, October 10, 2018 at 1:13:32 PM UTC-4, Mike Bayer wrote:
> >>
> >> On Wed, Oct 10, 2018 at 12:56 PM Alex Rothberg
> wrote:
> >> >
> >> > let me get that. in the meantime, what are your thoughts on just
> removing the view only from the original relationship and then using an
> explicit primary join where none of the columns are marked foreign?
> Theoretically that should solve this problem, no?
> >>
> >> is this just for the warning?I don't think the relationship() can
> >> be set up with no columns marked as foreign, it takes that as a cue
> >> that it should figure out the "foreign" columns on its own.
> >>
> >> There's another way to make sure Employee is always dependent on
> >> FundTitle but it's a little bit off-label. Add the dependency you
> >> want directly into the unit of work:
> >>
> >> from sqlalchemy.orm import unitofwork
> >> from sqlalchemy import event
> >>
> >>
> >> @event.listens_for(Session, "before_flush")
> >> def _add_dep(session, context, objects):
> >> context.dependencies.update([
> >> (
> >> unitofwork.SaveUpdateAll(context, inspect(FundTitle)),
> >> unitofwork.SaveUpdateAll(context, inspect(Employee))
> >> )
> >> ])
> >>
> >>
> >>
> >>
> >>
> >> >
> >> > On Wednesday, October 10, 2018 at 12:41:25 PM UTC-4, Alex Rothberg
> wrote:
> >> >>
> >> >> Is it possible to specific a non viewonly relationship in which I
> have a primary join specified in which none of the fk's are marked
> "foreign"? ie where I can mark the relationship dependancy but it wont set
> any columns? It looks like there may be some logic in sqla that assume all
> columns are fk if none are specified as foreign?
> >> >>
> >> >> On Wednesday, October 10, 2018 at 11:56:49 AM UTC-4, Alex Rothberg
> wrote:
> >> >>>
> >> >>> So one minor issue and one big issue with that solution:
> >> >>> 1. minor issue, I now get these: SAWarning: relationship ''
> will copy column to column , which conflicts with relationship(s):
> '
> >> >>> 2. major issue, I use raiseload("*") and now I start seeing:
> sqlalchemy.exc.InvalidRequestError: 'Employee._ft_for_dependency' is not
> available due to lazy='raise'
> >> >>>
> >> >>> On Wednesday, October 10, 2018 at 9:57:55 AM UTC-4, Mike Bayer
> wrote:
> >>
> >> On Tue, Oct 9, 2018 at 6:45 PM Alex Rothberg
> wrote:
> >> >
> >> > Okay with some small tweaks to your original code, I am able to
> show the issue I am having. comment out flush to see issue:
> >>
> >> so what you're doing here is making Employee dependent on
> FundTitle,
> >> which makes this a little out of the ordinary but this is fine.
> You
> >> need to give the ORM a clue that this dependency exists, since it
> >> never looks at foreign key constraints unless you tell it to.
> >> Adding a relationship to FundTitle that doesn't have viewonly=True
> is
> >> an easy way to do this, there's no need to ever make use of the
> >> relationship otherwise:
> >>
> >> class Employee(Base):
> >> __tablename__ = 'employee'
> >>
> >> # ...
> >> fund_title = relationship(FundTitle, viewonly=True)
> >>
> >> _ft_for_dependency = relationship(FundTitle)
> >>
> >> __table_args__ = (
> >> ForeignKeyConstraint(
> >> (title_id, department_id, fund_id),
> >> (FundTitle.title_id, FundTitle.department_id,
> FundTitle.fund_id)
> >> ),
> >> )
> >>
> >> then you can take the flush() out and there's no issue, as long as
> >> you're always making sure that FundTitle object is present either
> in
> >> the current Session or the row in the database exists.
> >>
> >>
> >> >
> >> > from sqlalchemy import *
> >> > from sqlalchemy.orm import *
> >> > from sqlalchemy.ext.declarative import declarative_base
> >> >
> >> > Base = declarative_base()
> >> >
> >> >
> >> > class Title(Base):
> >> > __tablename__ = 'title'
> >> > id = Column(Integer, primary_key=True)
> >> >
Re: [sqlalchemy] Controlling table dependency for flushing
the raise load issue is because without passive_deletes, it has to
load the collection to make sure those objects are all updated.
passive_deletes fixes, now you just have a warning. or use the unit
of work recipe which is more direct.
On Wed, Oct 10, 2018 at 1:15 PM Alex Rothberg wrote:
>
> Not just for warning. Also this raise load issue. yes, i see that I can't
> mark none. If I could though, that would be awesome since I think it would
> solve this problem? I can test by setting one foreign and seeing if that
> works.
>
> On Wednesday, October 10, 2018 at 1:13:32 PM UTC-4, Mike Bayer wrote:
>>
>> On Wed, Oct 10, 2018 at 12:56 PM Alex Rothberg wrote:
>> >
>> > let me get that. in the meantime, what are your thoughts on just removing
>> > the view only from the original relationship and then using an explicit
>> > primary join where none of the columns are marked foreign? Theoretically
>> > that should solve this problem, no?
>>
>> is this just for the warning?I don't think the relationship() can
>> be set up with no columns marked as foreign, it takes that as a cue
>> that it should figure out the "foreign" columns on its own.
>>
>> There's another way to make sure Employee is always dependent on
>> FundTitle but it's a little bit off-label. Add the dependency you
>> want directly into the unit of work:
>>
>> from sqlalchemy.orm import unitofwork
>> from sqlalchemy import event
>>
>>
>> @event.listens_for(Session, "before_flush")
>> def _add_dep(session, context, objects):
>> context.dependencies.update([
>> (
>> unitofwork.SaveUpdateAll(context, inspect(FundTitle)),
>> unitofwork.SaveUpdateAll(context, inspect(Employee))
>> )
>> ])
>>
>>
>>
>>
>>
>> >
>> > On Wednesday, October 10, 2018 at 12:41:25 PM UTC-4, Alex Rothberg wrote:
>> >>
>> >> Is it possible to specific a non viewonly relationship in which I have a
>> >> primary join specified in which none of the fk's are marked "foreign"? ie
>> >> where I can mark the relationship dependancy but it wont set any columns?
>> >> It looks like there may be some logic in sqla that assume all columns are
>> >> fk if none are specified as foreign?
>> >>
>> >> On Wednesday, October 10, 2018 at 11:56:49 AM UTC-4, Alex Rothberg wrote:
>> >>>
>> >>> So one minor issue and one big issue with that solution:
>> >>> 1. minor issue, I now get these: SAWarning: relationship '' will
>> >>> copy column to column , which conflicts with relationship(s):
>> >>> '
>> >>> 2. major issue, I use raiseload("*") and now I start seeing:
>> >>> sqlalchemy.exc.InvalidRequestError: 'Employee._ft_for_dependency' is not
>> >>> available due to lazy='raise'
>> >>>
>> >>> On Wednesday, October 10, 2018 at 9:57:55 AM UTC-4, Mike Bayer wrote:
>>
>> On Tue, Oct 9, 2018 at 6:45 PM Alex Rothberg wrote:
>> >
>> > Okay with some small tweaks to your original code, I am able to show
>> > the issue I am having. comment out flush to see issue:
>>
>> so what you're doing here is making Employee dependent on FundTitle,
>> which makes this a little out of the ordinary but this is fine. You
>> need to give the ORM a clue that this dependency exists, since it
>> never looks at foreign key constraints unless you tell it to.
>> Adding a relationship to FundTitle that doesn't have viewonly=True is
>> an easy way to do this, there's no need to ever make use of the
>> relationship otherwise:
>>
>> class Employee(Base):
>> __tablename__ = 'employee'
>>
>> # ...
>> fund_title = relationship(FundTitle, viewonly=True)
>>
>> _ft_for_dependency = relationship(FundTitle)
>>
>> __table_args__ = (
>> ForeignKeyConstraint(
>> (title_id, department_id, fund_id),
>> (FundTitle.title_id, FundTitle.department_id,
>> FundTitle.fund_id)
>> ),
>> )
>>
>> then you can take the flush() out and there's no issue, as long as
>> you're always making sure that FundTitle object is present either in
>> the current Session or the row in the database exists.
>>
>>
>> >
>> > from sqlalchemy import *
>> > from sqlalchemy.orm import *
>> > from sqlalchemy.ext.declarative import declarative_base
>> >
>> > Base = declarative_base()
>> >
>> >
>> > class Title(Base):
>> > __tablename__ = 'title'
>> > id = Column(Integer, primary_key=True)
>> > department_id = Column(ForeignKey('department.id'),
>> > nullable=False)
>> >
>> > department = relationship(lambda: Department)
>> >
>> >
>> > class Department(Base):
>> > __tablename__ = 'department'
>> > id = Column(Integer, primary_key=True)
>> >
>> >
>> > class Fund(Base):
>> > __tablename__ = 'fund'
>> > id = Column
Re: [sqlalchemy] Controlling table dependency for flushing
Not just for warning. Also this raise load issue. yes, i see that I can't
mark none. If I could though, that would be awesome since I think it would
solve this problem? I can test by setting one foreign and seeing if that
works.
On Wednesday, October 10, 2018 at 1:13:32 PM UTC-4, Mike Bayer wrote:
>
> On Wed, Oct 10, 2018 at 12:56 PM Alex Rothberg > wrote:
> >
> > let me get that. in the meantime, what are your thoughts on just
> removing the view only from the original relationship and then using an
> explicit primary join where none of the columns are marked foreign?
> Theoretically that should solve this problem, no?
>
> is this just for the warning?I don't think the relationship() can
> be set up with no columns marked as foreign, it takes that as a cue
> that it should figure out the "foreign" columns on its own.
>
> There's another way to make sure Employee is always dependent on
> FundTitle but it's a little bit off-label. Add the dependency you
> want directly into the unit of work:
>
> from sqlalchemy.orm import unitofwork
> from sqlalchemy import event
>
>
> @event.listens_for(Session, "before_flush")
> def _add_dep(session, context, objects):
> context.dependencies.update([
> (
> unitofwork.SaveUpdateAll(context, inspect(FundTitle)),
> unitofwork.SaveUpdateAll(context, inspect(Employee))
> )
> ])
>
>
>
>
>
> >
> > On Wednesday, October 10, 2018 at 12:41:25 PM UTC-4, Alex Rothberg
> wrote:
> >>
> >> Is it possible to specific a non viewonly relationship in which I have
> a primary join specified in which none of the fk's are marked "foreign"? ie
> where I can mark the relationship dependancy but it wont set any columns?
> It looks like there may be some logic in sqla that assume all columns are
> fk if none are specified as foreign?
> >>
> >> On Wednesday, October 10, 2018 at 11:56:49 AM UTC-4, Alex Rothberg
> wrote:
> >>>
> >>> So one minor issue and one big issue with that solution:
> >>> 1. minor issue, I now get these: SAWarning: relationship '' will
> copy column to column , which conflicts with relationship(s): '
> >>> 2. major issue, I use raiseload("*") and now I start seeing:
> sqlalchemy.exc.InvalidRequestError: 'Employee._ft_for_dependency' is not
> available due to lazy='raise'
> >>>
> >>> On Wednesday, October 10, 2018 at 9:57:55 AM UTC-4, Mike Bayer wrote:
>
> On Tue, Oct 9, 2018 at 6:45 PM Alex Rothberg
> wrote:
> >
> > Okay with some small tweaks to your original code, I am able to
> show the issue I am having. comment out flush to see issue:
>
> so what you're doing here is making Employee dependent on FundTitle,
> which makes this a little out of the ordinary but this is fine. You
> need to give the ORM a clue that this dependency exists, since it
> never looks at foreign key constraints unless you tell it to.
> Adding a relationship to FundTitle that doesn't have viewonly=True is
> an easy way to do this, there's no need to ever make use of the
> relationship otherwise:
>
> class Employee(Base):
> __tablename__ = 'employee'
>
> # ...
> fund_title = relationship(FundTitle, viewonly=True)
>
> _ft_for_dependency = relationship(FundTitle)
>
> __table_args__ = (
> ForeignKeyConstraint(
> (title_id, department_id, fund_id),
> (FundTitle.title_id, FundTitle.department_id,
> FundTitle.fund_id)
> ),
> )
>
> then you can take the flush() out and there's no issue, as long as
> you're always making sure that FundTitle object is present either in
> the current Session or the row in the database exists.
>
>
> >
> > from sqlalchemy import *
> > from sqlalchemy.orm import *
> > from sqlalchemy.ext.declarative import declarative_base
> >
> > Base = declarative_base()
> >
> >
> > class Title(Base):
> > __tablename__ = 'title'
> > id = Column(Integer, primary_key=True)
> > department_id = Column(ForeignKey('department.id'),
> nullable=False)
> >
> > department = relationship(lambda: Department)
> >
> >
> > class Department(Base):
> > __tablename__ = 'department'
> > id = Column(Integer, primary_key=True)
> >
> >
> > class Fund(Base):
> > __tablename__ = 'fund'
> > id = Column(Integer, primary_key=True)
> > title_id = Column(ForeignKey('title.id'), nullable=False)
> > department_id = Column(ForeignKey('department.id'),
> nullable=False)
> > department = relationship("Department")
> > title = relationship("Title")
> >
> >
> > class FundTitle(Base):
> > __tablename__ = 'fund_tit
Re: [sqlalchemy] Controlling table dependency for flushing
that's a delete, turn those off by putting passive_deletes on the relationship:
_ft_for_dependency = relationship(FundTitle, passive_deletes=True)
On Wed, Oct 10, 2018 at 1:08 PM Alex Rothberg wrote:
>
> /Users/alex/.pyenv/versions/my-app/lib/python3.7/site-packages/flask_resty/view.py:484:
> in destroy
> self.commit()
> /Users/alex/.pyenv/versions/my-app/lib/python3.7/site-packages/flask_resty/view.py:403:
> in commit
> self.session.commit()
> /Users/alex/.pyenv/versions/my-app/lib/python3.7/site-packages/sqlalchemy/orm/scoping.py:153:
> in do
> return getattr(self.registry(), name)(*args, **kwargs)
> /Users/alex/.pyenv/versions/my-app/lib/python3.7/site-packages/sqlalchemy/orm/session.py:943:
> in commit
> self.transaction.commit()
> /Users/alex/.pyenv/versions/my-app/lib/python3.7/site-packages/sqlalchemy/orm/session.py:467:
> in commit
> self._prepare_impl()
> /Users/alex/.pyenv/versions/my-app/lib/python3.7/site-packages/sqlalchemy/orm/session.py:447:
> in _prepare_impl
> self.session.flush()
> /Users/alex/.pyenv/versions/my-app/lib/python3.7/site-packages/sqlalchemy/orm/session.py:2254:
> in flush
> self._flush(objects)
> /Users/alex/.pyenv/versions/my-app/lib/python3.7/site-packages/sqlalchemy/orm/session.py:2380:
> in _flush
> transaction.rollback(_capture_exception=True)
> /Users/alex/.pyenv/versions/my-app/lib/python3.7/site-packages/sqlalchemy/util/langhelpers.py:66:
> in __exit__
> compat.reraise(exc_type, exc_value, exc_tb)
> /Users/alex/.pyenv/versions/my-app/lib/python3.7/site-packages/sqlalchemy/util/compat.py:249:
> in reraise
> raise value
> /Users/alex/.pyenv/versions/my-app/lib/python3.7/site-packages/sqlalchemy/orm/session.py:2344:
> in _flush
> flush_context.execute()
> /Users/alex/.pyenv/versions/my-app/lib/python3.7/site-packages/sqlalchemy/orm/unitofwork.py:370:
> in execute
> postsort_actions = self._generate_actions()
> /Users/alex/.pyenv/versions/my-app/lib/python3.7/site-packages/sqlalchemy/orm/unitofwork.py:329:
> in _generate_actions
> if action.execute(self):
> /Users/alex/.pyenv/versions/my-app/lib/python3.7/site-packages/sqlalchemy/orm/unitofwork.py:463:
> in execute
> prop_has_changes(uow, delete_states, True) or
> /Users/alex/.pyenv/versions/my-app/lib/python3.7/site-packages/sqlalchemy/orm/dependency.py:234:
> in prop_has_changes
> passive)
> /Users/alex/.pyenv/versions/my-app/lib/python3.7/site-packages/sqlalchemy/orm/unitofwork.py:225:
> in get_attribute_history
> attributes.LOAD_AGAINST_COMMITTED)
> /Users/alex/.pyenv/versions/my-app/lib/python3.7/site-packages/sqlalchemy/orm/attributes.py:755:
> in get_history
> current = self.get(state, dict_, passive=passive)
> /Users/alex/.pyenv/versions/my-app/lib/python3.7/site-packages/sqlalchemy/orm/attributes.py:599:
> in get
> value = callable_(state, passive)
> /Users/alex/.pyenv/versions/my-app/lib/python3.7/site-packages/sqlalchemy/orm/strategies.py:834:
> in __call__
> return strategy._load_for_state(state, passive)
> /Users/alex/.pyenv/versions/my-app/lib/python3.7/site-packages/sqlalchemy/orm/strategies.py:589:
> in _load_for_state
> self._invoke_raise_load(state, passive, "raise")
> _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
> _
>
>
> self =
> state = , passive =
> 51
> lazy = 'raise'
>
>
> def _invoke_raise_load(self, state, passive, lazy):
> raise sa_exc.InvalidRequestError(
> > "'%s' is not available due to lazy='%s'" % (self, lazy)
> )
> E sqlalchemy.exc.InvalidRequestError: 'Employee._ft_for_dependency' is
> not available due to lazy='raise'
>
>
> /Users/alex/.pyenv/versions/my-app/lib/python3.7/site-packages/sqlalchemy/orm/strategies.py:564:
> InvalidRequestError
>
>
>
>
> On Wednesday, October 10, 2018 at 12:56:14 PM UTC-4, Alex Rothberg wrote:
>>
>> let me get that. in the meantime, what are your thoughts on just removing
>> the view only from the original relationship and then using an explicit
>> primary join where none of the columns are marked foreign? Theoretically
>> that should solve this problem, no?
>>
>> On Wednesday, October 10, 2018 at 12:41:25 PM UTC-4, Alex Rothberg wrote:
>>>
>>> Is it possible to specific a non viewonly relationship in which I have a
>>> primary join specified in which none of the fk's are marked "foreign"? ie
>>> where I can mark the relationship dependancy but it wont set any columns?
>>> It looks like there may be some logic in sqla that assume all columns are
>>> fk if none are specified as foreign?
>>>
>>> On Wednesday, October 10, 2018 at 11:56:49 AM UTC-4, Alex Rothberg wrote:
So one minor issue and one big issue with that solution:
1. minor issue, I now get these: SAWarning: relationship '' will copy
column to column , which conflicts with relationship(s): '
2. major issue, I use raiseload("*") and now I start seeing:
sqlalchemy.exc.
Re: [sqlalchemy] Controlling table dependency for flushing
On Wed, Oct 10, 2018 at 12:56 PM Alex Rothberg wrote:
>
> let me get that. in the meantime, what are your thoughts on just removing the
> view only from the original relationship and then using an explicit primary
> join where none of the columns are marked foreign? Theoretically that should
> solve this problem, no?
is this just for the warning?I don't think the relationship() can
be set up with no columns marked as foreign, it takes that as a cue
that it should figure out the "foreign" columns on its own.
There's another way to make sure Employee is always dependent on
FundTitle but it's a little bit off-label. Add the dependency you
want directly into the unit of work:
from sqlalchemy.orm import unitofwork
from sqlalchemy import event
@event.listens_for(Session, "before_flush")
def _add_dep(session, context, objects):
context.dependencies.update([
(
unitofwork.SaveUpdateAll(context, inspect(FundTitle)),
unitofwork.SaveUpdateAll(context, inspect(Employee))
)
])
>
> On Wednesday, October 10, 2018 at 12:41:25 PM UTC-4, Alex Rothberg wrote:
>>
>> Is it possible to specific a non viewonly relationship in which I have a
>> primary join specified in which none of the fk's are marked "foreign"? ie
>> where I can mark the relationship dependancy but it wont set any columns? It
>> looks like there may be some logic in sqla that assume all columns are fk if
>> none are specified as foreign?
>>
>> On Wednesday, October 10, 2018 at 11:56:49 AM UTC-4, Alex Rothberg wrote:
>>>
>>> So one minor issue and one big issue with that solution:
>>> 1. minor issue, I now get these: SAWarning: relationship '' will copy
>>> column to column , which conflicts with relationship(s): '
>>> 2. major issue, I use raiseload("*") and now I start seeing:
>>> sqlalchemy.exc.InvalidRequestError: 'Employee._ft_for_dependency' is not
>>> available due to lazy='raise'
>>>
>>> On Wednesday, October 10, 2018 at 9:57:55 AM UTC-4, Mike Bayer wrote:
On Tue, Oct 9, 2018 at 6:45 PM Alex Rothberg wrote:
>
> Okay with some small tweaks to your original code, I am able to show the
> issue I am having. comment out flush to see issue:
so what you're doing here is making Employee dependent on FundTitle,
which makes this a little out of the ordinary but this is fine. You
need to give the ORM a clue that this dependency exists, since it
never looks at foreign key constraints unless you tell it to.
Adding a relationship to FundTitle that doesn't have viewonly=True is
an easy way to do this, there's no need to ever make use of the
relationship otherwise:
class Employee(Base):
__tablename__ = 'employee'
# ...
fund_title = relationship(FundTitle, viewonly=True)
_ft_for_dependency = relationship(FundTitle)
__table_args__ = (
ForeignKeyConstraint(
(title_id, department_id, fund_id),
(FundTitle.title_id, FundTitle.department_id,
FundTitle.fund_id)
),
)
then you can take the flush() out and there's no issue, as long as
you're always making sure that FundTitle object is present either in
the current Session or the row in the database exists.
>
> from sqlalchemy import *
> from sqlalchemy.orm import *
> from sqlalchemy.ext.declarative import declarative_base
>
> Base = declarative_base()
>
>
> class Title(Base):
> __tablename__ = 'title'
> id = Column(Integer, primary_key=True)
> department_id = Column(ForeignKey('department.id'), nullable=False)
>
> department = relationship(lambda: Department)
>
>
> class Department(Base):
> __tablename__ = 'department'
> id = Column(Integer, primary_key=True)
>
>
> class Fund(Base):
> __tablename__ = 'fund'
> id = Column(Integer, primary_key=True)
> title_id = Column(ForeignKey('title.id'), nullable=False)
> department_id = Column(ForeignKey('department.id'), nullable=False)
> department = relationship("Department")
> title = relationship("Title")
>
>
> class FundTitle(Base):
> __tablename__ = 'fund_title'
> id = Column(Integer, primary_key=True)
> title_id = Column(ForeignKey('title.id'), nullable=False)
> department_id = Column(ForeignKey('department.id'), nullable=False)
> fund_id = Column(ForeignKey('fund.id'), nullable=False)
> department = relationship("Department")
> title = relationship("Title")
> fund = relationship("Fund")
>
> __table_args__ = (
> UniqueConstraint(
> title_id, department_id, fund_id
> ),
> )
>
>
> class Employee(Base):
Re: [sqlalchemy] Controlling table dependency for flushing
let me get that. in the meantime, what are your thoughts on just removing
the view only from the original relationship and then using an explicit
primary join where none of the columns are marked foreign? Theoretically
that should solve this problem, no?
On Wednesday, October 10, 2018 at 12:41:25 PM UTC-4, Alex Rothberg wrote:
>
> Is it possible to specific a non viewonly relationship in which I have a
> primary join specified in which none of the fk's are marked "foreign"? ie
> where I can mark the relationship dependancy but it wont set any columns?
> It looks like there may be some logic in sqla that assume all columns are
> fk if none are specified as foreign?
>
> On Wednesday, October 10, 2018 at 11:56:49 AM UTC-4, Alex Rothberg wrote:
>>
>> So one minor issue and one big issue with that solution:
>> 1. minor issue, I now get these: SAWarning: relationship '' will copy
>> column to column , which conflicts with relationship(s): '
>> 2. major issue, I use raiseload("*") and now I start
>> seeing: sqlalchemy.exc.InvalidRequestError: 'Employee._ft_for_dependency'
>> is not available due to lazy='raise'
>>
>> On Wednesday, October 10, 2018 at 9:57:55 AM UTC-4, Mike Bayer wrote:
>>>
>>> On Tue, Oct 9, 2018 at 6:45 PM Alex Rothberg
>>> wrote:
>>> >
>>> > Okay with some small tweaks to your original code, I am able to show
>>> the issue I am having. comment out flush to see issue:
>>>
>>> so what you're doing here is making Employee dependent on FundTitle,
>>> which makes this a little out of the ordinary but this is fine. You
>>> need to give the ORM a clue that this dependency exists, since it
>>> never looks at foreign key constraints unless you tell it to.
>>> Adding a relationship to FundTitle that doesn't have viewonly=True is
>>> an easy way to do this, there's no need to ever make use of the
>>> relationship otherwise:
>>>
>>> class Employee(Base):
>>> __tablename__ = 'employee'
>>>
>>> # ...
>>> fund_title = relationship(FundTitle, viewonly=True)
>>>
>>> _ft_for_dependency = relationship(FundTitle)
>>>
>>> __table_args__ = (
>>> ForeignKeyConstraint(
>>> (title_id, department_id, fund_id),
>>> (FundTitle.title_id, FundTitle.department_id,
>>> FundTitle.fund_id)
>>> ),
>>> )
>>>
>>> then you can take the flush() out and there's no issue, as long as
>>> you're always making sure that FundTitle object is present either in
>>> the current Session or the row in the database exists.
>>>
>>>
>>> >
>>> > from sqlalchemy import *
>>> > from sqlalchemy.orm import *
>>> > from sqlalchemy.ext.declarative import declarative_base
>>> >
>>> > Base = declarative_base()
>>> >
>>> >
>>> > class Title(Base):
>>> > __tablename__ = 'title'
>>> > id = Column(Integer, primary_key=True)
>>> > department_id = Column(ForeignKey('department.id'),
>>> nullable=False)
>>> >
>>> > department = relationship(lambda: Department)
>>> >
>>> >
>>> > class Department(Base):
>>> > __tablename__ = 'department'
>>> > id = Column(Integer, primary_key=True)
>>> >
>>> >
>>> > class Fund(Base):
>>> > __tablename__ = 'fund'
>>> > id = Column(Integer, primary_key=True)
>>> > title_id = Column(ForeignKey('title.id'), nullable=False)
>>> > department_id = Column(ForeignKey('department.id'),
>>> nullable=False)
>>> > department = relationship("Department")
>>> > title = relationship("Title")
>>> >
>>> >
>>> > class FundTitle(Base):
>>> > __tablename__ = 'fund_title'
>>> > id = Column(Integer, primary_key=True)
>>> > title_id = Column(ForeignKey('title.id'), nullable=False)
>>> > department_id = Column(ForeignKey('department.id'),
>>> nullable=False)
>>> > fund_id = Column(ForeignKey('fund.id'), nullable=False)
>>> > department = relationship("Department")
>>> > title = relationship("Title")
>>> > fund = relationship("Fund")
>>> >
>>> > __table_args__ = (
>>> > UniqueConstraint(
>>> > title_id, department_id, fund_id
>>> > ),
>>> > )
>>> >
>>> >
>>> > class Employee(Base):
>>> > __tablename__ = 'employee'
>>> > id = Column(Integer, primary_key=True)
>>> > title_id = Column(ForeignKey('title.id'), nullable=False)
>>> > department_id = Column(ForeignKey('department.id'),
>>> nullable=False)
>>> > fund_id = Column(ForeignKey('fund.id'), nullable=False)
>>> >
>>> > department = relationship(lambda: Department)
>>> > title = relationship("Title")
>>> > fund = relationship("Fund")
>>> >
>>> > fund_title = relationship(FundTitle, viewonly=True)
>>> >
>>> >
>>> > __table_args__ = (
>>> > ForeignKeyConstraint(
>>> > (title_id, department_id, fund_id), (FundTitle.title_id,
>>> FundTitle.department_id, FundTitle.fund_id)
>>> > ),
>>> > )
>>> >
>>> >
>>> > e = create_engine("postgresql
Re: [sqlalchemy] Controlling table dependency for flushing
what's your stacktrace for the raise, the unit of work should not be
trying to load the items in this collection?
On Wed, Oct 10, 2018 at 11:56 AM Alex Rothberg wrote:
>
> So one minor issue and one big issue with that solution:
> 1. minor issue, I now get these: SAWarning: relationship '' will copy
> column to column , which conflicts with relationship(s): '
> 2. major issue, I use raiseload("*") and now I start seeing:
> sqlalchemy.exc.InvalidRequestError: 'Employee._ft_for_dependency' is not
> available due to lazy='raise'
>
> On Wednesday, October 10, 2018 at 9:57:55 AM UTC-4, Mike Bayer wrote:
>>
>> On Tue, Oct 9, 2018 at 6:45 PM Alex Rothberg wrote:
>> >
>> > Okay with some small tweaks to your original code, I am able to show the
>> > issue I am having. comment out flush to see issue:
>>
>> so what you're doing here is making Employee dependent on FundTitle,
>> which makes this a little out of the ordinary but this is fine. You
>> need to give the ORM a clue that this dependency exists, since it
>> never looks at foreign key constraints unless you tell it to.
>> Adding a relationship to FundTitle that doesn't have viewonly=True is
>> an easy way to do this, there's no need to ever make use of the
>> relationship otherwise:
>>
>> class Employee(Base):
>> __tablename__ = 'employee'
>>
>> # ...
>> fund_title = relationship(FundTitle, viewonly=True)
>>
>> _ft_for_dependency = relationship(FundTitle)
>>
>> __table_args__ = (
>> ForeignKeyConstraint(
>> (title_id, department_id, fund_id),
>> (FundTitle.title_id, FundTitle.department_id, FundTitle.fund_id)
>> ),
>> )
>>
>> then you can take the flush() out and there's no issue, as long as
>> you're always making sure that FundTitle object is present either in
>> the current Session or the row in the database exists.
>>
>>
>> >
>> > from sqlalchemy import *
>> > from sqlalchemy.orm import *
>> > from sqlalchemy.ext.declarative import declarative_base
>> >
>> > Base = declarative_base()
>> >
>> >
>> > class Title(Base):
>> > __tablename__ = 'title'
>> > id = Column(Integer, primary_key=True)
>> > department_id = Column(ForeignKey('department.id'), nullable=False)
>> >
>> > department = relationship(lambda: Department)
>> >
>> >
>> > class Department(Base):
>> > __tablename__ = 'department'
>> > id = Column(Integer, primary_key=True)
>> >
>> >
>> > class Fund(Base):
>> > __tablename__ = 'fund'
>> > id = Column(Integer, primary_key=True)
>> > title_id = Column(ForeignKey('title.id'), nullable=False)
>> > department_id = Column(ForeignKey('department.id'), nullable=False)
>> > department = relationship("Department")
>> > title = relationship("Title")
>> >
>> >
>> > class FundTitle(Base):
>> > __tablename__ = 'fund_title'
>> > id = Column(Integer, primary_key=True)
>> > title_id = Column(ForeignKey('title.id'), nullable=False)
>> > department_id = Column(ForeignKey('department.id'), nullable=False)
>> > fund_id = Column(ForeignKey('fund.id'), nullable=False)
>> > department = relationship("Department")
>> > title = relationship("Title")
>> > fund = relationship("Fund")
>> >
>> > __table_args__ = (
>> > UniqueConstraint(
>> > title_id, department_id, fund_id
>> > ),
>> > )
>> >
>> >
>> > class Employee(Base):
>> > __tablename__ = 'employee'
>> > id = Column(Integer, primary_key=True)
>> > title_id = Column(ForeignKey('title.id'), nullable=False)
>> > department_id = Column(ForeignKey('department.id'), nullable=False)
>> > fund_id = Column(ForeignKey('fund.id'), nullable=False)
>> >
>> > department = relationship(lambda: Department)
>> > title = relationship("Title")
>> > fund = relationship("Fund")
>> >
>> > fund_title = relationship(FundTitle, viewonly=True)
>> >
>> >
>> > __table_args__ = (
>> > ForeignKeyConstraint(
>> > (title_id, department_id, fund_id), (FundTitle.title_id,
>> > FundTitle.department_id, FundTitle.fund_id)
>> > ),
>> > )
>> >
>> >
>> > e = create_engine("postgresql://localhost/test_issue", echo=False)
>> >
>> > # Base.metadata.drop_all(e)
>> > Base.metadata.create_all(e)
>> >
>> > s = Session(e)
>> > # s.rollback()
>> >
>> > while True:
>> > d1 = Department()
>> > t1 = Title(department=d1)
>> > f1 = Fund(department=d1, title=t1)
>> > ft1 = FundTitle(title=t1, department=d1, fund=f1)
>> >
>> > s.add_all([d1, t1, f1, ft1])
>> >
>> > s.flush()
>> >
>> > e1 = Employee(title=t1, department=d1, fund=f1)
>> >
>> > s.add_all([e1,])
>> > s.commit()
>> >
>> > On Tuesday, October 9, 2018 at 12:20:30 PM UTC-4, Mike Bayer wrote:
>> >>
>> >> On Tue, Oct 9, 2018 at 10:44 AM Alex Rothberg wrote:
>> >> >
>> >> > In looking at what you wrote doesn't this cause an fk violation (it
>> >> > does for me):
>> >> > 2018-10-08 10:18:38,760 INFO sqlalchem
Re: [sqlalchemy] Controlling table dependency for flushing
Is it possible to specific a non viewonly relationship in which I have a
primary join specified in which none of the fk's are marked "foreign"? ie
where I can mark the relationship dependancy but it wont set any columns?
It looks like there may be some logic in sqla that assume all columns are
fk if none are specified as foreign?
On Wednesday, October 10, 2018 at 11:56:49 AM UTC-4, Alex Rothberg wrote:
>
> So one minor issue and one big issue with that solution:
> 1. minor issue, I now get these: SAWarning: relationship '' will copy
> column to column , which conflicts with relationship(s): '
> 2. major issue, I use raiseload("*") and now I start
> seeing: sqlalchemy.exc.InvalidRequestError: 'Employee._ft_for_dependency'
> is not available due to lazy='raise'
>
> On Wednesday, October 10, 2018 at 9:57:55 AM UTC-4, Mike Bayer wrote:
>>
>> On Tue, Oct 9, 2018 at 6:45 PM Alex Rothberg wrote:
>> >
>> > Okay with some small tweaks to your original code, I am able to show
>> the issue I am having. comment out flush to see issue:
>>
>> so what you're doing here is making Employee dependent on FundTitle,
>> which makes this a little out of the ordinary but this is fine. You
>> need to give the ORM a clue that this dependency exists, since it
>> never looks at foreign key constraints unless you tell it to.
>> Adding a relationship to FundTitle that doesn't have viewonly=True is
>> an easy way to do this, there's no need to ever make use of the
>> relationship otherwise:
>>
>> class Employee(Base):
>> __tablename__ = 'employee'
>>
>> # ...
>> fund_title = relationship(FundTitle, viewonly=True)
>>
>> _ft_for_dependency = relationship(FundTitle)
>>
>> __table_args__ = (
>> ForeignKeyConstraint(
>> (title_id, department_id, fund_id),
>> (FundTitle.title_id, FundTitle.department_id,
>> FundTitle.fund_id)
>> ),
>> )
>>
>> then you can take the flush() out and there's no issue, as long as
>> you're always making sure that FundTitle object is present either in
>> the current Session or the row in the database exists.
>>
>>
>> >
>> > from sqlalchemy import *
>> > from sqlalchemy.orm import *
>> > from sqlalchemy.ext.declarative import declarative_base
>> >
>> > Base = declarative_base()
>> >
>> >
>> > class Title(Base):
>> > __tablename__ = 'title'
>> > id = Column(Integer, primary_key=True)
>> > department_id = Column(ForeignKey('department.id'),
>> nullable=False)
>> >
>> > department = relationship(lambda: Department)
>> >
>> >
>> > class Department(Base):
>> > __tablename__ = 'department'
>> > id = Column(Integer, primary_key=True)
>> >
>> >
>> > class Fund(Base):
>> > __tablename__ = 'fund'
>> > id = Column(Integer, primary_key=True)
>> > title_id = Column(ForeignKey('title.id'), nullable=False)
>> > department_id = Column(ForeignKey('department.id'),
>> nullable=False)
>> > department = relationship("Department")
>> > title = relationship("Title")
>> >
>> >
>> > class FundTitle(Base):
>> > __tablename__ = 'fund_title'
>> > id = Column(Integer, primary_key=True)
>> > title_id = Column(ForeignKey('title.id'), nullable=False)
>> > department_id = Column(ForeignKey('department.id'),
>> nullable=False)
>> > fund_id = Column(ForeignKey('fund.id'), nullable=False)
>> > department = relationship("Department")
>> > title = relationship("Title")
>> > fund = relationship("Fund")
>> >
>> > __table_args__ = (
>> > UniqueConstraint(
>> > title_id, department_id, fund_id
>> > ),
>> > )
>> >
>> >
>> > class Employee(Base):
>> > __tablename__ = 'employee'
>> > id = Column(Integer, primary_key=True)
>> > title_id = Column(ForeignKey('title.id'), nullable=False)
>> > department_id = Column(ForeignKey('department.id'),
>> nullable=False)
>> > fund_id = Column(ForeignKey('fund.id'), nullable=False)
>> >
>> > department = relationship(lambda: Department)
>> > title = relationship("Title")
>> > fund = relationship("Fund")
>> >
>> > fund_title = relationship(FundTitle, viewonly=True)
>> >
>> >
>> > __table_args__ = (
>> > ForeignKeyConstraint(
>> > (title_id, department_id, fund_id), (FundTitle.title_id,
>> FundTitle.department_id, FundTitle.fund_id)
>> > ),
>> > )
>> >
>> >
>> > e = create_engine("postgresql://localhost/test_issue", echo=False)
>> >
>> > # Base.metadata.drop_all(e)
>> > Base.metadata.create_all(e)
>> >
>> > s = Session(e)
>> > # s.rollback()
>> >
>> > while True:
>> > d1 = Department()
>> > t1 = Title(department=d1)
>> > f1 = Fund(department=d1, title=t1)
>> > ft1 = FundTitle(title=t1, department=d1, fund=f1)
>> >
>> > s.add_all([d1, t1, f1, ft1])
>> >
>> > s.flush()
>> >
>> > e1 = Employee(title=t1, depart
Re: [sqlalchemy] Controlling table dependency for flushing
So one minor issue and one big issue with that solution:
1. minor issue, I now get these: SAWarning: relationship '' will copy
column to column , which conflicts with relationship(s): '
2. major issue, I use raiseload("*") and now I start
seeing: sqlalchemy.exc.InvalidRequestError: 'Employee._ft_for_dependency'
is not available due to lazy='raise'
On Wednesday, October 10, 2018 at 9:57:55 AM UTC-4, Mike Bayer wrote:
>
> On Tue, Oct 9, 2018 at 6:45 PM Alex Rothberg > wrote:
> >
> > Okay with some small tweaks to your original code, I am able to show the
> issue I am having. comment out flush to see issue:
>
> so what you're doing here is making Employee dependent on FundTitle,
> which makes this a little out of the ordinary but this is fine. You
> need to give the ORM a clue that this dependency exists, since it
> never looks at foreign key constraints unless you tell it to.
> Adding a relationship to FundTitle that doesn't have viewonly=True is
> an easy way to do this, there's no need to ever make use of the
> relationship otherwise:
>
> class Employee(Base):
> __tablename__ = 'employee'
>
> # ...
> fund_title = relationship(FundTitle, viewonly=True)
>
> _ft_for_dependency = relationship(FundTitle)
>
> __table_args__ = (
> ForeignKeyConstraint(
> (title_id, department_id, fund_id),
> (FundTitle.title_id, FundTitle.department_id,
> FundTitle.fund_id)
> ),
> )
>
> then you can take the flush() out and there's no issue, as long as
> you're always making sure that FundTitle object is present either in
> the current Session or the row in the database exists.
>
>
> >
> > from sqlalchemy import *
> > from sqlalchemy.orm import *
> > from sqlalchemy.ext.declarative import declarative_base
> >
> > Base = declarative_base()
> >
> >
> > class Title(Base):
> > __tablename__ = 'title'
> > id = Column(Integer, primary_key=True)
> > department_id = Column(ForeignKey('department.id'), nullable=False)
> >
> > department = relationship(lambda: Department)
> >
> >
> > class Department(Base):
> > __tablename__ = 'department'
> > id = Column(Integer, primary_key=True)
> >
> >
> > class Fund(Base):
> > __tablename__ = 'fund'
> > id = Column(Integer, primary_key=True)
> > title_id = Column(ForeignKey('title.id'), nullable=False)
> > department_id = Column(ForeignKey('department.id'), nullable=False)
> > department = relationship("Department")
> > title = relationship("Title")
> >
> >
> > class FundTitle(Base):
> > __tablename__ = 'fund_title'
> > id = Column(Integer, primary_key=True)
> > title_id = Column(ForeignKey('title.id'), nullable=False)
> > department_id = Column(ForeignKey('department.id'), nullable=False)
> > fund_id = Column(ForeignKey('fund.id'), nullable=False)
> > department = relationship("Department")
> > title = relationship("Title")
> > fund = relationship("Fund")
> >
> > __table_args__ = (
> > UniqueConstraint(
> > title_id, department_id, fund_id
> > ),
> > )
> >
> >
> > class Employee(Base):
> > __tablename__ = 'employee'
> > id = Column(Integer, primary_key=True)
> > title_id = Column(ForeignKey('title.id'), nullable=False)
> > department_id = Column(ForeignKey('department.id'), nullable=False)
> > fund_id = Column(ForeignKey('fund.id'), nullable=False)
> >
> > department = relationship(lambda: Department)
> > title = relationship("Title")
> > fund = relationship("Fund")
> >
> > fund_title = relationship(FundTitle, viewonly=True)
> >
> >
> > __table_args__ = (
> > ForeignKeyConstraint(
> > (title_id, department_id, fund_id), (FundTitle.title_id,
> FundTitle.department_id, FundTitle.fund_id)
> > ),
> > )
> >
> >
> > e = create_engine("postgresql://localhost/test_issue", echo=False)
> >
> > # Base.metadata.drop_all(e)
> > Base.metadata.create_all(e)
> >
> > s = Session(e)
> > # s.rollback()
> >
> > while True:
> > d1 = Department()
> > t1 = Title(department=d1)
> > f1 = Fund(department=d1, title=t1)
> > ft1 = FundTitle(title=t1, department=d1, fund=f1)
> >
> > s.add_all([d1, t1, f1, ft1])
> >
> > s.flush()
> >
> > e1 = Employee(title=t1, department=d1, fund=f1)
> >
> > s.add_all([e1,])
> > s.commit()
> >
> > On Tuesday, October 9, 2018 at 12:20:30 PM UTC-4, Mike Bayer wrote:
> >>
> >> On Tue, Oct 9, 2018 at 10:44 AM Alex Rothberg
> wrote:
> >> >
> >> > In looking at what you wrote doesn't this cause an fk violation (it
> does for me):
> >> > 2018-10-08 10:18:38,760 INFO sqlalchemy.engine.base.Engine INSERT
> INTO employee (title_id, department_id, fund_id) VALUES (%(title_id)s,
> %(department_id)s, %(fund_id)s) RETURNING employee.id
> >> > 2018-10-08 10:18:38,763 INFO sqlalc
Re: [sqlalchemy] Re: View DDL Statements with Parameters in Select Statement
good catch, I've updated the recipe at https://bitbucket.org/zzzeek/sqlalchemy/wiki/UsageRecipes/Views On Tue, Oct 9, 2018 at 11:26 PM Philip Martin wrote: > > Ok. I think I got it. From looking at the documentation here, I want to do > something like: > > compiler.sql_compiler.process(create.selectable, literal_binds=True) > > > > > On Tuesday, October 9, 2018 at 9:07:03 PM UTC-5, Philip Martin wrote: >> >> I am attempting to use Sqlalchemy to build views along the table and schema >> DDL statements. In mainly relied on the view recipe wiki with a couple >> modifications to build logic for view DDL statements. I've included this >> code inside my gist, alongside a DDL schema that is causing my issue. >> >> Everything worked as planned when I had simple select statements generating >> views. However, when I tried to build a view that contained a string in a >> FILTER (WHERE) clause or in a WHERE clause, I received an error like the one >> below which seems to relate to the DDL compiler not being able to process >> parameters inside a DDL statement. I was wondering if anyone knows if it is >> possible to compile parameterized strings in a DDL statement, or if I >> perhaps have a different issue with the code? I am using Python 3.7, >> Sqlalchemy 1.2.12 and Postgresql 10. >> >> >> Enter code here... File "", line 1, in >> >> declare(engine) >> File "", line 164, in declare >> Base.metadata.create_all(engine) >> File "/sqlalchemy/sql/schema.py", line 4005, in create_all >> tables=tables) >> File "/sqlalchemy/engine/base.py", line 1940, in _run_visitor >> conn._run_visitor(visitorcallable, element, **kwargs) >> File "/sqlalchemy/engine/base.py", line 1549, in _run_visitor >> **kwargs).traverse_single(element) >> File "/sqlalchemy/sql/visitors.py", line 121, in traverse_single >> return meth(obj, **kw) >> File "/sqlalchemy/sql/ddl.py", line 765, in visit_metadata >> _ddl_runner=self) >> File "/sqlalchemy/event/attr.py", line 284, in __call__ >> fn(*args, **kw) >> File "/sqlalchemy/sql/ddl.py", line 245, in __call__ >> return bind.execute(self.against(target)) >> File "/sqlalchemy/engine/base.py", line 948, in execute >> return meth(self, multiparams, params) >> File "/sqlalchemy/sql/ddl.py", line 68, in _execute_on_connection >> return connection._execute_ddl(self, multiparams, params) >> File "/sqlalchemy/engine/base.py", line 1009, in _execute_ddl >> compiled >> File "/sqlalchemy/engine/base.py", line 1200, in _execute_context >> context) >> File "/sqlalchemy/engine/base.py", line 1416, in _handle_dbapi_exception >> util.reraise(*exc_info) >> File "/sqlalchemy/util/compat.py", line 249, in reraise >> raise value >> File "/sqlalchemy/engine/base.py", line 1193, in _execute_context >> context) >> File "/sqlalchemy/engine/default.py", line 509, in do_execute >> cursor.execute(statement, parameters) >> KeyError: 'asset_class_code_1' >> > -- > SQLAlchemy - > The Python SQL Toolkit and Object Relational Mapper > > http://www.sqlalchemy.org/ > > To post example code, please provide an MCVE: Minimal, Complete, and > Verifiable Example. See http://stackoverflow.com/help/mcve for a full > description. > --- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to sqlalchemy+unsubscr...@googlegroups.com. > To post to this group, send email to sqlalchemy@googlegroups.com. > Visit this group at https://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/d/optout. -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Controlling table dependency for flushing
On Tue, Oct 9, 2018 at 6:45 PM Alex Rothberg wrote:
>
> Okay with some small tweaks to your original code, I am able to show the
> issue I am having. comment out flush to see issue:
so what you're doing here is making Employee dependent on FundTitle,
which makes this a little out of the ordinary but this is fine. You
need to give the ORM a clue that this dependency exists, since it
never looks at foreign key constraints unless you tell it to.
Adding a relationship to FundTitle that doesn't have viewonly=True is
an easy way to do this, there's no need to ever make use of the
relationship otherwise:
class Employee(Base):
__tablename__ = 'employee'
# ...
fund_title = relationship(FundTitle, viewonly=True)
_ft_for_dependency = relationship(FundTitle)
__table_args__ = (
ForeignKeyConstraint(
(title_id, department_id, fund_id),
(FundTitle.title_id, FundTitle.department_id, FundTitle.fund_id)
),
)
then you can take the flush() out and there's no issue, as long as
you're always making sure that FundTitle object is present either in
the current Session or the row in the database exists.
>
> from sqlalchemy import *
> from sqlalchemy.orm import *
> from sqlalchemy.ext.declarative import declarative_base
>
> Base = declarative_base()
>
>
> class Title(Base):
> __tablename__ = 'title'
> id = Column(Integer, primary_key=True)
> department_id = Column(ForeignKey('department.id'), nullable=False)
>
> department = relationship(lambda: Department)
>
>
> class Department(Base):
> __tablename__ = 'department'
> id = Column(Integer, primary_key=True)
>
>
> class Fund(Base):
> __tablename__ = 'fund'
> id = Column(Integer, primary_key=True)
> title_id = Column(ForeignKey('title.id'), nullable=False)
> department_id = Column(ForeignKey('department.id'), nullable=False)
> department = relationship("Department")
> title = relationship("Title")
>
>
> class FundTitle(Base):
> __tablename__ = 'fund_title'
> id = Column(Integer, primary_key=True)
> title_id = Column(ForeignKey('title.id'), nullable=False)
> department_id = Column(ForeignKey('department.id'), nullable=False)
> fund_id = Column(ForeignKey('fund.id'), nullable=False)
> department = relationship("Department")
> title = relationship("Title")
> fund = relationship("Fund")
>
> __table_args__ = (
> UniqueConstraint(
> title_id, department_id, fund_id
> ),
> )
>
>
> class Employee(Base):
> __tablename__ = 'employee'
> id = Column(Integer, primary_key=True)
> title_id = Column(ForeignKey('title.id'), nullable=False)
> department_id = Column(ForeignKey('department.id'), nullable=False)
> fund_id = Column(ForeignKey('fund.id'), nullable=False)
>
> department = relationship(lambda: Department)
> title = relationship("Title")
> fund = relationship("Fund")
>
> fund_title = relationship(FundTitle, viewonly=True)
>
>
> __table_args__ = (
> ForeignKeyConstraint(
> (title_id, department_id, fund_id), (FundTitle.title_id,
> FundTitle.department_id, FundTitle.fund_id)
> ),
> )
>
>
> e = create_engine("postgresql://localhost/test_issue", echo=False)
>
> # Base.metadata.drop_all(e)
> Base.metadata.create_all(e)
>
> s = Session(e)
> # s.rollback()
>
> while True:
> d1 = Department()
> t1 = Title(department=d1)
> f1 = Fund(department=d1, title=t1)
> ft1 = FundTitle(title=t1, department=d1, fund=f1)
>
> s.add_all([d1, t1, f1, ft1])
>
> s.flush()
>
> e1 = Employee(title=t1, department=d1, fund=f1)
>
> s.add_all([e1,])
> s.commit()
>
> On Tuesday, October 9, 2018 at 12:20:30 PM UTC-4, Mike Bayer wrote:
>>
>> On Tue, Oct 9, 2018 at 10:44 AM Alex Rothberg wrote:
>> >
>> > In looking at what you wrote doesn't this cause an fk violation (it does
>> > for me):
>> > 2018-10-08 10:18:38,760 INFO sqlalchemy.engine.base.Engine INSERT INTO
>> > employee (title_id, department_id, fund_id) VALUES (%(title_id)s,
>> > %(department_id)s, %(fund_id)s) RETURNING employee.id
>> > 2018-10-08 10:18:38,763 INFO sqlalchemy.engine.base.Engine INSERT INTO
>> > fund_title (title_id, department_id, fund_id) VALUES (%(title_id)s,
>> > %(department_id)s, %(fund_id)s) RETURNING fund_title.id
>> >
>> > in that a a (non deferred) fk is violated between employee and fund_title ?
>>
>> see we need to see how youve laid out your ForeignKeyConstraints, if
>> they are composite and overlapping, there are additional options that
>> may be needed (specifically the post_update flag). you'll note I laid
>> out all FKs as single column.
>>
>> >
>> > On Mon, Oct 8, 2018 at 10:20 AM Mike Bayer wrote:
>> >>
>> >> On Sun, Oct 7, 2018 at 7:11 PM Alex Rothberg wrote:
>> >> >
>> >> > Okay so I investigated / thought about this further. The issue is that
>> >> > while I do have a relationship between the various models, some of the
>> >
Re: [sqlalchemy] joinedload_all or equivalent onto the end of a chain?
On Tue, Oct 9, 2018 at 5:34 PM seaders wrote: > > I've an ORM query that looks like, > > > ActiveAccountStrats.query > .options( > joinedload_all( > ActiveAccountStrats.strats, > AccountStrat.account), > *(joinedload_all( > ActiveAccountStrats.strats, > AccountStrat.strat, > col) > for col in (Strat.moneyline, > Strat.handicap, > Strat.overunder)) > ) > > > Which works perfectly well, but I was wondering if there's a way to do a > joinedload of multiple keys/columns, just like you're able to do from the > root, as in, > > > ActiveAccountStrats.query > .options( > joinedload_all( > ActiveAccountStrats.strats, > AccountStrat.account), > joinedload_all( > ActiveAccountStrats.strats, > AccountStrat.strat) > .joinedload_all( > Strat.moneyline, > Strat.handicap, > Strat.overunder) > ) > > > Or something equivalent. so the thing you're doing with the "root" is not actually how joinedload_all() was meant to work, but it happens to work here. The "all" aspect of it is part of an older usage pattern where you'd be able to indicate joined loading for multiple elements in a path at once, that is, joinedload_all(A.b, B.c, C.d) is equivalent in the more modern API to: joinedload(A.b).joinedload(B.c).joinedload(C.d) it's for this reason that joinedload_all() is marked as deprecated (but will not be removed in any 1.x series of SQLAlchemy). if you just want a "joinedload all these sibling paths" function, just make that yourself: def my_joinedload_all(*paths): return [joinedload(path) for path in paths] > > -- > SQLAlchemy - > The Python SQL Toolkit and Object Relational Mapper > > http://www.sqlalchemy.org/ > > To post example code, please provide an MCVE: Minimal, Complete, and > Verifiable Example. See http://stackoverflow.com/help/mcve for a full > description. > --- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to sqlalchemy+unsubscr...@googlegroups.com. > To post to this group, send email to sqlalchemy@googlegroups.com. > Visit this group at https://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/d/optout. -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
