CODEWITHSUNDEEP
sqlalchemy
misterkevin
misterkevin

Reputation: 37

Having trouble writting a query with "case" in sqlalchemy

In the query below, I keep getting the error "An expression of non boolean type specified in a context where a condition is expected near End". Down below is my code I'm not trying to return the rows where the pk__street_name == NULL in the join. But I get the error listed above. How can I fix this.

result = session.query(
   tamDnRangeMap, tamStreet
 ).join(tamStreet)
  .filter(
     case(
         [(tamDnRangeMap.pk_street_name == NULL, 0)],
         else_ = 1
     )
 ).all()

Upvotes: 0

Views: 78

Answers (1)

ljmc
ljmc

Reputation: 5264

First remark is that you don't want equality comparisons anywhere near NULL in SQL, it is done with IS or IS NOT.

Once you know that, you can use SQLAlchemy's is_ or isnot* operators.

All in all, you're using CASE where you don't really need it, put the IS NOT NULL condition in filter directly.

result = (
    session.query(tamDnRangeMap, tamStreet)
    .join(tamStreet)
    .filter(tamDnRangeMap.pk_street_name.isnot(None))
    .all()
)

* NB. isnot has been deprecated and is replaced by is_not since SQLAlchemy 1.4, but the question uses case with list of whens which was also deprecated in 1.4.

Upvotes: 1

Related Questions