On Dec 21, 2010, at 5:03 AM, Franck Vonbau wrote:
> Dear all,
>
> I'm starting a web project based on Python, and I've decided to rely on
> web.py and SQLAlchemy.
> My DB is for the moment a SQLite database, and I'm trying to figure out how
> the framework deals with sessions / transactions. Your help would be greatly
> appreciated !
>
> Here's how I declare my global Session :
>
> engine = create_engine('sqlite:///.....', echo = True,
> listeners=[MyListener()])
> Session = scoped_session(sessionmaker(bind=engine))
>
> Q1) The documentation recommends to use that kind of scoped_session for a web
> project : why, actually ? What if I use "regular" sessions ? What would
> happen in that case if different users of my site would get their own
> sessions ?
the scoped_session is a "regular" session, it just places them into a thread
local registry. This so that distinct web requests running in separate
threads each get a Session object dedicated to their local scope of work, while
allowing a global "Session" object that can be accessed from everywhere without
the need to explicitly pass it to all functions and methods.
>
> I've made a simple test : I declare 2 actions, 'add' and 'commit'.
> 'add' deliberately doesn't commit the Session because I want to understand
> the behavior of the framework.
>
> class Add:
>
> def GET(self, name=None):
>
> print Session.connection()
> print engine.pool.__dict__
> for conn in engine.pool._all_conns:
> print "[CONN] %s" %conn
>
> if prenom is not None :
> user = User(name)
> Session.add(user)
> # No commit here
>
> class Commit:
>
> def GET(self):
>
> Session.commit()
>
>
> I ran several tests : I triggered the 'Add' action from different browsers,
> adding 'Jack' then 'John'....
> Sometimes 'Jack' appears in Session.new, sometimes not.
> Sometimes Session.new is empty, sometimes not.
> It seems to depend on the underlying connection of the session.
I would imagine that web.py is using a pool of threads. As each request
enters, a thread is chosen randomly from the pool. If the thread is the one
that you happened to use previously for a particular operation, its previous
state is still left around. Its for this reason the documentation recommends
closing out sessions at the end of each request, see
http://www.sqlalchemy.org/docs/orm/session.html#lifespan-of-a-contextual-session
.
>
> Q2) Why's that ? Shouldn't the session be somehow "global" ? Why doesn't it
> encapsulate all my users ?
scoped_session provides a Session object that is local per thread. You
wouldn't want to use a single Session for all web requests, as the Session
represents a single transaction, a collection of objects and state local to a
particular operation - your web request. The metaphor used by the tutorial is
that having one session for all requests is like throwing a banquet and having
all your guests eat from the same plate.
>
> Q3) Moreover, what happens to the connections I open when I add a user
> without committing ?
if you leave the Session hanging open, it holds onto the connection/transaction
resources it has established.
> Why are they recycled by the framework on the next call ? My listeners
> indicates they're never checked-in (except on commit time)
you must explicitly close out the session using rollback(), commit(), or
close(), or with scoped_session you can also use remove(). web.py should have
some kind of hook so that this operation can be automated.
>
> Q4) When I run the "commit" action, it seems to randomly pick a connection
> and flush the objects. Why's that ?
again there's nothing "random" going on in the Session, this is probably a
function of your test which is choosing different sessions based on which
thread web.py is picking. Just a guess.
>
> Besides , I'm trying to restrict pool_size to 1 in the engine.
>
> Q5) It does not seem to change anything : more and more connections are open
> when I run "add" multiple times. How could I actually restrict the pool size ?
the pool limits to 15 connections total by default, per engine. If you are
seeing more than 15 connections simultaneously, this would correspond to more
than one Engine being created, or subprocesses being created perhaps. I
haven't used web.py so perhaps they have some guidelines on ORM integration.
The configuration of the pool itself is controlled by the "pool_size" and
"max_overflow" parameters, see
http://www.sqlalchemy.org/docs/core/pooling.html#connection-pool-configuration .
>
> Q6) What is the best strategy when using scoped_session in a web project ?
> Forgetting a commit seems to have very dangerous consequences (delayed flush).
the guidelines at
http://www.sqlalchemy.org/docs/orm/session.html#lifespan-of-a-contextual-session
should be followed, so that the lifespan of the Session local to a thread is
linked to that of the web request.
>
> Thanks very much for your help !
> Franck
>
>
>
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To post to this group, send email to sqlalch...@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.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@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.
