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 <mik...@zzzcomputing.com
> <javascript:>> wrote:
> >
> > On Sat, Dec 1, 2018 at 9:21 PM Alex Rothberg <agrot...@gmail.com
> <javascript:>> 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("xxxx")
> > > e.records.clear()
> > >
> > > and I see:
> > >
> > > xxxx 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 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 <javascript:>.
> > > To post to this group, send email to sqlal...@googlegroups.com
> <javascript:>.
> > > 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.
