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

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?

CodePudding user response：

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