Hello,
I have a query that fails when executed: the engine is passed the
string representation of a sqlalchemy.sql.expression._BinaryExpression
object as a parameter, resulting in a syntax error. This expression is
part of
a subquery which is joined to a table. There is a simplified exemple
below along with the resulting traceback.
Is this a problem with my query or a bug in SA ?
Thanks in advance.
-- Test code --
from sqlalchemy import MetaData, create_engine
from sqlalchemy.orm import create_session
from sqlalchemy import Table, Column, Integer, Unicode, String,
DateTime, ForeignKey, Boolean, \
desc, select, and_, or_, subquery, CheckConstraint
from sqlalchemy.orm import relation, backref, mapper, eagerload
from sqlalchemy.orm.exc import NoResultFound
from sqlalchemy.sql import func, label, union
metadata = MetaData()
engine = create_engine('sqlite://')
session = create_session(engine, autoflush=True, autocommit=False)
foo_table = Table('foo', metadata,
Column('id', Integer, primary_key=True, nullable=False),
)
bar_table = Table('bar', metadata,
Column('id', Integer, primary_key=True, nullable=False),
Column('foo_id', Integer, ForeignKey('foo.id')),
Column('v', Integer),
)
sub = select([bar_table.c.foo_id, func.sum(bar_table.c.v).label
('sum')]).\
where(func.case([(bar_table.c.v > 1, True)], else_=False)).\
group_by(bar_table.c.foo_id)
query = select([foo_table.c.id, sub.c.sum], from_obj=[foo_table.join
(bar_table, foo_table.c.id == bar_table.c.foo_id)])
print list(session.query(query))
-- Results below --
$ python test.py
Traceback (most recent call last):
File "test.py", line 29, in <module>
print list(session.query(query))
File "/usr/lib/python2.5/site-packages/SQLAlchemy-0.5.4p2-py2.5.egg/
sqlalchemy/orm/query.py", line 1287, in __iter__
return self._execute_and_instances(context)
File "/usr/lib/python2.5/site-packages/SQLAlchemy-0.5.4p2-py2.5.egg/
sqlalchemy/orm/query.py", line 1290, in _execute_and_instances
result = self.session.execute(querycontext.statement,
params=self._params, mapper=self._mapper_zero_or_none())
File "/usr/lib/python2.5/site-packages/SQLAlchemy-0.5.4p2-py2.5.egg/
sqlalchemy/orm/session.py", line 755, in execute
clause, params or {})
File "/usr/lib/python2.5/site-packages/SQLAlchemy-0.5.4p2-py2.5.egg/
sqlalchemy/engine/base.py", line 824, in execute
return Connection.executors[c](self, object, multiparams, params)
File "/usr/lib/python2.5/site-packages/SQLAlchemy-0.5.4p2-py2.5.egg/
sqlalchemy/engine/base.py", line 874, in _execute_clauseelement
return self.__execute_context(context)
File "/usr/lib/python2.5/site-packages/SQLAlchemy-0.5.4p2-py2.5.egg/
sqlalchemy/engine/base.py", line 896, in __execute_context
self._cursor_execute(context.cursor, context.statement,
context.parameters[0], context=context)
File "/usr/lib/python2.5/site-packages/SQLAlchemy-0.5.4p2-py2.5.egg/
sqlalchemy/engine/base.py", line 950, in _cursor_execute
self._handle_dbapi_exception(e, statement, parameters, cursor,
context)
File "/usr/lib/python2.5/site-packages/SQLAlchemy-0.5.4p2-py2.5.egg/
sqlalchemy/engine/base.py", line 931, in _handle_dbapi_exception
raise exc.DBAPIError.instance(statement, parameters, e,
connection_invalidated=is_disconnect)
sqlalchemy.exc.OperationalError: (OperationalError) near "GROUP":
syntax error u'SELECT id AS id, sum AS sum \nFROM (SELECT foo.id AS
id, sum \nFROM (SELECT bar.foo_id AS foo_id, sum(bar.v) AS sum \nFROM
bar \nWHERE case(?) GROUP BY bar.foo_id), foo JOIN bar ON foo.id =
bar.foo_id)' [[(<sqlalchemy.sql.expression._BinaryExpression object at
0x7fbb1d6c>, True)]]
-- End --
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
