Re: [sqlalchemy] testing that a session is committed correctly

[Mike Bayer]

On Wed, Jun 5, 2019, at 2:30 PM, Chris Withers wrote:
> On 05/06/2019 17:15, Mike Bayer wrote:
> > 
> >> How come close() doesn't rollback the SessionTransaction if it throws it
> >> away?
> > 
> > that's currently what .close() does, it discards the connection. this 
> > is safe because the connection pool ensures transactions are rolled 
> > back. This might have to change with some of the 2.0 things I'm 
> > thinking about but it's not clear yet.
> > 
> > Anyway, you don't get a rollback here because the session is bound to an 
> > external connection so the connection pool is not involved. 
> 
> Ah, right, so under normal conditions, if I just close a session, any 
> transaction and subtransactions in flight will be rolled back when the 
> connection is returned to the pool?
> 
> 
> > if you 
> > want to roll back the work that the application did, that would be your 
> > sub_transaction.rollback():
> > 
> > sub_transaction = conn.begin_nested()
> > try:
> > 
> > session = Session()
> > 
> > # code under test:
> > event = Event(text='some stuff got done')
> > session.add(event)
> > session.flush()
> > session.close()
> > 
> > finally:
> > sub_transaction.rollback()
> > assert session.query(Event).count() == 0
> 
> Sure, but the test then fails when the code is correct:
> 
> try:
>  Base.metadata.create_all(bind=conn, checkfirst=False)
>  Session.configure(bind=conn)
> 
>  sub_transaction = conn.begin_nested()
>  try:
> 
>  session = Session()
> 
>  # code under test:
>  event = Event(text='some stuff got done')
>  session.add(event)
>  session.flush()
>  session.commit()
>  session.close()
> 
>  # test:
>  sub_transaction.rollback()
> 
>  assert session.query(Event).count() == 1
> 
>  finally:
>  sub_transaction.rollback()
> 
> finally:
>  transaction.rollback()
> 
> The panacea I'm after is to be able to run the DDL in a transaction, run 
> each test in a subtransaction off that which is rolled back at the end 
> of each test, but also be able to check that the code under test is 
> doing session.commit() where it should. Where the pattern we're 
> discussing, I certainly have the first two, but as you can see from what 
> I've just pasted above, the third one isn't there, but is it possible?


so your app code calls .flush(), .commit(), and .close() explicitly within each 
persistence method? and you need to make sure the .commit() is in the middle? 
I'd probably use mock.patch for that level of granularity. Usually I'd not have 
specific business methods calling commit() and close() at all, at the very 
least not .close().



> 
> > the test harness is giving you two choices. you can look at the state 
> > of the DB after your program has done some things and *before* your 
> > harness has reversed its work, or you can look at the state of the DB 
> > *after* your harness has reversed its work. 
> 
> Unless I'm missing something, neither of these let the test confirm that 
> the code under test has called commit() when it should.
> 
> cheers,
> 
> Chris
> 
> -- 
> 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.
> To view this discussion on the web visit 
> https://groups.google.com/d/msgid/sqlalchemy/d7f7c9ac-2d32-6e60-efac-f86b501683f7%40withers.org.
> 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.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/2936fbda-b7bb-49e3-ae2c-8e6141cdb56f%40www.fastmail.com.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] testing that a session is committed correctly

[Chris Withers]

On 05/06/2019 17:15, Mike Bayer wrote:



How come close() doesn't rollback the SessionTransaction if it throws it
away?


that's currently what .close() does, it discards the connection.   this 
is safe because the connection pool ensures transactions are rolled 
back.   This might have to change with some of the 2.0 things I'm 
thinking about but it's not clear yet.


Anyway, you don't get a rollback here because the session is bound to an 
external connection so the connection pool is not involved.   


Ah, right, so under normal conditions, if I just close a session, any 
transaction and subtransactions in flight will be rolled back when the 
connection is returned to the pool?



if you 
want to roll back the work that the application did, that would be your 
sub_transaction.rollback():


     sub_transaction = conn.begin_nested()
     try:

     session = Session()

     # code under test:
     event = Event(text='some stuff got done')
     session.add(event)
     session.flush()
     session.close()

     finally:
     sub_transaction.rollback()
     assert session.query(Event).count() == 0


Sure, but the test then fails when the code is correct:

try:
Base.metadata.create_all(bind=conn, checkfirst=False)
Session.configure(bind=conn)

sub_transaction = conn.begin_nested()
try:

