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