logo标签列表

SQLAlchemy：如何通过一次查询连接多个表？

pythonsqljoinsqlalchemy
139

我有以下SQLAlchemy映射类：

class User(Base):
    __tablename__ = 'users'
    email = Column(String, primary_key=True)
    name = Column(String)

class Document(Base):
    __tablename__ = "documents"
    name = Column(String, primary_key=True)
    author = Column(String, ForeignKey("users.email"))

class DocumentsPermissions(Base):
    __tablename__ = "documents_permissions"
    readAllowed = Column(Boolean)
    writeAllowed = Column(Boolean)

    document = Column(String, ForeignKey("documents.name"))

我需要获取像这样的表格，其针对user.email = "user@email.com":

email | name | document_name | document_readAllowed | document_writeAllowed

如何使用一个查询请求来制作SQLAlchemy？下面的代码对我不起作用：

result = session.query(User, Document, DocumentPermission).filter_by(email = "user@email.com").all()

谢谢。

1
我发现以下代码可以将两个表连接起来：result = session.query(User, Document).select_from(join(User, Document)).filter(User.email=='user@email.com').all() 但是我还没有找到如何将三个表（包括DocumentPermissions）连接起来的方法。有什么想法吗？ - barankin
当我执行类似的任务时，会出现SyntaxError: 关键字不能是表达式。 - Ishan Tomar
5个回答
142

试试这个

q = Session.query(
         User, Document, DocumentPermissions,
    ).filter(
         User.email == Document.author,
    ).filter(
         Document.name == DocumentPermissions.document,
    ).filter(
        User.email == 'someemail',
    ).all()
16
它执行哪种类型的“join”？内连接、外连接、交叉连接还是其他？ - Nawaz
16
实际上，这并不做任何连接（join）操作，它返回一个元组中的行对象。在这种情况下，它会返回 [（<user>、<document>、<documentpermissions>），……]。 - Fake Name
73
“doesn't do a join at all”这个说法有点误导。实际上，它会生成像select x from a, b, c这样的SQL语句，也就是一个交叉连接。然后通过筛选条件转换为内连接。 - Aidan Kane
9
如果你不加上.all()，你可以通过打印查询来查看它的结果。因此，可以使用print Session.query...来准确地了解它正在做什么。 - boatcoder
6
我对SQLAlchemy还不熟悉。我注意到.filter()可以接收多个条件，如果用逗号分隔的话。使用一个带有括号内逗号分隔的.filter()是否更好，还是像上面的答案一样使用多个.filter()呢？ - Intrastellar Explorer
显示剩余4条评论
102

就像@letitbee所说的那样，为表分配主键并正确定义关系是最佳实践，以允许进行适当的ORM查询。话虽如此...

如果您有兴趣编写一个类似于以下内容的查询：

SELECT
    user.email,
    user.name,
    document.name,
    documents_permissions.readAllowed,
    documents_permissions.writeAllowed
FROM
    user, document, documents_permissions
WHERE
    user.email = "user@email.com";

那么你应该选择类似于这样的东西：

session.query(
    User, 
    Document, 
    DocumentsPermissions
).filter(
    User.email == Document.author
).filter(
    Document.name == DocumentsPermissions.document
).filter(
    User.email == "user@email.com"
).all()
如果您想做类似于以下的操作：
SELECT 'all the columns'
FROM user
JOIN document ON document.author_id = user.id AND document.author == User.email
JOIN document_permissions ON document_permissions.document_id = document.id AND document_permissions.document = document.name

那么您应该做类似以下的事情：

session.query(
    User
).join(
    Document
).join(
    DocumentsPermissions
).filter(
    User.email == "user@email.com"
).all()

有一件事需要注意...

query.join(Address, User.id==Address.user_id) # explicit condition
query.join(User.addresses)                    # specify relationship from left to right
query.join(Address, User.addresses)           # same, with explicit target
query.join('addresses')                       # same, using a string

欲了解更多信息，请访问文档。

12
做这个。看一下，联接中没有指定很多内容。这是因为如果表格/数据库模型已经设置好了这些表格之间的外键关系，SQLAlchemy会为您处理正确的列联接。 - Brad
1
这是最正确的答案，也是SQLAlchemy的正确用法。然而，我不得不使用过滤器解决方案，因为SQLAlchemy不接受带有join()的删除语句。 - tbarbot
65

一个好的风格是为权限设置一些关系和一个主键（其实通常为所有内容设置整数主键都是好风格的，但无论如何）：

class User(Base):
    __tablename__ = 'users'
    email = Column(String, primary_key=True)
    name = Column(String)

class Document(Base):
    __tablename__ = "documents"
    name = Column(String, primary_key=True)
    author_email = Column(String, ForeignKey("users.email"))
    author = relation(User, backref='documents')

class DocumentsPermissions(Base):
    __tablename__ = "documents_permissions"
    id = Column(Integer, primary_key=True)
    readAllowed = Column(Boolean)
    writeAllowed = Column(Boolean)
    document_name = Column(String, ForeignKey("documents.name"))
    document = relation(Document, backref = 'permissions')

然后使用连接（join）进行简单查询：

query = session.query(User, Document, DocumentsPermissions).join(Document).join(DocumentsPermissions)
1
最后一行中query设置为什么，如何访问其中连接的记录？ - Petrus Theron
@pate 我不确定你所说的“查询集是什么”，但它将根据关系进行连接，并产生3元组。query()调用的参数本质上是sqlalchemy中的选择列表。 - thule
1
@PetrusTheron 像我说的那样，查询将产生3元组。你可以索引元素，或者只是解压缩：for (user, doc, perm) in query: print "Document: %s" % doc - thule
document = relation(Document, backref='permissions') 中的 'permissions' 是什么意思？ - Ullauri
1
哇，来自过去的冲击。'permissions'是Document对象的属性，它给你一组DocumentPermission对象。因此有了backref。 - thule
显示剩余6条评论
12

在阿卜杜勒的回答上进行扩展，您可以通过连接列获取KeyedTuple而不是离散的行集合：

q = Session.query(*User.__table__.columns + Document.__table__.columns).\
        select_from(User).\
        join(Document, User.email == Document.author).\
        filter(User.email == 'someemail').all()
1
这样做是可以的。然而，在序列化对象时，列名丢失了。 - Lucas
3
这个函数将生成所需的表格，以元组列表的形式呈现。
def get_documents_by_user_email(email):
    query = session.query(
       User.email, 
       User.name, 
       Document.name, 
       DocumentsPermissions.readAllowed, 
       DocumentsPermissions.writeAllowed,
    )
    join_query = query.join(Document).join(DocumentsPermissions)

    return join_query.filter(User.email == email).all()

user_docs = get_documents_by_user_email(email)
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接