在SQLAlchemy中选择空值

Question

我想做一个和下面这个一样的事情：

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';

但是我在使用sqlalchemy 0.6的时候，似乎无法把这个NULL加进去。

这是我目前大致写的内容：

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

Answer 1

我终于搞明白了。解决方案是这样的：

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

q2 = session.query(Baz.foo, 
                   sqlalchemy.sql.expression.literal_column('NULL as 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()

Answer 2

还有一个更简单的解决办法，就是使用 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()

Answer 3

另一个选择是使用 sqlalchemy.text() 来写一个查询语句，比如：

import sqlalchemy as sa
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base

mymetadata = sa.MetaData()
Base = declarative_base(metadata=mymetadata)
Session = sessionmaker(bind=sa.engine)

session = Session()

class Person(Base):
    __tablename__ = 'some_table'
    id = sa.Column(sa.Integer, primary_key=True)
    name = sa.Column(sa.String(50))


print sa.select([Person.name, sa.text('NULL as null_bar')])

>>> SELECT some_table.name, NULL as null_bar 
FROM some_table