On 8/19/15 12:05 AM, Lyall Pearce wrote:
SQL Server 2012 supports the following...
select *
from table
where clause
ORDER BY column
OFFSET :offset ROWS
FETCH NEXT :limit ROWS
which is ideal for use with slice() and runs in SQL Server Management
Studio 2012.
However, the documentation indicates that SQL Server does not support
this and the sqlalchemy 1.0.8 actually generates code as follows
select *
from table
where clause
ORDER BY column
LIMIT :limit
OFFSET :offset
When being used with Python 2.7 and pyodbc-3.0.10, it actually runs
but the limit is actually the limit - offset, so limit is being
treated as an absolute offset, not a row count.
Is this expected?
When you call a slice off an ORM Query object (I assume that's what
we're referring to), it considers the result to be a list of items and
translates the Python slice values into LIMIT/OFFSET values that
preserve the behavior of a Python slice of a list. That is,
LIMIT=slice.end - slice.start, OFFSET=slice.start. I'm not familiar
with the "FETCH NEXT :limit ROWS" syntax.
Here is a demonstration:
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class A(Base):
__tablename__ = 'a'
id = Column(Integer, primary_key=True)
e = create_engine("sqlite://", echo=True)
Base.metadata.create_all(e)
s = Session(e)
s.add_all([A() for i in range(50)])
s.commit()
q = s.query(A).order_by(A.id)
print [a.id for a in q[15:20]]
the SQL at the end and the result is:
SELECT a.id AS a_id
FROM a ORDER BY a.id
LIMIT ? OFFSET ?
2015-08-19 01:27:23,123 INFO sqlalchemy.engine.base.Engine (5, 15)
[16, 17, 18, 19, 20]
Where you can see the values for LIMIT/OFFSET are (slice.end -
slice.start, slice.start). This is the same as you'd get in Python if
you had a list of 50 integers:
>>> mylist = [i for i in range(1, 51)]
>>> print mylist[15:20]
[16, 17, 18, 19, 20]
--
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]
<mailto:[email protected]>.
To post to this group, send email to [email protected]
<mailto:[email protected]>.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
