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}))
>
> 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 : sqlalchemy@googlegroups.com [mailto:sqlalch...@googlegroups.com] De la
> part de Michael Bayer
> Envoyé : jeudi 14 octobre 2010 15:52
> À : sqlalchemy@googlegroups.com
> Objet : Re: [sqlalchemy] Cannot retrieve PostgreSQL array column with
> session.query() : TypeError: unhashable type: 'list'
>
>
> 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#sqlal
> chemy.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.
>
>
>
>> 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 : sqlalchemy@googlegroups.com [mailto:sqlalch...@googlegroups.com] De
> la
>> part de Michael Bayer
>> Envoyé : mercredi 13 octobre 2010 23:37
>> À : sqlalchemy@googlegroups.com
>> Objet : Re: [sqlalchemy] Cannot retrieve PostgreSQL array column with
>> session.query() : TypeError: unhashable type: 'list'
>>
>> The Query runs the result through unique_list() anytime there are mapped
>> entities in the columns list. The ARRAY result, returning a Python list
>> [], isn't hashable, so thats that.
>>
>> 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).
>>
>>
>>
>> On Oct 13, 2010, at 2:22 PM, Julien Demoor wrote:
>>
>>> Hello,
>>>
>>> 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
>>>
>>> --
>>> You received this message because you are subscribed to the Google Groups
>> "sqlalchemy" group.
>>> To post to this group, send email to sqlalch...@googlegroups.com.
>>> To unsubscribe from this group, send email to
>> sqlalchemy+unsubscr...@googlegroups.com.
>>> For more options, visit this group at
>> http://groups.google.com/group/sqlalchemy?hl=en.
>>>
>>
>> --
>> You received this message because you are subscribed to the Google Groups
>> "sqlalchemy" group.
>> To post to this group, send email to sqlalch...@googlegroups.com.
>> To unsubscribe from this group, send email to
>> sqlalchemy+unsubscr...@googlegroups.com.
>> For more options, visit this group at
>> http://groups.google.com/group/sqlalchemy?hl=en.
>>
>>
>> --
>> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
>> To post to this group, send email to sqlalch...@googlegroups.com.
>> To unsubscribe from this group, send email to
> sqlalchemy+unsubscr...@googlegroups.com.
>> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en.
>>
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To post to this group, send email to sqlalch...@googlegroups.com.
> To unsubscribe from this group, send email to
> sqlalchemy+unsubscr...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en.
>
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To post to this group, send email to sqlalch...@googlegroups.com.
> To unsubscribe from this group, send email to
> sqlalchemy+unsubscr...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en.
>
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
