[sqlalchemy] SQL Server 2012+ slice(x,y) -> OFFSET X ROWS FETCH NEXT Y ROWS ONLY

[Lyall Pearce]

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?

[Mike Bayer]

Post by Lyall Pearce
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

Post by Lyall Pearce

mylist = [i for i in range(1, 51)]
print mylist[15:20]

[16, 17, 18, 19, 20]

Post by Lyall Pearce
--
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
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.