session = Session()

# code under test:
event = Event(text='some stuff got done')
session.add(event)
session.flush()
session.commit()
session.close()

# test:
sub_transaction.rollback()

assert session.query(Event).count() == 1

finally:
sub_transaction.rollback()

finally:
transaction.rollback()

The panacea I'm after is to be able to run the DDL in a transaction, run 
each test in a subtransaction off that which is rolled back at the end 
of each test, but also be able to check that the code under test is 
doing session.commit() where it should. Where the pattern we're 
discussing, I certainly have the first two, but as you can see from what 
I've just pasted above, the third one isn't there, but is it possible?


the test harness is giving you two choices.  you can look at the state 
of the DB after your program has done some things and *before* your 
harness has reversed its work, or you can look at the state of the DB 
*after* your harness has reversed its work. 


Unless I'm missing something, neither of these let the test confirm that 
the code under test has called commit() when it should.


cheers,

Chris

--
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.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/d7f7c9ac-2d32-6e60-efac-f86b501683f7%40withers.org.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Mixins and lazy (query) attributes

[Mike Bayer]

On Wed, Jun 5, 2019, at 12:02 PM, Christian Barra wrote:
> Hi, I am trying to understand what the best approach is to have lazy 
> attributes defined on a mixin and then used them on the subclasses.
> 
> Ideally I'd would like to defer the load of that attribute (controller) and 
> use `undefer` when I know that that attribute is needed, to execute only one 
> query.
> 
> Below there's the code I am trying to use, `recipe_set` is a backref, same 
> for `unit`, Controller is another model.
> 
> 
> class BaseRecipeMixin(APIResourceMixin):
>  _key = None
> 
> @declared_attr
> def recipe_set_id(self):
> return db.Column(db.ForeignKey('recipe_set.id'), unique=True, nullable=False)
> 
> @property
> def controller(self):
> return column_property(
> Controller.query.filter(
> Controller.unit == self.recipe_set.unit,
> Controller.is_master.is_(True),
> Controller.is_deleted.is_(False),
> ).one_or_none(),
>  deferred=True,
> )
> 
> 
> class EcRecipe(DosingRecipeMixin, BaseRecipeMixin, UpdateMixin, db.Model):
>  id = db.Column(db.Integer, primary_key=True)
>  pump_time = db.Column(db.Float, nullable=False, default=8)
> 
> 
> But when I try to run this


when you declare mapped attributes on a mixin you have to use @declared_attr:

https://docs.sqlalchemy.org/en/13/orm/extensions/declarative/mixins.html#mixing-in-deferred-column-property-and-other-mapperproperty-classes

The column_property() object needs to be made part of the mapping and 
declarative uses this decorator to know it has to do that.





> 
> 
> models.EcRecipe.query.first().controller
> 
> 
> I get this error
> 
> Traceback (most recent call last):
> File 
> "/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/code.py", 
> line 90, in runcode
> exec(code, self.locals)
> File "", line 1, in 
> File "/Users/cbarra/Projects/kompost/recipes/models.py", line 262, in 
> controller
>  deferred=True,
> File "", line 2, in column_property
> File "", line 2, in __init__
> File 
> "/Users/cbarra/Projects/.venv/lib/python3.7/site-packages/sqlalchemy/util/deprecations.py",
>  line 130, in warned
> return fn(*args, **kwargs)
> File 
> "/Users/cbarra/Projects/.venv/lib/python3.7/site-packages/sqlalchemy/orm/properties.py",
>  line 134, in __init__
> self._orig_columns = [expression._labeled(c) for c in columns]
> File 
> "/Users/cbarra/Projects/.venv/lib/python3.7/site-packages/sqlalchemy/orm/properties.py",
>  line 134, in 
> self._orig_columns = [expression._labeled(c) for c in columns]
> File "", line 1, in 
> File 
> "/Users/cbarra/Projects/.venv/lib/python3.7/site-packages/sqlalchemy/sql/elements.py",
>  line 4373, in _labeled
> return element.label(None)
> AttributeError: 'NoneType' object has no attribute 'label'
> 
> 
> while when I try to use `undefer` with
> 
> 
> models.EcRecipe.query.options(orm.undefer('controller'))
> 
> 
> the error is different
> 
> Traceback (most recent call last):
> File 
> "/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/code.py", 
> line 90, in runcode
> exec(code, self.locals)
> File "", line 1, in 
> File 
> "/Users/cbarra/Projects/.venv/lib/python3.7/site-packages/sqlalchemy/orm/query.py",
>  line 1523, in options
> return self._options(False, *args)
> File "", line 2, in _options
> File 
> "/Users/cbarra/Projects/.venv/lib/python3.7/site-packages/sqlalchemy/orm/base.py",
>  line 220, in generate
>  fn(self, *args[1:], **kw)
> File 
> "/Users/cbarra/Projects/.venv/lib/python3.7/site-packages/sqlalchemy/orm/query.py",
>  line 1542, in _options
>  opt.process_query(self)
> File 
> "/Users/cbarra/Projects/.venv/lib/python3.7/site-packages/sqlalchemy/orm/strategy_options.py",
>  line 168, in process_query
> self._process(query, True)
> File 
> "/Users/cbarra/Projects/.venv/lib/python3.7/site-packages/sqlalchemy/orm/strategy_options.py",
>  line 542, in _process
>  raiseerr,
> File 
> "/Users/cbarra/Projects/.venv/lib/python3.7/site-packages/sqlalchemy/orm/strategy_options.py",
>  line 720, in _bind_loader
>  raiseerr,
> File 
> "/Users/cbarra/Projects/.venv/lib/python3.7/site-packages/sqlalchemy/orm/strategy_options.py",
>  line 235, in _generate_path
>  attr = found_property = attr.property
> AttributeError: 'property' object has no attribute 'property'
> 
> I am bit puzzled between property, hybrid_property and column_property, what 
> is the best way to have lazy attributes defined on a mixin?
> 

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

