https://groups.google.com/forum/#!topic/sqlalchemy/twoHzgXcR60

========================================================================================================


Hi everybody,

I'm newwith SQLAlchemy and I'm trying to understand better the Session object.

First ofall, why the expire_all() exists? Shouldn't it be always better toexpunge_all() instead? If it is expired, at the use of the instance anotherquery would be issued to reattach it anyway, so why keep "useless"instances in the identity map (maybe i'm wrong about this fact, i dont knowexactly how the identity map works).

Second,when we should call the close() method? I dont think if i get it at all. Let'ssay that I have the following DAO:

PersonDAO:
   def insert(self, person):
        session = Session()
        session.add(person)
        session.commit() // why the commit starts a new transaction? should'n it onlystart again the next database access?
        session.close() // is it necessary?

Is the aSession instance directly associated to a Connection in the pool? With thedefault pool size of 5, after 5 call of the insert method, theconnection_overflow would be used? What's the time to a Session"expire"?

And justto finish. Why do we use remove() for the scoped_session instead of theclose()? Shouldn't it be semantically identical only overrided for thecontextual stuff?

 

On Mar 18, 2011, at 2:55 PM, Israel Ben Guilherme Fonsecawrote:

> Hi everybody,
>
> I'm new with SQLAlchemy and I'm trying to understand better the Sessionobject.
>
> First of all, why the expire_all() exists?

The purpose is to remove any database-loaded state from allcurrent objects so that the next access of any attribute, or any queryexecution, will retrieve new state, freshening those objects which are stillreferenced outside of the session with the most recent available state.  It is called automatically upon commit() or rollback() assuming anautocommit=False session, so that when the transaction, and its isolatedenvironment, come to an end, subsequent accesses of those objects will acquirenew data from whatever other transactions were committed subsequent to theprevious transaction.   expire_all() itself is useful when:

- the session is used in autocommit=True mode,and new changes from other transactions are desired.
- against a database that does not supporttransactions, or perhaps within a weakly isolated transaction, again to loadchanges from other transactions or connections.
- when SQL statements have been executed againstthe current transaction using execute() which may have changed significantportions of loaded state on the database.



> Shouldn't it be always better to expunge_all() instead? If it is expired,at the use of the instance another query would be issued to reattach it anyway,so why keep "useless" instances in the identity map (maybe i'm wrongabout this fact, i dont know exactly how the identity map works).

They're not useless at all if you are performing operationsupon them which span the scope of multiple transactions, or have any of theabove use cases, and don't wish to re-establish a full graph of objects inmemory.   In-memory objects are essentially proxy objects to an underlyingdatabase transaction.  The Session mediates this relationship.



>
> Second, when we should call the close() method?

when you wish to release the transactional and connectionpool resources of the Session and remove all objects.



> I dont think if i get it at all. Let's say that I have the following DAO:
>
> PersonDAO:
>     def insert(self, person):
>          session = Session()
>          session.add(person)
>          session.commit() // why the commitstarts a new transaction? should'n it only start again the next databaseaccess?

the commit ends the current transaction, and starts a new transaction from theSession's point of view.  However, no SQL or transactional directives areemitted until the first SQL statement is emitted via the Session (either viaexecute(), query() iteration, or flush). So there is no new databasetransaction if you commit then cease to use that Session further.


>         session.close() // is it necessary?

Not strictly although it removes any state left over in thesession, thereby establishing any remaining objects as detached.  This isdesirable since you might want to ensure that subsequent operations on thoseobjects don't re-emit new SQL.


>
> Is the a Session instance directly associated to a Connection in the pool?

Yes.  This is documented here:   http://www.sqlalchemy.org/docs/orm/session.html#what-does-the-session-do


> With the default pool size of 5, after 5 call of the insert method, theconnection_overflow would be used?

No. A single session uses one Connection per Engine at a time.  By default, it keeps one connectionopen until rollback(), commit(), or close() is called.    http://www.sqlalchemy.org/docs/orm/session.html#managing-transactions illustrates this.


> What's the time to a Session "expire"?

it expires things when commit or rollback is called.   This is also in the above docs.


>
> And just to finish. Why do we use remove() for the scoped_session insteadof the close()?

scoped_session offers all of the methods of the underlyingSession via a proxy pattern, so you can call close() on scoped_session, whichcalls close() on the actual session, or you can call remove(), which emitsclose() then removes the Session object itself from the registry.  Thelatter has the advantage that any particular state established on the session,such as a Connection-based bind (see the example in http://www.sqlalchemy.org/docs/orm/session.html#joining-a-session-into-an-external-transaction ), or other particularconstructor options, are discarded.



> Shouldn't it be semantically identical only overrided for the contextualstuff?

this would be scoped_session.close().  The distinction is discussed to some degree at http://www.sqlalchemy.org/docs/orm/session.html#lifespan-of-a-contextual-session .



>
> --
> You received this message because you aresubscribed to the Google Groups "sqlalchemy" group.
> To post to this group, send email to sqlal...@googlegroups.com.
> To unsubscribe from this group, send emailto sqlalchemy+...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.

--
You received this message because you are subscribed to the Google Groups"sqlalchemy" group.
To post to this group, send email to sqlal...@googlegroups.com.
To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.



====================================================================================

How to disable SQLAlchemy caching?

Following what we commented in How to close sqlalchemy connection in MySQL, I am checking the connections that SQLAlchemy creates into my database and I cannot manage to close them without exiting from Python.

If I run this code in a python console, it keeps the session opened until I exit from python:

from sqlalchemy.orm import sessionmakerfrom models import OneTable, get_engine

engine = get_engine(database="mydb")session = sessionmaker(bind=engine)()results = session.query(OneTable.company_name).all()# some work with the data #session.close()

and the only workaround I found to close it is to call engine.dispose() at the end.

As per the comments in the link I gave above, my question are now:

  • Why is engine.dispose() necessary to close sessions?

  • Doesn't session.close() suffice?




There's a central confusion here over the word "session". I'm not sure here, but it appears like you may be confusing the SQLAlchemy Session with a MySQL @@session, which refers to the scope of when you first make a connection to MySQL and when you disconnect.

These two concepts are not the same. A SQLAlchemy Session generally represents the scope ofone or more transactions, upon a particular database connection.

Therefore, the answer to your question as literally asked, is to call session.close(), that is, "how to properly close a SQLAlchemy session".

However, the rest of your question indicates you'd like some functionality whereby when a particular Session is closed, you'd like the actual DBAPI connection to be closed as well.

What this basically means is that you wish to disable connection pooling. Which as other answers mention, easy enough, use NullPool


This completely made it. By you pointing it I noticed I was mixing the SQLAlchemy and MySQL sessions. Now with get_engine(database="mydb", poolclass=NullPool) I get it closed once I session.close(). Thanks a lot! –  fedorqui Feb 13 '14 at 16:56