[Sqlalchemy-tickets] [sqlalchemy] #2877: text as from
Brought to you by:
zzzeek
|
From: sqlalchemy <mi...@zz...> - 2013-11-29 17:11:45
|
#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
|