Subquery to the same table in SQLAlchemy ORM

Question

Hello SQLAlchemy masters, I am just facing a problem with how to use SQLAlchemy ORM in python for the SQL query

SELECT systems.name,
       (
       SELECT date 
       FROM accounting A
       WHERE A.ticker = C.ticker AND A.info = 'Trade_opened'
       ) AS entry,
 C.*
 FROM accounting C
 JOIN systems ON C.system_id = systems.id
 WHERE C.status = 'open'

And I can't use an aliased() in a right way:

    H = aliased(Accounting, name='H')
    C = aliased(Accounting, name='C')

    his = db.session.query(H.date) \
                    .filter(H.ticker == C.ticker, H.info == r'Trade_opened')
    sql = db.session.query(Systems.name, C, his) \
                    .join(Systems, C.system_id == Systems.id) \
                    .filter(C.status == r'Open') \
                    .statement

    print(sql)

Can you help me, please?

The question is not about getting an exception, it is about how to define mentioned SQL in SQLAlchemy way in the proper form. So I expect that print(sql) will show the "same" SQL. Sure - I can use SQL as is - but really I am wondering how can be created in the SQLAlchemy ORM way. — Unlik
– Unlik, Commented Mar 5, 2022 at 20:51

snakecharmerb · Accepted Answer · 2022-03-06 06:39:13Z

I think you need:

scalar_subquery to be able to use the subquery as a column
select_from to be able to set the "left" side of the joins to be different from the first column (ie. C instead of systems).

I didn't test this with actual data so I don't know if it works correctly. It helps if you post your schema and some test data. I used Account because it has an easy plural, accounts, to setup a test.


Base = declarative_base()

class Account(Base):
    __tablename__ = 'accounts'
    id = Column(Integer, primary_key=True)
    date = Column(Date)
    ticker = Column(String(length=200))
    info = Column(String(length=200))
    status = Column(String(length=200))
    system = relationship('System', backref='accounts')
    system_id = Column(Integer, ForeignKey('systems.id'))

class System(Base):
    __tablename__ = 'systems'
    id = Column(Integer, primary_key=True)
    name = Column(String(length=200))


with Session(engine) as session:
    C = aliased(Account, name='C')
    A = aliased(Account, name='A')
    date_subq = session.query(A.date).filter(and_(A.ticker == C.ticker, A.info == 'Trade_opened')).scalar_subquery()
    q = session.query(System.name, date_subq.label('entry'), C).select_from(C).join(C.system).filter(C.status == 'open')
    print (q)

Formatted SQL:

SELECT 
systems.name AS systems_name,
(SELECT "A".date 
FROM accounts AS "A" WHERE "A".ticker = "C".ticker AND "A".info = %(info_1)s) AS entry,
"C".id AS "C_id",
"C".date AS "C_date",
"C".ticker AS "C_ticker",
"C".info AS "C_info", 
"C".status AS "C_status",
"C".system_id AS "C_system_id" 
FROM accounts AS "C" 
JOIN systems ON systems.id = "C".system_id 
WHERE "C".status = %(status_1)s

Collectives™ on Stack Overflow

Subquery to the same table in SQLAlchemy ORM

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related