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.
--
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 http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
