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