sqlalchemy: how to join several tables by one query?

python sql join sqlalchemy

I have the following SQLAlchemy mapped classes:

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":

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:

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

Thanks,

I've found that the following works to join two tables: result = session.query(User, Document).select_from(join(User, Document)).filter(User.email=='user@email.com').all() But I have not managed yet how to make work the similar for three tables (to include DocumentPermissions). Any Idea?

When I perform similar task, I get SyntaxError: keyword can't be an expression

Ryabchenko Alexander

Try this

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

What kind of join does it do? inner, outer, cross or what?

This actually doesn't do a join at all, it returns row objects in a tuple. In this case, it'd return [(<user>, <document>, <documentpermissions>),...]/

"doesn't do a join at all" - that's a little misleading. It will have sql like select x from a, b ,c which is a cross join. The filters then make it an inner join.

You can print the query by leaving off the .all(). So print Session.query.... to see exactly what it is doing.

I am new to SQLAlchemy. I noticed .filter() can receive multiple criteria if comma separated. Is it preferable to use one .filter() with comma separations inside the parenthesis, or use multiple .filter() like the above answer?

Francesco Frassinelli

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...

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

For more information, visit the docs.

DO THIS. See - not a lot of stuff specified in the joins. That's because if the tables/db-model has already been setup-up with proper foreign keys between these tables - SQLAlchemy will take care of joining ON the proper columns for you.

This is the most correct answer and proper use of sqlalchemy. However, I had to use the filter solution because SQLAlchemy does not accept delete statements with join()

letitbee

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)

What is query set to in the last line and how do you access the joined records in it?

@pate I'm not sure what you mean by 'what is query set to', but it will join according the relations, and the yield 3-tuples. The arguments to the query() call are essentially the select list in sqlalchemy.

How do I access the Document (2nd tuple value) in the query result set?

@PetrusTheron Like I said, query will yield 3-tuples. You can index elements, or just unpack: for (user, doc, perm) in query: print "Document: %s" % doc

Whoa, blast from the past. 'permissions' is an attribute of Document object, that gives you set of DocumentPermission objects. Hence backref.

Matt-Heun Hong

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

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

This works. However, they column names are missing when serializing the object.

Valery Ramusik

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)

sqlalchemy: how to join several tables by one query?

Follow WeChat

Want to stay one step ahead of the latest teleworks?

相似问题

Platform

Support

Contact US