[Sqlalchemy-tickets] [sqlalchemy] #2754: Ordering by composite column gives sqlite3 OperationalErro
Brought to you by:
zzzeek
|
From: sqlalchemy <mi...@zz...> - 2013-06-10 02:23:41
|
#2754: Ordering by composite column gives sqlite3 OperationalError
-------------------------------------+-------------------------------------
Reporter: sorcererofdm | Owner: zzzeek
Type: defect | Status: new
Priority: medium | Milestone:
Component: declarative | Severity: minor - half an
Keywords: sqlite, declarative, | hour
composite columns, order_by | Progress State: awaiting triage
clause |
-------------------------------------+-------------------------------------
Right now query.order_by(composite) gives a sqlite3 operational error,
because the rendered SQL is ORDER BY (composite_val1, composite_val2,
composite_val3) instead of ORDER BY composite_val1, composite_val2,
composite_val3. (The parenthesis is causing an error)
For example, consider the code below modified from the documentation.
{{{
from sqlalchemy.engine import create_engine
from sqlalchemy.ext.associationproxy import association_proxy
from sqlalchemy.ext.declarative.api import declarative_base
from sqlalchemy.orm import relationship, composite
from sqlalchemy.orm.session import sessionmaker
from sqlalchemy.schema import Column, ForeignKey
from sqlalchemy.types import Integer, String
import itertools
Base = declarative_base()
class Point(object):
def __init__(self, x, y):
self.x = x
self.y = y
def __composite_values__(self):
return self.x, self.y
def __repr__(self):
return "Point(x=%r, y=%r)" % (self.x, self.y)
def __eq__(self, other):
return isinstance(other, Point) and \
other.x == self.x and \
other.y == self.y
def __ne__(self, other):
return not self.__eq__(other)
class Vertex(Base):
__tablename__ = 'vertice'
id = Column(Integer, primary_key=True)
x1 = Column(Integer)
y1 = Column(Integer)
x2 = Column(Integer)
y2 = Column(Integer)
start = composite(Point, x1, y1)
end = composite(Point, x2, y2)
if __name__ == '__main__':
engine = create_engine('sqlite:///:memory:')
Session = sessionmaker(engine)
session = Session()
Base.metadata.create_all(engine)
pts = [((1, 2), (3, 4)),
((2, 3), (1, 5)),
((0, 5), (6, 3))]
session.add_all(itertools.starmap(
lambda a, b: Vertex(start=Point(*a),
end=Point(*b)),
pts))
}}}
We run the following in the console:
{{{
>>> q = session.query(Vertex).order_by(Vertex.start)
>>> q
Out[1]: <sqlalchemy.orm.query.Query at 0x3bc1f30>
>>> str(q)
Out[1]: 'SELECT vertice.id AS vertice_id, vertice.x1 AS vertice_x1,
vertice.y1 AS vertice_y1, vertice.x2 AS vertice_x2, vertice.y2 AS
vertice_y2 \nFROM vertice ORDER BY (vertice.x1, vertice.y1)'
>>> q.all()
Traceback (most recent call last):
File "C:\Anaconda\Lib\site-packages\IPython\core\interactiveshell.py",
line 2731, in run_code
exec code_obj in self.user_global_ns, self.user_ns
File "<ipython-input-1-511354a8265d>", line 1, in <module>
q.all()
File "C:\Python27\lib\site-
packages\sqlalchemy-0.8.0-py2.7.egg\sqlalchemy\orm\query.py", line 2140,
in all
return list(self)
File "C:\Python27\lib\site-
packages\sqlalchemy-0.8.0-py2.7.egg\sqlalchemy\orm\query.py", line 2252,
in __iter__
return self._execute_and_instances(context)
File "C:\Python27\lib\site-
packages\sqlalchemy-0.8.0-py2.7.egg\sqlalchemy\orm\query.py", line 2267,
in _execute_and_instances
result = conn.execute(querycontext.statement, self._params)
File "C:\Python27\lib\site-
packages\sqlalchemy-0.8.0-py2.7.egg\sqlalchemy\engine\base.py", line 664,
in execute
params)
File "C:\Python27\lib\site-
packages\sqlalchemy-0.8.0-py2.7.egg\sqlalchemy\engine\base.py", line 764,
in _execute_clauseelement
compiled_sql, distilled_params
File "C:\Python27\lib\site-
packages\sqlalchemy-0.8.0-py2.7.egg\sqlalchemy\engine\base.py", line 878,
in _execute_context
context)
File "C:\Python27\lib\site-
packages\sqlalchemy-0.8.0-py2.7.egg\sqlalchemy\engine\base.py", line 871,
in _execute_context
context)
File "C:\Python27\lib\site-
packages\sqlalchemy-0.8.0-py2.7.egg\sqlalchemy\engine\default.py", line
320, in do_execute
cursor.execute(statement, parameters)
OperationalError: (OperationalError) near ",": syntax error u'SELECT
vertice.id AS vertice_id, vertice.x1 AS vertice_x1, vertice.y1 AS
vertice_y1, vertice.x2 AS vertice_x2, vertice.y2 AS vertice_y2 \nFROM
vertice ORDER BY (vertice.x1, vertice.y1)' ()
}}}
Whereas, if we directly execute the correct SQL, without the parenthesis,
{{{
>>> session.execute(u'SELECT vertice.id AS vertice_id, vertice.x1 AS
vertice_x1, vertice.y1 AS vertice_y1, vertice.x2 AS vertice_x2, vertice.y2
AS vertice_y2 \nFROM vertice ORDER BY vertice.x1, vertice.y1' )
Out[1]: <sqlalchemy.engine.result.ResultProxy at 0x3bc1d70>
>>> _.fetchall()
Out[1]: [(3, 0, 5, 6, 3), (1, 1, 2, 3, 4), (2, 2, 3, 1, 5)]
}}}
We get the right result back, albeit not wrapped in Vertex objects
So it seems like a fairly simple bug.
--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2754>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python
|