SQLAlchemy not closing sessions with ORM?
bottle, python, sqlalchemy
Solution
when you say `q = session.query(Link)`, that's a `Query` object that isn't executed yet. You say `session.close()` fine, but then that `Query` object gets passed to your template and you iterate through it, which opens a new transaction on that `Session` to emit the SQL.
in this case you'd want to either stick with calling `all()` before closing the `Session`, or more flexibly just leave the `Session` open until the template is finished rendering. Then the template can refer to lazy-loaded attributes as well.
Problem
I have a week end project with Bottle.py (0.10.11) and SQLAlchemy(0.7.9) using MySQL as a backend. I had a lot of "MySQL server has gone away" and drilled it down to the fact that some sessions would be left opened during the night while I was not using my program. Now I can see where the MySQL session is left open but I don't know how I should proceed. This is what I have in my router page web.py ``` [...] db = create_engine('mysql://USER:PASSWORD@DATABASE', poolclass=NullPool) session = scoped_session(sessionmaker(bind=db)) @route("/") links = session.query(Link) session.close() return bottle.template("index", links=links) ``` in my view index.tpl ``` [...] %for link in links: <div class="link"> <a class="link" href="{{link.url}}">{{link.title}}</a> %for tag in link.tags: <a href="/tag/{{tag.text}}" class="tag">{{tag.text}}</a> %end <a href="/edit/{{link.id}}">edit</a> </div> %end [...] ``` If I use `session.query(Link).all()` instead of `session.query(Link)` the MySQL sessions are closed properly, but I can't benefit from the ORM factor. How can I close all session ? What am I doing wrong ?