On Thu, Sep 28, 2017 at 9:29 AM, Doug Farrell <[email protected]> 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]. > 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. On Thu, Sep 28, 2017 at 9:29 AM, Doug Farrell <[email protected]> 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]. > 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. -- 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.
