I am having trouble figuring out SQLalchemy--I switched from flask-SQLalchemy to SQLalchemy for some more flexibility--but I may just get rid of the SQLalchemy wrapper altogether if I can't figure this out.
I am using the declarative pattern from this guide: http://flask.pocoo.org/docs/0.10/patterns/sqlalchemy/
Initapp.py
#main app from flask import Flask from flask.ext import restful from flask_s3 import FlaskS3 import os from sqlalchemy import create_engine, event from sqlalchemy.orm import scoped_session, sessionmaker from sqlalchemy.ext.declarative import declarative_base ''' import logging logging.basicConfig() logging.getLogger('sqlalchemy.engine').setLevel(logging.INFO) ''' app = Flask(__name__) def my_on_checkout(dbapi_conn, connection_rec, connection_proxy): print "checkout",dbapi_conn def my_on_checkin(dbapi_connection, connection_record): print "checkin",dbapi_connection #database engine = create_engine("postgres://localhost:5432/schmoozeedb", convert_unicode=True, pool_size=20, max_overflow=0, echo=False) db_session = scoped_session(sessionmaker(autocommit=False, autoflush=False, bind=engine)) # for just 1 load of our app, number of checkouts from the engine pool does not equal number of checkins -- are things not getting # returned to our connection pool? event.listen(engine, 'checkout', my_on_checkout) event.listen(engine, 'checkin', my_on_checkin) Base = declarative_base() Base.query = db_session.query_property() def init_db(): # import all modules here that might define models so that # they will be registered properly on the metadata. Otherwise # you will have to import them first before calling init_db() import models Base.metadata.create_all(bind=engine) Webapp.py
@app.route('/demo2/<user_email>/<zip_code>', methods=['GET', 'POST']) def demo2(user_email=None, zip_code=None): # do some stuff which interacts with a db then render a template # the template starts polling the server until polling is complete return render_template('cardangular2.html', ssId = ssId, data = rlayer.hgetall(ssId)) # this is how I am closing the sessions. @app.teardown_appcontext def shutdown_session(exception=None): print 'closing session' db_session.remove() Logs
from initapp.py I have the event listeners and here is what I am noticing and here is my problem: There are 5 checkouts from the connection pool and only 3 checkins even after the polling is complete and the page is no longer interacting with the server. Just fyi, the /canvaslocal2/update is just the poller, it finished after 5 polls in this instance.
checkout <connection object at 0x108c192b0; dsn: 'dbname=schmoozeedb host=localhost port=5432', closed: 0> checkout <connection object at 0x108c19770; dsn: 'dbname=schmoozeedb host=localhost port=5432', closed: 0> checkout <connection object at 0x108c198a0; dsn: 'dbname=schmoozeedb host=localhost port=5432', closed: 0> checkout <connection object at 0x108c19640; dsn: 'dbname=schmoozeedb host=localhost port=5432', closed: 0> checkin <connection object at 0x108c192b0; dsn: 'dbname=schmoozeedb host=localhost port=5432', closed: 0> 127.0.0.1 - - [17/Feb/2015 14:31:23] "GET /demo2 HTTP/1.1" 200 - checkout <connection object at 0x108c192b0; dsn: 'dbname=schmoozeedb host=localhost port=5432', closed: 0> checkin <connection object at 0x108c198a0; dsn: 'dbname=schmoozeedb host=localhost port=5432', closed: 0> checkin <connection object at 0x108c19770; dsn: 'dbname=schmoozeedb host=localhost port=5432', closed: 0> storeFeedWrapper: 0.352962970734 s closing session 127.0.0.1 - - [17/Feb/2015 14:31:23] "POST /canvaslocal2/update HTTP/1.1" 200 - storeFeedWrapper: 0.373705148697 s storeFeedWrapper: 0.541649103165 s closing session 127.0.0.1 - - [17/Feb/2015 14:31:24] "POST /canvaslocal2/update HTTP/1.1" 200 - closing session 127.0.0.1 - - [17/Feb/2015 14:31:25] "POST /canvaslocal2/update HTTP/1.1" 200 - storeFeedWrapper: 2.3683412075 s closing session 127.0.0.1 - - [17/Feb/2015 14:31:26] "POST /canvaslocal2/update HTTP/1.1" 200 - storeFeedWrapper: 3.85505199432 s storeFeedWrapper: 4.00069713593 s aggAllFeeds total operation: 4.00373697281 s aggAllFeeds: 4.00382304192 s closing session 127.0.0.1 - - [17/Feb/2015 14:31:27] "POST /canvaslocal2/update HTTP/1.1" 200 - Closing
When I stop my Server (ctrl+C):
checkin <connection object at 0x108c192b0; dsn: 'dbname=schmoozeedb host=localhost port=5432', closed: 0> checkin <connection object at 0x108c19640; dsn: 'dbname=schmoozeedb host=localhost port=5432', closed: 0> The remaining connections check themselves back in.
I am not a db or SQLalchemy expert--anyone have any idea? I have a test class where I make 50 requests to /demo2 in webapp. And because of this problem where there is some sort of checkin leakage, I can't pass the test.
