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?
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.
