Databases
StackOverflow2013
Postsdbo
POHow do I make Flask SQLAlchemy reuse db connections?
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow do I make Flask SQLAlchemy reuse db connections?
    text
    copied!<p>I can't seem to get my Flask app to close or reuse DB connections. I'm using PostgreSQL 9.1.3 and</p> <pre><code>Flask==0.8 Flask-SQLAlchemy==0.16 psycopg2==2.4.5 </code></pre> <p>As my test suite runs the number of open connections climbs until it hits 20 (the <code>max_connections</code> setting in <code>postgresql.conf</code>), then I see:</p> <pre><code>OperationalError: (OperationalError) FATAL: sorry, too many clients already None None </code></pre> <p>I've reduced the code to the point where it's just calling <code>create_all</code> and <code>drop_all</code> (but not issuing any sql as there are no models). </p> <p>I see connections being checked in and out in the logs:</p> <pre><code>DEBUG:sqlalchemy.pool.QueuePool:Connection &lt;connection object at 0x101c1dff0; dsn: 'dbname=cx_test host=localhost', closed: 0&gt; checked out from pool DEBUG:sqlalchemy.pool.QueuePool:Connection &lt;connection object at 0x101c1dff0; dsn: 'dbname=cx_test host=localhost', closed: 0&gt; being returned to pool WARNING:root:impl &lt;-------- That's the test running DEBUG:sqlalchemy.pool.QueuePool:Connection &lt;connection object at 0x101c1dff0; dsn: 'dbname=cx_test host=localhost', closed: 0&gt; checked out from pool DEBUG:sqlalchemy.pool.QueuePool:Connection &lt;connection object at 0x101c1dff0; dsn: 'dbname=cx_test host=localhost', closed: 0&gt; being returned to pool </code></pre> <p>For each test run the address of the connection (the "connection object at xyz" part) is different. I suspect this has something to do with the problem, but I'm not sure how to investigate further. </p> <p>The code below reproduces the problem in a new venv:</p> <pre><code>from flask import Flask from flask.ext.sqlalchemy import SQLAlchemy from unittest import TestCase import logging logging.basicConfig(level=logging.DEBUG) logging.getLogger('sqlalchemy.pool').setLevel(logging.DEBUG) logging.getLogger('sqlalchemy.engine').setLevel(logging.DEBUG) logging.getLogger('sqlalchemy.dialects').setLevel(logging.DEBUG) logging.getLogger('sqlalchemy.orm').setLevel(logging.DEBUG) db = SQLAlchemy() def create_app(config=None): app = Flask(__name__) app.config.from_object(config) db.init_app(app) return app class AppTestCase(TestCase): SQLALCHEMY_DATABASE_URI = "postgresql://localhost/cx_test" TESTING = True def create_app(self): return create_app(self) def setUp(self): self.app = self.create_app() self.client = self.app.test_client() self._ctx = self.app.test_request_context() self._ctx.push() db.create_all() def tearDown(self): db.session.remove() db.drop_all() self._ctx.pop() class TestModel(AppTestCase): def impl(self): logging.warn("impl") pass def test_01(self): self.impl() def test_02(self): self.impl() def test_03(self): self.impl() def test_04(self): self.impl() def test_05(self): self.impl() def test_06(self): self.impl() def test_07(self): self.impl() def test_08(self): self.impl() def test_09(self): self.impl() def test_10(self): self.impl() def test_11(self): self.impl() def test_12(self): self.impl() def test_13(self): self.impl() def test_14(self): self.impl() def test_15(self): self.impl() def test_16(self): self.impl() def test_17(self): self.impl() def test_18(self): self.impl() def test_19(self): self.impl() if __name__ == "__main__": import unittest unittest.main() </code></pre> <p>This is the first time I've used app factories in flask, and I copied this code partly from the <a href="http://packages.python.org/Flask-SQLAlchemy/contexts.html" rel="noreferrer">Flask-SQLAlchemy docs</a>. <a href="http://packages.python.org/Flask-SQLAlchemy/api.html#flask.ext.sqlalchemy.SQLAlchemy.init_app" rel="noreferrer">Elseware</a> those docs mention that using a db in the wrong context will cause connections to leak - maybe I am doing the init incorrectly?</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload