Triggering connection pools with sqlalchemy in flask

Question

I am using Flask + SQLAlchemy (DB is Postgres) for my server, and am wondering how connection pooling happens. I know that it is enabled by default with a pool size of 5, but I don't know if my code triggers it.

Assuming I use the default flask SQLalchemy bridge :

db = SQLAlchemy(app)

And then use that object to place database calls like

db.session.query(......)

How does flask-sqlalchemy manage the connection pool behind the scene? Does it grab a new session every time I access db.session? When is this object returned to the pool (assuming I don't store it in a local variable)?

What is the correct pattern to write code to maximize concurrency + performance? If I access the DB multiple times in one serial method, is it a good idea to use db.session every time?

I was unable to find documentation on this manner, so I don't know what is happening behind the scene (the code works, but will it scale?)

Thanks!

I don't know how my server will respond under heavy load - will multiple concurrent requests coming in at the same time block? Will they be treated correctly? Will I be taking/returning objects to the pool more than I have to? Will the pool expand more than it has to? — Noam
– Noam, Commented Dec 11, 2014 at 14:12
I am using Flask-SQLAlchemy and get 70+ concurrent connection and it works just fine. — Johnston
– Johnston, Commented Dec 11, 2014 at 16:06
That doesn't necessarily answer how it works. I know that it does infact work. I believe ( could be mistaken ) that reddit use SQLAlchemy. — Johnston
– Johnston, Commented Dec 11, 2014 at 16:13
I think it uses SQLAlchemy's scoped_session, if you're referring on how SQLAlchemy handles sessions. — Leandro Poblet
– Leandro Poblet, Commented Dec 11, 2014 at 16:27

emeier · Accepted Answer · 2014-12-12 17:25:16Z

2

You can use event registration - http://docs.sqlalchemy.org/en/latest/core/event.html#event-registration There are many different event types that can be monitored, checkout, checkin, connect etc... - http://docs.sqlalchemy.org/en/latest/core/events.html

Here is a basic example from the docs on printing a when a new connection is established.

from sqlalchemy.event import listen
from sqlalchemy.pool import Pool

def my_on_connect(dbapi_con, connection_record):
    print "New DBAPI connection:", dbapi_con

listen(Pool, 'connect', my_on_connect)

answered Dec 12, 2014 at 17:25

emeier

1112 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Triggering connection pools with sqlalchemy in flask

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related