Using multiple database with same model in flask-sqlalchemy

user_2929701 0
Viewed 6752

So I have multiple database. DB_US and DB_UK. They have the exact same structure but they hold data for different countries. I have an api with flask_restful. The endpoint accept a country to retrieve the data of this specific country.

My problem is I can't figure out how to select the database per request. I've read the bind method, but you have to specify a __bind_key per model and all my model are in both databases.

I've tried to initialize the database within the @before_request, but you can't call the init_db method (or any setup method) after the first request has been handled.

This is a particular case because the database are identical so it seems like it would make sense to have it in the same database, but it's part of a larger system and needs to be that way.

How could I select the database on a per request basis?

flask-restful flask-sqlalchemy flask mysql python
2 Answers

I had the same problem. I've described it here Using multiple POSTGRES databases and schemas with the same Flask-SQLAlchemy model

Your cases is a little easier, because you don't have to deal with the schema differences. You could do what stamaimer suggested, but the problem is created new User models for each country (and if you have more than 2, it just gets messy).

To fix it I stopped using the flask-sqlAlchemy db instantiation method and let the content provider init method handle which db to choose:

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

Session = sessionmaker()

class ContentProvider():

    db = None
    connection = None
    session = None

    def __init__(self, center):
        if center == UK:
            self.db = create_engine('sqlite://UK', echo=True, pool_threadlocal=True)
            self.connection = self.db.connect()
        elif center == US:
            self.db = create_engine('sqlite://US', echo=True, pool_threadlocal=True)
            self.connection = self.db.connect()
        self.session = Session(bind=self.connection)

    def index(country):
        user = models[country]["User"](username=country + str(time.time()))
        self.session.add(user)
        self.session.commit()
        return country, "added"
user_16422000
Related