Flask-Sqlalchemy multiple databases (binds) with the same model (class DB.Model)

Question

I'm a beginner with python/Flask/SQLAlchemy so sorry if my questions are dumb.

I want to create an API with Flask using Flask-SQLAlchemy as following:

one sqlite database for users/passwords

SQLALCHEMY_DATABASE_URI = 'sqlite:////path/to/users.db'

class User(DB.Model):
    __tablename__ = 'users'
    id = DB.Column(DB.Integer, primary_key=True)
    username = DB.Column(DB.String(64), index=True)
    password = DB.Column(DB.String(128))

Lets say I have multiple "customers" witch a user can create using

$ http POST http://localhost:5000/api/customers/ name=customer1

class Customer(DB.Model):
    __tablename__ = 'customer'
    customer_id = DB.Column(DB.Integer, primary_key=True)
    customer_name = DB.Column(DB.String, unique=True, index=True)

I need to create a separate sqlite file for each "customers" :

SQLALCHEMY_BINDS = {
    'customer1' = 'sqlite:////path/customer1.db',
    'customer2' = 'sqlite:////path/customer2.db',
    ...
}

My questions are:

I do not have fixed number of "customers" so I cannot create a model class for each and specify the "bind_key" for each. Is it possible to do this with Flask-SQLAlchemy or I need to use plain SQLAlchemy?
I have 3 "customers" in data/ as customer1.db, customer2.db and customer3.db.
I would start the application and create SQLALCHEMY_BINDS dictionary listing the files in data/ and then DB.create_all() on a request for a specific "customer" .
how can I bind to the right .db file using the Flask-SQLAlchemy DB.session?

I've read Using different binds in the same class in Flask-SQLAlchemy

jumbopap jumbopap · Accepted Answer · 2016-03-03T15:18:27

Why exactly do you want entirely separate DB files for each customer?

In any case this is easier with straight SQLAlchemy. You can create a getter function which returns a session pointing to your db file.

def get_session(customer_id):
    sqlite_url = 'sqlite:////path/customer%s.db' % customer_id
    engine = create_engine(sqlite_url)

    # initialize the db if it hasn't yet been initialized
    Base.metadata.create_all(engine)

    Session = sessionmaker(bind=engine)
    session = Session()

    return session

You can then use and close that session.

But without knowing your specific use case, it is difficult to understand why you would want to do this instead of just using a single SQLite database.

Flask-Sqlalchemy multiple databases (binds) with the same model (class DB.Model)

1 Answers