That fixed it. I'm a bit surprised that passing the type_ argument doesn't seem to work. Is that a bug or not how that argument is intended to be used?
On Friday, November 3, 2017 at 1:52:21 PM UTC-7, Mike Bayer wrote: > > On Fri, Nov 3, 2017 at 2:42 PM, Tucker Beck <[email protected] > <javascript:>> wrote: > > I'm writing a recursive cte with sqlalchemy, and I want it to aggregate > > things in an array as it recurses. However, I'm having trouble > initializing > > the 'top' half of the cte with an empty array. I've distilled the > problem > > down the the basic problem of including an empty array in an > with_entities > > clause. Even when I try to set the type of the array explicitly, the sql > > that is produced does not have a type for the emtpy array > > > > Here's a distilation of the problem causing code: > > > > from sqlalchemy import * > > from sqlalchemy.orm import * > > from sqlalchemy.ext.declarative import declarative_base > > from sqlalchemy.dialects.postgresql import array > > > > Base = declarative_base() > > > > class Dummy(Base): > > __tablename__ = 'dummies' > > id = Column(Integer, primary_key=True) > > name = Column(Text) > > > > > > e = create_engine('postgresql+psycopg2://localhost:5432/local_dev', > > echo=False) > > Base.metadata.create_all(e) > > session = Session(e) > > > > > > session.add(Dummy(name='idiot')) > > session.add(Dummy(name='fool')) > > > > > > print(session.query(Dummy).with_entities( > > Dummy.id, > > Dummy.name, > > array([], type_=Integer).label('stuff') > > ).all()) > > > > And Here's the error that's being produced: > > > > /venv:cem/ $ python demo2.py > > --- Test with_entities using emtpy array --- > > Traceback (most recent call last): > > File > > > "/Users/tbeck/.virtualenvs/cem/lib/python3.5/site-packages/sqlalchemy/engine/base.py", > > > > line 1182, in _execute_context > > context) > > File > > > "/Users/tbeck/.virtualenvs/cem/lib/python3.5/site-packages/sqlalchemy/engine/default.py", > > > > line 470, in do_execute > > cursor.execute(statement, parameters) > > > > psycopg2.ProgrammingError: cannot determine type of empty array > > LINE 1: ...s.id AS dummies_id, dummies.name AS dummies_name, ARRAY[] > AS... > > ^ > > HINT: Explicitly cast to the desired type, for example > ARRAY[]::integer[]. > > > the error expresses the solution, which is to CAST: > > from sqlalchemy import cast > > expr = cast(array(), ARRAY(Integer)) > > > > > > > > The above exception was the direct cause of the following exception: > > > > Traceback (most recent call last): > > File "demo2.py", line 27, in <module> > > array([], type_=Integer).label('stuff') > > File > > > "/Users/tbeck/.virtualenvs/cem/lib/python3.5/site-packages/sqlalchemy/orm/query.py", > > > > line 2703, in all > > return list(self) > > File > > > "/Users/tbeck/.virtualenvs/cem/lib/python3.5/site-packages/sqlalchemy/orm/query.py", > > > > line 2855, in __iter__ > > return self._execute_and_instances(context) > > File > > > "/Users/tbeck/.virtualenvs/cem/lib/python3.5/site-packages/sqlalchemy/orm/query.py", > > > > line 2878, in _execute_and_instances > > result = conn.execute(querycontext.statement, self._params) > > File > > > "/Users/tbeck/.virtualenvs/cem/lib/python3.5/site-packages/sqlalchemy/engine/base.py", > > > > line 945, in execute > > return meth(self, multiparams, params) > > File > > > "/Users/tbeck/.virtualenvs/cem/lib/python3.5/site-packages/sqlalchemy/sql/elements.py", > > > > line 263, in _execute_on_connection > > return connection._execute_clauseelement(self, multiparams, params) > > File > > > "/Users/tbeck/.virtualenvs/cem/lib/python3.5/site-packages/sqlalchemy/engine/base.py", > > > > line 1053, in _execute_clauseelement > > compiled_sql, distilled_params > > File > > > "/Users/tbeck/.virtualenvs/cem/lib/python3.5/site-packages/sqlalchemy/engine/base.py", > > > > line 1189, in _execute_context > > context) > > File > > > "/Users/tbeck/.virtualenvs/cem/lib/python3.5/site-packages/sqlalchemy/engine/base.py", > > > > line 1402, in _handle_dbapi_exception > > exc_info > > File > > > "/Users/tbeck/.virtualenvs/cem/lib/python3.5/site-packages/sqlalchemy/util/compat.py", > > > > line 203, in raise_from_cause > > reraise(type(exception), exception, tb=exc_tb, cause=cause) > > File > > > "/Users/tbeck/.virtualenvs/cem/lib/python3.5/site-packages/sqlalchemy/util/compat.py", > > > > line 186, in reraise > > raise value.with_traceback(tb) > > File > > > "/Users/tbeck/.virtualenvs/cem/lib/python3.5/site-packages/sqlalchemy/engine/base.py", > > > > line 1182, in _execute_context > > context) > > File > > > "/Users/tbeck/.virtualenvs/cem/lib/python3.5/site-packages/sqlalchemy/engine/default.py", > > > > line 470, in do_execute > > cursor.execute(statement, parameters) > > sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) cannot > > determine type of empty array > > LINE 1: ...s.id AS dummies_id, dummies.name AS dummies_name, ARRAY[] > AS... > > ^ > > HINT: Explicitly cast to the desired type, for example > ARRAY[]::integer[]. > > [SQL: 'SELECT dummies.id AS dummies_id, dummies.name AS dummies_name, > > ARRAY[] AS stuff \nFROM dummies'] > > > > > > I worked really hard on getting the cte to work (and it does) but now > that > > I'm trying to aggregate with it, I'm running into what seems like a > > fundamental issue. Any help would be greatly appreciated. > > > > -- > > SQLAlchemy - > > The Python SQL Toolkit and Object Relational Mapper > > > > http://www.sqlalchemy.org/ > > > > To post example code, please provide an MCVE: Minimal, Complete, and > > Verifiable Example. See http://stackoverflow.com/help/mcve for a full > > description. > > --- > > You received this message because you are subscribed to the Google > Groups > > "sqlalchemy" group. > > To unsubscribe from this group and stop receiving emails from it, send > an > > email to [email protected] <javascript:>. > > To post to this group, send email to [email protected] > <javascript:>. > > Visit this group at https://groups.google.com/group/sqlalchemy. > > For more options, visit https://groups.google.com/d/optout. > -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
