Python 在 SQLAlchemy/Flask 中连接多个表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21996288/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Join multiple tables in SQLAlchemy/Flask
提问by user3347953
I am trying to figure out the correct join query setup within SQLAlchemy, but I can't seem to get my head around it.
我试图在 SQLAlchemy 中找出正确的连接查询设置，但我似乎无法理解它。
I have the following table setup (simplified, I left out the non-essential fields):
我有以下表格设置（简化，我省略了非必要字段）：
class Group(db.Model):
id = db.Column(db.Integer, primary_key = True)
number = db.Column(db.SmallInteger, index = True, unique = True)
member = db.relationship('Member', backref = 'groups', lazy = 'dynamic')
class Member(db.Model):
id = db.Column(db.Integer, primary_key = True)
number = db.Column(db.SmallInteger, index = True)
groupid = db.Column(db.Integer, db.ForeignKey('group.id'))
item = db.relationship('Item', backref = 'members', lazy = 'dynamic')
class Version(db.Model):
id = db.Column(db.Integer, primary_key = True)
name = db.Column(db.String(80), index = True)
items = db.relationship('Item', backref='versions', lazy='dynamic')
class Item(db.Model):
id = db.Column(db.Integer, primary_key = True)
member = db.Column(db.Integer, db.ForeignKey('member.id'))
version = db.Column(db.Integer, db.ForeignKey('version.id'))
So the relationships are the following:
所以关系如下：
- 1:n Group Member
- 1:n Member Item
- 1:n Version Item
- 1:n 组员
- 1:n 会员项目
- 1:n 版本项目
I would like to construct a query by selecting all Item-Rows from the database, that have a certain version. Then I would like to order them by Group and then by Member. The output using Flask/WTForm should look something like this:
我想通过从数据库中选择所有具有特定版本的 Item-Rows 来构建查询。然后我想按组然后按成员订购它们。使用 Flask/WTForm 的输出应如下所示：
* GroupA
* MemberA
* ItemA (version = selected by user)
* ItemB ( dito )
* Member B
* ItemC ( dito )
....
I have come up with something like the following query, but I am pretty sure that it is not correct (and inefficient)
我想出了类似以下查询的内容，但我很确定它不正确（而且效率低下）
session.query(Item,Member,Group,Version)
.join(Member).filter(version.id==1)
.order_by(Group).order_by(Member).all()
My first intuitive approach would have been to create something like
我的第一个直观方法是创建类似
Item.query.join(Member, Item.member==Member.id)
.filter(Member.versions.name=='MySelection')
.order_by(Member.number).order_by(Group.number)
but obviously, this doesn't work at all. The join operation on the Version table does not seem to produce the type of join between the two tables that I expected. Maybe I am totally misunderstanding the concept, but after reading the tutorials this would have made sense to me.
但显然，这根本行不通。Version 表上的连接操作似乎没有产生我期望的两个表之间的连接类型。也许我完全误解了这个概念，但在阅读教程后，这对我来说很有意义。
回答by van
Following will give you the objects you need in one query:
以下将在一个查询中为您提供所需的对象：
q = (session.query(Group, Member, Item, Version)
.join(Member)
.join(Item)
.join(Version)
.filter(Version.name == my_version)
.order_by(Group.number)
.order_by(Member.number)
).all()
print_tree(q)
However, the result you get will be a list of tuples (Group, Member, Item, Version). Now it is up to you to display it in a tree form. Code below might prove useful though:
但是，您得到的结果将是一个 tuples 列表(Group, Member, Item, Version)。现在由您来以树的形式显示它。不过，下面的代码可能很有用：
def print_tree(rows):
def get_level_diff(row1, row2):
""" Returns tuple: (from, to) of different item positions. """
if row1 is None: # first row handling
return (0, len(row2))
assert len(row1) == len(row2)
for col in range(len(row1)):
if row1[col] != row2[col]:
return (col, len(row2))
assert False, "should not have duplicates"
prev_row = None
for row in rows:
level = get_level_diff(prev_row, row)
for l in range(*level):
print 2 * l * " ", row[l]
prev_row = row
Update-1:If you are willing to forgo lazy = 'dynamic'for the first two relationships, you can a query to load a whole object network(as opposed to tuples above) with the code:
更新 1：如果您愿意放弃lazy = 'dynamic'前两个关系，您可以object network使用以下代码查询以加载一个整体（而不是上面的元组）：
q = (session.query(Group)
.join(Member)
.join(Item)
.join(Version)
# @note: here we are tricking sqlalchemy to think that we loaded all these relationships,
# even though we filter them out by version. Please use this only to get data and display,
# but not to continue working with it as if it were a regular UnitOfWork
.options(
contains_eager(Group.member).
contains_eager(Member.items).
contains_eager(Item.version)
)
.filter(Version.name == my_version)
.order_by(Group.number)
.order_by(Member.number)
).all()
# print tree: easy navigation of relationships
for g in q:
print "", g
for m in g.member:
print 2 * " ", m
for i in m.items:
print 4 * " ", i
