Flask inherited classes of tables in multiple identical databases using __bind_key__

Question

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.

Is there any way change bind_key of one Model not define diferent Model — user1369887
– user1369887, Commented Sep 18, 2018 at 7:16

Christopher Grebs · Accepted Answer · 2011-08-10 10:30:14Z

3

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.

edited Aug 10, 2011 at 10:30

answered Aug 1, 2011 at 9:07

Christopher Grebs

563 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Christopher Grebs Over a year ago

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

user1369887 · Accepted Answer · 2018-09-18 07:32:59Z

1

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

I found a way to make things easy

answered Sep 18, 2018 at 7:32

user1369887

6011 gold badge7 silver badges10 bronze badges

1 Comment

Francois Over a year ago

Which version of flask_sqlalchemy are you using ?

Collectives™ on Stack Overflow

Flask inherited classes of tables in multiple identical databases using __bind_key__

What's Going Wrong?

Code

basemodels.py

site2models.py

init.py

views.py

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

What's Going Wrong?

Code

basemodels.py

site2models.py

__init__.py

views.py

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related

init.py