Hi Mike, Thanks very much for the response and the clear explanation, and the link. All of that was very helpful to me, and I'm made some changes to my code accordingly. I am using Flask-SQLAlchemy by the way, so what you wrote makes even more sense in that context.
Doug On Thursday, September 28, 2017 at 10:54:13 AM UTC-4, Mike Bayer wrote: > > On Thu, Sep 28, 2017 at 9:29 AM, Doug Farrell <[email protected] > <javascript:>> wrote: > > Hi all, > > > > I'm having trouble understanding how to use > > session.begin(subtransactions=True) as a context manager. I'm working in > > Python 2.7.5 with SQLAlchemy 1.1.14 in a Flask 0.12.2 application on > CentOS > > 7 servers. I like session.begin() as a context manager, but things > aren't > > working as I thought they would. For example: > > > > with db.session.begin(subtransactions=True): > > # create a model instance > > thing = Thing(...) > > db.session.add(thing) > > > I would not recommend using the Session in this pattern. Leaving > the session with its default setting of "autocommit=False" means you > never have to call begin() at all; this is desirable because there's > no reason to use a Session outside of an explicit transaction (this > was not the case many years ago which is why begin() remains). > Methods that work with objects should not also be demarcating > transaction scope; there's general discussion of this at > > http://docs.sqlalchemy.org/en/latest/orm/session_basics.html#when-do-i-construct-a-session-when-do-i-commit-it-and-when-do-i-close-it. > > > > if you're doing Flask, the commit of the Session should be at the end > of the request, the begin is implicit, and that's it. If you > absolutely need a commit to occur at some special place before you've > reached the official end of the request + commit, you can just simply > call session.commit() at any time. The Session will start a new > transaction if and when additional work is performed with it. > > Now, if you need your work to be in an **independent** transaction, > that is, totally separate from the "main" one, then you need to do > your work in a totally different Session object. This is an > acceptable pattern that is sometimes necessary. > > > > > > I thought when the context manager went out of scope it would perform a > > db.session.commit() to persist the thing instance, but I haven't seen > > changes to the database. > > the commit() will happen if the begin() is the outermost begin(). If > not, then it's one of the "inner" blocks and no commit will occur. > If you haven't placed this session into autocommit=True, which is > required for making explicit use of the begin() method, then there is > already a transaction in progress and the block will never cause a > commit() to occur. > > > But if I change the code to this: > > > > with db.session.begin(subtransactions=True): > > # create a model instance > > thing = Thing(...) > > db.session.add(thing) > > db.session.commit() > > > > it raises a ResourceClosedError: This transaction is closed > > This is because that code interferes with the state of the context > manager. The context manager is referring to the current > SessionTransaction upon which it will call commit() at the end of the > block. Because you are calling commit() on it, that effectively ends > that SessionTransaction being in a usable state, and the context > manager then fails to do its final step. > > > > > > What works for me is this: > > > > with db.session.begin(subtransactions=True): > > # create a model instance > > thing = Thing(...) > > db.session.add(thing) > > db.session.commit() > > > > Where the commit() is outside the scope of the context manager. But this > > seems contrary to me, and makes me think I'm doing something wrong as my > > expectation of what > > this sounds a lot like you have the session in its default mode of > autocommit=False, which is great, but you shouldn't be using the > begin() method. > > If we go to the doc for begin: > > > http://docs.sqlalchemy.org/en/latest/orm/session_api.html?highlight=session%20begin#sqlalchemy.orm.session.Session.begin > > > the second line is: "The Session.begin() method is only meaningful if > this session is in autocommit mode prior to it being called; see > Autocommit Mode for background on this setting." > > then if we go to autocommit mode at > > http://docs.sqlalchemy.org/en/latest/orm/session_transaction.html#session-autocommit > > there is a giant green/red warning box referring to the whole thing as > a legacy pattern. SQLAlchemy back in the 0.1, 0.2 days was > developed against Python 2.3/2.4, before context managers existed and > also when the Session had a more naive view of transactions and > flushing. Ideally the subtransactions concept would not have been > present at all nor would Session have a begin() method. > > > > > > > > > > with db.session.begin(...) > > > > does is this (pseudocode): > > > > begin transaction > > try: > > create some model instance > > add them to the session > > commit handled by leaving the scope of the context manager > > except > > rollback on exception > > > > It would be very much appreciated is someone could point me in the right > > direction, give me some suggestions or references about what I'm > missing. > > > > Thanks, > > Doug > > > > > > > > > > > > > > > > -- > > 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 [email protected] <javascript:>. > > To post to this group, send email to [email protected] > <javascript:>. > > Visit this group at https://groups.google.com/group/sqlalchemy. > > For more options, visit https://groups.google.com/d/optout. > > On Thu, Sep 28, 2017 at 9:29 AM, Doug Farrell <[email protected] > <javascript:>> wrote: > > Hi all, > > > > I'm having trouble understanding how to use > > session.begin(subtransactions=True) as a context manager. I'm working in > > Python 2.7.5 with SQLAlchemy 1.1.14 in a Flask 0.12.2 application on > CentOS > > 7 servers. I like session.begin() as a context manager, but things > aren't > > working as I thought they would. For example: > > > > with db.session.begin(subtransactions=True): > > # create a model instance > > thing = Thing(...) > > db.session.add(thing) > > > > I thought when the context manager went out of scope it would perform a > > db.session.commit() to persist the thing instance, but I haven't seen > > changes to the database. But if I change the code to this: > > > > with db.session.begin(subtransactions=True): > > # create a model instance > > thing = Thing(...) > > db.session.add(thing) > > db.session.commit() > > > > it raises a ResourceClosedError: This transaction is closed > > > > What works for me is this: > > > > with db.session.begin(subtransactions=True): > > # create a model instance > > thing = Thing(...) > > db.session.add(thing) > > db.session.commit() > > > > Where the commit() is outside the scope of the context manager. But this > > seems contrary to me, and makes me think I'm doing something wrong as my > > expectation of what > > > > with db.session.begin(...) > > > > does is this (pseudocode): > > > > begin transaction > > try: > > create some model instance > > add them to the session > > commit handled by leaving the scope of the context manager > > except > > rollback on exception > > > > It would be very much appreciated is someone could point me in the right > > direction, give me some suggestions or references about what I'm > missing. > > > > Thanks, > > Doug > > > > > > > > > > > > > > > > -- > > 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 [email protected] <javascript:>. > > To post to this group, send email to [email protected] > <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 [email protected]. To post to this group, send email to [email protected]. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
