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
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.
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.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 canTake 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.
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
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.