How to manage user-specific database connections in a Pyramid Web Application?
The best way to do this that I know is to use the same database with multiple schemas. Unfortunately I don't think this works with MySQL. The idea is that you connection pool engines to the same database and then when you know what user is associated with the request you can switch schemas for that connection.
We are using Python Pyramid with SQLAlchemy and MySQL to build a web application. We would like to have user-specific database connections, so every web application user has their own database credentials. This is primarily for security reasons, so each user only has privileges for their own database content. We would also like to maintain the performance advantage of connection pooling. Is there a way we can setup a new engine at login time based on the users credentials, and reuse that engine for requests made by the same user?