多对多的数据库加入Flask-SQLAlchemy [英] Many-to-many multi-database join with Flask-SQLAlchemy
问题描述
我试图用Flask-SQLAlchemy和两个MySQL数据库来实现这个多对多的连接,而且它非常接近,除了连接表使用了错误的数据库。这里的基本知识...
$ b $我有 main_db
和 vendor_db
。这些表设置为 main_db.users
, main_db.user_products
(关系表),然后 vendor_db.products
。应该很清楚这些是如何连接的。
在我的app.py中,我正在设置这样的数据库:
app.config ['SQLALCHEMY_DATABASE_URI'] ='mysql:// user:pass @ localhost / main_db'
app.config ['SQLALCHEMY_BINDS'] = {
'vendor_db':'mysql:// user:pass @ localhost / vendor_db'
}
模型定义如下所示:
from app import db
#安装关系
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,
(db.Model):
__bind_key__ ='vendor_db'
__tablename__ ='products'
$ b $ 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'}
on关系表定义,但没有运气。谢谢!
原来我需要做的是在我的 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'
)
希望这可以帮助别人! b $ b
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!
这篇关于多对多的数据库加入Flask-SQLAlchemy的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!