On 9/29/15 9:50 AM, Ralph Heinkel wrote:
Hi everybody,
oracle 12c has received the long awaited possibility to properly
provide LIMIT and OFFSET parameters to a select query.
See
https://oracle-base.com/articles/12c/row-limiting-clause-for-top-n-queries-12cr1
for details.
wow!
In short this looks like:
SELECT val FROM some_table ORDER BY val OFFSET 4 ROWS FETCH NEXT 4
ROWS ONLY;
it's like OK we can add a LIMIT/OFFSET feature, but first! Let's send
it off to the Oracle department of "make this syntax as awkward and
obtuse as possible - OraTuse! (tm)"
and is so much faster than the nested approach that has been used so
far (at least on my system).
I am sure.
I've successfully implemented some code for SA in order to apply the
new paging method but I'm not sure whether this is the best way to go.
It looks like:
from sqlalchemy.ext.compiler import compiles
from sqlalchemy.sql.expression import Select
from sqlalchemy.dialects.oracle.base import OracleCompiler
@compiles(Select, 'oracle')
def compile_select(element, compiler, **kw):
# The following line basically suppresses the original, nested
approach in SA for limit/offset:
element._oracle_visit = True
return compiler.visit_select(element, **kw)
def limit_clause(self, select):
# This code overrides the empty method in OracleCompiler:
my_limit_clause = ""
if select._offset:
my_limit_clause += " OFFSET %d ROWS" % select._offset
if select._limit:
my_limit_clause += " FETCH FIRST %d ROWS ONLY" % select._limit
return my_limit_clause
OracleCompiler.limit_clause = limit_clause
I did not find out how to use the @compile directive for overriding
the 'limit_clause()' method - is there a way to properly do this?
Well first off, SQLAlchemy *has* to add this properly, so let me add a
ticket:
https://bitbucket.org/zzzeek/sqlalchemy/issues/3543/support-new-oracle-12c-offset-top-n
Secondly, OK I think the monkeypatch you're doing is almost as good as
we can do for the moment, though we can make this a little cleaner since
we can in fact make a new compiler class totally and associate it with a
dialect:
from sqlalchemy.sql import compiler
def patch_engine(engine):
class Oracle12CCompiler(engine.dialect.statement_compiler):
def visit_select(self, element, **kw):
return compiler.SQLCompiler.visit_select(self, element, **kw)
def limit_clause(self, select):
# This code overrides the empty method in OracleCompiler:
my_limit_clause = ""
if select._offset:
my_limit_clause += " OFFSET %d ROWS" % select._offset
if select._limit:
my_limit_clause += " FETCH FIRST %d ROWS ONLY" %
select._limit
return my_limit_clause
engine.dialect.statement_compiler = Oracle12CCompiler
if __name__ == '__main__':
from sqlalchemy import create_engine, select, table, column
import mock
e = create_engine(
"oracle://",
# only because I don't have cx_oracle installed here, this
# is not part of the example
module=mock.Mock(version="10.2", paramstyle="named")
)
patch_engine(e)
t = table('foo', column('bar'))
stmt = select([t]).limit(5).offset(12)
"""
SELECT foo.bar
FROM foo OFFSET 12 ROWS FETCH FIRST 5 ROWS ONLY
"""
print stmt.compile(e)
thanks for bringing this to my attention!
Thanks,
Ralph
--
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.
