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

Question

我有以下 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 = "[email protected]"：

email | name | document_name | document_readAllowed | document_writeAllowed

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

result = session.query(User, Document, DocumentPermission).filter_by(email = "[email protected]").all()

谢谢，

Answer 1

试试这个

q = Session.query(
         User, Document, DocumentPermissions,
    ).filter(
         User.email == Document.author,
    ).filter(
         Document.name == DocumentPermissions.document,
    ).filter(
        User.email == 'someemail',
    ).all()

Answer 2

正如 @letitbee 所说，最佳实践是将主键分配给表并正确定义关系以允许正确的 ORM 查询。话虽这么说...

如果您有兴趣编写如下查询：

SELECT
    user.email,
    user.name,
    document.name,
    documents_permissions.readAllowed,
    documents_permissions.writeAllowed
FROM
    user, document, documents_permissions
WHERE
    user.email = "[email protected]";

那么您应该执行以下操作：

session.query(
    User, 
    Document, 
    DocumentsPermissions
).filter(
    User.email == Document.author
).filter(
    Document.name == DocumentsPermissions.document
).filter(
    User.email == "[email protected]"
).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 == "[email protected]"
).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

有关更多信息，请访问 文档< /a>.

Answer 3

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

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

然后使用连接进行简单的查询：

query = session.query(User, Document, DocumentsPermissions).join(Document).join(DocumentsPermissions)

Answer 4

扩展阿卜杜勒的答案，您可以获得 KeyedTuple 通过连接列来代替离散的行集合：

q = Session.query(*User.__table__.columns + Document.__table__.columns).\
        select_from(User).\
        join(Document, User.email == Document.author).\
        filter(User.email == 'someemail').all()

Answer 5

该函数将生成所需的表作为元组列表。

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)

Answer 6

sqlalchemy中的联接查询

我们可以使用SQLAlchemy实现联接（从多个表中提取数据）。

bond_details = conn.execute(
    Customers.join(Orders, Customers.c.cust_id == Orders.c.cust_id)
    .select()
    .with_only_columns(
       Customers.c.cust_id,
       Customers.c.cust_name,
       Customers.c.cust_address,
       Customers.c.cust_email,
       Orders.c.order_id,
       Orders.c.order_date
    )
    .where(Customers.c.cust_id == '23451')
)

下面的链接提到了详细信息：

https://evidenttutorials.com/SQL/join- sqlalchemy 中的查询/