I'm trying to build an admin control panel that brings together 4 different ecommerce sites. The sites all have identical database structures (all MySQL).

What's Going Wrong?

I get 404 Not Found on any order ID and site I put in. No matter how I mix it I can not get any record to come up. Always a 404 and I have no idea why. SO here I am.

Code

I tried to do this by creating base model classes of every table. Then creating inherited clases of those base classes with a different bind key dependent on the DB it is meant for. This is a summarised view of the code - if you'd need anymore than this 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__.py

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)

The original sites are built in PHP and have less than tidy structures and naming conventions.

Thank you for your time.

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

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

There's work ongoing to change that but it's not in the trunk yet.

Your code could be like:

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

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

If your models are identical over all databases this could also be the way to only have one class for all databases, leave the bind_key and query them with a special bind session object.

Edit: with the Flask-SQLAlchemy 0.15 Release a simple MyModel.query.filter(...) is possible for different databases if you defined __bind_key__ properly.

db.Model.metadata.tables['your model name'].info['bind_key'] = 'your bind_name'

I found a way to make things easy