struggling with postgres json related query using sqlalchemy -- sqlalchemy bug or issue with query?

[Darin Gordon]

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'

[mike bayer]

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()

> --
> 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
> <mailto:sqlalchemy+...@googlegroups.com>.
> To post to this group, send email to sqlal...@googlegroups.com
> <mailto:sqlal...@googlegroups.com>.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

[Darin Gordon]

The as_row recipe worked.  Thanks for the quick response, Mike.

[Lukas Siemon]

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

> <mailto:sqlalchemy+unsub...@googlegroups.com>.

[Lukas Siemon]

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

[mike bayer]

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>,

> > an email to sqlalchemy+...@googlegroups.com <javascript:>
> > <mailto:sqlalchemy+...@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+...@googlegroups.com

> <mailto:sqlalchemy+...@googlegroups.com>.

[mike bayer]

func.XYZ(<aliased>)? I can look into that. ideally you'd be doing
func.XYZ(selectable.as_scalar()), but letting alias work there is fine.

> 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>,

> > <mailto:sqlalchemy+...@googlegroups.com>.

> > To post to this group, send email to sqlal...@googlegroups.com
> > <mailto:sqlal...@googlegroups.com>.
> > 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+...@googlegroups.com

> <mailto:sqlalchemy+...@googlegroups.com>.

[mike bayer]

see here's what I don't get, hence why complete test case v helpful:

if i fix self_group() (which yes is a bug, I can fix that), this case
still fails, because the alias() has no "type":

from sqlalchemy import *

t = table('t', column('x'))

expr = func.array_agg(select([t]).alias())

stmt = select([expr])

print stmt


if I do a plain function like func.XYZ(...) then I get nonsensical SQL
in the output.


I fix way too many issues per week/month/year to remember the specifics
of this area so a quick test of what someone is trying to do is always
extremely helpful.

[Lukas Siemon]

Makes perfect sense. I'll post a complete bug report in the tracker later today (it's early morning here atm).

[mike bayer]

I put up
https://bitbucket.org/zzzeek/sqlalchemy/issues/3939/alias-as-column-expr-needs-tweak-to
with the specific Alias issue but if you can add context what the end
goal is that would be helpful, thanks.

[Darin Gordon]

I'm working on another json query and thought I'd circle back around to see whether I'd have to continue using the original "as_row" recipe.  Not sure whether Lukas was correct about sharing a similar issue as the one I originally raised. 

[mike bayer]

there's a long term issue to get around to handling all of PG's syntaxes
fully at
https://bitbucket.org/zzzeek/sqlalchemy/issues/3566/figure-out-how-to-support-all-of-pgs.
Current target is 1.3 because it will be a huge effort, and it may
continue moving out milestones.