selecting with an empty array in with_entities doesn't seem to work

[Tucker Beck]

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

[Mike Bayer]

the error expresses the solution, which is to CAST:

from sqlalchemy import cast

expr = cast(array(), ARRAY(Integer))

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

[Tucker Beck]

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?

[Mike Bayer]

On Mon, Nov 6, 2017 at 12:37 PM, Tucker Beck <tucke...@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 [];

[Tucker Beck]

OK. So, I'm guessing that the type_ parameter is meant to override the type that is inferred at creation time. Is that right?

> email to sqlalchemy+unsubscribe@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 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+unsubscribe@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.

--

-=Tucker A. Beck=-

Illustrious Writer
  Devious Coder
    Last Hope for the Free World
      Also, Modest

[Mike Bayer]

On Mon, Nov 6, 2017 at 3:20 PM, Tucker Beck <tucke...@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.

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

> -=Tucker A. Beck=-
>
> Illustrious Writer
> Devious Coder
> Last Hope for the Free World
> Also, Modest
>

[Tucker Beck]

Using the cast worked great. However, now when I want to print the query statement I get an error

code:

from sqlalchemy import Column, Integer, Text, ARRAY, cast, create_engine

from sqlalchemy.orm import Session

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'))

query = session.query(Dummy).with_entities(

    Dummy.id,

    Dummy.name,

    cast(array([], type_=Integer), ARRAY(Integer)).label('stuff')

)

print(str(query.statement))

print(query.all())

Output:

Traceback (most recent call last):

  File "/Users/tbeck/.virtualenvs/cem/lib/python3.5/site-packages/sqlalchemy/sql/visitors.py", line 77, in _compiler_dispatch

    meth = getter(visitor)

AttributeError: 'StrSQLCompiler' object has no attribute 'visit_array'

During handling of the above exception, another exception occurred:

Traceback (most recent call last):

  File "demo.py", line 29, in <module>

    print(str(query.statement))

  File "/Users/tbeck/.virtualenvs/cem/lib/python3.5/site-packages/sqlalchemy/sql/elements.py", line 446, in __str__

    return str(self.compile())

  File "<string>", line 1, in <lambda>

  File "/Users/tbeck/.virtualenvs/cem/lib/python3.5/site-packages/sqlalchemy/sql/elements.py", line 436, in compile

    return self._compiler(dialect, bind=bind, **kw)

  File "/Users/tbeck/.virtualenvs/cem/lib/python3.5/site-packages/sqlalchemy/sql/elements.py", line 442, in _compiler

    return dialect.statement_compiler(dialect, self, **kw)

  File "/Users/tbeck/.virtualenvs/cem/lib/python3.5/site-packages/sqlalchemy/sql/compiler.py", line 435, in __init__

    Compiled.__init__(self, dialect, statement, **kwargs)

  File "/Users/tbeck/.virtualenvs/cem/lib/python3.5/site-packages/sqlalchemy/sql/compiler.py", line 216, in __init__

    self.string = self.process(self.statement, **compile_kwargs)

  File "/Users/tbeck/.virtualenvs/cem/lib/python3.5/site-packages/sqlalchemy/sql/compiler.py", line 242, in process

    return obj._compiler_dispatch(self, **kwargs)

  File "/Users/tbeck/.virtualenvs/cem/lib/python3.5/site-packages/sqlalchemy/sql/annotation.py", line 80, in _compiler_dispatch

    self, visitor, **kw)

  File "/Users/tbeck/.virtualenvs/cem/lib/python3.5/site-packages/sqlalchemy/sql/visitors.py", line 81, in _compiler_dispatch

    return meth(self, **kw)

  File "/Users/tbeck/.virtualenvs/cem/lib/python3.5/site-packages/sqlalchemy/sql/compiler.py", line 1725, in visit_select

    for name, column in select._columns_plus_names

  File "/Users/tbeck/.virtualenvs/cem/lib/python3.5/site-packages/sqlalchemy/sql/compiler.py", line 1725, in <listcomp>

    for name, column in select._columns_plus_names

  File "/Users/tbeck/.virtualenvs/cem/lib/python3.5/site-packages/sqlalchemy/sql/compiler.py", line 1497, in _label_select_column

    **column_clause_args

  File "/Users/tbeck/.virtualenvs/cem/lib/python3.5/site-packages/sqlalchemy/sql/visitors.py", line 81, in _compiler_dispatch

    return meth(self, **kw)

  File "/Users/tbeck/.virtualenvs/cem/lib/python3.5/site-packages/sqlalchemy/sql/compiler.py", line 664, in visit_label

    OPERATORS[operators.as_] + \

  File "/Users/tbeck/.virtualenvs/cem/lib/python3.5/site-packages/sqlalchemy/sql/visitors.py", line 81, in _compiler_dispatch

    return meth(self, **kw)

  File "/Users/tbeck/.virtualenvs/cem/lib/python3.5/site-packages/sqlalchemy/sql/compiler.py", line 831, in visit_cast

    (cast.clause._compiler_dispatch(self, **kwargs),

  File "/Users/tbeck/.virtualenvs/cem/lib/python3.5/site-packages/sqlalchemy/sql/visitors.py", line 79, in _compiler_dispatch

    raise exc.UnsupportedCompilationError(visitor, cls)

sqlalchemy.exc.UnsupportedCompilationError: Compiler <sqlalchemy.sql.compiler.StrSQLCompiler object at 0x11023f470> can't render element of type <class 'sqlalchemy.dialects.postgresql.array.array'>

Is there any way to address this? We print the generated query out in our debug logging, and it's useful. Would prefer to not have to avoid printing the query

[Mike Bayer]

print a Core statement like this:

print(query.statement.compile(e))

however, if you print(query), in 1.1 that will work as well because it
uses the Engine that comes from the Session associated with the Query.

http://docs.sqlalchemy.org/en/latest/faq/sqlexpressions.html#how-do-i-render-sql-expressions-as-strings-possibly-with-bound-parameters-inlined

but then also, the above should be amended to include:

http://docs.sqlalchemy.org/en/latest/changelog/migration_11.html#stringify-of-query-will-consult-the-session-for-the-correct-dialect

[Tucker Beck]

Thanks so much! I ended up doing the compilation with the postgresql.dialect() instead of the engine in our source code because of import ordering, but it worked like a charm!

> email to sqlalchemy+unsubscribe@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 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+unsubscribe@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.