Re: [sqlalchemy] testing that a session is committed correctly

[Mike Bayer]

On Wed, Jun 5, 2019, at 11:47 AM, Chris Withers wrote:
> On 05/06/2019 16:41, Mike Bayer wrote:
> > 
> >> Which gives me:
> >>
> >> $ python sessions_are_weird.py
> >> Traceback (most recent call last):
> >> File "sessions_are_weird.py", line 40, in 
> >> assert session.query(Event).count() == 0
> >> AssertionError
> >>
> >> Whereas after the rollback, I'd expect that count to be zero...
> > 
> > this is not working because of this:
> > 
> > session.close()
> > 
> > # test:
> > session.rollback()
> > 
> > 
> > the session is closed first so rollback() will do nothing.
> 
> Yeah, that was my point: That session.close() appears to be the problem. 
> It's a normal and required part of the code under test, but it throws 
> away the SessionTransaction without rolling it back, so by the time the 
> test does session.rollback(), it's doing it on a new SessionTransaction 
> and so has no effect and the assertion fails because there event created 
> is still around...
> 
> Put differently, the web app closes the session at the end of its 
> request handling, which seems legit, right?
> 
> How come close() doesn't rollback the SessionTransaction if it throws it 
> away?

that's currently what .close() does, it discards the connection. this is safe 
because the connection pool ensures transactions are rolled back. This might 
have to change with some of the 2.0 things I'm thinking about but it's not 
clear yet.

Anyway, you don't get a rollback here because the session is bound to an 
external connection so the connection pool is not involved. if you want to roll 
back the work that the application did, that would be your 
sub_transaction.rollback():

 sub_transaction = conn.begin_nested()
 try:

 session = Session()

 # code under test:
 event = Event(text='some stuff got done')
 session.add(event)
 session.flush()
 session.close()

 finally:
 sub_transaction.rollback()
 assert session.query(Event).count() == 0

the test harness is giving you two choices. you can look at the state of the DB 
after your program has done some things and *before* your harness has reversed 
its work, or you can look at the state of the DB *after* your harness has 
reversed its work. I'm not sure what the combination of "session.rollback()" + 
"sub_transaction.rollback()" is trying to achieve or what would be happening 
between the two. 






> 
> Chris
> 
> -- 
> 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.
> To view this discussion on the web visit 
> https://groups.google.com/d/msgid/sqlalchemy/806b6413-8537-8813-5c32-1238086300dd%40withers.org.
> 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.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/485d0429-ea0c-4d18-96a1-d2e03ffb29a1%40www.fastmail.com.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Mixins and lazy (query) attributes

[Christian Barra]

Hi, I am trying to understand what the best approach is to have lazy 
attributes defined on a mixin and then used them on the subclasses.

Ideally I'd would like to defer the load of that attribute (controller) and 
use `undefer` when I know that that attribute is needed, to execute only 
one query.

Below there's the code I am trying to use, `recipe_set` is a backref, same 
for `unit`, Controller is another model.


