How to do an automatic join using SQLAlchemy core?

I have the below ORM schema in sqlalchemy that represents my DB, and I want an automatic join with invoices from customer.

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship
from sqlalchemy import Column, ForeignKey, Integer, String

Base = declarative_base()


class Customer(Base):
    __tablename__ = 'customers'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    address = Column(String)
    email = Column(String)
    invoices = relationship("Invoice", primaryjoin="Customer.id == Invoice.custid")


class Invoice(Base):
    __tablename__ = 'invoices'
    id = Column(Integer, primary_key=True)
    custid = Column(Integer, ForeignKey("customers.id"))
    invno = Column(Integer)
    amount = Column(Integer)

If I use this statement:

s = session.query(Customer, Invoice).join(Customer.invoices)

It gives me the correct result:

SELECT customers.id AS customers_id, customers.name AS customers_name, customers.address AS customers_address, customers.email AS customers_email, invoices.id AS invoices_id, invoices.custid AS invoices_custid, invoices.invno AS invoices_invno, invoices.amount AS invoices_amount FROM customers JOIN invoices ON customers.id = invoices.custid

But I want to use the core of SQL Alchemy using Select, Join, without query, how could I do it?

I tried with:

j = Customer.join(Customer.invoices)
stmt = select([Customer,Invoice]).select_from(j)

but is not working, any idea?


Asked by: Arthur893 | Posted: 06-12-2021






Answer 1

The result is:

s = select([Customer, Invoice]).select_from(join(Customer, Invoice))

Answered by: Melissa136 | Posted: 07-01-2022



Similar questions

python - SqlAlchemy, how to make automatic join

I got 3 tables class Article_Comment(Base): __tablename__ = 'article_comment' article_id = Column(Integer, ForeignKey('article.id'), primary_key=True) comment_id = Column(Integer, ForeignKey('comment.id'), primary_key=True) child = relationship("Comment", lazy="joined", innerjoin=True) class Article(Base): __tablename__ = 'article' id = Column(Integer, primary_key=True) title ...


python - How to add an automatic filter to a relation with SQLAlchemy?

I'm using SQLAlchemy 0.5rc, and I'd like to add an automatic filter to a relation, so that every time it tries to fetch records for that relation, it ignores the "remote" ones if they're flagged as "logically_deleted" (a boolean field of the child table) For example, if an object "parent" has a "children" relation that has 3 records, but one of them is logically deleted, when I query for "Parent" I'd like SQLA to f...


python - SqlAlchemy, how to make automatic join

I got 3 tables class Article_Comment(Base): __tablename__ = 'article_comment' article_id = Column(Integer, ForeignKey('article.id'), primary_key=True) comment_id = Column(Integer, ForeignKey('comment.id'), primary_key=True) child = relationship("Comment", lazy="joined", innerjoin=True) class Article(Base): __tablename__ = 'article' id = Column(Integer, primary_key=True) title ...


python - Automatic Rollbacks SQLALCHEMY

I am getting Rollbacks automatically. Here is my code: @socketio.on('update2') def update_table_infocorp(trigger): checknotprocess = Infocorp.query.filter(Infocorp.Procesado == False) for row in checknotprocess: getidentityuser = Usuarios.query.filter(Usuarios.id_user == row.id_user).first() getidentityconsulta = Consolidado.query.filter(Consolidado.id_user == row.id_user and ...


python - How can I use UUIDs in SQLAlchemy?

Is there a way to define a column (primary key) as a UUID in SQLAlchemy if using PostgreSQL (Postgres)?


python - Getting random row through SQLAlchemy

How do I select one or more random rows from a table using SQLAlchemy?


python - How to add an automatic filter to a relation with SQLAlchemy?

I'm using SQLAlchemy 0.5rc, and I'd like to add an automatic filter to a relation, so that every time it tries to fetch records for that relation, it ignores the "remote" ones if they're flagged as "logically_deleted" (a boolean field of the child table) For example, if an object "parent" has a "children" relation that has 3 records, but one of them is logically deleted, when I query for "Parent" I'd like SQLA to f...


python - What is the sqlalchemy equivalent column type for 'money' and 'OID' in Postgres?

What is the sqlalchemy equivalent column type for 'money' and 'OID' column types in Postgres?


python - SQLAlchemy and empty columns

When I try to insert a new record into the database using SQLAlchemy and I don't fill out all values, it tries to insert them as "None" (instead of omitting them). It then complains about "can't be null" errors. Is there a way to have it just omit columns from the sql query if I also omitted them when declaring the instance?


python - SQLAlchemy DateTime timezone

SQLAlchemy's DateTime type allows for a timezone=True argument to save a non-naive datetime object to the database, and to return it as such. Is there any way to modify the timezone of the tzinfo that SQLAlchemy passes in so it could be, for instance, UTC? I realize that I could just use default=datetime.datetime.utcnow; however, this is a naive time that would happily ac...


python - How can I get all rows with keys provided in a list using SQLalchemy?

I have sequence of IDs I want to retrieve. It's simple: session.query(Record).filter(Record.id.in_(seq)).all() Is there a better way to do it?


python - How can I order objects according to some attribute of the child in sqlalchemy?

Here is the situation: I have a parent model say BlogPost. It has many Comments. What I want is the list of BlogPosts ordered by the creation date of its' Comments. I.e. the blog post which has the most newest comment should be on top of the list. Is this possible with SQLAlchemy?


python - SQLAlchemy - INSERT OR REPLACE equivalent

does anybody know what is the equivalent to SQL "INSERT OR REPLACE" clause in SQLAlchemy and its SQL expression language? Many thanks -- honzas


python - Defining a table with sqlalchemy with a mysql unix timestamp

Background, there are several ways to store dates in MySQ. As a string e.g. "09/09/2009". As integer using the function UNIX_TIMESTAMP() this is supposedly the traditional unix time representation (you know seconds since the epoch plus/minus leap seconds). As a MySQL TIMESTAMP, a mysql specific data type not the same than unix timestamps. As a MySQL Date field, another mysql spec...






Still can't find your answer? Check out these communities...



PySlackers | Full Stack Python | NHS Python | Pythonist Cafe | Hacker Earth | Discord Python



top