On Fri, Nov 3, 2017 at 2:42 PM, Tucker Beck <[email protected]> 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].
> 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.
--
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.
