skip to Main Content

Sqlalchemy creates a new connection whenever you call create_engine. If you have multiple databases on the same instance, this gets tricky because Postgres can only support so many active connections.

I have a topology where my backend needs to access different databases on the same database instance. That id for a Postgres URI postgresql://[ userspec @][ hostspec ][/ dbname ], userspec and hostspec are always the same but dbname can change. I’d like for Sqlalchemy to re-use the same engine connection since it’s hitting the same host even if it’s going to different databases. Any ideas?

2

Answers


  1. Create a shared connection pool with a common user and host information using create_engine in SQLAlchemy to reuse the same connection for several databases on the same PostgreSQL instance. Then, access various databases by specifying the database name in your SQL queries. Keep in mind to appropriately handle connections by opening and closing them as necessary.

    Login or Signup to reply.
  2. Sqlalchemy creates a new connection whenever you call create_engine.

    Not really. As @snakecharmerb mentions in a comment to the question, create_engine() creates a connection pool but no actual connections are created until they are needed, e.g., by calling .execute(), .execute_driver_sql(), or similar.

    I’d like for Sqlalchemy to re-use the same engine connection since it’s hitting the same host even if it’s going to different databases.

    That’s not a particularly good idea. An Engine is more than just a connection (or pool of connections), and the expectation is that the engine is associated with a particular database. If you want to limit the number of actual database (DBAPI) connections to the server while maintaining separate engines for each database, you can

    Take care when managing SQLAlchemy connections

    In particular, use a context manager to ensure that the connection is released back to the pool when you are done with it.

    with engine.begin() as conn:
        conn.execute(…)
    # conn is released back to the pool when the context manager exits
    

    Limit the size of each connection pool

    Limiting the size of the connection pools will help prevent your app from using up all of the server connections, and will help identify situations where your app might be leaking connections (if you exhaust the pool).

    Periodically close inactive connections

    engine.pool.dispose()
    

    will close any checked-in database connections in the pool. That won’t help if your app is leaking connections, but it will prevent idle connections from "sitting around" on the server.

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search