flask-sqlalchemy

flask-sqlalchemy copied to clipboard

I would like to be able to specify different SQLALCHEMY_ENGINE_OPTIONS for every bind in the SQLALCHEMY_BINDS dict.

It would be nice if SQLALCHEMY_DATABASE_URI + SQLALCHEMY_ENGINE_OPTIONS would form a configuration pair, and SQLALCHEMY_BINDS + SQLALCHEMY_BINDS_ENGINE_OPTIONS.

This would greatly help to set database-specific options when using multiple different types of databases (e.g. MySQL + SQLite + MSSQL)

Did you find a work around to pass different SQLALCHEMY_ENGINE_OPTIONS for each of the databases defined in SQLALCHEMY_BINDS?

I ended up resolving the issue by overriding apply_driver_hacks:

class SQLiteAlchemy(SQLAlchemy):
    def apply_driver_hacks(self, app, info, options):
        options.update({
            'isolation_level': 'AUTOCOMMIT', 
            'encoding': 'latin1', 
            'echo': True
        })
        super(SQLiteAlchemy, self).apply_driver_hacks(app, info, options)

db = SQLiteAlchemy(app)

https://stackoverflow.com/questions/33089144/flask-sqlalchemy-setup-engine-configuration?answertab=active#tab-top

you can define different engine options for different binds overwriting the apply_driver_hacks and define the options for each of your databases. Forexample, if you want to define different pool classes for different databases:

app.config['SQLALCHEMY_DATABASE_URI'] = "monetdb://..//.."
app.config['SQLALCHEMY_BINDS '] = {
    'pg':       'postgres+psycopg2://..//..'
}
app.config['POOL_CLASS'] = {'monetdb' : StaticPool , "postgres+psycopg2" : QueuePool}


class MySQLAlchemy(SQLAlchemy):
    def apply_driver_hacks(self, app, info, options):
        super().apply_driver_hacks(app, info, options)
        try:
            options['poolclass'] = app.config['POOL_CLASS'][info.drivername]
        except KeyError: #if the pool class is not defined it will be ignored, means that it will use the default option
            pass

db = MySQLAlchemy(app)

I find a way out (on flask-sqlalchemy v2.5.1)

# MySQLA.py
from flask_sqlalchemy import SQLAlchemy, _EngineConnector

class MySQLA(SQLAlchemy):
    def make_connector(self, app=None, bind=None):
        """Creates the connector for a given state and bind."""
        return _MyEngineConnector(self, self.get_app(app), bind)

class _MyEngineConnector(_EngineConnector):
    def get_options(self, sa_url, echo):
        options = {}

        options = self._sa.apply_pool_defaults(self._app, options)
        sa_url, options = self._sa.apply_driver_hacks(self._app, sa_url, options)

        if echo:
            options['echo'] = echo

        # Give the config options set by a developer explicitly priority
        # over decisions FSA makes.
        if self._bind is None: # for SQLALCHEMY_DATABASE_URI
            options.update(self._app.config['SQLALCHEMY_ENGINE_OPTIONS'])
        else: # for SQLALCHEMY_BINDS
            try:
                options.update(self._app.config['SQLALCHEMY_BINDS_ENGINE_OPTIONS'][self._bind])
            except KeyError:
                pass
        # Give options set in SQLAlchemy.__init__() ultimate priority
        options.update(self._sa._engine_options)

        return sa_url, options

db = MySQLA()

# config.py
SQLALCHEMY_DATABASE_URI = "sqlite:///app.db")
SQLALCHEMY_ENGINE_OPTIONS = {
    'pool_size': 1
}
SQLALCHEMY_BINDS = {
    'db1': 'mysql://...'
    'db2': 'mysql://...'
}
SQLALCHEMY_BINDS_ENGINE_OPTIONS = {
    'db1': {'pool_size': 0}, # engine_options for 'db1' 
    'db2': {'pool_size': 0} # engine_options for 'db2' 
}

Fixed in #1087. SQLALCHEMY_ENGINE_OPTIONS only affects the default bind SQLALCHEMY_DATABASE_URI. Values in SQLALCHEMY_BINDS can be a dict with engine options for that bind, including the "url" key instead of only the connection string.