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 · Accepted Answer · 2016-03-03 15:18:27Z

4

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.

answered Mar 3, 2016 at 15:18

jumbopap

4,1575 gold badges30 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

theo_ni Over a year ago

Hi jumbopap and thanks for the answer. this app will be used in different sites to connect to some servers and get some data. this app will also be used by several persons to look at the data(1 db) and compare with other data (other db). it would be easier just to copy the db file and put it on my db directory and the app would work. I could end up with 100 db files.

theo_ni Over a year ago

I'm new to SQLAlchemy and do not see exactly the advantage of using Flask-SQLAlchemy but I'm sure I want to use the paginate from it. I'm not sure I can use it with your approach right?

jumbopap Over a year ago

Everything you are saying can be done easier in a single DB file using relational schema, but your call. Plain SQLAlchemy doesn't come with pagination, but you can easily add the class in from the Flask-SQLAlchemy source.

theo_ni Over a year ago

ok, thanks. From other questions I found different ways to do it with plain SQLAlchemy but I want to know if it's possible to do it with Flask-SQLAlchemy and binds because I plan to use other packages that integrates with Flask-SQLAlchemy like Flask-Migrate and Flask-Marshmallow.

kta Over a year ago

it looks like supporting multi-tenants

Collectives™ on Stack Overflow

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

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related