On Aug 27, 2010, at 8:56 PM, David Gardner wrote:
> Fantastic! That works.
>
> Out of curiosity I noticed that the compile function expects to receive
> instances of Column.
> This isn't a big problem because I just reverted to doing table_var.c.my_col,
> but is there
> a simpler way to use MyClassName.Col?
you coerce incoming arguments into expressions at the constructor level:
from sqlalchemy.sql.expression import _literal_as_column
class MyWhatever(ColumnElement):
def __init__(self, expr, ...):
self.expr = _literal_as_column(expr)
_literal_as_column basically calls __clause_element__() on the incoming object,
and if not present tries turning it into a literal_column() if its a string.
You might also try _no_literals, _only_column_elements, etc. based on what
you'd expect to see there, all of which do the __clause_element__() conversion.
>
>
> On 08/27/2010 04:02 PM, Conor wrote:
>>
>> On 08/27/2010 05:06 PM, David Gardner wrote:
>>>
>>> I should have linked to the docs in question
>>> http://developer.postgresql.org/pgdocs/postgres/sql-expressions.html#SYNTAX-AGGREGATES
>>>
>>>
>>> On 08/27/2010 03:03 PM, David Gardner wrote:
>>>> Recently Postgres added a new aggregate function called string_agg().
>>>> I have been able to use it like:
>>>> Session.query(Asset, func.string_agg(some_col, ','))
>>>>
>>>> This works, but according to the docs I should be able to do
>>>> string_agg(some_col, ',' ORDER BY some_col)
>>>> Is there a way to do this in SQLAlchemy?
>> I think you have to write your own compiler extension:
>> import sqlalchemy as sa
>> from sqlalchemy.ext.compiler import compiles
>> from sqlalchemy.sql.expression import ColumnElement
>> from StringIO import StringIO
>>
>> class string_agg(ColumnElement):
>> type = sa.String()
>>
>> def __init__(self, expr, separator=None, order_by=None):
>> self.expr = expr
>> self.order_by = order_by
>> self.separator = separator
>>
>> @compiles(string_agg, 'mysql')
>> def _compile_string_agg_mysql(element, compiler, **kw):
>> buf = StringIO()
>> buf.write('group_concat(')
>> buf.write(compiler.process(element.expr))
>> if element.order_by is not None:
>> buf.write(' ORDER BY ')
>> buf.write(compiler.process(element.order_by))
>> if element.separator is not None:
>> buf.write(' SEPARATOR ')
>> buf.write(compiler.process(sa.literal(element.separator)))
>> buf.write(')')
>> return buf.getvalue()
>>
>> # Use 'postgres' for SQLAlchemy < 0.6.
>> @compiles(string_agg, 'postgresql')
>> def _compile_string_agg_postgresql(element, compiler, **kw):
>> buf = StringIO()
>> buf.write('string_agg(')
>> buf.write(compiler.process(element.expr))
>> if element.separator is not None:
>> buf.write(', ')
>> buf.write(compiler.process(sa.literal(element.separator)))
>> if element.order_by is not None:
>> buf.write(' ORDER BY ')
>> buf.write(compiler.process(element.order_by))
>> buf.write(')')
>> return buf.getvalue()
>>
>> if __name__ == '__main__':
>> clause = string_agg(sa.literal_column('some_column'),
>> ', ',
>>
>> order_by=sa.literal_column('some_other_column').asc())
>>
>> mysql_engine = sa.create_engine('mysql:///')
>> print 'MySQL: %s' % clause.compile(dialect=mysql_engine.dialect)
>>
>> pg_engine = sa.create_engine('postgresql:///')
>> print 'PostgreSQL: %s' % clause.compile(dialect=pg_engine.dialect)
>>
>> -Conor
>> --
>> You received this message because you are subscribed to the Google Groups
>> "sqlalchemy" group.
>> To post to this group, send email to [email protected].
>> To unsubscribe from this group, send email to
>> [email protected].
>> For more options, visit this group at
>> http://groups.google.com/group/sqlalchemy?hl=en.
>
>
> --
> David Gardner
> Pipeline Tools Programmer
> Jim Henson Creature Shop
> [email protected]
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To post to this group, send email to [email protected].
> To unsubscribe from this group, send email to
> [email protected].
> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
