Was the underlying issue ever resolved? Running into the same error here, but my query generation is automatic, so taking it apart will take a little longer...
On Thursday, 17 November 2016 07:57:56 UTC-8, Mike Bayer wrote: > > Hi Darin - > > That's definitely a bug because all self_group() methods are supposed to > at least have a **kw to let that argument pass in. I located the one > method that has this issue which is the self_group() method of Alias. > However, this also suggests that an Alias object is being used in a > columnar-context which is not actually what you want, an Alias is only > good for a FROM clause. > > Looking at your code where I think it's going wrong is: > > > func.row_to_json(stmt2) > > because stmt2 is an alias(), and func.xyz() expects a columnar > expression. > > We are dealing here with Postgresql's very awkward SQL extensions to > functions. There's a wide variety of these that are to be supported as > part of > > https://bitbucket.org/zzzeek/sqlalchemy/issues/3566/figure-out-how-to-support-all-of-pgs, > > > though this case seems to be yet another case. I've added a recipe for > this as example three on the "pinned" response: > > > from sqlalchemy.sql import Alias, ColumnElement > from sqlalchemy.ext.compiler import compiles > > > class as_row(ColumnElement): > def __init__(self, expr): > assert isinstance(expr, Alias) > self.expr = expr > > > @compiles(as_row) > def _gen_as_row(element, compiler, **kw): > return compiler.visit_alias(element.expr, ashint=True, **kw) > > > if __name__ == '__main__': > from sqlalchemy import Column, Integer, create_engine, func > from sqlalchemy.orm import Session > from sqlalchemy.ext.declarative import declarative_base > > Base = declarative_base() > > class A(Base): > __tablename__ = 'a' > id = Column(Integer, primary_key=True) > x = Column(Integer) > y = Column(Integer) > > e = create_engine("postgresql://scott:tiger@localhost/test", > echo=True) > Base.metadata.drop_all(e) > Base.metadata.create_all(e) > > s = Session(e) > s.add_all([ > A(x=1, y=2), > A(x=5, y=4) > ]) > s.commit() > > subq = s.query(A).subquery() > > print s.query(func.row_to_json(as_row(subq))).select_from(subq).all() > > > > > > > On 11/17/2016 07:25 AM, 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+...@googlegroups.com <javascript:> > > <mailto:sqlalchemy+unsubscr...@googlegroups.com <javascript:>>. > > To post to this group, send email to sqlal...@googlegroups.com > <javascript:> > > <mailto:sqlal...@googlegroups.com <javascript:>>. > > Visit this group at https://groups.google.com/group/sqlalchemy. > > For more options, visit https://groups.google.com/d/optout. > -- 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.
