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!

It looks like you're trying to do the equivalent of an outer join. Maybe there's a way to do that in MySQL. — Omnifarious
– Omnifarious, Commented Apr 14, 2011 at 18:07
It's very similar to an outer join. the problem is that I don't want rows from quux with bar = NULL; quux has no such rows. I want to add extra rows into the union with that column null. — SingleNegationElimination
– SingleNegationElimination, Commented Apr 14, 2011 at 18:13
Also, I didn't put too fine a point on it, since my question is really about sqlalchemy, but the database I'm querying on is actually Firebird. The supplied SQL query does return the desired results, I just haven't figured out how to express that in sqlalchemy. — SingleNegationElimination
– SingleNegationElimination, Commented Apr 14, 2011 at 18:14
Oops, I'm sorry, I meant 'SQLAlchemy', not 'MySQL'. I thought one and typed the other. sigh And I believe a left outer join will give you everything where there is a value in bar regardless of if it matches a value in quux. It will fill in NULL for all the quux field values if there is no matching quux row. — Omnifarious
– Omnifarious, Commented Apr 14, 2011 at 18:26

root · Accepted Answer · 2012-05-20 22:24:12Z

15

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()

answered May 20, 2012 at 22:24

root

4724 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

SingleNegationElimination · Accepted Answer · 2011-04-14 19:15:39Z

2

I managed to work it out. The solution looks like this:

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()

answered Apr 14, 2011 at 19:15

SingleNegationElimination

157k35 gold badges269 silver badges306 bronze badges

Comments

Randy Syring · Accepted Answer · 2011-04-14 23:32:34Z

-1

Another option is to use sqlalchemy.text() with a select statement, like:

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

answered Apr 14, 2011 at 23:32

Randy Syring

2,1391 gold badge16 silver badges19 bronze badges

Collectives™ on Stack Overflow

Selecting Null in SQLAlchemy

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related