Flask-Sqlalchemy dynamic bind

Question

I have the following config.py:

SQLALCHEMY_DATABASE_URI = os.environ.get('DATABASE_URL') or 'sqlite:///' + os.path.join(basedir, 'master.db')
SQLALCHEMY_BINDS = {
    'project0':        'sqlite:///' + os.path.join(basedir, 'project0.db')
    'project1':        'sqlite:///' + os.path.join(basedir, 'project1.db')
    'project2':        'sqlite:///' + os.path.join(basedir, 'project2.db')

    }

I need my user to select one of the project db at login. I would like to store this choice in some variable and pass it to the model class.

class Punchlist(db.Model,choice):
    __bind_key__ = choice

The db object will be initialised with the default db in __init.py:

app.config.from_object(Config)
db = SQLAlchemy(app)

Which is the optimal way to associate this choice to the user?

Did you get round to figuring this out? I'm also trying to do something similar! — HaDoMin, Apr 22 '21 at 15:17

score 0 · Answer 1 · answered Apr 23 '21 at 12:12

You could create a base class contains the bind_key variable. For example:

class Base(db.Model):
    __abstract__ = True

    def __init__(self, **kwargs):
        self.bind_key = kwargs.pop('bind_key') if kwargs.get('bind_key') else 'project0'
        db.Model.metadata.tables[self.__tablename__].info['bind_key'] = self.bind_key
        super(Base, self).__init__(**kwargs)

Derived class

class PunchList(Base):
   __tablename__ = 'Punchlist'

Initialise PunchList class

punch_list_0 = PunchList()
punch_list_1 = PunchList(bind_key='project1')
punch_list_2 = PunchList(bind_key='project2')

Flask-Sqlalchemy dynamic bind

1 Answers1