no idea, may I have a complete test case please?
On 03/15/2017 07:53 PM, 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 <http://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
<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
<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
<https://groups.google.com/group/sqlalchemy>.
> For more options, visit https://groups.google.com/d/optout
<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
<mailto:sqlalchemy+unsubscr...@googlegroups.com>.
To post to this group, send email to sqlalchemy@googlegroups.com
<mailto:sqlalchemy@googlegroups.com>.
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.
