1. Michael Bayer
  2. sqlalchemy
  3. Issues

Issues

Issue #3137 resolved

decide what func.XYZ().alias() should do

Adrian
created an issue

The following query is causing an error inside the SQLAlchemy code since 0.9.4:

subquery = (select(['latitude'])
            .select_from(func.unnest(func.array_agg(Room.latitude))
                         .alias('latitude')).limit(1).as_scalar())
Room.query.with_entities(Room.building, subquery).group_by(Room.building).all()

Partial traceback:

  File ".../sqlalchemy/sql/selectable.py", line 429, in columns
    self._populate_column_collection()
  File ".../sqlalchemy/sql/selectable.py", line 992, in _populate_column_collection
    for col in self.element.columns._all_columns:
AttributeError: 'list' object has no attribute '_all_columns'

Related SO question with more details: http://stackoverflow.com/questions/24930155/strange-error-after-sqlalchemy-update-list-object-has-no-attribute-all-colu

Comments (9)

  1. Michael Bayer repo owner

    per reporting bugs please post a fully reproducing test case , cannot reproduce, thank you

    from sqlalchemy import *
    from sqlalchemy.orm import *
    from sqlalchemy.ext.declarative import declarative_base
    
    Base = declarative_base()
    
    
    class A(Base):
        __tablename__ = 'a'
    
        id = Column(Integer, primary_key=True)
        bs = relationship("B")
    
    
    class B(Base):
        __tablename__ = 'b'
    
        id = Column(Integer, primary_key=True)
        a_id = Column(Integer, ForeignKey('a.id'))
    
    e = create_engine("sqlite://", echo=True)
    Base.metadata.create_all(e)
    
    s = Session(e)
    subquery = (select(['id'])
                .select_from(A)).limit(1).as_scalar()
    q = s.query(B).with_entities(B.a_id, subquery).group_by(B.a_id)
    print q
    print q.all()
    

    output:

    SELECT b.a_id AS b_a_id, (SELECT id 
    FROM a
     LIMIT :param_1) AS anon_1 
    FROM b GROUP BY b.a_id
    2014-07-24 09:35:20,524 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
    2014-07-24 09:35:20,526 INFO sqlalchemy.engine.base.Engine SELECT b.a_id AS b_a_id, (SELECT id 
    FROM a
     LIMIT ? OFFSET ?) AS anon_1 
    FROM b GROUP BY b.a_id
    2014-07-24 09:35:20,526 INFO sqlalchemy.engine.base.Engine (1, 0)
    []
    
  2. Adrian reporter
    from sqlalchemy import *
    from sqlalchemy.orm import *
    from sqlalchemy.ext.declarative import declarative_base
    
    Base = declarative_base()
    
    
    class A(Base):
        __tablename__ = 'a'
    
        id = Column(Integer, primary_key=True)
        latitude = Column(String)
        building = Column(String)
    
    
    e = create_engine('sqlite://', echo=True)
    Base.metadata.create_all(e)
    
    s = Session(e)
    subquery = (select(['latitude']).select_from(func.unnest(func.array_agg(A.latitude)).alias('latitude'))
                .limit(1)
                .as_scalar())
    q = s.query(A).with_entities(A.building, subquery).group_by(A.building)
    print q
    print q.all()
    

    Output:

    Traceback (most recent call last):
      File "satest.py", line 23, in <module>
        q = s.query(A).with_entities(A.building, subquery).group_by(A.building)
      File "<string>", line 2, in with_entities
      File "/home/adrian/dev/indico/env/lib/python2.7/site-packages/sqlalchemy/orm/base.py", line 191, in generate
        fn(self, *args[1:], **kw)
      File "/home/adrian/dev/indico/env/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 1028, in with_entities
        self._set_entities(entities)
      File "/home/adrian/dev/indico/env/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 116, in _set_entities
        entity_wrapper(self, ent)
      File "/home/adrian/dev/indico/env/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 3426, in __init__
        for elem in visitors.iterate(column, {})
      File "/home/adrian/dev/indico/env/lib/python2.7/site-packages/sqlalchemy/util/_collections.py", line 350, in __init__
        self.update(d)
      File "/home/adrian/dev/indico/env/lib/python2.7/site-packages/sqlalchemy/util/_collections.py", line 390, in update
        for e in iterable:
      File "/home/adrian/dev/indico/env/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 3425, in <genexpr>
        elem._annotations['parententity']
      File "/home/adrian/dev/indico/env/lib/python2.7/site-packages/sqlalchemy/sql/visitors.py", line 219, in iterate
        for c in t.get_children(**opts):
      File "/home/adrian/dev/indico/env/lib/python2.7/site-packages/sqlalchemy/sql/selectable.py", line 1020, in get_children
        for c in self.c:
      File "/home/adrian/dev/indico/env/lib/python2.7/site-packages/sqlalchemy/util/langhelpers.py", line 725, in __get__
        obj.__dict__[self.__name__] = result = self.fget(obj)
      File "/home/adrian/dev/indico/env/lib/python2.7/site-packages/sqlalchemy/sql/selectable.py", line 429, in columns
        self._populate_column_collection()
      File "/home/adrian/dev/indico/env/lib/python2.7/site-packages/sqlalchemy/sql/selectable.py", line 992, in _populate_column_collection
        for col in self.element.columns._all_columns:
    AttributeError: 'list' object has no attribute '_all_columns'
    
  3. Michael Bayer repo owner

    OK the original query is using some undefined API, the expected form in this particular case is:

    subquery = (select(['latitude']).select_from(
                        func.foo(A.latitude)
                    )
                .limit(1)
                .as_scalar())
    

    func.foo().alias() should in fact be equivalent to func.foo().select().alias(), however in this case that will push out a second level of nesting here which you don't want. So to make that correction to the API probably needs to be a 1.0 thing, unless I can determine that func.foo().alias() is totally unusable right now.

  4. Michael Bayer repo owner

    I'm restoring the old behavior, but for now just use select(['*']). the column is unnamed. PG's behavior of assigning the column name based on the alias in the FROM is a little bit magic (e.g., if the function returned multiple columns, then it ignores that name and uses the ones the function reports?)

  5. Michael Bayer repo owner
    • Added a supported :meth:.FunctionElement.alias method to functions, e.g. the func construct. Previously, behavior for this method was undefined. The current behavior mimics that of pre-0.9.4, which is that the function is turned into a single-column FROM clause with the given alias name, where the column itself is anonymously named. fixes #3137

    → <<cset 0df977ccad63>>

  6. Michael Bayer repo owner
    • Added a supported :meth:.FunctionElement.alias method to functions, e.g. the func construct. Previously, behavior for this method was undefined. The current behavior mimics that of pre-0.9.4, which is that the function is turned into a single-column FROM clause with the given alias name, where the column itself is anonymously named. fixes #3137

    → <<cset 9a7c411def96>>

  7. Log in to comment