SQLAlchemy Engine cleanup

[Anoop K]

It seems engine created using create_engine is not freed after cleanup of a session. Object graph indicates that event.listen for pool is holding reference to engine even after session is garbage collected.

What is the right way/api to delete an engine ?

UseCase

As there are lot of databases in multiple boxes whose ip/user/password can change I would like to destroy session and engine after use. Cost of creating/closing connection can be ignored for my use case.

[Michael Bayer]

engines can be disposed using engine.dispose() to close connections referred to by the pool, and then removing all references to that engine.

if you aren't concerned about connection pooling (which seems to be the case here if you're looking to dispose engines regularly) then just use NullPool with create_engine(..., pool_class=NullPool).   Then dispose() isn't really needed, just lose references to the engine.

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/bTtgyK4eAy4J.
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.

[Anoop K]

I tried session.bind.dispose() and NullPool. But engine is still not cleared. It still seems to have some reference from SessionEventsDispatch => sqlalchemy.event._DispatchDescriptor.

[Michael Bayer]

this is all about whatever you've done specifically.  If you have the Session instance still around, where my_session.bind still points to your Engine, then that Engine is still in memory - you're holding a reference to it.  Similarly, if you've assigned an event to the Session class whose callable refers to the Engine in question, then the Engine is still referenced as well, such as:

def make_evt(engine):

    @event.listens_for(Session, "before_flush")

    def go(session, ctx):

        # do something with engine

    

make_evt(my_engine)

the above code will associate "my_engine" with a class-level Session listener and cannot be dereferenced without unloading all of sqlalchemy.orm.

dispose() and NullPool have *nothing to do* with the Engine itself, only the connections inside the pool contained within.   Your Engine will remain present as long as its reachable via references, just like any other Python object.

To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/xtb_18kV1ZUJ.

[Anoop K]

I  am not holding engine or using event.listen anywhere. Code is as simple as

def create_sn(url):

  engine = create_engine(url)

  return sessionmaker(bind=engine)(expire_on_commit=False)

sn = create_sn(url)

try:

   sn...

finally:

  sn.close()

  sn.bind.dispose()

In engine/strategies.py DefaultEngineStrategy => create

I see 

event.listen(pool, 'first_connect', ...)

event.listen(pool, 'connect', ...)

But didn't find an equivalent event.remove ??

Anoop

[Michael Bayer]

On Jan 11, 2013, at 12:42 AM, Anoop K wrote:

I  am not holding engine or using event.listen anywhere. Code is as simple as

def create_sn(url):

  engine = create_engine(url)

  return sessionmaker(bind=engine)(expire_on_commit=False)

sn = create_sn(url)

try:

   sn...

finally:

  sn.close()

  sn.bind.dispose()

In engine/strategies.py DefaultEngineStrategy => create

I see 

event.listen(pool, 'first_connect', ...)

event.listen(pool, 'connect', ...)

But didn't find an equivalent event.remove ??

there's no remove for events implemented right now.   When the host object of an event is garbage collected, so are its listeners that are not referred to elsewhere.

Here is a test, confirms the Engine is gone using your code sample:

from sqlalchemy import create_engine

from sqlalchemy.orm import sessionmaker

import weakref

import gc

the_weak_ref = None

engine_is_removed = False

def engine_removed(ref):

    global engine_is_removed

    engine_is_removed = True

    print "Engine was GCed!"

def create_sn(url):

    global the_weak_ref

    engine = create_engine(url)

    the_weak_ref = weakref.ref(engine, engine_removed)

    return sessionmaker(bind=engine)(expire_on_commit=False)

sn = create_sn("sqlite://")

sn.execute("select 1")

sn.close()

print "about to delete sn..."

del sn

print "about to gc.collect()..."

gc.collect()   # this is always needed to make sure cycles are cleared in python

assert engine_is_removed

output (using CPython, results will differ with pypy):

about to delete sn...

about to gc.collect()...

Engine was GCed!

To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/N65z6tSD6IsJ.

[Anoop K]

I tried the code in my setup.(SQLAlchemy-0.7.8-py2.6). Looks like engine did not got GCed.

[anoop@localhost tmp]$ p engtest.py 

about to delete sn...

about to gc.collect()...

Traceback (most recent call last):

  File "engtest.py", line 31, in <module>

    assert engine_is_removed

AssertionError

[Michael Bayer]

there's some side effect occurring as a result of how 0.7 creates a new subclass when using sessionmaker.  Since you don't need a sessionmaker here, please use this form:

return Session(bind=engine, expire_on_commit=False)

or upgrade to 0.8.0b2.

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.

To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/RWBNo1sT1ogJ.

[Anoop K]

Great ...

Session(bind=engine, expire_on_commit=False) fixed the problem.

Looks like 0.8.0b2 is BETA. Is it OK to use in production ?

Thanks

Anoop

[Michael Bayer]

the sessionmaker() object is not GC-able in 0.7 right now due to event mechanics.   This issue does not exist in 0.8.   I'll add a note.

[Michael Bayer]

its beta very close to release

To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/W9fqz6mAypMJ.

[Michael Bayer]

the event mechanics issue applies to 0.7 and 0.8.   while the engine will be gc'ed in 0.8, the sessionmaker itself, if used, will still create an anonymous subclass that is not cleared.   creating many ad-hoc sessionmaker() objects, while this was not its intended use, will cause memory to grow.

the patch at http://www.sqlalchemy.org/trac/attachment/ticket/2650/2650.patch resolves.

[Anoop K]

OK.

So does Session(bind=engine, expire_on_commit=False)  usage always guarantee that engine and all other associated objects are cleared on doing a session.close() + engine.dispose().

[Michael Bayer]

there's no class level GC cycle when dealing with the Session object, no, this bug is local to the ad-hoc subclass mechanism of sessionmaker().

To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/fAGeeGQfeRgJ.