Selecting Null in SQLAlchemy

Question

I want to do the equivalent of

SELECT * FROM 
(SELECT foo, bar FROM baz JOIN quux ON baz.id = quux.id
    UNION
SELECT foo, NULL AS bar FROM baz) 
GROUP BY (foo, bar) HAVING foo = 'John Doe';

using sqlalchemy 0.6, but I can't seem to sneak that NULL in there.

This roughly what I have so far:

q1 = session.query(Baz.foo, Quux.bar).join(Quux)
q2 = session.query(Baz.foo, None)
#                           ^^^^ This breaks!

Answer 1

A yet simpler solution is to use sqlalchemy.null():

q1 = session.query(Baz.foo, Quux.bar) \
            .join(Quux.bar)

q2 = session.query(Baz.foo, 
                   sqlalchemy.null().label('null_bar'))

qall = q1.union(q2)
foocol = qall.column_descriptions[0]['expr']
qgrp = qall.group_by([col['name'] for col in qall.column_descriptions])
q = qgrp.having(foocol == 'John Doe')
q.all()