class BaseRecipeMixin(APIResourceMixin):
_key = None

@declared_attr
def recipe_set_id(self):
return db.Column(db.ForeignKey('recipe_set.id'), unique=True, 
nullable=False)

@property
def controller(self):
return column_property(
Controller.query.filter(
Controller.unit == self.recipe_set.unit,
Controller.is_master.is_(True),
Controller.is_deleted.is_(False),
).one_or_none(),
deferred=True,
)


class EcRecipe(DosingRecipeMixin, BaseRecipeMixin, UpdateMixin, db.Model):
id = db.Column(db.Integer, primary_key=True)
pump_time = db.Column(db.Float, nullable=False, default=8)


But when I try to run this


models.EcRecipe.query.first().controller


I get this error

Traceback (most recent call last):
  File 
"/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/code.py", 
line 90, in runcode
exec(code, self.locals)
  File "", line 1, in 
  File "/Users/cbarra/Projects/kompost/recipes/models.py", line 262, in 
controller
deferred=True,
  File "", line 2, in column_property
  File "", line 2, in __init__
  File 
"/Users/cbarra/Projects/.venv/lib/python3.7/site-packages/sqlalchemy/util/deprecations.py"
, line 130, in warned
return fn(*args, **kwargs)
  File 
"/Users/cbarra/Projects/.venv/lib/python3.7/site-packages/sqlalchemy/orm/properties.py"
, line 134, in __init__
self._orig_columns = [expression._labeled(c) for c in columns]
  File 
"/Users/cbarra/Projects/.venv/lib/python3.7/site-packages/sqlalchemy/orm/properties.py"
, line 134, in 
self._orig_columns = [expression._labeled(c) for c in columns]
  File "", line 1, in 
  File 
"/Users/cbarra/Projects/.venv/lib/python3.7/site-packages/sqlalchemy/sql/elements.py"
, line 4373, in _labeled
return element.label(None)
AttributeError: 'NoneType' object has no attribute 'label'


while when I try to use `undefer` with


models.EcRecipe.query.options(orm.undefer('controller'))


the error is different

Traceback (most recent call last):
  File 
"/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/code.py", 
line 90, in runcode
exec(code, self.locals)
  File "", line 1, in 
  File 
"/Users/cbarra/Projects/.venv/lib/python3.7/site-packages/sqlalchemy/orm/query.py"
, line 1523, in options
return self._options(False, *args)
  File "", line 2, in _options
  File 
"/Users/cbarra/Projects/.venv/lib/python3.7/site-packages/sqlalchemy/orm/base.py"
, line 220, in generate
fn(self, *args[1:], **kw)
  File 
"/Users/cbarra/Projects/.venv/lib/python3.7/site-packages/sqlalchemy/orm/query.py"
, line 1542, in _options
opt.process_query(self)
  File 
"/Users/cbarra/Projects/.venv/lib/python3.7/site-packages/sqlalchemy/orm/strategy_options.py"
, line 168, in process_query
self._process(query, True)
  File 
"/Users/cbarra/Projects/.venv/lib/python3.7/site-packages/sqlalchemy/orm/strategy_options.py"
, line 542, in _process
raiseerr,
  File 
"/Users/cbarra/Projects/.venv/lib/python3.7/site-packages/sqlalchemy/orm/strategy_options.py"
, line 720, in _bind_loader
raiseerr,
  File 
"/Users/cbarra/Projects/.venv/lib/python3.7/site-packages/sqlalchemy/orm/strategy_options.py"
, line 235, in _generate_path
attr = found_property = attr.property
AttributeError: 'property' object has no attribute 'property'

I am bit puzzled between property, hybrid_property and column_property, 
what is the best way to have lazy attributes defined on a mixin?

-- 
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.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/dd9354d6-5759-43f5-9fe4-b69f331e4822%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] testing that a session is committed correctly

[Chris Withers]

On 05/06/2019 16:41, Mike Bayer wrote:



Which gives me:

$ python sessions_are_weird.py
Traceback (most recent call last):
  File "sessions_are_weird.py", line 40, in 
    assert session.query(Event).count() == 0
AssertionError

Whereas after the rollback, I'd expect that count to be zero...


this is not working because of this:

     session.close()

     # test:
     session.rollback()


the session is closed first so rollback() will do nothing.


