How to reduce number of connections using SQLAlchemy + postgreSQL?

Question

I'm developing on heroku using their Postgres add-on with the Dev plan, which has a connection limit of 20. I'm new to python and this may be trivial, but I find it difficult to abstract the database connection without causing OperationalError: (OperationalError) FATAL: too many connections for role.

Currently I have databeam.py:

import os
from flask import Flask
from flask.ext.sqlalchemy import SQLAlchemy
from settings import databaseSettings

class Db(object):
    def __init__(self):
        self.app = Flask(__name__)
        self.app.config.from_object(__name__)
        self.app.config['SQLALCHEMY_DATABASE_URI'] = os.environ.get('DATABASE_URL', databaseSettings())
        self.db = SQLAlchemy(self.app)

db = Db()

And when I'm creating a controller for a page, I do this:

import databeam

db = databeam.db
locations = databeam.locations

templateVars = db.db.session.query(locations).filter(locations.parent == 0).order_by(locations.order.asc()).all()

This does produce what I want, but slowly and at times causes the error metioned above. Since I come from a php background I have a certain mindset of how to deal with DB connections (I.e. like the example above), but I fear it doesn't fit well with python.

What is the proper way of abstracting the db connection in one place and then just using the same connection in all imports?

CraigKerstiens · Accepted Answer · 2013-03-19 18:17:02Z

15

Within SQL Alchemy you should be able to create a connection pool. This pool is what the pool size would be for each Dyno. On the Dev and Basic plan since you could have up to 20, you could set this at 20 if you run 1 dyno, 10 if you run 2, etc. To configure your pool you can setup the engine:

engine = create_engine('postgresql://me@localhost/mydb',
                   pool_size=20, max_overflow=0)

This sets up your db engine with a pool which you pull from automatically then. You can also configure the pool manually, more details on that can be found on the pooling guide of SQL Alchemy - http://docs.sqlalchemy.org/en/latest/core/pooling.html

answered Mar 19, 2013 at 18:17

CraigKerstiens

5,9891 gold badge27 silver badges29 bronze badges

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

1 Comment

Joe Over a year ago

I know this is an old answer, but I'm having trouble finding this detail and I think you can help. If you set the pool size to 20, doesn't that mean the dyno can only handle 20 simultaneous requests (since each request uses the database, and therefore takes a connection from the pool)? This seems like a huge bottleneck, so surely I'm misunderstanding something. Help!

Collectives™ on Stack Overflow

How to reduce number of connections using SQLAlchemy + postgreSQL?

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related