Flask inherited table classes in several identical databases using __bind_key__

Question

I am trying to create an admin control panel that integrates 4 different e-commerce sites. All sites have identical database structures (all MySQL).

What is going wrong?

I get 404 Not Found on any order ID and site I insert. No matter how I mix it, I can’t get any record. Always 404, and I have no idea why. I'm here.

Code

I tried to do this by creating base model classes for each table. Then, inherited clans of these base classes are created with a different binding key, depending on the database for which it is intended. This is a generalized kind of code - if you need more than that, let me know:

basemodels.py

MyOrderClass(db.Model): __tablename__ = 'messytablename' id = db.Column('order_id', db.Integer, primary_key=True) order_total = db.Column(db.Float) order_status = db.Column(db.String(1)) 

site2models.py

 class Site2Order(MyOrderClass): __bind_key__ = 'site2' 

__ __ INIT. RU

 app = Flask(__name__) app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql://user: pass@localhost /site' app.config['SQLALCHEMY_BINDS'] = { 'site1':'mysql://user: pass@localhost /site1', 'site2':'mysql://user: pass@localhost /site2', 'site3':'mysql://user: pass@localhost /site3', 'site4':'mysql://user: pass@localhost /site4' } 

views.py

 @app.route('/order/<site>/<orderid>') def show_order(site, orderid): if site == 'site1': orderObject = Site1Order if site == 'site2': orderObject = Site2Order if site == 'site3': orderObject = Site3Order if site == 'site4': orderObject = Site4Order order = orderObject.query.get(orderid) return render_template('order.html', order=order) 

Source sites are built in PHP and have less neat structures and naming conventions.

Thank you for your time.

Answer 1

Currently, the problem with SQLALCHEMY_BINDS is that it is used only for operations like create_all () or drop_all () - for this you need to change the session binding:

 db.session.bind = db.get_engine(app, 'site2') 

Everything works there to change this, but not in the trunk yet.

Your code might look like this:

 db.session.bind = db.get_engine(app, orderObject.__bind_key__) order = orderObject.query.get(orderid) 

But remember that this modifies the global session and does not reset it, you need to do it yourself or write a contextmanager so that you can use the with statement to do this.

If your models are identical across all databases, it could also be a way to have only one class for all databases, leave bind_key and request them with a special session object.

Edit : With Flask-SQLAlchemy 0.15 Release, you can set up a simple MyModel.query.filter (...) for different databases if you correctly defined __bind_key__.