sqlalchemy: how to join several tables by one query?

sqlalchemy join on
sqlalchemy select_from multiple joins
flask-sqlalchemy join
sqlalchemy join()
sqlalchemy core join
sqlalchemy join without foreign key
sqlalchemy join subquery
sqlalchemy join many-to many

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,

Try this

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

SQLAlchemy ORM - Working with Joins, SQLAlchemy ORM - Working with Joins - Now that we have two tables, we will see how to create queries on both tables at the same time. To construct a simple implicit join between Customer and Invoice. Using DELETE Expression · Using Multiple Tables · Using Multiple Table Updates · Parameter-Ordered Updates  I split it out into two queries because I couldn't figure out how to make the Suit join optional in just the one query. But this approach is of course terrible and worse the second query didn't work 100% - it wasn't joining all of the different city.text_strings for subsequent filtering.

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)

Multi-table filters in SQLAlchemy - ShopRunner, For my team at Spring, one of the benefits of moving from Go to Python itself in its full glory only after turning on query logging in SQLAlchemy. when applying joins: only perform a join with table t if you apply a filter to t. Tag: python,flask,sqlalchemy. I'm attempting to join two tables together, however, I keep receving the errors of: sqlalchemy.exc.InvalidRequestError: Could not find a FROM clause to join from. Tried joining to , but got: Can't find any foreign key relationships between 'recipe' and 'ingredient'. and

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.

join - sqlalchemy - Python documentation, join(column) - Create a SQL JOIN against this Query object's criterion and apply generatively, How to join multiple tables together in SQLAlchemy in Python  InvalidRequestError: Could not find a FROM clause to join from. Tried joining to <class 'models.friendships'>, but got: Can't determine join between 'Users' and 'Friendships'; tables have more than one foreign key constraint relationship between them. Please specify the 'onclause' of this join explicitly. If I query like this:

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

Configuring how Relationship Joins, relationship() will normally create a join between two tables by examining the foreign to deal with is when there are more than one foreign key path between two tables. there are multiple foreign key paths linking the tables. or when a join is constructed at query time, such as via Query.join() , or via the  Query.join () knows how to join between these tables because there’s only one foreign key between them. Similarly outerjoin () function is available to achieve left outer join. The subquery () method produces a SQL expression representing SELECT statement embedded within an alias.

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)

Query API, Care must be taken in any multiple-table delete to first accommodate via some other means how the Produce an EXCEPT of this Query against one or more queries. join(User.addresses).filter(Address.email.like('q%')). SQLAlchemy with count, group_by, and order_by using ORM I've got several function where I need to do a one-to-many join, using count(), group_by, and order_by. I'm using the sqlalchemy.select function to produce a query that will return me a set of id's, which I then iterate over to do an ORM select on th

SQL Alchemy join the tables : flask, Everything i read is talking about keys, but all I want to do is just join on a column like so: Yeah I have no problem with this - it literally takes 2 seconds to write that query. But the way postgres and SQLite deal with variables in this is different so i would rather use the There is also a one-click deploy button to Heroku :). All SELECT statements generated by SQLAlchemy ORM are constructed by Query object. It provides a generative interface, hence successive calls return a new Query object, a copy of the former with additional criteria and options associated with it. Query objects are initially generated using the query () method of the Session as follows −

Left Outer Join in Flask-SQLAlchemy, In this video I show you how you can write a left outer join query in Flask-​SQLAlchemy. Join Duration: 13:04 Posted: May 1, 2018 The custom criteria we use in a primaryjoin is generally only significant when SQLAlchemy is rendering SQL in order to load or represent this relationship. That is, it’s used in the SQL statement that’s emitted in order to perform a per-attribute lazy load, or when a join is constructed at query time,

Constructing Database Queries with SQLAlchemy, Query your data models using SQLAlchemy's query API. scalar() returns a single value if one exists, None if no values exist, or raises an exception if multiple records = session.query(Customer).join(Order, Order.customer_id Unlike add() , insert() is actually called on an SQLAlchemy Table object and  The method supports multiple table updates, as detailed in Multiple Table Updates, and this behavior does extend to support updates of joined-inheritance and other multiple table mappings. However, the join condition of an inheritance mapper is not automatically rendered. Care must be taken in any multiple-table update to explicitly include the

Comments
  • 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?
  • 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?
  • 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.
  • 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 works. However, they column names are missing when serializing the object.