The as_row recipe worked. Thanks for the quick response, Mike.
On Thursday, November 17, 2016 at 7:25:07 AM UTC-5, Darin Gordon wrote: > > Using: postgres 9.6 and latest sqlalchemy 1.1.4 > > I've been trying to port a query to a sqlalchemy query but have gotten an > exception about an unrecognized keyword arg, which > confuses me as to whether the issue is my code or a bug in sqlalchemy: > TypeError: self_group() got an unexpected keyword argument 'against' > > > > I've been trying to port the following query, which works in psql, to a > sqlalchemy query: > > > ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- > > select domain, json_agg(parts) as permissions from > (select domain, row_to_json(r) as parts from > (select domain, action, array_agg(distinct target) as > target from > (select (case when domain is null then '*' else > domain end) as domain, > (case when target is null then '*' else > target end) as target, > array_agg(distinct (case when action is > null then '*' else action end)) as action > from permission > group by domain, target > ) x > group by domain, action) > r) parts > group by domain; > > > ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- > > The following sqlalchemy query raises the exception: > > thedomain = case([(Domain.name == None, '*')], else_=Domain.name) > theaction = case([(Action.name == None, '*')], else_=Action.name) > theresource = case([(Resource.name == None, '*')], > else_=Resource.name) > > action_agg = func.array_agg(theaction.distinct()) > > stmt1 = ( > session.query(thedomain.label('domain'), > theresource.label('resource'), > action_agg.label('action')). > select_from(User). > join(role_membership_table, User.pk_id == > role_membership_table.c.user_id). > join(role_permission_table, role_membership_table.c.role_id == > role_permission_table.c.role_id). > join(Permission, role_permission_table.c.permission_id == > Permission.pk_id). > outerjoin(Domain, Permission.domain_id == Domain.pk_id). > outerjoin(Action, Permission.action_id == Action.pk_id). > outerjoin(Resource, Permission.resource_id == Resource.pk_id). > filter(User.identifier == identifier). > group_by(Permission.domain_id, > Permission.resource_id)).subquery() > > stmt2 = (session.query(stmt1.c.domain, > stmt1.c.action, > > func.array_agg(stmt1.c.resource.distinct())). > select_from(stmt1). > group_by(stmt1.c.domain, stmt1.c.action)).subquery() > > stmt3 = (session.query(stmt2.c.domain, > func.row_to_json(stmt2)). > select_from(stmt2)).subquery() > > final = (session.query(stmt3.c.domain, func.json_agg(stmt3)). > select_from(stmt3). > group_by(stmt3.c.domain)) > > > ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- > > Here's the exception trace: > > ...../yosai_alchemystore/accountstore/accountstore.py in > _get_indexed_permissions_query(self, session, identifier) > 156 > 157 stmt3 = (session.query(stmt2.c.domain, > --> 158 func.row_to_json(stmt2)). > 159 select_from(stmt2)).subquery() > 160 > > ...../lib/python3.5/site-packages/sqlalchemy/sql/functions.py in > __call__(self, *c, **kwargs) > 322 > 323 return Function(self.__names[-1], > --> 324 packagenames=self.__names[0:-1], *c, **o) > 325 > 326 > > ...../lib/python3.5/site-packages/sqlalchemy/sql/functions.py in > __init__(self, name, *clauses, **kw) > 432 self.type = sqltypes.to_instance(kw.get('type_', None)) > 433 > --> 434 FunctionElement.__init__(self, *clauses, **kw) > 435 > 436 def _bind_param(self, operator, obj, type_=None): > > ...../lib/python3.5/site-packages/sqlalchemy/sql/functions.py in > __init__(self, *clauses, **kwargs) > 60 self.clause_expr = ClauseList( > 61 operator=operators.comma_op, > ---> 62 group_contents=True, *args).\ > 63 self_group() > 64 > > ...../lib/python3.5/site-packages/sqlalchemy/sql/elements.py in > __init__(self, *clauses, **kwargs) > 1783 self.clauses = [ > 1784 > text_converter(clause).self_group(against=self.operator) > -> 1785 for clause in clauses] > 1786 else: > 1787 self.clauses = [ > > ...../lib/python3.5/site-packages/sqlalchemy/sql/elements.py in > <listcomp>(.0) > 1783 self.clauses = [ > 1784 > text_converter(clause).self_group(against=self.operator) > -> 1785 for clause in clauses] > 1786 else: > 1787 self.clauses = [ > > TypeError: self_group() got an unexpected keyword argument 'against' > > -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- 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 https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.