[Sqlalchemy-tickets] [sqlalchemy] #2877: text as from

[sqlalchemy <mi...@zz...>]

#2877: text as from
-------------------------+------------------------------------
 Reporter:  zzzeek       |           Owner:  zzzeek
     Type:  enhancement  |          Status:  new
 Priority:  high         |       Milestone:  0.9.0
Component:  sql          |        Severity:  major - 1-3 hours
 Keywords:               |  Progress State:  in progress
-------------------------+------------------------------------
 simple patch, might want to clarify `SelectBase` in general, several
 methods don't apply to `CompoundSelect` either

 {{{
 #!diff
 diff --git a/lib/sqlalchemy/sql/compiler.py
 b/lib/sqlalchemy/sql/compiler.py
 index 3ba3957..b088916 100644
 --- a/lib/sqlalchemy/sql/compiler.py
 +++ b/lib/sqlalchemy/sql/compiler.py
 @@ -726,6 +726,9 @@ class SQLCompiler(Compiled):
      def function_argspec(self, func, **kwargs):
          return func.clause_expr._compiler_dispatch(self, **kwargs)

 +    def visit_text_as_from(self, taf, asfrom=False, parens=True, **kw):
 +        return self.process(taf.element, **kw)
 +
      def visit_compound_select(self, cs, asfrom=False,
                              parens=True, compound_index=0, **kwargs):
          toplevel = not self.stack
 diff --git a/lib/sqlalchemy/sql/elements.py
 b/lib/sqlalchemy/sql/elements.py
 index f349923..80ff064 100644
 --- a/lib/sqlalchemy/sql/elements.py
 +++ b/lib/sqlalchemy/sql/elements.py
 @@ -1023,6 +1023,31 @@ class TextClause(Executable, ClauseElement):
              for b in bindparams:
                  self.bindparams[b.key] = b

 +    @util.dependencies('sqlalchemy.sql.selectable')
 +    def as_fromclause(self, selectable, *cols):
 +        """Turn this :class:`.Text` object into a :class:`.FromClause`
 +        object that can be embedded into another statement.
 +
 +        This function essentially bridges the gap between an entirely
 +        textual SELECT statement and the SQL expression language concept
 +        of a "selectable"::
 +
 +            from sqlalchemy.sql import column, text
 +
 +            stmt = text("SELECT * FROM some_table")
 +            stmt = stmt.as_fromclause(column('id'),
 column('name')).alias('st')
 +
 +            stmt = select([mytable]).\\
 +                    select_from(
 +                        mytable.join(stmt, mytable.c.name == stmt.c.name)
 +                    ).where(stmt.c.id > 5)
 +
 +        .. versionadded:: 0.9.0
 +
 +        """
 +
 +        return selectable.TextAsFrom(self, cols)
 +
      @property
      def type(self):
          if self.typemap is not None and len(self.typemap) == 1:
 diff --git a/lib/sqlalchemy/sql/selectable.py
 b/lib/sqlalchemy/sql/selectable.py
 index 28c757a..7a4a0b7 100644
 --- a/lib/sqlalchemy/sql/selectable.py
 +++ b/lib/sqlalchemy/sql/selectable.py
 @@ -2912,6 +2912,25 @@ class Exists(UnaryExpression):
          return e


 +class TextAsFrom(SelectBase):
 +    """Wrap a :class:`.Text` construct within a :class:`.FromClause`
 +    interface.
 +
 +    This allows the :class:`.Text` object to gain a ``.c`` collection and
 +    other FROM-like capabilities such as :meth:`.FromClause.alias`,
 +    :meth:`.FromClause.cte`, etc.
 +
 +    """
 +    __visit_name__ = "text_as_from"
 +
 +    def __init__(self, text, columns):
 +        self.element = text
 +        self.column_args = columns
 +
 +    def _populate_column_collection(self):
 +        for c in self.column_args:
 +            c._make_proxy(self)
 +
  class AnnotatedFromClause(Annotated):
      def __init__(self, element, values):
          # force FromClause to generate their internal
 }}}

 demo:

 {{{
 #!python
 from sqlalchemy import *
 from sqlalchemy.orm import *
 from sqlalchemy.ext.declarative import declarative_base
 from sqlalchemy.sql import column

 positions = text("""
 select instrument_id, sum(quantity) as quantity
        from transaction where
            account_id = :account_id and
            timestamp < :dt and
        group by instrument_id
        having sum(quantity) != 0
 """).as_fromclause(column("instrument_id"),
 column("quantity")).cte('positions')


 Base = declarative_base()

 class A(Base):
     __tablename__ = 'a'

     id = Column(Integer, primary_key=True)
     data = Column(String)

 s = Session()

 print s.query(A).join(
             positions, A.id == positions.c.instrument_id).\
             filter(positions.c.quantity > 5)

 }}}

--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2877>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python