How to make null/none count 0 instead?

Question

I have some sqlalchemy that counts rows with the value "3" and then prints out the count. It works fine if the count is 1, 2, 3, etc, but when there are no items, it prints nothing. I wanted it to print 0. So I added an if/else statement that didn't work (it prints nothing, no errors).

How would I do this? Here's my current code — not really sure if I should be doing this part in sqlalchemy or python.

q = (db.session.query(Article.snippet, sa.func.count(ArticleVote.id))
     .join(ArticleVote, Article.id == ArticleVote.article_id)
     .filter(Article.snippet == a_obj.snippet)
     .filter(ArticleVote.vote_choice_id == 3)
     .group_by(Article.snippet))
for snippet, count in q:
    if count is None:
        threevotes = 0
    else:
        threevotes = count
    print(snippet, count)
    print("threevotes", threevotes)

Update: I made it an outer join. That didn't help.

     .join(ArticleVote, Article.id == ArticleVote.article_id, isouter=True)

Update: I found a workaround. I don't think it's the proper way to do things, and I'm concerned it might be problematic somehow in the future, so if anyone has a better idea (or can tell me whether or not this could be problematic) that'd be great.

if 'threevotes' in locals():
    print("threevotes in locals")
else:
    print("threevote not in locals")
    threevotes= 0

Answer 1

I know it is old question, but I was looking for the same answer and here is my approach

RAW SQL:

select
cp.id,
case
    when w.quantity isnull then 0
    else w.quantity
end
from case_property cp
left outer join (
SELECT
    single_sma_workups.case_property_id AS case_property_id,
    count(single_sma_workups.case_property_id) AS quantity
FROM single_sma_workups
GROUP BY single_sma_workups.case_property_id
) w on w.case_property_id = cp.id;

SQLAlchemy:

workups_subquery = (
    db.session.query(
        SingleCMAWorkups.case_property_id,
        func.count(SingleCMAWorkups.case_property_id).label("quantity")
    )
    .group_by(SingleCMAWorkups.case_property_id)
    .subquery()
)

query = (
    db.session.query(
        CaseProperty.id,
        db.case(
            [
                (workups_subquery.c.quantity.is_(None), 0),
            ],
            else_=workups_subquery.c.quantity
        )
        # workups_subquery.c.quantity
    )
    .outerjoin(workups_subquery, CaseProperty.id == workups_subquery.c.case_property_id)
)
items = query.all()