On Wed, Nov 8, 2017 at 6:17 PM, Tucker Beck <[email protected]> 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 <[email protected]> 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 <[email protected]> >> > wrote: >> >> >> >> On Mon, Nov 6, 2017 at 12:37 PM, Tucker Beck <[email protected]> >> >> 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 <[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. >> >> >> >> -- >> >> 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 >> >> [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. >> > >> > >> > >> > >> > -- >> > -=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 [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. -- 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.
