I'm trying to make this many-to-many join work with Flask-SQLAlchemy and two MySQL databases, and it's very close except it's using the wrong database for the join table. Here's the basics...

I've got main_db and vendor_db. The tables are setup as main_db.users, main_db.user_products (the relation table), and then vendor_db.products. Should be pretty clear how those are all connected.

in my app.py, I'm seting up the databases like this:

app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql://user:pass@localhost/main_db'
app.config['SQLALCHEMY_BINDS'] = {'vendor_db': 'mysql://user:pass@localhost/vendor_db'
}

Model definitions are set up like this:

from app import db# Setup relationship
user_products_tbl = db.Table('user_products', db.metadata,db.Column('user_id', db.Integer, db.ForeignKey('users.user_id')),db.Column('product_id', db.Integer, db.ForeignKey('products.product_id'))
)class User(db.Model):__tablename__ = 'users'id = db.Column('user_id', db.Integer, primary_key=True)products = db.relationship("Product", secondary=user_products_tbl,backref="users", lazy="dynamic")class Product(db.Model):__bind_key__ = 'vendor_db'__tablename__ = 'products'id = db.Column('product_id', db.Integer, primary_key=True)name = db.Column(db.String(120))

The problem is that when I try to get a user's products it's trying to use vendor_db for the join table instead of main_db. Any ideas how I can make it use main_db instead? I've tried setting up another bind to main_db and setting info={'bind_key': 'main_db'} on the relationship table definition, but no luck. Thanks!

Turns out what I needed to do here was specify the schema in my user_products_tbl table definition. So,

user_products_tbl = db.Table('user_products', db.metadata,db.Column('user_id', db.Integer, db.ForeignKey('users.user_id')),db.Column('product_id', db.Integer, db.ForeignKey('products.product_id')),schema='main_db'
)

Hope this helps someone else!