Cannot retrieve PostgreSQL array column with session.query() : TypeError: unhashable type: 'list'
Julien Demoor
The problem I'm seeing is illustrated by the code below. I tried a
workaround using TypeDecorator with process_result_value returning a
tuple rather than a list, to no avail.
Any help will be greatly appreciated.
Regards.
Traceback :
Traceback (most recent call last):
File "satest2.py", line 23, in <module>
session.query(Foo, 'col').from_statement("SELECT 55 AS foo_bar,
'{1,2,3}'::integer[] AS col;").first()
File "/home/user/programs/env/lib/python2.6/site-packages/sqlalchemy/
orm/query.py", line 1494, in first
ret = list(self)[0:1]
File "/home/user/programs/env/lib/python2.6/site-packages/sqlalchemy/
orm/query.py", line 1682, in instances
rows = filter(rows)
File "/home/jdemoor/programs/km/lib/python2.6/site-packages/
sqlalchemy/util.py", line 1193, in unique_list
return [x for x in seq if x not in seen and not seen.add(x)]
TypeError: unhashable type: 'list'
Full code :
import os
from sqlalchemy import create_engine, Table, Integer, MetaData, Column
from sqlalchemy.orm import create_session, mapper
sa_engine = create_engine(os.environ['TEST_DSN'])
session = create_session(sa_engine, autoflush=True,
expire_on_commit=True, autocommit=False)
metadata = MetaData()
foo = Table('foo', metadata, Column('bar', Integer, primary_key=True))
class Foo(object):
pass
mapper(Foo, foo)
# This works
assert session.query('col').from_statement("SELECT 'abc' AS
col;").first() == ('abc',)
assert session.query('col').from_statement("SELECT
'{1,2,3}'::integer[] AS col;").first() == ([1,2,3],)
assert session.query('col1', 'col2').from_statement("SELECT
'{1,2,3}'::integer[] AS col1, 'abc' AS col2;").first() == ([1,2,3],
'abc')
foo_obj = session.query(Foo).from_statement("SELECT 1 AS
foo_bar;").first()
assert foo_obj.bar == 1
try:
# This fails
session.query(Foo, 'col').from_statement("SELECT 55 AS foo_bar,
'{1,2,3}'::integer[] AS col;").first()
except TypeError, e:
print e
from sqlalchemy.dialects.postgresql.base import ARRAY
col = Column('col', ARRAY(Integer, mutable=False))
try:
# This fails too
session.query(Foo, col).from_statement("SELECT 55 AS foo_bar,
'{1,2,3}'::integer[] AS col;").first()
except TypeError, e:
print e
Michael Bayer
If you only queried for columns, it wouldn't be running through unique_list().
I suppose we'd modify ARRAY to return tuples if it's "mutable" flag isn't set. that could only be in 0.7, though. Let me know if that works for you, we'll add a ticket (hard for me to say since I never use the ARRAY type).
> --
> You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
> To post to this group, send email to sqlal...@googlegroups.com.
> To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
>
Julien Demoor
What you propose would work for me, but in the meantime I need a workaround.
I thought of serializing the arrays in SQL, then converting back to tuples
in my code, but the objects contained in the arrays are quite complex to
parse (decimals, datetims...). So I tried patching the ARRAY class to return
tuples when mutable=False, and that had no effect. If I understand
correctly, by the time unique_list() is called, ARRAY hasn't been involved
yet, and the database's array is converted to a Python list by the driver
(psycopg2 in my case).
The workaround I've found is to make the following change, in
sqlalchemy.orm.query.Query.instances :
if filtered:
#if single_entity:
# filter = lambda x: util.unique_list(x, util.IdentitySet)
#else:
# filter = util.unique_list
filter = lambda x: util.unique_list(x, util.IdentitySet)
Should I expect negative side-effects from this?
-----Message d'origine-----
De : sqlal...@googlegroups.com [mailto:sqlal...@googlegroups.com] De la
part de Michael Bayer
Envoyé : mercredi 13 octobre 2010 23:37
À : sqlal...@googlegroups.com
Objet : Re: [sqlalchemy] Cannot retrieve PostgreSQL array column with
session.query() : TypeError: unhashable type: 'list'
Michael Bayer
On Oct 14, 2010, at 4:38 AM, Julien Demoor wrote:
> Thanks for your reply.
>
> What you propose would work for me, but in the meantime I need a workaround.
If you need that exact pattern to work, build a TypeDecorator around ARRAY and have it return a tuple around the result.
TypeDecorator is described at: http://www.sqlalchemy.org/docs/core/types.html?highlight=typedecorator#sqlalchemy.types.TypeDecorator
The SQLAlchemy type is always involved in between where psycopg2 returns data and where unique_list() is called.
As far as changing Query, the unique_list() is what makes it such that if you load a Parent object with many Child objects in a joined-load collection, you get just one Parent and not the same Parent for as many Child objects as are in the result set.
Julien Demoor
I had tried using TypeDecorator without success. Now I tested further and
found the problem is more narrow in scope than I thought. If I create a
table MyTable that contains a column MyArray, with MyArray a TypeDecorator
subclass that converts lists to tuples, insert a row, then do
session.query(MyTable).get(row_id), it works fine and I get a tuple for my
array. (The code is at the bottom of this message)
If I do session.query(MyTable).from_statement('SELECT ... FROM
my_table;').first(), then MyArray.process_result_value() is not called and
the returned instance's array attribute is a list rather than a tuple. In
fact, ARRAY's result processor is not used either in that case. I added some
print statements to ResultMetaData.__init__ to try to understand why : with
a regular query, the column type is MyArray; with a query that uses
from_statement(), the column type is NullType.
From there I'm lost. Is there a way to force Query() to a apply a column
type with from_statement()?
CODE :
import os
from sqlalchemy import create_engine, Table, Integer, MetaData, Column
from sqlalchemy.orm import create_session, mapper
from sqlalchemy.dialects.postgresql.base import ARRAY
sa_engine = create_engine(os.environ['TEST_DSN'])
session = create_session(sa_engine, autoflush=True, expire_on_commit=True,
autocommit=False)
from sqlalchemy import types
class MyArray(types.TypeDecorator):
impl = ARRAY
def process_bind_param(self, value, engine):
return value
def process_result_value(self, value, engine):
print 'process_result_value() called'
if value is None:
return None
else:
return tuple(value)
def copy(self):
return MyArray(self.impl.item_type, self.impl.mutable)
metadata = MetaData(bind=sa_engine)
foo = Table('foo', metadata,
Column('bar', Integer, primary_key=True),
Column('my_array', MyArray(Integer, mutable=False))
)
class Foo(object):
pass
mapper(Foo, foo)
foo_obj = session.query(Foo).from_statement("SELECT 1 AS foo_bar,
'{1,2,3}'::integer[] AS foo_my_array;").first()
print foo_obj.my_array # A list
foo.drop(checkfirst=True)
foo.create()
foo_obj = Foo()
foo_obj.bar = -1
foo_obj.my_array = [-1, -2]
session.add(foo_obj)
session.flush()
session.expunge_all()
del foo_obj
foo_obj = session.query(Foo).get(-1)
print foo_obj.my_array # A tuple
session.expunge_all()
del foo_obj
foo_obj = session.query(Foo).from_statement("SELECT * FROM foo WHERE
bar=-1;").first()
print foo_obj.my_array # A list
-----Message d'origine-----
De : sqlal...@googlegroups.com [mailto:sqlal...@googlegroups.com] De la
part de Michael Bayer
Envoyé : jeudi 14 octobre 2010 15:52
À : sqlal...@googlegroups.com
Objet : Re: [sqlalchemy] Cannot retrieve PostgreSQL array column with
session.query() : TypeError: unhashable type: 'list'
Michael Bayer
On Oct 14, 2010, at 11:53 AM, Julien Demoor wrote:
> Thanks again for your help.
>
> I had tried using TypeDecorator without success. Now I tested further and
> found the problem is more narrow in scope than I thought. If I create a
> table MyTable that contains a column MyArray, with MyArray a TypeDecorator
> subclass that converts lists to tuples, insert a row, then do
> session.query(MyTable).get(row_id), it works fine and I get a tuple for my
> array. (The code is at the bottom of this message)
>
> If I do session.query(MyTable).from_statement('SELECT ... FROM
> my_table;').first(), then MyArray.process_result_value() is not called and
> the returned instance's array attribute is a list rather than a tuple. In
> fact, ARRAY's result processor is not used either in that case. I added some
> print statements to ResultMetaData.__init__ to try to understand why : with
> a regular query, the column type is MyArray; with a query that uses
> from_statement(), the column type is NullType.
>
> From there I'm lost. Is there a way to force Query() to a apply a column
> type with from_statement()?
oh, right, with from_statement() SQLA knows nothing about the types - and in fact in that case you are getting psycopg2's returned array directly.
For that you can use the text() construct:
query.from_statement(text("select * from ...", typemap={'your_array_column':MyArrayType}))
Julien Demoor
-----Message d'origine-----
De : sqlal...@googlegroups.com [mailto:sqlal...@googlegroups.com] De la
part de Michael Bayer
Envoyé : jeudi 14 octobre 2010 18:08
