Re: [sqlalchemy] Unexplained SELECT Being Issued
Makes sense. The code posted was a stripped down example of my issue. What
I was seeing was an integrity error caused by the autoflush of that load:
s = Session(e)
e = Employee(id=1)
s.add(e)
s.flush()
er = EmployeeRecord() # there are other attributes to EmployeeRecord
# assume this is caused by another attribute on EmployeeRecord:
s.add(er)
# this then blows up:
e.records = [er]
On Saturday, December 1, 2018 at 10:08:08 PM UTC-5, Mike Bayer wrote:
>
> On Sat, Dec 1, 2018 at 9:55 PM Mike Bayer > wrote:
> >
> > On Sat, Dec 1, 2018 at 9:21 PM Alex Rothberg > wrote:
> > >
> > > I set up the DB:
> > > from sqlalchemy import *
> > > from sqlalchemy.orm import *
> > > from sqlalchemy.ext.declarative import declarative_base
> > >
> > >
> > > Base = declarative_base()
> > >
> > > class Employee(Base):
> > > __tablename__ = 'employee'
> > > id = Column(Integer, primary_key=True)
> > >
> > > class EmployeeRecord(Base):
> > > __tablename__ = 'employee_record'
> > >
> > > employee_id = Column(Integer, ForeignKey(Employee.id),
> primary_key=True)
> > >
> > >
> > > employee = relationship(
> > > Employee,
> > > viewonly=True,
> > > backref=backref("records", passive_deletes="all",),
> > > passive_deletes="all",
> > > )
> > >
> > > e = create_engine("postgresql://localhost/test_issue2", echo=True)
> > >
> > > Base.metadata.drop_all(e)
> > > Base.metadata.create_all(e)
> > >
> > > and then:
> > > s = Session(e)
> > >
> > > e = Employee(id=1)
> > > s.add(e)
> > >
> > > s.flush()
> > >
> > > print("")
> > > e.records.clear()
> > >
> > > and I see:
> > >
> > > 2018-12-01 21:16:13,608 INFO sqlalchemy.engine.base.Engine SELECT
> employee_record.employee_id AS employee_record_employee_id FROM
> employee_record WHERE %(param_1)s = employee_record.employee_id
> > >
> > > I don't understand why that SELECT is needed given the passive_deletes
> being set.
> >
> > passive_deletes=all is not used here because you have no cascade
> > delete set on Employee.records. passive_deletes only takes effect for
> > a cascaded delete when you were to mark the parent Employee as deleted
> > - this is because databases have ON DELETE CASCADE features that can
> > do the delete for us.
>
> slight correction, with passive_deletes=all, you don't have to have
> any other "cascade" settings on the relationship - the "all" setting
> refers to the nulling out of foreign key columns that would normally
> occur when you deleted the Employee without specifying any cascade to
> the child objects. but this is still a flag that only applies to the
> case of the parent object being deleted. an actual access to a
> collection, even to remove all the items from it, is always going to
> need to know what objects were in that collection, at the very least
> to handle backref events.
>
>
> >
> > In this case, you are directly removing the records from the Employee,
> > which means you would like to emit UPDATE statements for each of
> > those records marking their foreign key attribute to NULL. SQLAlchemy
> > needs to know all the identities for this operation so the list is
> > loaded. As it turns out, there are no records, but the ORM didn't
> > know that because the attribute was not initialized (they may have
> > been persisted separately, such as, if you added individual
> > EmployeeRecord() objects with the foreign key of that Employee.
> >
> > Otherwise it seems like you are expecting that "e.records.clear()"
> > would do absolutely nothing, in which case, why are you calling
> > "e.records.clear()". If you want to avoid the SELECT in this very
> > specific case, set the list to [] when you first persist the object.
> >
> >
> >
> > >
> > > --
> > > 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
[sqlalchemy] Unexplained SELECT Being Issued
I set up the DB:
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class Employee(Base):
__tablename__ = 'employee'
id = Column(Integer, primary_key=True)
class EmployeeRecord(Base):
__tablename__ = 'employee_record'
employee_id = Column(Integer, ForeignKey(Employee.id), primary_key=True)
employee = relationship(
Employee,
viewonly=True,
backref=backref("records", passive_deletes="all",),
passive_deletes="all",
)
e = create_engine("postgresql://localhost/test_issue2", echo=True)
Base.metadata.drop_all(e)
Base.metadata.create_all(e)
and then:
s = Session(e)
e = Employee(id=1)
s.add(e)
s.flush()
print("")
e.records.clear()
and I see:
2018-12-01 21:16:13,608 INFO sqlalchemy.engine.base.Engine SELECT
employee_record.employee_id AS employee_record_employee_id FROM employee_record
WHERE %(param_1)s = employee_record.employee_id
I don't understand why that SELECT is needed given the passive_deletes being
set.
--
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] Creating Sub Object with out inserting into Base Table
I have added a new sub class to my model hierarchy. I would like to instantiate it however there will be cases where the base object / row already exists. I tried to solve this by passing in the user object to the StaffUser but it looks like sqla still tried to INSERT into the User table leading to an unique constraint violation. My models are: class User(db.Model): id = db.Column(UUID, default=uuid.uuid4, primary_key=True) class StaffUser(User): id = db.Column(UUID, db.ForeignKey(User.id), primary_key=True) user = db.relationship(User is there anyway to tell sqla to re-use an existing base instance (ie to not attempt an insert)? user = User.query.get(id) staff_user = StaffUser(user=user) -- 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
>
> 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".
So the issue comes up when setting any of the relationships to None. For
example if I cease to have all of fund, department and title, then the
FundTitle is None. If i assign that to the Employee it then clears all of
the other (overlapping) fks.
On Wednesday, October 10, 2018 at 8:28:39 PM UTC-4, Mike Bayer wrote:
>
> 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
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
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)) > >> >> >> ) > >> >>
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
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'
> >> >>>>
> >> >>>> # ...
> >> >>>>
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()
>
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")
>>> >
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&q
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, d
Re: [sqlalchemy] Controlling table dependency for flushing
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:
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
> relationships are viewonly since I have overlapping fks.
> >> >
> >> > For example I have a model Employee, which has fks: department_id,
> title_id, and fund_id. The related models are Department (fk
> department_id), Title (fk department_id and title_id) , Fund (fk fund_id)
> and FundTitle (fk department_id, title_id and fund_id). I have set
> FundTitle to viewonly. When updating / creating an Employee, I do create
> and add a new FundTitle to the session, however I don't assign it to the
> employee as the relationship is viewonly. If I don't flush before making
> the assignment, the final flush / commit attempts to update / create the
> employee before creating the FundTitle.
> >>
> >> let's work with source code that is runnable (e.g. MCVE). Below is
> >> the model that i
Re: [sqlalchemy] Controlling table dependency for flushing
I should say, I didn't run your exact code but essentially that ordering
is what is causing my issues with my code in that the new fund_title is
inserted after the new employee.
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 ?
>
> 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
>> relationships are viewonly since I have overlapping fks.
>> >
>> > For example I have a model Employee, which has fks: department_id,
>> title_id, and fund_id. The related models are Department (fk
>> department_id), Title (fk department_id and title_id) , Fund (fk fund_id)
>> and FundTitle (fk department_id, title_id and fund_id). I have set
>> FundTitle to viewonly. When updating / creating an Employee, I do create
>> and add a new FundTitle to the session, however I don't assign it to the
>> employee as the relationship is viewonly. If I don't flush before making
>> the assignment, the final flush / commit attempts to update / create the
>> employee before creating the FundTitle.
>>
>> let's work with source code that is runnable (e.g. MCVE). Below is
>> the model that it seems you are describing, and then there's a
>> demonstration of assembly of all those components using relationships,
>> a single flush and it all goes in in the correct order, all FKs are
>> nullable=False.
>>
>> from sqlalchemy import *
>> from sqlalchemy.orm import *
>> from sqlalchemy.ext.declarative import declarative_base
>>
>> Base = declarative_base()
>>
>>
>> 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("Department")
>> title = relationship("Title")
>> fund = relationship("Fund")
>>
>>
>> class Title(Base):
>> __tablename__ = 'title'
>> id = Column(Integer, primary_key=True)
>> department_id = Column(ForeignKey('department.id'), nullable=False)
>> department = relationship("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")
>>
>> e = create_engine("postgresql://scott:tiger@localhost/test", echo=True)
>> Base.metadata.create_all(e)
>>
>> s = Session(e)
>>
>> d1 = Department()
>> t1 = Title(department=d1)
>> f1 = Fund(department=d1, title=t1)
>> ft1 = FundTitle(title=t1, department=d1, fund=f1)
>> e1 = Employee(title=t1, department=d1, fund=f1)
>>
>> s.add_all([d1, t1, f1, ft1, e1])
>> s.commit()
>>
>>
>> the INSERTs can be ordered naturally here and the unit of work will do
>> that for you if you use relationship:
>>
>> BEGIN (implicit)
>> 2
Re: [sqlalchemy] Controlling table dependency for flushing
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 ?
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
> relationships are viewonly since I have overlapping fks.
> >
> > For example I have a model Employee, which has fks: department_id,
> title_id, and fund_id. The related models are Department (fk
> department_id), Title (fk department_id and title_id) , Fund (fk fund_id)
> and FundTitle (fk department_id, title_id and fund_id). I have set
> FundTitle to viewonly. When updating / creating an Employee, I do create
> and add a new FundTitle to the session, however I don't assign it to the
> employee as the relationship is viewonly. If I don't flush before making
> the assignment, the final flush / commit attempts to update / create the
> employee before creating the FundTitle.
>
> let's work with source code that is runnable (e.g. MCVE). Below is
> the model that it seems you are describing, and then there's a
> demonstration of assembly of all those components using relationships,
> a single flush and it all goes in in the correct order, all FKs are
> nullable=False.
>
> from sqlalchemy import *
> from sqlalchemy.orm import *
> from sqlalchemy.ext.declarative import declarative_base
>
> Base = declarative_base()
>
>
> 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("Department")
> title = relationship("Title")
> fund = relationship("Fund")
>
>
> class Title(Base):
> __tablename__ = 'title'
> id = Column(Integer, primary_key=True)
> department_id = Column(ForeignKey('department.id'), nullable=False)
> department = relationship("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")
>
> e = create_engine("postgresql://scott:tiger@localhost/test", echo=True)
> Base.metadata.create_all(e)
>
> s = Session(e)
>
> d1 = Department()
> t1 = Title(department=d1)
> f1 = Fund(department=d1, title=t1)
> ft1 = FundTitle(title=t1, department=d1, fund=f1)
> e1 = Employee(title=t1, department=d1, fund=f1)
>
> s.add_all([d1, t1, f1, ft1, e1])
> s.commit()
>
>
> the INSERTs can be ordered naturally here and the unit of work will do
> that for you if you use relationship:
>
> BEGIN (implicit)
> 2018-10-08 10:18:38,750 INFO sqlalchemy.engine.base.Engine INSERT INTO
> department DEFAULT VALUES RETURNING department.id
> 2018-10-08 10:18:38,750 INFO sqlalchemy.engine.base.Engine {}
> 2018-10-08 10:18:38,753 INFO sqlalchemy.engine.base.Engine INSERT INTO
> title (department_id) VALUES (%(department_id)s) RETURNING title.id
> 2018-10-08 10:18:38,753 INFO sqlalchemy.engine.base.Engine
> {'department_id': 1}
> 2018-10-08 10:18:38,757 INFO sqlalchemy.engine.base.Engine INSERT INTO
> fund (title_id, department_id) VALUES (%(title_id)s,
> %(department_id)s) RETURNING fund.id
> 2018-10-08 10:18:38,757 INFO sqlalchemy.engine.base.Engine
Re: [sqlalchemy] Controlling table dependency for flushing
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 relationships are viewonly since I have overlapping fks. For example I have a model Employee, which has fks: department_id, title_id, and fund_id. The related models are Department (fk department_id), Title (fk department_id and title_id) , Fund (fk fund_id) and FundTitle (fk department_id, title_id and fund_id). I have set FundTitle to viewonly. When updating / creating an Employee, I do create and add a new FundTitle to the session, however I don't assign it to the employee as the relationship is viewonly. If I don't flush before making the assignment, the final flush / commit attempts to update / create the employee before creating the FundTitle. On Tuesday, September 18, 2018 at 9:02:30 AM UTC-4, Mike Bayer wrote: > > if there are no dependencies between two particular objects of > different classes, say A and B, then there is no deterministic > ordering between them. For objects of the same class, they are > inserted in the order in which they were added to the Session. > > the correct way to solve this problem in SQLAlchemy is to use > relationship() fully. I know you've stated that these objects have a > relationship() between them but you have to actually use it, that is: > > obj_a = A() > obj_b = B() > > obj_a.some_relationship = obj_b # will definitely flush correctly > unless there is a bug > > OTOH if you are only using foreign key attributes, the ORM does *not* > have any idea in how it should be flushing these: > > obj_a = A() > obj_b = B() > > obj_a.some_fk = obj_b.some_id# ORM doesn't care about this, no > ordering is implied > > > since you said you're not setting any IDs, I'm not sure how you could > be doing the above. > > > > > > > On Tue, Sep 18, 2018 at 5:53 AM Simon King > wrote: > > > > It's not something I've ever looked into, but I'm not aware of any > > debugging options here, no. You'd probably want to start by scattering > > print statements around the UOWTransaction class > > ( > https://bitbucket.org/zzzeek/sqlalchemy/src/c94d67892e68ac317d72eb202cca427084b3ca74/lib/sqlalchemy/orm/unitofwork.py?at=master=file-view-default#unitofwork.py-111) > > > > > > Looking at that code made me wonder whether you've set any particular > > cascade options on your relationship; I'm not sure if cascade options > > affect the dependency calculation. > > > > Simon > > > > On Tue, Sep 18, 2018 at 5:28 AM Alex Rothberg > wrote: > > > > > > In order to guide me in stripping down this code to produce an example > for positing, are there any options / flags / introspections I can turn on > to understand how sql makes decisions about the order in which is writes > statements to the DB? > > > > > > On Friday, September 14, 2018 at 10:13:45 AM UTC-4, Simon King wrote: > > >> > > >> In that case can you show us the code that is causing the problem? > > >> On Fri, Sep 14, 2018 at 2:55 PM Alex Rothberg > wrote: > > >> > > > >> > I am not generating any IDs myself and I already have relationships > between the models. > > >> > > > >> > On Friday, September 14, 2018 at 4:33:08 AM UTC-4, Simon King > wrote: > > >> >> > > >> >> On Thu, Sep 13, 2018 at 10:50 PM Alex Rothberg > wrote: > > >> >> > > > >> >> > Is it possible to hint at sqla the order in which it should > write out changes to the DB? > > >> >> > > > >> >> > I am having issues in which I add two new objects to a session, > a and b where a depends on b, but sqla is flushing a before b leading to an > fk issue. I can solve this a few ways: explicitly calling flush after > adding b, or changing the fk constraint to be initially deferred. Ideally I > would not have to do either of these. > > >> >> > > > >> >> > > >> >> If you have configured a relationship between the two classes > > >> >> ( > http://docs.sqlalchemy.org/en/latest/orm/tutorial.html#building-a-relationship), > > > > >> >> and you've linked the objects together using that relationship > (a.b = > > >> >> b), then SQLAlchemy will flush them in the correct order. If you > are > > >> >> generating your IDs in Python and assigning them to the primary > and > > >> >> foreign key columns directly, SQLAlchemy pr
Re: Migrating PEP-435 Enums
This seems to work / provide a good template of how to get that
info: https://github.com/dw/alembic-autogenerate-enums
On Monday, September 24, 2018 at 5:19:39 PM UTC-4, Alex Rothberg wrote:
>
> and is there an easy way to progrmatically get the name of the enum from
> the model field (given I declared it as as above)?
>
> On Monday, September 24, 2018 at 5:16:44 PM UTC-4, Mike Bayer wrote:
>>
>> you don't gain much since it only works on Postgresql anyway.Also,
>> the syntax you suggested wouldn't work, because Postgresql needs to
>> know the name of the enumeration.
>>
>> This is part of why all the "enum" issues for alembic are just open.
>> The way PG does it vs. MySQL are immensely different, and then none
>> of the other databases have an ENUM type.Your request for an
>> "op.alter_column()" directive is basically asking for those issues to
>> be done. I'm on a long term search for code contributors who can
>> work on that stuff, ENUM is going to be very hard to work front to
>> back in all cases.
>>
>>
>>
>>
>>
>>
>> On Mon, Sep 24, 2018 at 2:49 PM Alex Rothberg
>> wrote:
>> >
>> > is there no way to get this alter statement without writing raw sql?
>> > e.g. something like: op.alter_column("my_table", "my_column",
>> existing_type=ENUM(...), type_=ENUM()) ?
>> >
>> > On Monday, September 24, 2018 at 2:36:52 PM UTC-4, Mike Bayer wrote:
>> >>
>> >> Postgresql ENUMs are entirely different from any other database so it
>> >> matters a lot. For PG, you'd want to be doing op.execute("ALTER TYPE
>> >> myenum ..."), full syntax is at
>> >> https://www.postgresql.org/docs/9.1/static/sql-altertype.html
>> >> On Mon, Sep 24, 2018 at 12:45 PM Alex Rothberg
>> wrote:
>> >> >
>> >> > Assuming that I am using the PEP-435 enum feature in SQLA, e.g.:
>> >> > class InvitationReason(str, enum.Enum):
>> >> > ORIGINAL_ADMIN = "ORIGINAL_ADMIN"
>> >> > FIRM_USER = "FIRM_USER"
>> >> > ...
>> >> >
>> >> > reason = db.Column(db.Enum(InvitationReason), nullable=False)
>> >> >
>> >> > and I want to add / change the values in the enum. I know that
>> alembic won't auto generate the migration. Given that, what is the simplest
>> way to specify the migration by hand? I am using postgres, if that matters.
>> >> >
>> >> > --
>> >> > You received this message because you are subscribed to the Google
>> Groups "sqlalchemy-alembic" group.
>> >> > To unsubscribe from this group and stop receiving emails from it,
>> send an email to sqlalchemy-alembic+unsubscr...@googlegroups.com.
>> >> > For more options, visit https://groups.google.com/d/optout.
>> >
>> > --
>> > You received this message because you are subscribed to the Google
>> Groups "sqlalchemy-alembic" group.
>> > To unsubscribe from this group and stop receiving emails from it, send
>> an email to sqlalchemy-alembic+unsubscr...@googlegroups.com.
>> > For more options, visit https://groups.google.com/d/optout.
>>
>
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy-alembic" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy-alembic+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
Re: Migrating PEP-435 Enums
and is there an easy way to progrmatically get the name of the enum from
the model field (given I declared it as as above)?
On Monday, September 24, 2018 at 5:16:44 PM UTC-4, Mike Bayer wrote:
>
> you don't gain much since it only works on Postgresql anyway.Also,
> the syntax you suggested wouldn't work, because Postgresql needs to
> know the name of the enumeration.
>
> This is part of why all the "enum" issues for alembic are just open.
> The way PG does it vs. MySQL are immensely different, and then none
> of the other databases have an ENUM type.Your request for an
> "op.alter_column()" directive is basically asking for those issues to
> be done. I'm on a long term search for code contributors who can
> work on that stuff, ENUM is going to be very hard to work front to
> back in all cases.
>
>
>
>
>
>
> On Mon, Sep 24, 2018 at 2:49 PM Alex Rothberg > wrote:
> >
> > is there no way to get this alter statement without writing raw sql?
> > e.g. something like: op.alter_column("my_table", "my_column",
> existing_type=ENUM(...), type_=ENUM()) ?
> >
> > On Monday, September 24, 2018 at 2:36:52 PM UTC-4, Mike Bayer wrote:
> >>
> >> Postgresql ENUMs are entirely different from any other database so it
> >> matters a lot. For PG, you'd want to be doing op.execute("ALTER TYPE
> >> myenum ..."), full syntax is at
> >> https://www.postgresql.org/docs/9.1/static/sql-altertype.html
> >> On Mon, Sep 24, 2018 at 12:45 PM Alex Rothberg
> wrote:
> >> >
> >> > Assuming that I am using the PEP-435 enum feature in SQLA, e.g.:
> >> > class InvitationReason(str, enum.Enum):
> >> > ORIGINAL_ADMIN = "ORIGINAL_ADMIN"
> >> > FIRM_USER = "FIRM_USER"
> >> > ...
> >> >
> >> > reason = db.Column(db.Enum(InvitationReason), nullable=False)
> >> >
> >> > and I want to add / change the values in the enum. I know that
> alembic won't auto generate the migration. Given that, what is the simplest
> way to specify the migration by hand? I am using postgres, if that matters.
> >> >
> >> > --
> >> > You received this message because you are subscribed to the Google
> Groups "sqlalchemy-alembic" group.
> >> > To unsubscribe from this group and stop receiving emails from it,
> send an email to sqlalchemy-alembic+unsubscr...@googlegroups.com
> .
> >> > For more options, visit https://groups.google.com/d/optout.
> >
> > --
> > You received this message because you are subscribed to the Google
> Groups "sqlalchemy-alembic" group.
> > To unsubscribe from this group and stop receiving emails from it, send
> an email to sqlalchemy-alembic+unsubscr...@googlegroups.com .
>
> > For more options, visit https://groups.google.com/d/optout.
>
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy-alembic" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy-alembic+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
Migrating PEP-435 Enums
Assuming that I am using the PEP-435 enum feature in SQLA, e.g.: class InvitationReason(str, enum.Enum): ORIGINAL_ADMIN = "ORIGINAL_ADMIN" FIRM_USER = "FIRM_USER" ... reason = db.Column(db.Enum(InvitationReason), nullable=False) and I want to add / change the values in the enum. I know that alembic won't auto generate the migration. Given that, what is the simplest way to specify the migration by hand? I am using postgres, if that matters. -- You received this message because you are subscribed to the Google Groups "sqlalchemy-alembic" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy-alembic+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Setting join_depth on Query
Is there anyway to set the join_depth on the options, or do I just have to
write that myself with a for loop?
On Thursday, September 20, 2018 at 8:50:59 AM UTC-4, Mike Bayer wrote:
>
> On Wed, Sep 19, 2018 at 5:26 PM Alex Rothberg > wrote:
> >
> > Following up on
> https://groups.google.com/forum/#!searchin/sqlalchemy/join_depth%7Csort:date/sqlalchemy/WstKKbEFaRo/hL910npaBQAJ
>
> and
> https://stackoverflow.com/questions/4381712/how-do-you-dynamically-adjust-the-recursion-depth-for-eager-loading-in-the-sqlal,
>
> is there any way to set the join_depth on the query object rather than on
> the relationship?
> >
> > Right now I have:
> > class Geography(db.Model):
> > id = db.Column(UUID, default=uuid.uuid4, primary_key=True)
> > name = db.Column(db.String(), nullable=False, unique=True)
> > parent_geography_id = db.Column(UUID, db.ForeignKey(id))
> > children = db.relationship(
> > lambda: Geography,
> > lazy="joined",
> > join_depth=3,
> > backref=backref("parent", remote_side=[id]),
> > )
> >
> > however if I would like to customize the join_depth on the query.
> >
> > A related issue is that if I then take
> Geography.query.options(raiseload("*", sql_only=True)), the join_depth
> seems to be lost and I just get an exception. Also printing the query when
> the options is set shows that the join_depth is not used.
>
> a loader option supersedes join_depth entirely, because you are
> setting it directly, e.g.
>
> query.options(joinedload(Geography.parent).joinedload(Geography.parent))
>
>
> >
> > --
> > 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+...@googlegroups.com .
> > To post to this group, send email to sqlal...@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.
[sqlalchemy] Setting join_depth on Query
Following up
on
https://groups.google.com/forum/#!searchin/sqlalchemy/join_depth%7Csort:date/sqlalchemy/WstKKbEFaRo/hL910npaBQAJ
and
https://stackoverflow.com/questions/4381712/how-do-you-dynamically-adjust-the-recursion-depth-for-eager-loading-in-the-sqlal,
is there any way to set the join_depth on the query object rather than on
the relationship?
Right now I have:
class Geography(db.Model):
id = db.Column(UUID, default=uuid.uuid4, primary_key=True)
name = db.Column(db.String(), nullable=False, unique=True)
parent_geography_id = db.Column(UUID, db.ForeignKey(id))
children = db.relationship(
lambda: Geography,
lazy="joined",
join_depth=3,
backref=backref("parent", remote_side=[id]),
)
however if I would like to customize the join_depth on the query.
A related issue is that if I then take Geography.query.options(raiseload("*",
sql_only=True)), the join_depth seems to be lost and I just get an
exception. Also printing the query when the options is set shows that the
join_depth is not used.
--
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
In order to guide me in stripping down this code to produce an example for positing, are there any options / flags / introspections I can turn on to understand how sql makes decisions about the order in which is writes statements to the DB? On Friday, September 14, 2018 at 10:13:45 AM UTC-4, Simon King wrote: > > In that case can you show us the code that is causing the problem? > On Fri, Sep 14, 2018 at 2:55 PM Alex Rothberg > wrote: > > > > I am not generating any IDs myself and I already have relationships > between the models. > > > > On Friday, September 14, 2018 at 4:33:08 AM UTC-4, Simon King wrote: > >> > >> On Thu, Sep 13, 2018 at 10:50 PM Alex Rothberg > wrote: > >> > > >> > Is it possible to hint at sqla the order in which it should write out > changes to the DB? > >> > > >> > I am having issues in which I add two new objects to a session, a and > b where a depends on b, but sqla is flushing a before b leading to an fk > issue. I can solve this a few ways: explicitly calling flush after adding > b, or changing the fk constraint to be initially deferred. Ideally I would > not have to do either of these. > >> > > >> > >> If you have configured a relationship between the two classes > >> ( > http://docs.sqlalchemy.org/en/latest/orm/tutorial.html#building-a-relationship), > > > >> and you've linked the objects together using that relationship (a.b = > >> b), then SQLAlchemy will flush them in the correct order. If you are > >> generating your IDs in Python and assigning them to the primary and > >> foreign key columns directly, SQLAlchemy probably won't understand the > >> dependency. > >> > >> Does using a relationship fix your problem? > >> > >> Simon > > > > -- > > 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+...@googlegroups.com . > > To post to this group, send email to sqlal...@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
I am not generating any IDs myself and I already have relationships between the models. On Friday, September 14, 2018 at 4:33:08 AM UTC-4, Simon King wrote: > > On Thu, Sep 13, 2018 at 10:50 PM Alex Rothberg > wrote: > > > > Is it possible to hint at sqla the order in which it should write out > changes to the DB? > > > > I am having issues in which I add two new objects to a session, a and b > where a depends on b, but sqla is flushing a before b leading to an fk > issue. I can solve this a few ways: explicitly calling flush after adding > b, or changing the fk constraint to be initially deferred. Ideally I would > not have to do either of these. > > > > If you have configured a relationship between the two classes > ( > http://docs.sqlalchemy.org/en/latest/orm/tutorial.html#building-a-relationship), > > > and you've linked the objects together using that relationship (a.b = > b), then SQLAlchemy will flush them in the correct order. If you are > generating your IDs in Python and assigning them to the primary and > foreign key columns directly, SQLAlchemy probably won't understand the > dependency. > > Does using a relationship fix your problem? > > Simon > -- 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] Controlling table dependency for flushing
Is it possible to hint at sqla the order in which it should write out changes to the DB? I am having issues in which I add two new objects to a session, a and b where a depends on b, but sqla is flushing a before b leading to an fk issue. I can solve this a few ways: explicitly calling flush after adding b, or changing the fk constraint to be initially deferred. Ideally I would not have to do either of these. -- 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] ForeignKeyConstraint using Forward Declared Model
I tracked down the error on my side. Looks like I have to use the table
name rather than the model name (doh) in the string. That being said, there
may still be a bug in sqla where it tries to read the name off a join
(rather than a table).
That being said, any reason not to support the lambda syntax for
ForeignKeyConstraint rather than just the string syntax?
On Tuesday, September 4, 2018 at 10:43:13 PM UTC-4, Alex Rothberg wrote:
>
> You're right the error I posted is coming from somewhere else. I am trying
> to get a stripped down example. In the meantime, it looks like when I add
> the additional fk constraint, model.__mapper__.get_property(property_name)
> on a different model starts failing.
>
> File
> "/Users/alex/.pyenv/versions/api2/lib/python3.7/site-packages/marshmallow_sqlalchemy/convert.py"
> , line 151, in field_for
> prop = model.__mapper__.get_property(property_name)
> File
> "/Users/alex/.pyenv/versions/api2/lib/python3.7/site-packages/sqlalchemy/orm/mapper.py"
> , line 1923, in get_property
> configure_mappers()
> File
> "/Users/alex/.pyenv/versions/api2/lib/python3.7/site-packages/sqlalchemy/orm/mapper.py"
> , line 3033, in configure_mappers
> mapper._post_configure_properties()
> File
> "/Users/alex/.pyenv/versions/api2/lib/python3.7/site-packages/sqlalchemy/orm/mapper.py"
> , line 1832, in _post_configure_properties
> prop.init()
> File
> "/Users/alex/.pyenv/versions/api2/lib/python3.7/site-packages/sqlalchemy/orm/interfaces.py"
> , line 183, in init
> self.do_init()
> File
> "/Users/alex/.pyenv/versions/api2/lib/python3.7/site-packages/sqlalchemy/orm/relationships.py"
> , line 1656, in do_init
> self._setup_join_conditions()
> File
> "/Users/alex/.pyenv/versions/api2/lib/python3.7/site-packages/sqlalchemy/orm/relationships.py"
> , line 1731, in _setup_join_conditions
> can_be_synced_fn=self._columns_are_mapped
> File
> "/Users/alex/.pyenv/versions/api2/lib/python3.7/site-packages/sqlalchemy/orm/relationships.py"
> , line 1998, in __init__
> self._determine_joins()
> File
> "/Users/alex/.pyenv/versions/api2/lib/python3.7/site-packages/sqlalchemy/orm/relationships.py"
> , line 2082, in _determine_joins
> consider_as_foreign_keys=consider_as_foreign_keys
> File "", line 2, in join_condition
> File
> "/Users/alex/.pyenv/versions/api2/lib/python3.7/site-packages/sqlalchemy/sql/selectable.py"
> , line 964, in _join_condition
> a, a_subset, b, consider_as_foreign_keys)
> File
> "/Users/alex/.pyenv/versions/api2/lib/python3.7/site-packages/sqlalchemy/sql/selectable.py"
> , line 1021, in _joincond_scan_left_right
> if nrte.table_name == b.name:
> AttributeError: 'Join' object has no attribute 'name'
>
>
> On Tuesday, September 4, 2018 at 9:40:11 PM UTC-4, Mike Bayer wrote:
>>
>> On Tue, Sep 4, 2018 at 7:54 PM, Alex Rothberg
>> wrote:
>> > Is it possible to set up a `ForeignKeyConstraint` that uses a class not
>> yet
>> > declared? ie is there a way to use either the lambda or string syntax
>> to
>> > forward declare the fk constrains? Neither works for me. Using strings
>> > yields:
>> >
>> > File "", line 2, in join_condition
>> > File
>> >
>> "/Users/alex/.pyenv/versions/api2/lib/python3.7/site-packages/sqlalchemy/sql/selectable.py",
>>
>>
>> > line 964, in _join_condition
>> > a, a_subset, b, consider_as_foreign_keys)
>> > File
>> >
>> "/Users/alex/.pyenv/versions/api2/lib/python3.7/site-packages/sqlalchemy/sql/selectable.py",
>>
>>
>> > line 1021, in _joincond_scan_left_right
>> > if nrte.table_name == b.name:
>> > AttributeError: 'Join' object has no attribute 'name'
>> >
>> > and I can't get the lambda form to work.
>> > I tried:
>> > db.ForeignKeyConstraint((employee_id, year, home_fund_id),
>> > ('FundEmployee.employee_id', 'FundEmployee.year',
>> 'FundEmployee.fund_id'))
>>
>> ForeignKeyConstraint can be fully declared with just strings and the
>> referenced table and/or declarative class doesn't need to exist yet,
>> see
>> http://docs.sqlalchemy.org/en/latest/core/constraints.html#metadata-foreignkeys.
>>
>>
>>That AttributeError doesn't seem to be raised by a
>> ForeignKeyConstraint, looks like it's coming from orm.relatiionship or
>> something. Feel free to provide a more complete example of what
>> you're
Re: [sqlalchemy] ForeignKeyConstraint using Forward Declared Model
You're right the error I posted is coming from somewhere else. I am trying
to get a stripped down example. In the meantime, it looks like when I add
the additional fk constraint, model.__mapper__.get_property(property_name)
on a different model starts failing.
File
"/Users/alex/.pyenv/versions/api2/lib/python3.7/site-packages/marshmallow_sqlalchemy/convert.py"
, line 151, in field_for
prop = model.__mapper__.get_property(property_name)
File
"/Users/alex/.pyenv/versions/api2/lib/python3.7/site-packages/sqlalchemy/orm/mapper.py"
, line 1923, in get_property
configure_mappers()
File
"/Users/alex/.pyenv/versions/api2/lib/python3.7/site-packages/sqlalchemy/orm/mapper.py"
, line 3033, in configure_mappers
mapper._post_configure_properties()
File
"/Users/alex/.pyenv/versions/api2/lib/python3.7/site-packages/sqlalchemy/orm/mapper.py"
, line 1832, in _post_configure_properties
prop.init()
File
"/Users/alex/.pyenv/versions/api2/lib/python3.7/site-packages/sqlalchemy/orm/interfaces.py"
, line 183, in init
self.do_init()
File
"/Users/alex/.pyenv/versions/api2/lib/python3.7/site-packages/sqlalchemy/orm/relationships.py"
, line 1656, in do_init
self._setup_join_conditions()
File
"/Users/alex/.pyenv/versions/api2/lib/python3.7/site-packages/sqlalchemy/orm/relationships.py"
, line 1731, in _setup_join_conditions
can_be_synced_fn=self._columns_are_mapped
File
"/Users/alex/.pyenv/versions/api2/lib/python3.7/site-packages/sqlalchemy/orm/relationships.py"
, line 1998, in __init__
self._determine_joins()
File
"/Users/alex/.pyenv/versions/api2/lib/python3.7/site-packages/sqlalchemy/orm/relationships.py"
, line 2082, in _determine_joins
consider_as_foreign_keys=consider_as_foreign_keys
File "", line 2, in join_condition
File
"/Users/alex/.pyenv/versions/api2/lib/python3.7/site-packages/sqlalchemy/sql/selectable.py"
, line 964, in _join_condition
a, a_subset, b, consider_as_foreign_keys)
File
"/Users/alex/.pyenv/versions/api2/lib/python3.7/site-packages/sqlalchemy/sql/selectable.py"
, line 1021, in _joincond_scan_left_right
if nrte.table_name == b.name:
AttributeError: 'Join' object has no attribute 'name'
On Tuesday, September 4, 2018 at 9:40:11 PM UTC-4, Mike Bayer wrote:
>
> On Tue, Sep 4, 2018 at 7:54 PM, Alex Rothberg > wrote:
> > Is it possible to set up a `ForeignKeyConstraint` that uses a class not
> yet
> > declared? ie is there a way to use either the lambda or string syntax to
> > forward declare the fk constrains? Neither works for me. Using strings
> > yields:
> >
> > File "", line 2, in join_condition
> > File
> >
> "/Users/alex/.pyenv/versions/api2/lib/python3.7/site-packages/sqlalchemy/sql/selectable.py",
>
>
> > line 964, in _join_condition
> > a, a_subset, b, consider_as_foreign_keys)
> > File
> >
> "/Users/alex/.pyenv/versions/api2/lib/python3.7/site-packages/sqlalchemy/sql/selectable.py",
>
>
> > line 1021, in _joincond_scan_left_right
> > if nrte.table_name == b.name:
> > AttributeError: 'Join' object has no attribute 'name'
> >
> > and I can't get the lambda form to work.
> > I tried:
> > db.ForeignKeyConstraint((employee_id, year, home_fund_id),
> > ('FundEmployee.employee_id', 'FundEmployee.year',
> 'FundEmployee.fund_id'))
>
> ForeignKeyConstraint can be fully declared with just strings and the
> referenced table and/or declarative class doesn't need to exist yet,
> see
> http://docs.sqlalchemy.org/en/latest/core/constraints.html#metadata-foreignkeys.
>
>
>That AttributeError doesn't seem to be raised by a
> ForeignKeyConstraint, looks like it's coming from orm.relatiionship or
> something. Feel free to provide a more complete example of what
> you're trying to do.
>
>
> >
> >
> > --
> > 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+...@googlegroups.com .
> > To post to this group, send email to sqlal...@googlegroups.com
> .
> > Visit this group at https://groups.google.com/group/sqlalchemy.
> > For more options, visit https://groups.google.
[sqlalchemy] ForeignKeyConstraint using Forward Declared Model
Is it possible to set up a `ForeignKeyConstraint` that uses a class not yet declared? ie is there a way to use either the lambda or string syntax to forward declare the fk constrains? Neither works for me. Using strings yields: File "", line 2, in join_condition File "/Users/alex/.pyenv/versions/api2/lib/python3.7/site-packages/sqlalchemy/sql/selectable.py" , line 964, in _join_condition a, a_subset, b, consider_as_foreign_keys) File "/Users/alex/.pyenv/versions/api2/lib/python3.7/site-packages/sqlalchemy/sql/selectable.py" , line 1021, in _joincond_scan_left_right if nrte.table_name == b.name: AttributeError: 'Join' object has no attribute 'name' and I can't get the lambda form to work. I tried: db.ForeignKeyConstraint((employee_id, year, home_fund_id), ( 'FundEmployee.employee_id', 'FundEmployee.year', 'FundEmployee.fund_id')) -- 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] Setting many to many collection with secondary relationship when having only pk to one of the models.
I didn't mean to confuse this question by showing both formats of the many-to-many relationship (using secondary and not); I am aware that using both can lead to problems / inconsistencies. I just wanted to show that I had both options available at my disposal. Is there anyway to use the secondary relationship (rather than the association one) with some combination of cascading options to get what I want where I can create and use a Geography knowing only its pk without the ORM trying to then save it to the DB? On Thursday, August 23, 2018 at 1:02:40 PM UTC-4, Mike Bayer wrote: > > On Wed, Aug 22, 2018 at 5:41 PM, Alex Rothberg > wrote: > > I am using an association model / table to represent a many to many > > relationship: > > > > class Geography(db.Model): > > > > id = > > ... > > > > class Fund(db.Model): > > id = > > ... > > geography_associations = db.relationship( > > lambda: FundGeographyAssociation, > > back_populates="fund", > > cascade='save-update, merge, delete, delete-orphan' > > ) > > > > geographies = db.relationship( > > Geography, > > backref="fund", > > secondary=lambda: FundGeographyAssociation.__table__, > > ) > > > > class FundGeographyAssociation(db.Model): > > fund_id = db.Column( > > UUID, db.ForeignKey(Fund.id), primary_key=True, > > ) > > geography_id = db.Column( > > UUID, db.ForeignKey(Geography.id), primary_key=True, > > ) > > > > fund = db.relationship(Fund, > back_populates='geography_associations') > > > > > > and then am attempting to update the list of geographies for a Fund > using: > >fund.geographies = [] > > > > > > my issue is what to put in ??? when I only have the pk of the geography > > model. > > it is not a recommended pattern to re-purpose a mapped association > class as a "secondary" elsewhere. The ORM does not know that > Fund.geography_associations and Fund.geographies refer to the same > table and mutations to each of these independently will conflict (see > > http://docs.sqlalchemy.org/en/latest/orm/basic_relationships.html#association-object) > > > . The usual pattern is to use an association proxy for > Fund.geographies (see > > http://docs.sqlalchemy.org/en/latest/orm/extensions/associationproxy.html#simplifying-association-objects). > > > > If you want to add a row having only the id of Geography, the most > straightforward approach is to append the association object directly: > > fund.geography_associations = [FundGeoAssoc(geo_id=1)] > > > > > > this works: Geography.query.get(id) however this does not: > Geography(id=id) > > as the latter tries to create a new Geography object leading to > conflicts. > > The former seems "silly" as it requires an extra query to db to load the > > object even though all i need is the geography id to create the > association > > object. I tried variation of session.merge with load=False however that > > doesn't work as the object is transient. > > > > -- > > 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+...@googlegroups.com . > > To post to this group, send email to sqlal...@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.
[sqlalchemy] Setting many to many collection with secondary relationship when having only pk to one of the models.
I am using an association model / table to represent a many to many relationship: class Geography(db.Model): id = ... class Fund(db.Model): id = ... geography_associations = db.relationship( lambda: FundGeographyAssociation, back_populates="fund", cascade='save-update, merge, delete, delete-orphan' ) geographies = db.relationship( Geography, backref="fund", secondary=lambda: FundGeographyAssociation.__table__, ) class FundGeographyAssociation(db.Model): fund_id = db.Column( UUID, db.ForeignKey(Fund.id), primary_key=True, ) geography_id = db.Column( UUID, db.ForeignKey(Geography.id), primary_key=True, ) fund = db.relationship(Fund, back_populates='geography_associations') and then am attempting to update the list of geographies for a Fund using: fund.geographies = [] my issue is what to put in ??? when I only have the pk of the geography model. this works: Geography.query.get(id) however this does not: Geography(id=id) as the latter tries to create a new Geography object leading to conflicts. The former seems "silly" as it requires an extra query to db to load the object even though all i need is the geography id to create the association object. I tried variation of session.merge with load=False however that doesn't work as the object is transient. -- 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] Remote side backpopulates
Is there any way to declare a "remote side" backpopulates? i.e. where I declare a relationship on class A to appear only on class B? I would like the relationship only to be available on the remote class but I do not want to / cannot modify the code for the remote class. For example: class User(Model): ... class Permission(Model): user_active = relationship(User, backpopulates_remote='permissions') where the user_active field does not get created on Permission but the permissions field does get created on User? This comes up since I end up writing: class Permission(Model): user = relationship(User) user_active = relationship(User, backref='permissions', primary_join=... is_active...) and I do want the user field on Permission and the permissions field on User, but I do not want the user_active, as it is extraneous. -- 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.
