[grantcox]

I'm fairly new to Python (~6 months) and started with SQLAlchemy, but recently switched our app to Peewee (http://peewee.readthedocs.org) because of frustrations with the SQLAlchemy "session".

The standard method of scoped_session(sessionmaker(engine)) will return a thread-local session - so all data manipulation effectively shares a global session.

- Every change needs to be followed with a commit() or rollback(), or you'll end up with rubbish in the session that some later call will inadvertantly commit.

- If you want to do a general "update where" call, make sure you use "synchronize_session=False" and then session.expire_all(), otherwise the session will be out of date.

It felt like every time I had code working with data, I also had a non-trivial amount of session management. It wasn't abstracting the database access away, it was making everything very SQLAlchemy session specific.

To be honest I'm somewhat second guessing my decision, because Peewee is far from the Python standard. But it's simple, understandable and clear, and doesn't force a strange "don't forget to manage the global state" mindset onto everything.

[jessedhillon]

FWIW I use Pyramid with SQLAlchemy and it comes with a transaction manager plugin which automates the begin, commit and rollback on error of transactions. 99% of the time I don't need to think about transactions, only during CLI tool development and only barely. YMMV

[aychedee]

Yeah, sounds like you're mileage does vary.

[ddorian43]

i think flask-sqlalchemy does something similar

[rectangletangle]

The documentation has a pretty good example where they bundle session related functionality into a reusable context manager (Python's `with` statement). I took this approach in one of my projects, and it worked out rather well.

[grantcox]

I assume you mean the example towards the bottom of http://docs.sqlalchemy.org/en/rel_0_9/orm/session.html#when-...

So where do you use this "with session_scope() as session:" context wrapper? Do you wrap your entry point in it, so the entire app is implicitly using the session (as the example shows)? Or do you call it in each separate function that needs data access?

If the former, how do you handle commit() and rollback() for "local" (per-function) changes? It sounds like either "never rollback" or "always commit" is necessary. And I think "always commit" is more likely, because usually an entire "request" (whether we're talking web apps or not) isn't atomic.

So you've got this SQLAlchemy session context wrapper around the entry point, nice and separated from all the internal data functions. Except every data function actually needs that session to commit(), so it's either pulled from some global state, or passed around like a stinky baby.

But this can't be the case, as this inelegance is not pythonic, and SQLAlchemy is the Python ORM. What am I missing?