Yeah, that was my point: That session.close() appears to be the problem. 
It's a normal and required part of the code under test, but it throws 
away the SessionTransaction without rolling it back, so by the time the 
test does session.rollback(), it's doing it on a new SessionTransaction 
and so has no effect and the assertion fails because there event created 
is still around...


Put differently, the web app closes the session at the end of its 
request handling, which seems legit, right?


How come close() doesn't rollback the SessionTransaction if it throws it 
away?


Chris

--
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.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/806b6413-8537-8813-5c32-1238086300dd%40withers.org.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] testing that a session is committed correctly

[Chris Withers]

(sorry, meant to send this to the list)

On 05/06/2019 15:52, Mike Bayer wrote:



That session.close() appears to be the problem. It's a normal and 
required part of the code under test, but it throws away the 
SessionTransaction without rolling it back, so by the time the test 
does session.rollback(), it's doing it on a new SessionTransaction and 
so has no effect and the assertion fails because there event created 
is still around...


Is this a bug or am I just confusing myself and everyone else?


I can run your script if you remove the "diary" thing from it


Hmm, I wonder what's different?

I'm on Python 3.7.1, SQLAlchemy 1.3.4, Postgres 11.3, here's a totally 
self contained script:


import os

from sqlalchemyimport Column, Integer, Text
from sqlalchemyimport create_engine
from sqlalchemy.ext.declarativeimport declarative_base
from sqlalchemy.ormimport sessionmaker

Base = declarative_base()

Session = sessionmaker()

class Event(Base):
__tablename__ ='entry' id = Column(Integer(),primary_key=True)
text = Column(Text)


engine = create_engine(os.environ['TEST_DB_URL'])
Session.configure(bind=engine)

conn = engine.connect()
transaction = conn.begin()
try:
Base.metadata.create_all(bind=conn,checkfirst=False)
Session.configure(bind=conn)

sub_transaction = conn.begin_nested()
try:

session = Session()

# code under test: event = Event(text='some stuff got done')
session.add(event)
session.flush()
session.close()

# test: session.rollback()
assert session.query(Event).count() ==0 finally:
sub_transaction.rollback()

finally:
transaction.rollback()


Which gives me:

$ python sessions_are_weird.py
Traceback (most recent call last):
  File "sessions_are_weird.py", line 40, in 
    assert session.query(Event).count() == 0
AssertionError

Whereas after the rollback, I'd expect that count to be zero...

Chris

--
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.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/d8b26fa3-aca9-7391-f1b8-c7900368b266%40withers.org.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] testing that a session is committed correctly

[Mike Bayer]

On Wed, Jun 5, 2019, at 3:50 AM, Chris Withers wrote:
> On 04/06/2019 23:21, Mike Bayer wrote:
> 
>> 
>> On Tue, Jun 4, 2019, at 4:33 PM, Chris Withers wrote:
>>> 
>>> So, how do I roll back the further subtransaction created by the web 
>>> framework instantiating Session from a sessionmaker bound to the 
>>> connection in which begin_nested() has been called, which under non-test 
>>> running would actually be a top level transaction assuming I understand 
>>> the pattern correctly, in such as way that if the code-under-test has 
>>> committed on is session, the session being used to check expectations in 
>>> the unit test will see the results, but if it that commit has been 
>>> forgotten, it will not?
>> 
>> I'm not following all your code but if there are two sessions in play I'd 
>> probably try to avoid that, there should be only one Session you care about. 
>> the test fixtures should be external to everything and make sure there's 
>> just the one session. if there are two in play, I'm not sure how they both 
>> get bound to your test transaction.
>> 
> Even this doesn't appear to be enough, here's the simplest reproducer script 
> I can get to:
> 
> 
> *import *os

> *from *diary.model *import *Session, Base, Event, Types
> *from *sqlalchemy *import *create_engine

engine = create_engine(os.environ[*'TEST_DB_URL'*])
Session.configure(bind=engine)

conn = engine.connect()
transaction = conn.begin()
> *try*:
Base.metadata.create_all(bind=conn, checkfirst=*False*)
Session.configure(bind=conn)

sub_transaction = conn.begin_nested()
*try*:

session = Session()

