How to migrate old code using SQL via pyodbc to Sqlalchemy?

Question

Python

I'm trying to translate old code using SQL via pyodbc to Sqlalchemy. Stuck on the following request:

SELECT D.ALERT_ID FROM 
TBL_ALERT_DESCRIPTION D
INNER JOIN 
TBL_ALERT_PROJECTCORR P ON P.ALERT_ID = D.ALERT_ID
INNER JOIN 
TBL_CONF_PROJECTRSSFEEDCORR PRC ON P.PROJECT_ID = PRC.PROJECT_ID
WHERE D.STATUS_ID = 1 AND D.ALERT_TYPE_ID = 5 AND PRC.RSS_LOCATION_ID = 1

I generated classes for Sqlalchemy using sqlacodegen, this is what happened:

Base = declarative_base()
metadata = Base.metadata

class TblAlertDescription(Base):
    __tablename__ = 'TBL_ALERT_DESCRIPTION'
    ALERT_ID = Column(Numeric(6, 0), primary_key=True)
    STATUS_ID = Column(ForeignKey(u'TBL_ALERT_STATUS.STATUS_ID'), nullable=False, index=True)
    alert_type_id = Column(ForeignKey(u'TBL_ALERT_TYPE.alert_type_id'), nullable=False, index=True)
    alert_type = relationship(u'TblAlertType')
    TBL_CONF_PROJECT = relationship(u'TblConfProject', secondary='TBL_ALERT_PROJECTCORR')

class TblConfProject(Base):
    __tablename__ = 'TBL_CONF_PROJECT'
    PROJECT_ID = Column(Numeric(6, 0), primary_key=True)
    DESCRIPTION = Column(String(50, u'SQL_Latin1_General_CP1_CI_AS'), nullable=False)
    TBL_CONF_RSSFEEDLOCATION = relationship(u'TblConfRssfeedlocation', secondary='TBL_CONF_PROJECTRSSFEEDCORR')

class TblConfRssfeedlocation(Base):
    __tablename__ = 'TBL_CONF_RSSFEEDLOCATION'
    RSS_LOCATION_ID = Column(Integer, primary_key=True)

Some fields have been omitted for simplicity.
Plus, classes are not generated for 2 tables (as far as I understand, because these are association tables). In sqlacodegen, there is a special condition that if a table contains only 2 foreign keys, then mapping is not created for it, but a table description is simply created:

t_TBL_ALERT_PROJECTCORR = Table(
    'TBL_ALERT_PROJECTCORR', metadata,
    Column('ALERT_ID', ForeignKey(u'TBL_ALERT_DESCRIPTION.ALERT_ID'), primary_key=True, nullable=False),
    Column('PROJECT_ID', ForeignKey(u'TBL_CONF_PROJECT.PROJECT_ID'), primary_key=True, nullable=False)
)
t_TBL_CONF_PROJECTRSSFEEDCORR = Table(
    'TBL_CONF_PROJECTRSSFEEDCORR', metadata,
    Column('PROJECT_ID', ForeignKey(u'TBL_CONF_PROJECT.PROJECT_ID'), primary_key=True, nullable=False),
    Column('RSS_LOCATION_ID', ForeignKey(u'TBL_CONF_RSSFEEDLOCATION.RSS_LOCATION_ID'), primary_key=True, nullable=False)
)

In SQL, I even more or less understand, but I'm just starting to study Sqlalchemy, but at this point I'm stuck. I would be grateful for help.
The system is: Python 2.7.5, Centos 6.5 64bit, Sqlalchemy 0.9.1, Pymssql 2.0.1. DB MS SQL Server 2012.

Answer 1

sqlalchemy has excellent documentation with a bunch of examples
specifically on how to compose queries - here
specifically this query can be written almost like in sql
the condition for join'a D and P can be omitted since there is a corresponding relationship

D = TblAlertDescription
P = t_TBL_ALERT_PROJECTCORR
PRC = t_TBL_CONF_PROJECTRSSFEEDCORR

query = session. \
    query(D.ALERT_ID). \
    select_from(D). \
    join(P). \
    join(PRC, P.c.PROJECT_ID == PRC.c.PROJECT_ID). \
    filter(D.STATUS_ID == 1,
           D.alert_type_id == 5,
           PRC.c.RSS_LOCATION_ID == 1)

print query

->

SELECT "TBL_ALERT_DESCRIPTION"."ALERT_ID" AS "TBL_ALERT_DESCRIPTION_ALERT_ID" 
FROM "TBL_ALERT_DESCRIPTION" JOIN "TBL_ALERT_PROJECTCORR" ON "TBL_ALERT_DESCRIPTION"."ALERT_ID" = "TBL_ALERT_PROJECTCORR"."ALERT_ID" JOIN "TBL_CONF_PROJECTRSSFEEDCORR" ON "TBL_ALERT_PROJECTCORR"."PROJECT_ID" = "TBL_CONF_PROJECTRSSFEEDCORR"."PROJECT_ID" 
WHERE "TBL_ALERT_DESCRIPTION"."STATUS_ID" = ? AND "TBL_ALERT_DESCRIPTION".alert_type_id = ? AND "TBL_CONF_PROJECTRSSFEEDCORR"."RSS_LOCATION_ID" = ?