CODEWITHSUNDEEP
postgresqlsqlalchemy
nikka
nikka

Reputation: 1

Select statement in cases with sqlalchemy

We are implementing a workflow engine with SQLAlchemy. Two tables in our model are:

class DbAttribute(Base):
    __tablename__ = "db_dbattribute"
    id  = Column(Integer, primary_key = True)
    dbnode_id = Column(Integer, ForeignKey('db_dbnode.id'))
    key = Column(String(255))
    datatype = Column(String(10))
    tval = Column(String, default='')
    fval = Column(Float, default=None, nullable=True)
    ival = Column(Integer, default=None, nullable=True)
    bval = Column(Boolean, default=None, nullable=True)
    dval = Column(DateTime, default=None, nullable=True)
class DbNode(Base):
    __tablename__ = "db_dbnode"
    id = Column(Integer, primary_key=True)
    uuid = Column(UUID(as_uuid=True), default=uuid_func)
    type = Column(String(255), index=True)
    label = Column(String(255), index=True, nullable=True)
    description = Column(Text(), nullable=True)
    ctime = Column(DateTime(timezone=True), default=timezone.now)
    mtime = Column(DateTime(timezone=True), default=timezone.now)

Attributes are related to a Node via the dbnode_id foreign key. I am now working on a query that would return an attribute value (for example tval) and None if this row does not exists because the key is not the right one. This is working on aliased classes now:

select_stmt =select([aliased_attributes.tval]).select_from(
         aliased_attributes
    ).where(and_(
        aliased_attributes.key==attrkey,
        aliased_attributes.dbnode_id==aliased_node.id
    ))
exists_stmt = exists(select_stmt)

entity = case([
    (
        exists_stmt,
        select_stmt
   ),
   (
        text('true'), 
        None
   )
])

This exits now with the following error:

sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) syntax error at or near "SELECT"

I am using SQLAlchemy 1.0.12 and postgresql 9.4. Any help on what I missed or other ways this could be done better are greatly appreciated

Cheers

Upvotes: 0

Views: 345

Answers (1)

van
van

Reputation: 77002

Why not just use simple outerjoin?

attrkey = 'my_attr_key'

q = (
    session
    .query(
        DbNode.label,
        DbAttribute.tval,
    )
    .outerjoin(
        DbAttribute,
        and_(
            DbAttribute.dbnode_id == DbNode.id,
            DbAttribute.key == attrkey,
        )
    )
)

Upvotes: 0

Related Questions