On Nov 15, 2008, at 4:53 PM, Randy Syring wrote:
> I went ahead and produced what I hope is a very narrow test case to > show that I am not explicitly holding onto connections (unless I > completely misunderstand, which is possible). Here is my test code: > > http://paste.pocoo.org/show/91285/ > Your Session is binding to the same thread local connection that your "contextual_connect()" method is returning, however while you are closing out the connection explicitly, you aren't closing the Session, which keeps the connection opened outside the boundaries of the WSGI method regardless of the type of pool and configuration in use. The Session is then garbage collected via asynchronous gc, the connection is returned to the pool, and the pool's attempt to rollback() the connection before returning to the pool raises the exception. The exception does not propagate outwards since it is during garbage collection. This is why the program keeps running without overall issue (except for your ISAPI plugin which probably cannot handle that kind of thing gracefully). The explicit connection as well as the "threadlocal" strategy are all unnecessary here. Configuring the sessionmaker() with a bind to a plain engine i.e. create_engine('sqlite:///mydb.sql'), and making sure sess.close() is called within the WSGI method are all that's needed. Pattern here is: Session = sessionmaker(bind=engine) sess = Session() try: < work with session> finally: sess.close() Alternatively, as I noted previously in http://www.sqlalchemy.org/docs/05/session.html#unitofwork_contextual_lifespan , using scoped_session in conjunction with Session.remove() at the end of the request works here as well, as I mentioned this is the practice that is standard among popular web frameworks such as Pylons. Pattern here is : Session = scoped_session(sessionmaker(bind=engine)) sess = Session() try: < work with session> finally: Session.remove() Still another way to do this is to eliminate the source of the error at the pool level - ultimately, the SingletonThreadPool is attempting to return the connection to the pool and call rollback() on it, which is why the threaded access fails. If you use NullPool, the connection is thrown away entirely when closed and nothing is done to it. Any version of your program will run without errors if NullPool is used - you'll just get a little overhead in opening more connections which in the case of file-based sqlite is extremely miniscule. In that case you can even reuse the same Session object repeatedly across requests as long as scoped_session is in place to enforce one-thread-per-session. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~----------~----~----~----~------~----~------~--~---
