On Jan 7, 2013, at 7:47 PM, Warwick Prince wrote:
>>
>> On Jan 7, 2013, at 7:20 PM, Diego Woitasen wrote:
>>
>>>
>>> I'm back to this :)
>>>
>>> My problem was that I'm not closing the session properly.
>>>
>>> My new question is... is there a way to autoclose the session? My example,
>>> at the end of the scope?
>>>
>>> What's the recommend way to do this?
>>>
>>> I've read the FAQ, but it's not very clear for me.
>>
>> you need to structure your application such that database operations occur
>> within a consistent framework.
>>
>> This is the simplest when using web frameworks, as all frameworks include
>> some kind of " request end" hook - that's where the close of the Session
>> would go.
>>
>> Looking at your example again, I see you have a "print delivery.name,
>> delivery.status" after you've done your commit(). That would be a likely
>> source for the new connection here, as accessing those attributes means the
>> Session needs to go back to the database post-commit to get their most
>> recent value.
>
> Hi Michael / Diego - I also have a couple of questions in this area, so I
> though I would jump on this thread.
>
> So, if you close down a Session with Session.close() is the Engine associated
> with that session (e.g. Session(bind=e) ) then freed up as far as the pool
> etc is concerned or is there something else I need to do to the Engine?
the Session is a user of an Engine. it does basically this:
connection = engine.connect() # check out from the pool
connection.close() # return to the pool
the "connection.close()" you see up there happens at three places:
- session.rollback()
- session.commit()
- session.close()
the Engine itself has a set of pooled connections, these are TCP/IP connections
to your database. These stay there until the pool and/or engine is disposed,
using engine.dispose().
However, the Engine wasn't really intended to be disposed() very often. The
vast majority of applications that talk to a database need to continue talking
to that DB through the lifespan of the app - hence the connection pool stays
open so it can quickly start new conversations.
if you want your app to not have any pooled connections when it's idle, then
just turn off pooling. Use NullPool. Engine.dispose() is not something you
typically need to call explicitly.
>
> If I have a result proxy and it simply gets garbage collected - is that
> 'freed' as far as SQLA is concerned or do I need to specifically do something
> to clean it up (other than exhausting it)?
a ResultProxy is an object returned by a Connection (which is the thing you get
from engine.connect()). This object refers to a DBAPI cursor also.
The ResultProxy holds onto that cursor/connection as you read results from it.
It then closes the connection/cursor when it has no more use for them.
The ResultProxy does this close when:
- all rows are exhausted - fetchall(), or enough calls to
fetchone()/fetchmany(), first(), scalar().
- immediately, if there are no rows. the result you get from insert(),
update(), delete() etc. assuming theres no RETURNING, that result is closed.
- in all cases when you call result.close().
- when the garbage collector collects it. but its bad form to rely
upon this. The only way you can get here is if the result returns rows, and
you don't read them all.
>
> The issue I'm having is that 'sometimes' my app gets handed a dead connection
> to the MySQL server which tends to make things unhappy. It is a home grown
> framework, however it is well structured and has clear Entry and Exit points
> for closing down any Session objects etc. *Somewhere* I am leaving something
> dangling and it's getting closed down by MySQL with a connection timeout.
there's a lot of ways to trace connection activity, there's echo_pool="debug",
there's the AssertionPool which can be helpful in some situations (it even
stores the stack trace where the connection was checked out, read its source
and perhaps emulate this approach for a more involved debugging approach),
there's a whole event system which you can use to make sure no Connection is
older than a certain time (put a timestamp in connection.info and check it), so
I'd look into these methods to figure out where this dead connection is coming
from.
> Finally, I'm using multi Session objects (potentially) within a threaded
> environment. i.e. Each thread may or may not have one or more Sessions
> created using sessionmaker(). (A second Session would typically be because of
> some nested requirement) This appears to work fine, or am I missing
> something?
its fine, just don't open up a Session then leave it hanging open.
> Each new Session is bound to the engine directly - and it *could* be a
> different Engine to other Sessions.
shouldn't matter...
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
