On Sun, Feb 11, 2018 at 11:15 AM, Antoine Lizée <antoine.li...@gmail.com> wrote:
>
> Here is the short example. On the last line, a query is issued to retrieve
> the object, but the pending DELETE operation is not triggered by the
> autoflush, resulting in an existing object instead of `None`. Can somebody
> explain why?
>
> us = Country(name="USA")
> session.add(us)
> session.commit()
>
> session.delete(us)
> self.assertIsNotNone(session.query(Country).get(us.id))  # "echo=True" shows
> a SELECT query, but not the pending DELETE

the SELECT is emitted by saying "us.id" - after the commit(), the "us"
object is expired due to expire_on_commit=True.   So in order to get
the "id" attribute it runs the SELECT to get the row back.  The
"unexpire attributes" query that runs per instance does not
participate in autoflush.    The query.get() in the example will never
do autoflush as long as the "us" object is in the Session and in scope
because it does not emit any SQL, it is pulling the object from the
local identity map.     query.get() has an emphasis on not emitting
SQL if it doesn't have to - if you want to definitely emit SQL then do
a normal filter() + one(), or call flush() before calling get().

the document at
http://docs.sqlalchemy.org/en/latest/orm/query.html?highlight=query%20get#sqlalchemy.orm.query.Query.get
could use a note here as this is not mentioned.

> self.assertIsNone(session.query(Country).get(us.id + 1))  # Executes the
> pending DELETE as part of autoflush
> self.assertIsNone(session.query(Country).get(us.id))  # executes another
> SELECT -> gives None.
>
> Having the  order of the queries make results different seems like a
> non-desirable behavior?
>
> Interestingly, if the first query is *also* done before the `delete()`, we
> get to the same result, because the query right after the `delete()` does
> not execute any query.
>
> us = Country(name="USA")
> session.add(us)
> session.commit()
>
> self.assertIsNotNone(session.query(Country).get(us.id))  # "echo=True" shows
> a SELECT query, normal
> session.delete(us)
> self.assertIsNotNone(session.query(Country).get(us.id))  # "echo=True" shows
> nothing!
> self.assertIsNone(session.query(Country).get(us.id + 1))  # Executes the
> pending DELETE as part of autoflush
> self.assertIsNone(session.query(Country).get(us.id))  # executes another
> SELECT -> gives None.
>
> ---
>
> Full example code is below:
>
> import unittest
>
>
> from sqlalchemy import create_engine, Column, Integer, String, ForeignKey
> from sqlalchemy.ext.declarative import declarative_base
> from sqlalchemy.orm import relationship, backref, sessionmaker
>
>
> engine = create_engine('sqlite:///:memory:', echo=True)
> Base = declarative_base()
> Session = sessionmaker(bind=engine, autoflush=True)
> session = Session()
>
>
>
>
> class Country(Base):
>     __tablename__ = 'countries'
>
>
>     id = Column(Integer(), primary_key=True)
>     name = Column(String(255))
>
>
>     def __repr__(self):
>         return '<Country {} [{}]>'.format(self.name, self.id)
>
>
>
> class Capital(Base):
>     __tablename__ = 'capitals'
>
>
>     id = Column(Integer(), primary_key=True)
>     name = Column(String(255))
>     country_id = Column(Integer(), ForeignKey(Country.id), unique=True)
>     country = relationship('Country', backref=backref('capital',
> uselist=False))
>
>
>     def __repr__(self):
>         return '<Capital {} [{}]>'.format(self.name, self.id)
>
>
>
>
> class President(Base):
>     __tablename__ = 'presidents'
>
>
>     id = Column(Integer(), primary_key=True)
>     name = Column(String(255))
>     country_id = Column(Integer(), ForeignKey(Country.id), unique=True)
>     country = relationship('Country', backref=backref('presidents'))
>
>
>     def __repr__(self):
>         return '<President {} [{}]>'.format(self.name, self.id)
>
>
> Base.metadata.create_all(engine)
>
>
> class TestSessionBasics(unittest.TestCase):
>
>
>     def setUp(self):
>         Base.metadata.create_all(engine)
>
>
>     def tearDown(self):
>         session.close()
>         Base.metadata.drop_all(engine)
>
>
>     def test_stuff(self):
>         us = Country(name="USA")
>         session.add(us)
>         session.commit()
>
>
>         print("Autoflush: ", session.autoflush)
>         print("\n\nDeleting\n")
>         session.delete(us)
>         print("\n\nChecking\n")
>         self.assertIsNotNone(session.query(Country).get(us.id))
>         print("\n\nFlushing\n")
>         session.flush()
>         print("\n\nChecking\n")
>         self.assertIsNone(session.query(Country).get(us.id))
>         print("\n\nComiting\n")
>         session.commit()
>         print("\n\nChecking\n")
>         self.assertIsNone(session.query(Country).get(us.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.

-- 
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.

Reply via email to