CODEWITHSUNDEEP
pythonsqlsqlalchemy
sat1017
sat1017

Reputation: 363

SQLAlchemy single query to return columns from two tables

Is there a way to use one SQLAlchemy query statement to look up the corresponding ID in the Index table and filter that those rows from the DimIndexPrice table without using two statements?

I feel like I am not using the full expressiveness of SQLAlchemy by using two statements. I'm a beginner with SQL but union and join don't make sense here so perhaps this is the right design pattern.

q = 'VOO'
first_query = db_session.query(Index).filter_by(Symbol=q).first()
second_query = db_session.query(DimIndexPrice).filter_by(IndexID=first_query.ID)

class Index(Base):

    __tablename__ = 'Index'

    ID = Column(Integer, primary_key=True, autoincrement=True)
    Symbol = Column(String(4), unique=True, nullable=False)
    FundName = Column(String(120), unique=True, nullable=False)

    def __init__(self, Symbol, FundName):
        self.Symbol = Symbol
        self.FundName = FundName

    def __repr__(self):
        return '<Index %r>' % (self.Symbol)


class DimIndexPrice(Base):

    __tablename__ = 'DimIndexPrice'

    ID = Column(Integer, primary_key=True, autoincrement=True)
    IndexID = Column(Integer, ForeignKey('Index.ID'), nullable=False)
    Date = Column(Date(), nullable=False)
    Open = Column(Float, nullable=False)
    High = Column(Float, nullable=False)
    Low =Column(Float, nullable=False)
    Close = Column(Float, nullable=False)
    CloseAdjusted = Column(Float, nullable=False)
    Volume = Column(Integer, nullable=False)
    DividendAmount = Column(Float, nullable=False)
    SplitCoefficient = Column(Float, nullable=False)

    def __init__(self,
                 IndexID,
                 Date,
                 Open,
                 High,
                 Low,
                 Close,
                 CloseAdjusted,
                 Volume,
                 DividendAmount,
                 SplitCoefficient):
        self.IndexID = IndexID
        self.Date = Date
        self.Open = Open
        self.High = High
        self.Low = Low
        self.Close = Close
        self.CloseAdjusted = CloseAdjusted
        self.Volume = Volume
        self.DividendAmount = DividendAmount
        self.SplitCoefficient = SplitCoefficient

    def __repr__(self):
        return '<DimIndexPrice %r %s>' % (self.IndexID, self.Date)

Upvotes: 0

Views: 276

Answers (2)

sat1017
sat1017

Reputation: 363

Using @sabik's answer and some trial and error, I got the result I was looking for. I am not sure if this is best practice or why .filter can go after the .join but .filter_by cannot or why == needs to be used instead of = but it works. See the docs but they are cryptic to me.

combined_query2 = db_session.query(DimIndexPrice).join(Index, DimIndexPrice.IndexID == Index.ID).filter(Index.Symbol==q)

I used below to output the raw SQL which was helpful for troubleshooting

from sqlalchemy.dialects import sqlite
print(combined_query2.statement.compile(dialect=sqlite.dialect()))

Upvotes: 1

Jiř&#237; Baum
Jiř&#237; Baum

Reputation: 6930

This is exactly what a JOIN does:

combined_query = Index.query.filter_by(Symbol=q).join(DimIndexPrice)

Since you have a ForeignKey set up, you don't need to tell it what columns to join on; otherwise, you could specify: .join(DimIndexPrice, Symbol.c.ID == DimIndexPrice.c.IndexID)

If needed, the database engine will automatically swap the order of operations to make it efficient.

See also this answer

Upvotes: 0

Related Questions