OK. So, I'm guessing that the type_ parameter is meant to override the type
that is inferred at creation time. Is that right?
On Mon, Nov 6, 2017 at 9:49 AM, Mike Bayer <mike...@zzzcomputing.com> wrote:
> On Mon, Nov 6, 2017 at 12:37 PM, Tucker Beck <tucker.b...@gmail.com>
> wrote:
> > 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?
>
> This is an unusual case because the array you are passing is empty, so
> Postgresql does not have enough information to infer the type, which
> usually it would have (or wouldn't need in some cases).
>
> The psql command line shows the difference:
>
> test=# select ARRAY [15];
> array
> -------
> {15}
> (1 row)
>
> test=# select ARRAY [];
> ERROR: cannot determine type of empty array
> LINE 1: select ARRAY [];
> ^
> HINT: Explicitly cast to the desired type, for example ARRAY[]::integer[].
>
>
>
>
>
> >
> > 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 <tucke...@gmail.com> 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 sqlalchemy+...@googlegroups.com.
> >> > To post to this group, send email to sqlal...@googlegroups.com.
> >> > 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 sqlalchemy+unsubscr...@googlegroups.com.
> > To post to this group, send email to sqlalchemy@googlegroups.com.
> > 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 a topic in the
> Google Groups "sqlalchemy" group.
> To unsubscribe from this topic, visit https://groups.google.com/d/
> topic/sqlalchemy/lRZLupHDzXQ/unsubscribe.
> To unsubscribe from this group and all its topics, send an email to
> sqlalchemy+unsubscr...@googlegroups.com.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.
>
--
-=Tucker A. Beck=-
Illustrious Writer
Devious Coder
Last Hope for the Free World
Also, Modest
--
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 sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
