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