Monkey patching seems to do the trick:
# Patch alias self_group kwargs
def patched_alias_self_group(self, target=None, **kwargs):
return original_alias_self_group(self, target=target)
original_alias_self_group = Alias.self_group
Alias.self_group = patched_alias_self_group
On Wednesday, 15 March 2017 16:53:34 UTC-7, Lukas Siemon wrote:
>
> 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 [email protected]
>> > <mailto:[email protected]>.
>> > To post to this group, send email to [email protected]
>> > <mailto:[email protected]>.
>> > 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 [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
