sqlalchemy: how to join several tables by one query?

Question

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 = "[email protected]":

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 = "[email protected]").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=='[email protected]').all() But I have not managed yet how to make work the similar for three tables (to include DocumentPermissions). Any Idea? — barankin
– barankin, Commented May 18, 2011 at 12:37
When I perform similar task, I get SyntaxError: keyword can't be an expression — Ishan Tomar
– Ishan Tomar, Commented Aug 25, 2020 at 12:40

Ryabchenko Alexander · Accepted Answer · 2020-04-08 09:57:58Z

147

Try this

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

edited Apr 8, 2020 at 9:57

Ryabchenko Alexander

12.7k9 gold badges74 silver badges105 bronze badges

answered May 18, 2011 at 13:04

Abdul Kader

5,8324 gold badges25 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Sarfaraz Nawaz Over a year ago

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

Fake Name Over a year ago

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>),...]/

Aidan Kane Over a year ago

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

boatcoder Over a year ago

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

Intrastellar Explorer Over a year ago

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 · Accepted Answer · 2018-10-24 12:57:48Z

113

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 = "[email protected]";

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 == "[email protected]"
).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 == "[email protected]"
).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.

edited Oct 24, 2018 at 12:57

Francesco Frassinelli

3,4152 gold badges34 silver badges44 bronze badges

answered Jun 5, 2018 at 17:13

Cabrera

2,0401 gold badge21 silver badges19 bronze badges

2 Comments

Brad Over a year ago

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.

tbarbot Over a year ago

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

thule · Accepted Answer · 2011-06-03 11:50:11Z

66

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)

answered Jun 3, 2011 at 11:50

thule

4,24225 silver badges31 bronze badges

11 Comments

Petrus Theron Over a year ago

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

thule Over a year ago

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

Petrus Theron Over a year ago

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

thule Over a year ago

@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

thule Over a year ago

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

|

Matt-Heun Hong · Accepted Answer · 2017-09-08 20:24:55Z

11

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

edited Sep 8, 2017 at 20:24

answered Sep 8, 2017 at 19:15

Matt-Heun Hong

3963 silver badges13 bronze badges

1 Comment

Lucas Over a year ago

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

Valery Ramusik · Accepted Answer · 2020-09-26 21:38:32Z

3

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)

edited Sep 26, 2020 at 21:38

answered Nov 19, 2017 at 21:32

Valery Ramusik

1,59218 silver badges19 bronze badges

Comments

Gaurav · Accepted Answer · 2024-06-11 19:45:59Z

-2

Join query in sqlalchemy

We can achieve the joins (extracting data from multiple tables) using 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')
)

Details are mentioned in below link:

https://evidenttutorials.com/SQL/join-query-in-sqlalchemy/

edited Jun 11, 2024 at 19:45

answered Jun 11, 2024 at 9:07

Gaurav

12 bronze badges

1 Comment

NotTheDr01ds Over a year ago

Welcome to Stack Overflow! I hate to nitpick, but the question here is, "How to join several tables by one query in SQLAlchemy?" You start off your answer with, "We can achieve the joins (extracting data from multiple tables) using SQLAlchemy." which doesn't make much sense in that context.

Collectives™ on Stack Overflow

sqlalchemy: how to join several tables by one query?

6 Answers 6

9 Comments

2 Comments

11 Comments

1 Comment

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

9 Comments

2 Comments

11 Comments

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related