flask, registering the same blueprint multiple times with different database connections, same model

user_8709023 0
Viewed 275

I have the databases foobar_pt, foobar_br, foobar_mx and so on, they all have the same model.

On the other hand, I created one blueprint using the (same) model, and I want to do something like:

class Product(db.Model):
    __tablename__ = 'product'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(10), nullable=False)

api = Blueprint('api', __name__)

@api.route('/products')
def product_get():
    ...
    result = Product.all()
    ...
    return jsonify(result)

and here is the problem:

# this blueprint should use the foobar_pt DB
app.register_blueprint(api, url_prefix='/pt')

# this blueprint should use the foobar_br DB
app.register_blueprint(api, url_prefix='/br')

# this blueprint should use the foobar_mx DB
app.register_blueprint(api, url_prefix='/mx')

...
[more countries]

the idea is to have an API like:

# get the products from PT
https://www.example.com/pt/products

# get the products from BR
https://www.example.com/br/products

# get the products from MX
https://www.example.com/mx/products

...
[more countries]

the model is exactly the same, the only thing that changes is the database name

Is there any way to do that?

flask-sqlalchemy flask sqlalchemy python
1 Answers

You can try to use SQLALCHEMY_BINDS + create_scoped_session(). Here is an example:

from flask import Flask, Blueprint, jsonify, request, g
from flask_restful import Api
from flask_sqlalchemy import SQLAlchemy

DB1 = 'br'
DB2 = 'mx'
app = Flask(__name__)
api = Api(app)
app.config['SQLALCHEMY_BINDS'] = {
    DB1: 'sqlite:////tmp/br.db',
    DB2: 'sqlite:////tmp/mx.db',
}
db = SQLAlchemy(app)


class Product(db.Model):
    __tablename__ = 'product'

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(10), nullable=False)


def get_session(name: str):
    return db.create_scoped_session(
        options=dict(bind=db.get_engine(app, name),
                     binds={}))


# prepare structure & data in different db
for name_ in [DB1, DB2]:
    session_ = get_session(name_)
    db.Model.metadata.create_all(session_.bind.engine)
    for i in range(10):
        session_.add(Product(name='{}-{}'.format(name_, i)))
    session_.commit()


# register all routes for each db...
for name_ in app.config['SQLALCHEMY_BINDS'].keys():
    bp = Blueprint(name_, __name__, url_prefix='/' + name_)

    @bp.before_request
    def before_request():
        # select session by blueprint name(connection name)
        g.session = get_session(request.blueprint)

    @bp.route('/products')
    def products():
        session = g.session
        return jsonify({
            'db': session.bind.engine.url.database,
            'products': [r.name for r in session.query(Product).all()],
        })

    app.register_blueprint(bp)

Run server. Open /mx/products and /br/products

user_49296460
Related