On Feb 11, 2011, at 8:50 AM, Will wrote:
> """
> Hello,
>
> I was curious about a default relationship behavior. It seems that
> the
> default behavior when deleting a child in a relationship is to query
> the parent first. I'm not sure why. This hampers something I'm
> working on because I want to only give privileges to a program to only
> what it will need to do its work.
Why map to a table if the application can't even SELECT from it ? seems a
little strange.
> I have a table that has two
> parents,
> that is a foreign key to two tables. One of those tables isn't needed
> by the application, but it needed for other applications that use
> these
> same SQLAlchemy models.
>
> The following example shows first a default parent/child relationship
> with the resulting query calls. The second example shows an example
> using passive_deletes=True in the relationship from child to parent,
> which I understand doesn't make too much sense but causes my desired
> behavior. This, also, has the resulting query calls.
>
> Is there a better way for me to get the behavior I desire, or is this
> a
> bug?
Its a bug. Many-to-ones are generally cheap since they're usually already
loaded, but in this case the load isn't needed and this is actually a recent
regression as of 0.6.6, so 0.6.5 won't exhibit this behavior...of course 0.6.5
has the previous issue that was fixed here but its likely not as common. This
is ticket #2049 and a fix will be available shortly.
Also you might want to consider linking the "parent" and "children"
relationships via the "back_populates" attribute, or otherwise map them at once
using relationship + backref. Otherwise the ORM treats mutations in each
attribute as separate which could lead to doubling of operations. If you're
only mutating one side (or none) then it doesn't matter much.
>
> Thank you for any insight.
>
> """
>
> from sqlalchemy import create_engine
> from sqlalchemy.ext.declarative import declarative_base
> from sqlalchemy.orm import sessionmaker, relationship
> from sqlalchemy.schema import Column, ForeignKey
> from sqlalchemy.types import Integer
>
>
> engine = create_engine('sqlite:///')
>
>
> ################ Without passive_deletes=True ###############
> Base = declarative_base()
>
>
> class Parent(Base):
> __tablename__ = 'parents'
> id = Column(Integer, primary_key=True)
> children = relationship('Child', lazy='dynamic')
>
>
> class Child(Base):
> __tablename__ = 'children'
> id = Column(Integer, primary_key=True)
> parent_id = Column(Integer, ForeignKey(Parent.id), nullable=False)
> parent = relationship('Parent')
>
>
> Base.metadata.create_all(engine)
>
> session = sessionmaker(bind=engine, autocommit=True)()
>
> parent = Parent()
> child = Child(parent=parent)
> session.add(parent)
> session.add(child)
> session.flush()
>
>
> session.expunge_all()
>
>
> engine.echo = True
> session.delete(session.query(Child).one())
> session.flush()
> engine.echo = False
>
> Base.metadata.drop_all(engine)
>
>
> # Ouput
> """
> 2011-02-11 08:16:39,242 INFO sqlalchemy.engine.base.Engine.0x...cd90
> SELECT children.id AS children_id, children.parent_id AS
> children_parent_id
> FROM children
> 2011-02-11 08:16:39,243 INFO sqlalchemy.engine.base.Engine.0x...cd90
> ()
> 2011-02-11 08:16:39,243 INFO sqlalchemy.engine.base.Engine.0x...cd90
> BEGIN (implicit)
> 2011-02-11 08:16:39,244 INFO sqlalchemy.engine.base.Engine.0x...cd90
> SELECT parents.id AS parents_id
> FROM parents
> WHERE parents.id = ?
> 2011-02-11 08:16:39,244 INFO sqlalchemy.engine.base.Engine.0x...cd90
> (1,)
> 2011-02-11 08:16:39,244 INFO sqlalchemy.engine.base.Engine.0x...cd90
> DELETE FROM children WHERE children.id = ?
> 2011-02-11 08:16:39,244 INFO sqlalchemy.engine.base.Engine.0x...cd90
> (1,)
> 2011-02-11 08:16:39,245 INFO sqlalchemy.engine.base.Engine.0x...cd90
> COMMIT
> """
>
>
> ################ With passive_deletes=True ###############
> Base = declarative_base()
>
>
> class Parent(Base):
> __tablename__ = 'parents'
> id = Column(Integer, primary_key=True)
> children = relationship('Child', lazy='dynamic')
>
>
> class Child(Base):
> __tablename__ = 'children'
> id = Column(Integer, primary_key=True)
> parent_id = Column(Integer, ForeignKey(Parent.id), nullable=False)
> parent = relationship('Parent', passive_deletes=True)
>
>
> Base.metadata.create_all(engine)
>
> session = sessionmaker(bind=engine, autocommit=True)()
>
> parent = Parent()
> child = Child(parent=parent)
> session.add(parent)
> session.add(child)
> session.flush()
>
>
> session.expunge_all()
>
>
> engine.echo = True
> session.delete(session.query(Child).one())
> session.flush()
> engine.echo = False
>
>
> # Output
> """
> /usr/lib/pymodules/python2.6/sqlalchemy/orm/properties.py:897:
> SAWarning: On Child.parent, 'passive_deletes' is normally configured
> on one-to-many, one-to-one, many-to-many relationships only.
> self._determine_direction()
> 2011-02-11 08:16:39,252 INFO sqlalchemy.engine.base.Engine.0x...cd90
> SELECT children.id AS children_id, children.parent_id AS
> children_parent_id
> FROM children
> 2011-02-11 08:16:39,252 INFO sqlalchemy.engine.base.Engine.0x...cd90
> ()
> 2011-02-11 08:16:39,253 INFO sqlalchemy.engine.base.Engine.0x...cd90
> BEGIN (implicit)
> 2011-02-11 08:16:39,253 INFO sqlalchemy.engine.base.Engine.0x...cd90
> DELETE FROM children WHERE children.id = ?
> 2011-02-11 08:16:39,253 INFO sqlalchemy.engine.base.Engine.0x...cd90
> (1,)
> 2011-02-11 08:16:39,253 INFO sqlalchemy.engine.base.Engine.0x...cd90
> COMMIT
> """
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To post to this group, send email to [email protected].
> To unsubscribe from this group, send email to
> [email protected].
> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en.
>
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
