this syntax is elaborate, as much as a CTE expression, which was a pretty
complicated feature add.
Anyway, to create an object that has a ".c." attribute and which you can
select() from, it has to be a FromClause subclass. The columns get filled in
using a method _populate_column_collection:
from sqlalchemy.sql.expression import FromClause, column
class Pivot(FromClause):
def __init__(self, keycol, pivot_col, attrnames, from_query):
self.keycol = keycol
self.pivot_col = pivot_col
self.attrnames = attrnames
self.from_query = from_query
def _populate_column_collection(self):
"""Called on subclasses to establish the .c collection.
Each implementation has a different way of establishing
this collection.
"""
columns = [column(name) for name in self.attrnames]
self._columns.update((col.name, col) for col in columns)
above, I'm using lower-case-c column objects. It's better if the column
collection is filled in with upper-case-C columns that are ultimately derived
from the Table they represent - if here that's keycol and pivot_col, you might
want to do this:
class Pivot(FromClause):
def __init__(self, keycol, pivot_col, attrnames, from_query):
self.keycol = keycol
self.pivot_col = pivot_col
self.attrnames = attrnames
self.from_query = from_query
def _populate_column_collection(self):
"""Called on subclasses to establish the .c collection.
Each implementation has a different way of establishing
this collection.
"""
self.keycol._make_proxy(self, self.attrnames[0])
self.pivot_col._make_proxy(self, self.attrnames[1])
so that an expression like x.c.attribute1 is really referring to a derivation
of the "keycol" (I'm not sure if that's how the Pivot is supposed to be
constructed, though).
Building a custom FROM clause though, a little tricky. Feel free to send
along a whole working example.
On May 10, 2013, at 7:17 PM, Dan Farmer <dfarme...@gmail.com> wrote:
> I'm trying to extend SQLA with a construct for using SQL Server's PIVOT
> functionality. I've written a class and a @compiles function to generate the
> query for this and this produces the correct query (e.g., given a
> sqlalchemy.Table and some Column objects it produces the right query).
>
> My problem is that the return type is a string, not a
> sqlalchemy.sql.expression.Select like object, so I can't do something like
>
> x = Pivot(table.c.key_col, table.c.pivot_col, ['attribute1, 'attribute2'],
> from_query)
> y = sqlalchemy.select([x.c.attribute1])
>
> After Googling around I really couldn't find any examples like this. The
> examples on the documentation page seem to also just produce strings (and are
> Executable rather than "Selectable" anyway). (Mainly
> http://www.sqlalchemy.org/trac/wiki/UsageRecipes and
> http://docs.sqlalchemy.org/en/rel_0_7/core/compiler.html)
>
> Anyone have some tips on how to make the above do-able? An explanation for
> how to produce any kind of custom select statement (that could then be
> selected from) would be fine. Or if this isn't possible currently please let
> me know.
>
> Thanks,
> Dan
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to sqlalchemy+unsubscr...@googlegroups.com.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
> For more options, visit https://groups.google.com/groups/opt_out.
>
>
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
