- changed status to closed
PostgreSQL ARRAY_AGG TypeError
Issue #3109
closed
I'm getting a TypeError at util/_collections.py:729
in 0.9.6 (also tested in 0.9.4) when using the postgresql ARRAY_AGG function and a GROUP BY. Here's a repro script:
import sqlalchemy
from sqlalchemy import create_engine, Column, Integer, String, ForeignKey, func
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import Session
print('version: %s' % sqlalchemy.__version__)
Base = declarative_base()
class Parent(Base):
__tablename__ = 'parent'
id = Column(Integer, primary_key=True)
name = Column(String)
class Child(Base):
__tablename__ = 'child'
id = Column(Integer, primary_key=True)
parent_id = Column(Integer, ForeignKey(Parent.id))
engine = create_engine("postgresql://scott:tiger@localhost/test", echo=True)
conn = engine.connect()
session = Session(conn)
Base.metadata.create_all(conn)
session.add(Parent(id=1, name='Test'))
session.flush()
session.add(Child(id=1, parent_id=1))
session.flush()
session.query(Parent, func.ARRAY_AGG(Child.id)).filter(Parent.id == Child.parent_id).group_by(Parent).all()
And the output:
version: 0.9.6
2014-07-01 23:33:35,088 INFO sqlalchemy.engine.base.Engine select version()
2014-07-01 23:33:35,088 INFO sqlalchemy.engine.base.Engine {}
2014-07-01 23:33:35,090 INFO sqlalchemy.engine.base.Engine select current_schema()
2014-07-01 23:33:35,090 INFO sqlalchemy.engine.base.Engine {}
2014-07-01 23:33:35,092 INFO sqlalchemy.engine.base.Engine {}
2014-07-01 23:33:35,093 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
2014-07-01 23:33:35,093 INFO sqlalchemy.engine.base.Engine {}
2014-07-01 23:33:35,093 INFO sqlalchemy.engine.base.Engine show standard_conforming_strings
2014-07-01 23:33:35,094 INFO sqlalchemy.engine.base.Engine {}
2014-07-01 23:33:35,096 INFO sqlalchemy.engine.base.Engine select relname from pg_class c join pg_namespace n on n.oid=c.relnamespace where n.nspname=current_schema() and relname=%(name)s
2014-07-01 23:33:35,096 INFO sqlalchemy.engine.base.Engine {'name': u'parent'}
2014-07-01 23:33:35,098 INFO sqlalchemy.engine.base.Engine select relname from pg_class c join pg_namespace n on n.oid=c.relnamespace where n.nspname=current_schema() and relname=%(name)s
2014-07-01 23:33:35,098 INFO sqlalchemy.engine.base.Engine {'name': u'child'}
2014-07-01 23:33:35,100 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2014-07-01 23:33:35,101 INFO sqlalchemy.engine.base.Engine INSERT INTO parent (id, name) VALUES (%(id)s, %(name)s)
2014-07-01 23:33:35,101 INFO sqlalchemy.engine.base.Engine {'name': 'Test', 'id': 1}
2014-07-01 23:33:35,104 INFO sqlalchemy.engine.base.Engine INSERT INTO child (id, parent_id) VALUES (%(id)s, %(parent_id)s)
2014-07-01 23:33:35,104 INFO sqlalchemy.engine.base.Engine {'parent_id': 1, 'id': 1}
2014-07-01 23:33:35,107 INFO sqlalchemy.engine.base.Engine SELECT parent.id AS parent_id, parent.name AS parent_name, ARRAY_AGG(child.id) AS "ARRAY_AGG_1"
FROM parent, child
WHERE parent.id = child.parent_id GROUP BY parent.id, parent.name
2014-07-01 23:33:35,108 INFO sqlalchemy.engine.base.Engine {}
Traceback (most recent call last):
File "./bin/python", line 123, in <module>
exec(compile(__file__f.read(), __file__, "exec"))
File "test.py", line 32, in <module>
session.query(Parent, func.ARRAY_AGG(Child.id)).filter(Parent.id == Child.parent_id).group_by(Parent).all()
File "eggs/SQLAlchemy-0.9.6-py2.7-linux-x86_64.egg/sqlalchemy/orm/query.py", line 2293, in all
return list(self)
File "eggs/SQLAlchemy-0.9.6-py2.7-linux-x86_64.egg/sqlalchemy/orm/loading.py", line 78, in instances
rows = util.unique_list(rows, filter_fn)
File "eggs/SQLAlchemy-0.9.6-py2.7-linux-x86_64.egg/sqlalchemy/util/_collections.py", line 729, in unique_list
if hashfunc(x) not in seen
TypeError: unhashable type: 'list'
Comments (4)
-
repo owner -
reporter Thanks! That's perfect.
-
repo owner - changed milestone to 0.9.8
-
repo owner - changed milestone to 0.9.7
- Log in to comment
the ORM runs all results that have entities (e.g. Parent) through a uniquifier, mostly to accommodate eager loading. This uniquifier can't handle Python lists. The ARRAY type allows you to set it up as tuples, which here you can get using this expression: