0

I figured I would make this a different question for the sake of being tidy. It is based on: SQLAlchemy won't update my database and SQLAlchemy session: how to keep it alive?.

So here's the deal: I have a Pyramid application that's talking to a daemon, which in turn talks to a database.

Now for some reason stuff isn't getting committed to the database when I add it to the database session variable, as in:

DBSession.add(ModelInstance)

Calling flush or commit doesn't make it commit.

This is how I make DBSession:

    settings = {
        'sqlalchemy.url':'blah blah'
        }
    DBSession = scoped_session(sessionmaker(extension=ZopeTransactionExtension()))
    engine = engine_from_config(settings, 'sqlalchemy.')
    DBSession.configure(bind=engine)

This seems fine to me because I can query the database fine. ie: this sort of thing works:

DBSession.query(ModelClass).get(id)

This fine gentleman https://stackoverflow.com/users/100297/martijn-pieters suggested the use of the following little bit of code:

import transaction
transaction.commit()

And that worked fine for making sure that my stuff got committed. The only problem is that it somehow renders my DBSession useless. So if I want to use the objects that my session is keeping track of I need to re instantiate the session and those items. This sucks. It takes up a whole lot of time.

My question is, in short, how can I avoid this?

And in long:

  • How do I get my DBSession to commit properly without breaking it?

OR

  • How do I fix my DBSession and associated model instances without the need for lengthly database calls?

AND

  • Any idea why this is happening? I have successfully constructed a DBSession in the same way inside the Pyramid app I mentioned and it worked totally fine, it committed when I wanted it to and everything.

For details of the errors I encountered please refer to the two questions I mentioned in the beginning

Community
  • 1
  • 1
Sheena
  • 15,590
  • 14
  • 75
  • 113

3 Answers3

2

According to sqlalchemy's doc

Another behavior of commit() is that by default it expires the state of all instances present after the commit is complete. This is so that when the instances are next accessed, either through attribute access or by them being present in a Query result set, they receive the most recent state. To disable this behavior, configure sessionmaker() with expire_on_commit=False.

What seems like a problem is actually done on purpose : when you commit, all the objects are marked as expired. This is done so that you don't keep using old cached values that may have changed in the database.

The reason it works in pyramid is because each request has its own transaction, and queries object before working on them. You try to use object from a preview transaction, which might not be a good idea, because their may not be in sync with the database.

To solve your problem, you can either make sure you don't reuse object after the end of a transaction (may you need your transactions to include more things), or you can use expire_on_commit=False as advised in the transaction. But if you use the latter, be aware that the object may be outdated.

madjar
  • 12,691
  • 2
  • 44
  • 52
2

In SQLAlchemy sessions expire the objects they are managing upon commit. This is sane because after commit you have no guarantees in a concurrent world that something else isn't changing the state they are attempting to mirror in the database.

Pyramid recommends the use of a transaction manager that helps you maintain a single transaction per request. It will automatically call transaction.commit() for you after the request is complete. In this way, you don't have to think/worry about objects expiring, and transactions are properly aborted if your code raises an exception.

The way to setup the transaction manager is by installing pyramid_tm and zope.sqlalchemy and then connecting your DBSession to zope.sqlalchemy.ZopeTransactionExtension. Then things will "just work".

DBSession = scoped_session(sessionmaker(extension=ZopeTransactionExtension()))

# ...

def main(global_conf, **settings):
    config = Configurator(...)
    config.include('pyramid_tm')
    # ...

If you need to populate a new object's primary key or ensure that some SQL will execute properly you can use DBSession.flush() to execute the SQL within your transaction without actually committing it. Any errors will be raised there for you to catch and deal with.

This basic setup of your sessions is described within the tutorial in the Pyramid documentation:

http://docs.pylonsproject.org/projects/pyramid/en/1.4-branch/tutorials/wiki2/basiclayout.html

Update: I realized I kind of answered your question about using the transaction manager in Pyramid, which you are already using successfully. I think that the answer also clearly explains what's going on with the ZopeTransactionExtension, however, and you just need confirmation about committing transactions. You'd be wise to simply use one transaction in your script, which you can create via

import transaction

with transaction.manager:
    # do tons of database stuff

Now if an exception happens the transaction will be aborted, and if not it will be committed.

Michael Merickel
  • 23,153
  • 3
  • 54
  • 70
0

If I'm reading this correctly, I think I know what is going on...

The downside of the pyramid/zope transaction managers is that they're all or nothing - due to the way they're implemented, you can't both use them and call commit(). I don't remember exactly why, but I once dug into all their code after fighting with this for hours , and there just wasn't a way to commit within the page.

For a variety of reasons I decided to not use the automatic transaction wrapping in my apps. i had a lot of scenarios where I want one or more commits , or needed to use savepoints (i use postgresql).

Jonathan Vanasco
  • 15,111
  • 10
  • 48
  • 72