On Mon, Nov 6, 2017 at 3:20 PM, Tucker Beck <tucker.b...@gmail.com> wrote:
> OK. So, I'm guessing that the type_ parameter is meant to override the type
> that is inferred at creation time. Is that right?
yes. it applies a lot more to func.xyz() since there's nothing to
infer there, but an empty array sent to array() also needs it.
>
> 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.
--
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.
