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!
On Wed, Nov 8, 2017 at 3:21 PM, Mike Bayer <mike...@zzzcomputing.com> wrote:
> On Wed, Nov 8, 2017 at 6:17 PM, Tucker Beck <tucker.b...@gmail.com> wrote:
> > 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 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
>
>
>
>
> > 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
> >
> >
> > On Monday, November 6, 2017 at 3:01:30 PM UTC-8, Mike Bayer wrote:
> >>
> >> 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.
> >>
> >>
> >> >
> >> > On Mon, Nov 6, 2017 at 9:49 AM, Mike Bayer <mik...@zzzcomputing.com>
> >> > wrote:
> >> >>
> >> >> 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 [];
> >> >> ^
> >> >> 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+...@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+...@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
> >> >
> >> > --
> >> > 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.
