Re: [sqlalchemy] testing that a session is committed correctly
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. the test fixtures should be external to everything I don't understand what you mean by this. 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? 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.
Re: [sqlalchemy] testing that a session is committed correctly
On Tue, Jun 4, 2019, at 4:33 PM, Chris Withers wrote: > On 04/06/2019 14:49, Mike Bayer wrote: > > > > > > On Tue, Jun 4, 2019, at 2:15 AM, Chris Withers wrote: > >> Now, what I'm trying to test is that I haven't forgotten to include > >> the "with session.transaction". The problem is that, without the > >> transaction.rollback(), the test passes regardless of whether the > >> "with session.transaction" is there, and with it there, it always fails. > >> > >> What's the best way for writing tests that have the database setup DDL > >> run in a transaction that's rolled back at the end of the session, > >> each test in a subtransaction that gets rolled back at the end of each > >> test, and also test that code under test is committing or rolling back > >> as required? > > > > > > So when you make the Session it has a .transaction that you start with, > > when you come out of your tests, that should be the same .transaction > > when you get the Session back. If it's different, then the test did not > > leave the Session in the same state. does that work ? > > I think what I'm getting to here is that there are two Session instances > here, one that's set up by the web app under test, and one by pytest as > a fixture. > > However, if I understand correctly, they're both running inside the > sub-transaction returned by engine.connect().begin_nested(), which is in > turn inside the transaction returned by engine.connect().begin(). > > 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. > > 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/471aa85f-776f-b899-1fe8-2f3cc009da38%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/38abc0c3-6854-42f7-8273-127ea15046c1%40www.fastmail.com. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] when does session.transaction come into being?
On Tue, Jun 4, 2019, at 4:34 PM, Chris Withers wrote: > On 04/06/2019 14:47, Mike Bayer wrote: > > > > > > On Tue, Jun 4, 2019, at 3:05 AM, Chris Withers wrote: > >> Hi All, > >> > >> What creates session.transaction? I can't spot get __getattr__ magic, > >> but the only place in the code I see it being created is in .begin(...), > >> which has a docstring saying that it should no longer be used, so I feel > >> like I must be missing something? > > > > > > self.begin() is called inside the __init__ method of the Session when > > autocommit is at the default of False. > > Okay, I must have missed that. > > Just double checking: I thought you said that Session() would not block? > If it's calling self.begin(), how come that isn't a blocking operation? begin() is not blocking because it doesn't do anything with the engine or any connections, it just creates a new SessionTransaction object. Looking at the source however it can block if you are using the session in autocommit mode, then add some pending objects, then say begin(), because it will flush on begin. So it depends a little bit how you are using the Session. > > 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/9a6480f3-3c03-49cc-8d5a-f4143e215236%40www.fastmail.com. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] when does session.transaction come into being?
On 04/06/2019 14:47, Mike Bayer wrote: On Tue, Jun 4, 2019, at 3:05 AM, Chris Withers wrote: Hi All, What creates session.transaction? I can't spot get __getattr__ magic, but the only place in the code I see it being created is in .begin(...), which has a docstring saying that it should no longer be used, so I feel like I must be missing something? self.begin() is called inside the __init__ method of the Session when autocommit is at the default of False. Okay, I must have missed that. Just double checking: I thought you said that Session() would not block? If it's calling self.begin(), how come that isn't a blocking operation? 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/e8bd076d-3d1e-a21f-35a9-6df6a94f0a65%40withers.org. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] testing that a session is committed correctly
On 04/06/2019 14:49, Mike Bayer wrote: On Tue, Jun 4, 2019, at 2:15 AM, Chris Withers wrote: Now, what I'm trying to test is that I haven't forgotten to include the "with session.transaction". The problem is that, without the transaction.rollback(), the test passes regardless of whether the "with session.transaction" is there, and with it there, it always fails. What's the best way for writing tests that have the database setup DDL run in a transaction that's rolled back at the end of the session, each test in a subtransaction that gets rolled back at the end of each test, and also test that code under test is committing or rolling back as required? So when you make the Session it has a .transaction that you start with, when you come out of your tests, that should be the same .transaction when you get the Session back. If it's different, then the test did not leave the Session in the same state. does that work ? I think what I'm getting to here is that there are two Session instances here, one that's set up by the web app under test, and one by pytest as a fixture. However, if I understand correctly, they're both running inside the sub-transaction returned by engine.connect().begin_nested(), which is in turn inside the transaction returned by engine.connect().begin(). 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? 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/471aa85f-776f-b899-1fe8-2f3cc009da38%40withers.org. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] testing that a session is committed correctly
On Tue, Jun 4, 2019, at 2:15 AM, Chris Withers wrote:
> Hi All,
>
> I'm working with the pattern described at
> https://docs.sqlalchemy.org/en/13/orm/session_transaction.html#joining-a-session-into-an-external-transaction-such-as-for-test-suites
> along with pytest and FastAPI, an async web app framework with good support
> for running blocking code.
>
> So, I have my fixtures:
>
> @pytest.fixture(scope=*'session'*)
> *def *db(client):
engine = Session.kw[*'bind'*]
conn = engine.connect()
transaction = conn.begin()
*try*:
Base.metadata.create_all(bind=conn, checkfirst=*False*)
*yield *conn
*finally*:
transaction.rollback()
Session.configure(bind=engine)
> @pytest.fixture()
> *def *transaction(db):
transaction = db.begin_nested()
*try*:
Session.configure(bind=db)
*yield *transaction
*finally*:
transaction.rollback()
> @pytest.fixture()
> *def *session(transaction):
*return *Session()
>
> And a test:
>
>
> *def *test_create_full_data(transaction, session, client):
response = client.post(*'/events/'*, json={
*'date'*: *'2019-06-02'*,
*'type'*: *'DONE'*,
*'text'*: *'some stuff got done'
***})
transaction.rollback()
actual = session.query(Event).one()
compare(actual.date, expected=date(2019, 6, 2))
compare(type(actual), expected=Done)
compare(response.json(), expected={
*'id'*: actual.id,
*'date'*: *'2019-06-02'*,
*'type'*: *'DONE'*,
*'text'*: *'some stuff got done'
***})
compare(response.status_code, expected=201)
>
> And some code:
>
> @router.post(*"/"*, response_model=EventRead, status_code=201)
> *def *create_object(
*,
session: Session = Depends(db_session),
event: EventCreate,
):
"""
> Create new Event.
> """
> *with *session.transaction:
event = Event(**event.dict())
session.add(event)
session.flush()
session.expunge(event)
*return *event
>
>
> Now, what I'm trying to test is that I haven't forgotten to include the "with
> session.transaction". The problem is that, without the
> transaction.rollback(), the test passes regardless of whether the "with
> session.transaction" is there, and with it there, it always fails.
>
> What's the best way for writing tests that have the database setup DDL run
> in a transaction that's rolled back at the end of the session, each test in a
> subtransaction that gets rolled back at the end of each test, and also test
> that code under test is committing or rolling back as required?
So when you make the Session it has a .transaction that you start with, when
you come out of your tests, that should be the same .transaction when you get
the Session back. If it's different, then the test did not leave the Session in
the same state. does that work ?
>
> 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/430d4156-d9ff-4349-5be5-62bee6ea4627%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/09de6765-b65b-4a5c-8013-64f2623a18c5%40www.fastmail.com.
For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] when does session.transaction come into being?
On Tue, Jun 4, 2019, at 3:05 AM, Chris Withers wrote: > Hi All, > > What creates session.transaction? I can't spot get __getattr__ magic, > but the only place in the code I see it being created is in .begin(...), > which has a docstring saying that it should no longer be used, so I feel > like I must be missing something? self.begin() is called inside the __init__ method of the Session when autocommit is at the default of False. the reason you can't call it yourself with autocommit=False is that the Session keeps calling it automatically within each rollback() and commit(). which might seem weird but the SessionTransaction doesn't use any database resources for begin(), it's just a logical boundary. > > 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/f0a290f1-6af6-01d5-2b45-c6dd72cc982b%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/d5f9c027-98ab-491f-a7e4-da511810a123%40www.fastmail.com. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Re: Should load_only work with hybrid properties?
I think I get the idea. That's why the properties with hybrid_property.expression defined are able to work as SQL SELECT fields in the cases where a tuple is returned rather than an ORM model... because you don't have to have a place in _dict__ to put it, it just goes into the tuple. My main goal was figuring out how to easily limit which columns are returned, without re-specifying joins that are already defined in the relationships on the Model, so that probably muddied my question. I think the answer to that is that either I query the whole ORM object (and get the joins for free, as defined in the Model) or if I must limit the columns returned, then I just have to re-specify the joins in each query. Thank you for the feedback and all the support in general. On Monday, June 3, 2019 at 6:24:29 PM UTC-4, Mike Bayer wrote: > > I think the thing you need to understand about the "hybrid" is that it > isn't "loaded" with the object, that is, it has no place in __dict__ to be > stored. The function that you create is invoked every time you access it > on your object or class. If you have an object instance of Task, as you've > observed, the in-python version of it will run every time when you say > some_task.my_hybrid. That's why it's called a "hybrid". > > So technically, it doesn't make sense to say > quey(Task).load_only(Task.some_hybrid), becuase there is no place on Task > to put that value. > > However, it *does* make sense that you might want to load_only the columns > on Task that your hybrid uses in order to create its value. If your hybrid > was like, "return self.x + self.y" you would want to load_only(Task.x, > Task.y). > > So from *there*, there could be a way that these column attributes could > be extracted from your hybrid automatically, so that if you said > load_only(Task.some_hybrid), it would actually mean load_only(Task.x > Task.y). I could even come up with a recipe to do that in the meantime. > But I'm not sure this would make a good feature or not because it adds more > layers of magic to a concept that is already proving to be confusing. > > let me know if that makes sense. > > > On Mon, Jun 3, 2019, at 5:06 PM, Gmoney wrote: > > I thought I figured out another clever way, but it's not working working > as I thought it might. Thought maybe using ".from_self" I could do the > base full automatic ORM Model query to get everything, then do the > .from_self to wrap it with an outer query and only return the columns I > need. > > q = (db.session.query(Task).from_self(*columns_i_want)) > > Unfortunately though, even though query(Task) alone can figure out the > relationships/joins, once it's in this statement with the from_self > version, you don't get them anymore. > > I was so excited - I thought that was definitely going to work. Any > chance I'm just missing a step that could make that method work? I see the > '.add_entity' option but that didn't seem to help and kind of defeats the > purpose of my simplicity goal anyway. > > On Monday, June 3, 2019 at 3:57:18 PM UTC-4, Gmoney wrote: > > I think it's starting to make sense. I did misunderstand exactly when the > .expression SQL was used - I had thought even queries for ORM models would > use it, but I see it does work as you describe. > > My goal is to return a subset of fields and make that query building > operation be dynamic and "simple" for downstream services. My trouble > seems to be that as I move away from the baseline full "query(MyORMClass)", > the relationship configured joins no longer automatically come along, so I > have to re-specify them in the query. > > Thanks for the response, I will consider the column_property alternative - > it might be better for our situation. > > As far as limiting fields returned then though - I think these are my > options, if I understand them right: > > query(ORM_Class) > -Returns list of ORM Model instances > -The only option that auto-includes all joins defined in ORM Model > relationships. > -Does not calculate hybrid_property in SQL > -Referencing hybrid_property on the returned ORM Model evaluates > hybrid_property in python. > > load_only(ORM_Class.column_attr,... etc) > -Returns list of ORM Models (sparse, lazily populated) > -Will *NOT* add necessary joins from relationship definitions in Model. > Must join manually in query. > -Can't ask for hybrid_property in query > -Referencing hybrid_property on the returned ORM Model still evaluates > hybrid_property in python. > > query(ORM_Class.column_attr, ORM_Class.hybrid_property) > -Returns Tuple > -Will *NOT* add necessary joins from relationship definitions in Model. > Must join manually in query. > -Uses the .expression form of the hybrid_property to SELECT/calculate it > in SQL > > with_entities(ORM_Class.column_attr,... etc) > -Returns Tuple > -Will *NOT* add necessary joins from relationship definitions in Model. > Must join manually in query. > -Uses
[sqlalchemy] when does session.transaction come into being?
Hi All, What creates session.transaction? I can't spot get __getattr__ magic, but the only place in the code I see it being created is in .begin(...), which has a docstring saying that it should no longer be used, so I feel like I must be missing something? 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/f0a290f1-6af6-01d5-2b45-c6dd72cc982b%40withers.org. For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] testing that a session is committed correctly
Hi All,
I'm working with the pattern described at
https://docs.sqlalchemy.org/en/13/orm/session_transaction.html#joining-a-session-into-an-external-transaction-such-as-for-test-suites
along with pytest and FastAPI, an async web app framework with good
support for running blocking code.
So, I have my fixtures:
@pytest.fixture(scope='session')
def db(client):
engine = Session.kw['bind']
conn = engine.connect()
transaction = conn.begin()
try:
Base.metadata.create_all(bind=conn,checkfirst=False)
yield conn
finally:
transaction.rollback()
Session.configure(bind=engine)
@pytest.fixture()
def transaction(db):
transaction = db.begin_nested()
try:
Session.configure(bind=db)
yield transaction
finally:
transaction.rollback()
@pytest.fixture()
def session(transaction):
return Session()
And a test:
def test_create_full_data(transaction, session, client):
response = client.post('/events/',json={
'date':'2019-06-02',
'type':'DONE',
'text':'some stuff got done' })
transaction.rollback()
actual = session.query(Event).one()
compare(actual.date,expected=date(2019,6,2))
compare(type(actual),expected=Done)
compare(response.json(),expected={
'id': actual.id,
'date':'2019-06-02',
'type':'DONE',
'text':'some stuff got done' })
compare(response.status_code,expected=201)
And some code:
@router.post("/",response_model=EventRead,status_code=201)
def create_object(
*,
session: Session = Depends(db_session),
event: EventCreate,
):
""" Create new Event. """ with session.transaction:
event = Event(**event.dict())
session.add(event)
session.flush()
session.expunge(event)
return event
Now, what I'm trying to test is that I haven't forgotten to include the
"with session.transaction". The problem is that, without the
transaction.rollback(), the test passes regardless of whether the "with
session.transaction" is there, and with it there, it always fails.
What's the best way for writing tests that have the database setup DDL
run in a transaction that's rolled back at the end of the session, each
test in a subtransaction that gets rolled back at the end of each test,
and also test that code under test is committing or rolling back as
required?
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/430d4156-d9ff-4349-5be5-62bee6ea4627%40withers.org.
For more options, visit https://groups.google.com/d/optout.