# code under test:
> event = Event(date=*'2019-06-02'*, type=Types.done, text=*'some stuff 
> got done'*)
session.add(event)
session.flush()
session.close()

# test:
> session.rollback()
*assert *session.query(Event).count() == 0
> 
> *finally*:
sub_transaction.rollback()

> *finally*:
transaction.rollback()
> 
> That session.close() appears to be the problem. It's a normal and required 
> part of the code under test, but it throws away the SessionTransaction 
> without rolling it back, so by the time the test does session.rollback(), 
> it's doing it on a new SessionTransaction and so has no effect and the 
> assertion fails because there event created is still around...
> 
>  Is this a bug or am I just confusing myself and everyone else?

I can run your script if you remove the "diary" thing from it


> 
>  Chris
> 
> 
> 

> --
>  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.
>  To view this discussion on the web visit 
> https://groups.google.com/d/msgid/sqlalchemy/55721019-2de3-12b4-3796-90ca34b5ea4c%40withers.org
>  
> .
>  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.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/54c7d33f-e7d8-4976-a015-894b9697772c%40www.fastmail.com.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] testing that a session is committed correctly

[Mike Bayer]

On Tue, Jun 4, 2019, at 6:31 PM, Chris Withers wrote:
> On 04/06/2019 23:21, Mike Bayer wrote:
> > 
> > 
> > I'm not following all your code but if there are two sessions in play 
> > I'd probably try to avoid that, there should be only one Session you 
> > care about. 
> 
> This comes back to something I asked you about on Twitter a while ago: 
> the code under test gets its session by calling a sessionmaker; how can 
> I have that return an existing session, which appears to be what you're 
> suggesting, rather than a new suggestion, which appears to be all they 
> can do.


yeah you have to inject and mock aggressively so that your code being tested 
only sees the Session objects your fixtures want them to.

> 
> > the test fixtures should be external to everything 
> 
> I don't understand what you mean by this.

the same thing as my statement above. The code being tested shouldn't be able 
to make it's own Session if you want it to have one that you are controlling, 
which means when your test harness runs it has to swap out everything in the 
application that might be in the way of this. The term "harness" implies your 
entire application sits "inside" the harness, that's what I mean by "external".

> 
> > and make 
> > sure there's just the one session. if there are two in play, I'm not 
> > sure how they both get bound to your test transaction.
> 
> I believe they start a nested transaction?

one Session has nothing to do with another so I don't know what you mean.


> 
> Chris
> 
> -- 
> 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.
> To view this discussion on the web visit 
> https://groups.google.com/d/msgid/sqlalchemy/50bb9529-f98b-24aa-484f-1274c8fe3290%40withers.org.
> 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.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/9953dcf8-d491-418c-ae1f-73c91f7666a6%40www.fastmail.com.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] testing that a session is committed correctly

[Chris Withers]

On 04/06/2019 23:21, Mike Bayer wrote:


On Tue, Jun 4, 2019, at 4:33 PM, Chris Withers wrote:


So, how do I roll back the further subtransaction created by the web
framework instantiating Session from a sessionmaker bound to the
connection in which begin_nested() has been called, which under non-test
running would actually be a top level transaction assuming I understand
the pattern correctly, in such as way that if the code-under-test has
committed on is session, the session being used to check expectations in
the unit test will see the results, but if it that commit has been
forgotten, it will not?


I'm not following all your code but if there are two sessions in play 
I'd probably try to avoid that, there should be only one Session you 
care about.  the test fixtures should be external to everything and 
make sure there's just the one session.   if there are two in play, 
I'm not sure how they both get bound to your test transaction.


Even this doesn't appear to be enough, here's the simplest reproducer 
script I can get to:


import os

from diary.modelimport Session, Base, Event, Types
from sqlalchemyimport create_engine

engine = create_engine(os.environ['TEST_DB_URL'])
Session.configure(bind=engine)

conn = engine.connect()
transaction = conn.begin()
try:
Base.metadata.create_all(bind=conn,checkfirst=False)
Session.configure(bind=conn)

sub_transaction = conn.begin_nested()
try:

session = Session()

# code under test: event = 
Event(date='2019-06-02',type=Types.done,text='some stuff got done')
session.add(event)
session.flush()
session.close()

# test: session.rollback()
assert session.query(Event).count() ==0 finally:
sub_transaction.rollback()

finally:
transaction.rollback()


That session.close() appears to be the problem. It's a normal and 
required part of the code under test, but it throws away the 
SessionTransaction without rolling it back, so by the time the test does 
session.rollback(), it's doing it on a new SessionTransaction and so has 
no effect and the assertion fails because there event created is still 
around...


Is this a bug or am I just confusing myself and everyone else?

Chris

--
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.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/55721019-2de3-12b4-3796-90ca34b5ea4c%40withers.org.
For more options, visit https://groups.google.com/d/optout.