Row Offset in SQL Server

[tdhopper]

I'm trying to use dataset with SQL Server. I'm trying to pull an entire table that has 180k rows and 52 columns.

Essentially I've written

rows = [x for x in table.all()]

This runs for a few minutes and then throws an exception:

---------------------------------------------------------------------------
CompileError                              Traceback (most recent call last)
<ipython-input-16-a9df205f5f00> in <module>()
----> 1 lrows = list(get(rows))

<ipython-input-15-9df065062177> in get(rows)
      1 rows = table.all()
      2 def get(rows):
----> 3     for item in rows:
      4         try:
      5             yield item

C:\Anaconda\lib\site-packages\dataset\persistence\util.pyc in next(self)
     42         row = self.rp.fetchone()
     43         if row is None:
---> 44             if self._next_rp():
     45                 return self.next()
     46             else:

C:\Anaconda\lib\site-packages\dataset\persistence\util.pyc in _next_rp(self)
     32     def _next_rp(self):
     33         try:
---> 34             self.rp = self.result_proxies.next()
     35             self.count += self.rp.rowcount
     36             self.keys = self.rp.keys()

C:\Anaconda\lib\site-packages\dataset\persistence\table.pyc in <genexpr>((q,))
    333             queries.append(self.table.select(whereclause=args, limit=qlimit,
    334                                              offset=qoffset, order_by=order_by))
--> 335         return ResultIter((self.database.executable.execute(q) for q in queries))
    336 
    337     def __len__(self):

C:\Anaconda\lib\site-packages\sqlalchemy\engine\base.pyc in execute(self, statement, *multiparams, **params)
   1612 
   1613         connection = self.contextual_connect(close_with_result=True)
-> 1614         return connection.execute(statement, *multiparams, **params)
   1615 
   1616     def scalar(self, statement, *multiparams, **params):

C:\Anaconda\lib\site-packages\sqlalchemy\engine\base.pyc in execute(self, object, *multiparams, **params)
    660                                                 object,
    661                                                 multiparams,
--> 662                                                 params)
    663         else:
    664             raise exc.InvalidRequestError(

C:\Anaconda\lib\site-packages\sqlalchemy\engine\base.pyc in _execute_clauseelement(self, elem, multiparams, params)
    752             compiled_sql = elem.compile(
    753                             dialect=dialect, column_keys=keys,
--> 754                             inline=len(distilled_params) > 1)
    755 
    756         ret = self._execute_context(

C:\Anaconda\lib\site-packages\sqlalchemy\sql\expression.pyc in compile(self, bind, dialect, **kw)
   1910             else:
   1911                 dialect = default.DefaultDialect()
-> 1912         return self._compiler(dialect, bind=bind, **kw)
   1913 
   1914     def _compiler(self, dialect, **kw):

C:\Anaconda\lib\site-packages\sqlalchemy\sql\expression.pyc in _compiler(self, dialect, **kw)
   1916         Dialect."""
   1917 
-> 1918         return dialect.statement_compiler(dialect, self, **kw)
   1919 
   1920     def __str__(self):

C:\Anaconda\lib\site-packages\sqlalchemy\dialects\mssql\base.pyc in __init__(self, *args, **kwargs)
    683     def __init__(self, *args, **kwargs):
    684         self.tablealiases = {}
--> 685         super(MSSQLCompiler, self).__init__(*args, **kwargs)
    686 
    687     def visit_now_func(self, fn, **kw):

C:\Anaconda\lib\site-packages\sqlalchemy\sql\compiler.pyc in __init__(self, dialect, statement, column_keys, inline, **kwargs)
    287         # dialect.label_length or dialect.max_identifier_length
    288         self.truncated_names = {}
--> 289         engine.Compiled.__init__(self, dialect, statement, **kwargs)
    290 
    291         if self.positional and dialect.paramstyle == 'numeric':

C:\Anaconda\lib\site-packages\sqlalchemy\engine\interfaces.pyc in __init__(self, dialect, statement, bind, compile_kwargs)
    785             self.statement = statement
    786             self.can_execute = statement.supports_execution
--> 787             self.string = self.process(self.statement, **compile_kwargs)
    788 
    789     @util.deprecated("0.7", ":class:`.Compiled` objects now compile "

C:\Anaconda\lib\site-packages\sqlalchemy\engine\interfaces.pyc in process(self, obj, **kwargs)
    804 
    805     def process(self, obj, **kwargs):
--> 806         return obj._compiler_dispatch(self, **kwargs)
    807 
    808     def __str__(self):

C:\Anaconda\lib\site-packages\sqlalchemy\sql\visitors.pyc in _compiler_dispatch(self, visitor, **kw)
     72 
     73             def _compiler_dispatch(self, visitor, **kw):
---> 74                 return getter(visitor)(self, **kw)
     75         else:
     76             # The optimization opportunity is lost for this case because the

C:\Anaconda\lib\site-packages\sqlalchemy\dialects\mssql\base.pyc in visit_select(self, select, **kwargs)
    745             # to use ROW_NUMBER(), an ORDER BY is required.
    746             if not select._order_by_clause.clauses:
--> 747                 raise exc.CompileError('MSSQL requires an order_by when '
    748                                               'using an offset.')
    749 

CompileError: MSSQL requires an order_by when using an offset.

[pudo]

This is a very odd error. Did you disable automatic creation of the id column by any chance?

[tdhopper]

I did not. (Not intentionally, anyway.)

[pudo]

Can you perhaps post your database schema to help with reproducing this?

[tdhopper]

@pudo I no longer work on this project (or at the company where I worked on it.)