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
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
> <javascript:>> 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
> <javascript:>> wrote:
> >>
> >> On Mon, Nov 6, 2017 at 12:37 PM, Tucker Beck <tucke...@gmail.com
> <javascript:>>
> >> 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 <javascript:>.
> >> > To post to this group, send email to sqlal...@googlegroups.com
> <javascript:>.
> >> > 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 <javascript:>.
> >> To post to this group, send email to sqlal...@googlegroups.com
> <javascript:>.
> >> 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 <javascript:>.
> > To post to this group, send email to sqlal...@googlegroups.com
> <javascript:>.
> > 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.
