sqlalchemy:如何通过一个查询连接多个表?

I have the following SQLAlchemy mapped classes:

我有以下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"))

I need to get a table like this for user.email = "user@email.com":

我需要一个这样的表格给用户。电子邮件=“user.com”:

email | name | document_name | document_readAllowed | document_writeAllowed

How can it be made using one query request for SQLAlchemy? The code below does not work for me:

如何使用SQLAlchemy的一个查询请求来实现它?下面的代码不适用于我:

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

Thanks,

谢谢,

5 个解决方案

#1

Try this

试试这个

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

#2

A good style would be to setup some relations and a primary key for permissions (actually, usually it is good style to setup integer primary keys for everything, but whatever):

一个好的风格是设置一些关系和权限的主键(实际上，通常设置整数主键是很好的风格，但是无论如何):

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

Then do a simple query with joins:

然后用连接做一个简单的查询:

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

#3

Expanding on Abdul's answer, you can obtain a KeyedTuple instead of a discrete collection of rows by joining the columns:

通过扩展Abdul的答案，您可以通过加入列来获得一个KeyedTuple而不是一个离散的行集合:

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

#4

As @letitbee said, its best practice to assign primary keys to tables and properly define the relationships to allow for proper ORM querying. That being said...

正如@letitbee所言，为表分配主键并正确定义关系以允许适当的ORM查询是最佳实践。那就是说……

If you're interested in writing a query along the lines of:

如果您有兴趣按照以下内容编写查询:

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

Then you should go for something like:

那么你应该这样做:

session.query(
    User, 
    Document, 
    DocumentsPermissions
).filter(
    User.email == Document.author
).filter(
    Document.name == DocumentsPermissions.document
).filter(
    User.email == "user@email.com"
).all()

If instead, you want to do something like:

如果你想做的是:

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

Then you should do something along the lines of:

那么你应该按照以下的思路去做:

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

One note about that...

一个关于……

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

查询。join(Address, User.id= Address.user_id) #显式条件查询。使用字符串连接(Address, user . Address) #和显式目标查询。join(Address) #相同

For more information, visit the docs.

更多信息请访问文档。

#5

This function will produce required table as list of tuples.

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

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)

#1