Search code examples
pythonsqlsqlalchemyapache-superset

selecting columns with tables created using double quotation fail

I connected a postgresql database to Apache Superset and am playing around with their SQL editor. I'm running into a problem where I cannot do a left join between two tables with an associated id.

SELECT id, profile_name FROM "ProductionRun"
LEFT JOIN "StatsAssociation" ON "ProductionRun".id = "StatsAssociation".production_run_id;

Is my above syntax correct? The tables must be referenced with double quotation because they are created case sensitive. This returns only the id and profile_name columns of ProductionRun table without joining with StatsAssociation table.

I created the tables using sqlalchemy and here are the table schema:

ProductionRun

class ProductionRun(Base):
    __tablename__ = 'ProductionRun'

    id = Column(Integer, primary_key=True, autoincrement=True)
    profile_name = Column(String, nullable=False)

StatsAssociation

class StatsAssociation(Base):
    __tablename__ = 'StatsAssociation'

    production_run_id = Column(Integer, ForeignKey('ProductionRun.id'), primary_key=True)
    stats_package_id = Column(Integer, ForeignKey('StatsPackage.id'), unique=True, nullable=False)

    stats_package = relationship('StatsPackage', back_populates='stats_association', cascade='all,delete')
    production_run = relationship('ProductionRun', back_populates='stats_association')

When I view the tables, they both exist and StatsAssociation has production_run_id column which shares the same ids as ProductionRun.

Solution

  • This was originally posted as a comment.

    You're not specifying any column from the "StatsAssociation" table, so it is expected that nothing would show up. To get columns in the output of the SELECT query, you need to list them -- the only exception that I can currently think of being if you use "TableName".* or * in SELECT.

    For example, and just to start you off:

    SELECT id, profile_name, production_run_id 
FROM ...

    where ... is the rest of your query.