Connecting many-to-many multi-databases with Flask-SQLAlchemy

I am trying to make this many, many connections work with Flask-SQLAlchemy and two MySQL databases, and it is very close except for it, using the wrong database for the connection table. Here are the basics ...

I have main_dband vendor_db. Tables are set as main_db.users, main_db.user_products(relationship table), and then vendor_db.products. It should be clear enough how all this is connected.

in my app.py, I configure the databases as follows:

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 configured as follows:

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))

, , vendor_db main_db. , main_db ? main_db info={'bind_key': 'main_db'} , . !

+5
1

, , user_products_tbl. ,

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'
)

, - !

+10

All Articles