sqlalchemy select problem

sqlalchemy_select_problem.py

from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
import sqlalchemy.exc
import pdb
from sqlalchemy.dialects import postgresql


INTERACTIVE = False

DOCS = """

    The current target query is :
    
        WITH 
            cte_steam_1 AS 
            (
                SELECT  resource_2_stream_event.resource_id AS resource_id, 
                        resource_2_stream_event.event_timestamp AS event_timestamp 
                FROM    resource_2_stream_event 
                JOIN    stream_event ON stream_event.id = resource_2_stream_event.stream_event_id
            ),
            cte_steam_2 AS 
            (
                SELECT  resource_2_stream_event.resource_id AS resource_id, 
                        resource_2_stream_event.event_timestamp AS event_timestamp 
                FROM    resource_2_stream_event 
                JOIN    stream_event ON stream_event.id = resource_2_stream_event.stream_event_id
            )    
        SELECT  cte_steam_1.resource_id, 
                cte_steam_1.event_timestamp 
        FROM    cte_steam_1

        UNION 

        SELECT  cte_steam_2.resource_id, 
                cte_steam_2.event_timestamp 
        FROM    cte_steam_2


    The full target query is :
        
        WITH cte_stream AS (
            ... above mentioned CTE/SELECTS/UNION wrapped as it's own cte ...
        )
        SELECT
        FROM
        WHERE
        GROUP
        ORDER
        LIMIT
        ;
"""

def print_query(q):
    print "-" * 30
    if hasattr(q, 'statement'):
        print str(q.statement.compile(dialect=postgresql.dialect()))
    else:
        print q
    print "-" * 30

Base = declarative_base()


class Resource(Base):
    __tablename__ = 'resource'
    id = Column(Integer, primary_key=True)
    foo = Column(Integer, primary_key=False)
    bar = Column(Integer, primary_key=False)


class StreamEvent(Base):
    __tablename__ = 'stream_event'
    id = Column(Integer, primary_key=True)
    event_timestamp = Column(Integer, primary_key=False)


class Resource2StreamEvent(Base):
    __tablename__ = 'resource_2_stream_event'
    resource_id = Column(Integer, ForeignKey("resource.id"), primary_key=True, nullable=False)
    stream_event_id = Column(Integer, ForeignKey("stream_event.id"), primary_key=True, nullable=False)


e = create_engine("sqlite://", echo=True)
Base.metadata.create_all(e)


s = Session(e)


cte_Stream1 = s.query(
            Resource2StreamEvent.resource_id.label('resource_id'),
            StreamEvent.event_timestamp.label('event_timestamp'),
    )\
    .join(
        StreamEvent,
        StreamEvent.id == Resource2StreamEvent.stream_event_id,
    )\
    .cte(name="cte_steam_1")
query_Stream1 = s.query(
        cte_Stream1.c.resource_id,
        cte_Stream1.c.event_timestamp,
    )

cte_Stream2 = s.query(
            Resource2StreamEvent.resource_id.label('resource_id'),
            StreamEvent.event_timestamp.label('event_timestamp'),
    )\
    .join(
        StreamEvent,
        StreamEvent.id == Resource2StreamEvent.stream_event_id,
    )\
    .cte(name="cte_steam_2")
query_Stream2 = s.query(
        cte_Stream2.c.resource_id,
        cte_Stream2.c.event_timestamp,
    )

print "*" * 20 
print "try 1 - query a union"
try:
    unioned = sqlalchemy.union(query_Stream1, query_Stream2)
    _selected = s.query(
                unioned.c.resource_id.label('resource_id'),
                unioned.c.event_timestamp.label('event_timestamp'),
            )
    print_query(_selected)
except AttributeError, e:
    print " - can not compile, AttributeError"
    print e


print "*" * 20 
print "form A - select off an alias/cte"
print " - i tried many variations of alias, cte in here.  also tried using a `s.query()` and `select`"
try:
    unioned = sqlalchemy.union(query_Stream1, query_Stream2)
    aliased = unioned.alias("aliased")
    _selected = aliased.select(
                (cte_Stream1.c.resource_id.label('resource_id'),
                 cte_Stream1.c.event_timestamp.label('event_timestamp'),
                 )
            )
    print_query(_selected)
except Exception as e:
    print " - fail"
    print e


print "*" * 20 
print "form B - select off an subquery"
print " - i tried many variations of alias, cte in here.  also tried using a `s.query()` and `select`"
try:
    unioned = sqlalchemy.union(query_Stream1, query_Stream2)
    sq = unioned.subquery("sq")
except AttributeError as e:
    print " - fail"
    print " - union doesn't have a subquery, as it is a CompoundSelect"
    print " - it also can't .alias()"
    print e


print "*" * 20 
print "try 2"
print " - this sort of works, but i can't access the columns"
try:
    # _unioned = sqlalchemy.union(query_Stream1, query_Stream2).cte("a")
    _unioned = sqlalchemy.union(query_Stream1, query_Stream2)
    _queried = s.query(unioned)
    print_query(_queried)
    if INTERACTIVE:
        print "active object -- '_queried'"
        pdb.set_trace()
except AttributeError, e:
    print " - can not compile, AttributeError"
    print e


print "*" * 20 
print "try 2 b"
print " - this doubles the query"
try:
    _unioned = sqlalchemy.union(query_Stream1, query_Stream2)
    _queried = s.query(
            cte_Stream1.c.resource_id.label('resource_id'),
            cte_Stream1.c.event_timestamp.label('event_timestamp'),
        )\
        .select_from(_unioned)\
        .order_by(
            sqlalchemy.desc('event_timestamp'),
        )
    print_query(_queried)
    if INTERACTIVE:
        print "active object -- '_queried'"
        pdb.set_trace()
    print "- passed"
except AttributeError, e:
    print " - can not compile, AttributeError"
    print e


print "*" * 20 
print "try 2 c"
print " - this nests things in a subquery"
try:
    _unioned = sqlalchemy.union(query_Stream1, query_Stream2)
    _queried = s.query("*")\
        .select_from(_unioned)\
        .order_by(
            sqlalchemy.desc('event_timestamp'),
        )
    print_query(_queried)
    if INTERACTIVE:
        print "active object -- '_queried'"
        pdb.set_trace()
    print "- passed"
except AttributeError, e:
    print " - can not compile, AttributeError"
    print e



print "*" * 20 
print "try 9"
try:
    _unioned = sqlalchemy.union(query_Stream1, query_Stream2).cte("a")
    _selected = s.query(
                _unioned.c.resource_id.label('resource_id'),
                _unioned.c.event_timestamp.label('event_timestamp'),
            )
except AttributeError, e:
    print " - can not compile, AttributeError"
    print e
    






s.close()