diff options
| -rw-r--r-- | lib/sqlalchemy/dialects/firebird/base.py | 9 | ||||
| -rw-r--r-- | lib/sqlalchemy/dialects/mssql/base.py | 9 | ||||
| -rw-r--r-- | lib/sqlalchemy/dialects/mysql/base.py | 16 | ||||
| -rw-r--r-- | lib/sqlalchemy/dialects/oracle/base.py | 18 | ||||
| -rw-r--r-- | lib/sqlalchemy/dialects/postgresql/base.py | 10 | ||||
| -rw-r--r-- | lib/sqlalchemy/dialects/sqlite/base.py | 10 | ||||
| -rw-r--r-- | lib/sqlalchemy/dialects/sybase/base.py | 12 | ||||
| -rw-r--r-- | lib/sqlalchemy/sql/compiler.py | 14 | ||||
| -rw-r--r-- | lib/sqlalchemy/sql/selectable.py | 75 | ||||
| -rw-r--r-- | test/orm/test_query.py | 13 |
10 files changed, 132 insertions, 54 deletions
diff --git a/lib/sqlalchemy/dialects/firebird/base.py b/lib/sqlalchemy/dialects/firebird/base.py index 21db57b68..a3867457e 100644 --- a/lib/sqlalchemy/dialects/firebird/base.py +++ b/lib/sqlalchemy/dialects/firebird/base.py @@ -77,7 +77,6 @@ from sqlalchemy.sql import expression from sqlalchemy.engine import base, default, reflection from sqlalchemy.sql import compiler - from sqlalchemy.types import (BIGINT, BLOB, DATE, FLOAT, INTEGER, NUMERIC, SMALLINT, TEXT, TIME, TIMESTAMP, Integer) @@ -300,10 +299,10 @@ class FBCompiler(sql.compiler.SQLCompiler): """ result = "" - if select._limit: - result += "FIRST %s " % self.process(sql.literal(select._limit)) - if select._offset: - result += "SKIP %s " % self.process(sql.literal(select._offset)) + if select._limit_clause: + result += "FIRST %s " % self.process(select._limit_clause) + if select._offset_clause: + result += "SKIP %s " % self.process(select._offset_clause) if select._distinct: result += "DISTINCT " return result diff --git a/lib/sqlalchemy/dialects/mssql/base.py b/lib/sqlalchemy/dialects/mssql/base.py index 3fcc95f46..6a13d1dca 100644 --- a/lib/sqlalchemy/dialects/mssql/base.py +++ b/lib/sqlalchemy/dialects/mssql/base.py @@ -740,16 +740,17 @@ class MSSQLCompiler(compiler.SQLCompiler): self.process(binary.right, **kw)) def get_select_precolumns(self, select): - """ MS-SQL puts TOP, its version of LIMIT, here """ - if select._distinct or select._limit is not None: + """ MS-SQL puts TOP, it's version of LIMIT here """ + limit = select._limit + if select._distinct or limit is not None: s = select._distinct and "DISTINCT " or "" # ODBC drivers and possibly others # don't support bind params in the SELECT clause on SQL Server. # so have to use literal here. - if select._limit is not None: + if limit is not None: if not select._offset: - s += "TOP %d " % select._limit + s += "TOP %d " % limit return s return compiler.SQLCompiler.get_select_precolumns(self, select) diff --git a/lib/sqlalchemy/dialects/mysql/base.py b/lib/sqlalchemy/dialects/mysql/base.py index 85cbd6e22..e8a15fdbc 100644 --- a/lib/sqlalchemy/dialects/mysql/base.py +++ b/lib/sqlalchemy/dialects/mysql/base.py @@ -1568,15 +1568,15 @@ class MySQLCompiler(compiler.SQLCompiler): # The latter is more readable for offsets but we're stuck with the # former until we can refine dialects by server revision. - limit, offset = select._limit, select._offset + limit_clause, offset_clause = select._limit_clause, select._offset_clause - if (limit, offset) == (None, None): + if (limit_clause, offset_clause) == (None, None): return '' - elif offset is not None: + elif offset_clause is not None: # As suggested by the MySQL docs, need to apply an # artificial limit if one wasn't provided # http://dev.mysql.com/doc/refman/5.0/en/select.html - if limit is None: + if limit_clause is None: # hardwire the upper limit. Currently # needed by OurSQL with Python 3 # (https://bugs.launchpad.net/oursql/+bug/686232), @@ -1584,15 +1584,15 @@ class MySQLCompiler(compiler.SQLCompiler): # bound as part of MySQL's "syntax" for OFFSET with # no LIMIT return ' \n LIMIT %s, %s' % ( - self.process(sql.literal(offset)), + self.process(offset_clause), "18446744073709551615") else: return ' \n LIMIT %s, %s' % ( - self.process(sql.literal(offset)), - self.process(sql.literal(limit))) + self.process(offset_clause), + self.process(limit_clause)) else: # No offset provided, so just use the limit - return ' \n LIMIT %s' % (self.process(sql.literal(limit)),) + return ' \n LIMIT %s' % (self.process(limit_clause),) def update_limit_clause(self, update_stmt): limit = update_stmt.kwargs.get('%s_limit' % self.dialect.name, None) diff --git a/lib/sqlalchemy/dialects/oracle/base.py b/lib/sqlalchemy/dialects/oracle/base.py index 5dcc93f79..b0f32f491 100644 --- a/lib/sqlalchemy/dialects/oracle/base.py +++ b/lib/sqlalchemy/dialects/oracle/base.py @@ -652,24 +652,26 @@ class OracleCompiler(compiler.SQLCompiler): # Wrap the middle select and add the hint limitselect = sql.select([c for c in select.c]) - if select._limit and self.dialect.optimize_limits: - limitselect = limitselect.prefix_with("/*+ FIRST_ROWS(%d) */" % select._limit) + limit = select._limit + if limit and self.dialect.optimize_limits: + limitselect = limitselect.prefix_with("/*+ FIRST_ROWS(%d) */" % limit) limitselect._oracle_visit = True limitselect._is_wrapper = True # If needed, add the limiting clause - if select._limit is not None: - max_row = select._limit - if select._offset is not None: - max_row += select._offset + offset = select._offset + if limit is not None: + max_row = limit + if offset is not None: + max_row += offset if not self.dialect.use_binds_for_limits: max_row = sql.literal_column("%d" % max_row) limitselect.append_whereclause( sql.literal_column("ROWNUM") <= max_row) # If needed, add the ora_rn, and wrap again with offset. - if select._offset is None: + if offset is None: limitselect._for_update_arg = select._for_update_arg select = limitselect else: @@ -683,7 +685,7 @@ class OracleCompiler(compiler.SQLCompiler): offsetselect._oracle_visit = True offsetselect._is_wrapper = True - offset_value = select._offset + offset_value = offset if not self.dialect.use_binds_for_limits: offset_value = sql.literal_column("%d" % offset_value) offsetselect.append_whereclause( diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py index bcbf0b12c..e2accfedb 100644 --- a/lib/sqlalchemy/dialects/postgresql/base.py +++ b/lib/sqlalchemy/dialects/postgresql/base.py @@ -1161,12 +1161,12 @@ class PGCompiler(compiler.SQLCompiler): def limit_clause(self, select): text = "" - if select._limit is not None: - text += " \n LIMIT " + self.process(sql.literal(select._limit)) - if select._offset is not None: - if select._limit is None: + if select._limit_clause is not None: + text += " \n LIMIT " + self.process(select._limit_clause) + if select._offset_clause is not None: + if select._limit_clause is None: text += " \n LIMIT ALL" - text += " OFFSET " + self.process(sql.literal(select._offset)) + text += " OFFSET " + self.process(select._offset_clause) return text def format_from_hint_text(self, sqltext, table, hint, iscrud): diff --git a/lib/sqlalchemy/dialects/sqlite/base.py b/lib/sqlalchemy/dialects/sqlite/base.py index 7687493b2..de8b01e6f 100644 --- a/lib/sqlalchemy/dialects/sqlite/base.py +++ b/lib/sqlalchemy/dialects/sqlite/base.py @@ -519,12 +519,12 @@ class SQLiteCompiler(compiler.SQLCompiler): def limit_clause(self, select): text = "" - if select._limit is not None: - text += "\n LIMIT " + self.process(sql.literal(select._limit)) - if select._offset is not None: - if select._limit is None: + if select._limit_clause is not None: + text += "\n LIMIT " + self.process(select._limit_clause) + if select._offset_clause is not None: + if select._limit_clause is None: text += "\n LIMIT " + self.process(sql.literal(-1)) - text += " OFFSET " + self.process(sql.literal(select._offset)) + text += " OFFSET " + self.process(select._offset_clause) else: text += " OFFSET " + self.process(sql.literal(0)) return text diff --git a/lib/sqlalchemy/dialects/sybase/base.py b/lib/sqlalchemy/dialects/sybase/base.py index 501270778..3e61b5ba6 100644 --- a/lib/sqlalchemy/dialects/sybase/base.py +++ b/lib/sqlalchemy/dialects/sybase/base.py @@ -325,18 +325,20 @@ class SybaseSQLCompiler(compiler.SQLCompiler): s = select._distinct and "DISTINCT " or "" # TODO: don't think Sybase supports # bind params for FIRST / TOP - if select._limit: + limit = select._limit + if limit: #if select._limit == 1: #s += "FIRST " #else: #s += "TOP %s " % (select._limit,) - s += "TOP %s " % (select._limit,) - if select._offset: - if not select._limit: + s += "TOP %s " % (limit,) + offset = select._offset + if offset: + if not limit: # FIXME: sybase doesn't allow an offset without a limit # so use a huge value for TOP here s += "TOP 1000000 " - s += "START AT %s " % (select._offset + 1,) + s += "START AT %s " % (offset + 1,) return s def get_from_hint_text(self, table, text): diff --git a/lib/sqlalchemy/sql/compiler.py b/lib/sqlalchemy/sql/compiler.py index cd01ea5e5..65d0169f4 100644 --- a/lib/sqlalchemy/sql/compiler.py +++ b/lib/sqlalchemy/sql/compiler.py @@ -773,7 +773,7 @@ class SQLCompiler(Compiled): text += " GROUP BY " + group_by text += self.order_by_clause(cs, **kwargs) - text += (cs._limit is not None or cs._offset is not None) and \ + text += (cs._limit_clause is not None or cs._offset_clause is not None) and \ self.limit_clause(cs) or "" if self.ctes and \ @@ -1557,7 +1557,7 @@ class SQLCompiler(Compiled): text += self.order_by_clause(select, order_by_select=order_by_select, **kwargs) - if select._limit is not None or select._offset is not None: + if select._limit_clause is not None or select._offset_clause is not None: text += self.limit_clause(select) if select._for_update_arg is not None: @@ -1625,12 +1625,12 @@ class SQLCompiler(Compiled): def limit_clause(self, select): text = "" - if select._limit is not None: - text += "\n LIMIT " + self.process(elements.literal(select._limit)) - if select._offset is not None: - if select._limit is None: + if select._limit_clause is not None: + text += "\n LIMIT " + self.process(select._limit_clause) + if select._offset_clause is not None: + if select._limit_clause is None: text += "\n LIMIT -1" - text += " OFFSET " + self.process(elements.literal(select._offset)) + text += " OFFSET " + self.process(select._offset_clause) return text def visit_table(self, table, asfrom=False, iscrud=False, ashint=False, diff --git a/lib/sqlalchemy/sql/selectable.py b/lib/sqlalchemy/sql/selectable.py index a13186097..767eb086c 100644 --- a/lib/sqlalchemy/sql/selectable.py +++ b/lib/sqlalchemy/sql/selectable.py @@ -10,7 +10,7 @@ SQL tables and derived rowsets. """ from .elements import ClauseElement, TextClause, ClauseList, \ - and_, Grouping, UnaryExpression, literal_column + and_, Grouping, UnaryExpression, literal_column, BindParameter from .elements import _clone, \ _literal_as_text, _interpret_as_column_or_from, _expand_cloned,\ _select_iterables, _anonymous_label, _clause_element_as_expr,\ @@ -28,6 +28,8 @@ import operator import collections from .annotation import Annotated import itertools +from sqlalchemy.sql.visitors import Visitable + def _interpret_as_from(element): insp = inspection.inspect(element, raiseerr=False) @@ -46,6 +48,39 @@ def _interpret_as_select(element): element = element.select() return element +def _offset_or_limit_clause(element, name=None, type_=None): + """ + If the element is a custom clause of some sort, returns (None, element) + If the element is a BindParameter, return (element.effective_value, element) + Otherwise, assume element is an int and create a new bindparam and return (asint(element), BindParameter(...)) + """ + if element is None: + return None + if hasattr(element, '__clause_element__'): + return element.__clause_element__() + if isinstance(element, Visitable): + return element + + value = util.asint(element) + return BindParameter(name, value, type_=type_, unique=True) + +def _offset_or_limit_clause_asint(clause): + """ + Get the integer value of an offset or limit clause, for database engines that + require it to be a plain integer instead of a BindParameter or other custom + clause. + + If the clause is None, returns None. + If the clause is not a BindParameter, throws an exception. + If the clause is a BindParameter but its value is not set yet or not an int, throws an exception. + Otherwise, returns the integer in the clause. + """ + if clause is None: + return None + if not isinstance(clause, BindParameter): + raise Exception("Limit is not a simple integer") + return util.asint(clause.effective_value) + def subquery(alias, *args, **kwargs): """Return an :class:`.Alias` object derived from a :class:`.Select`. @@ -1536,8 +1571,8 @@ class GenerativeSelect(SelectBase): """ _order_by_clause = ClauseList() _group_by_clause = ClauseList() - _limit = None - _offset = None + _limit_clause = None + _offset_clause = None _for_update_arg = None def __init__(self, @@ -1562,9 +1597,9 @@ class GenerativeSelect(SelectBase): self._execution_options.union( {'autocommit': autocommit}) if limit is not None: - self._limit = util.asint(limit) + self._limit_clause = _offset_or_limit_clause(limit) if offset is not None: - self._offset = util.asint(offset) + self._offset_clause = _offset_or_limit_clause(offset) self._bind = bind if order_by is not None: @@ -1639,19 +1674,37 @@ class GenerativeSelect(SelectBase): """ self.use_labels = True + @property + def _limit(self): + """ + Get an integer value for the limit. This should only be used by code that + cannot support a limit as a BindParameter or other custom clause as it will + throw an exception if the limit isn't currently set to an integer. + """ + return _offset_or_limit_clause_asint(self._limit_clause) + + @property + def _offset(self): + """ + Get an integer value for the offset. This should only be used by code that + cannot support an offset as a BindParameter or other custom clause as it will + throw an exception if the offset isn't currently set to an integer. + """ + return _offset_or_limit_clause_asint(self._offset_clause) + @_generative def limit(self, limit): """return a new selectable with the given LIMIT criterion applied.""" - self._limit = util.asint(limit) + self._limit_clause = _offset_or_limit_clause(limit) @_generative def offset(self, offset): """return a new selectable with the given OFFSET criterion applied.""" - self._offset = util.asint(offset) + self._offset_clause = _offset_or_limit_clause(offset) @_generative def order_by(self, *clauses): @@ -1712,6 +1765,12 @@ class GenerativeSelect(SelectBase): self._group_by_clause = ClauseList(*clauses) + def _copy_internals(self, clone=_clone, **kw): + if self._limit_clause is not None: + self._limit_clause = clone(self._limit_clause, **kw) + if self._offset_clause is not None: + self._offset_clause = clone(self._offset_clause, **kw) + class CompoundSelect(GenerativeSelect): """Forms the basis of ``UNION``, ``UNION ALL``, and other SELECT-based set operations. @@ -1930,6 +1989,7 @@ class CompoundSelect(GenerativeSelect): "addition of columns to underlying selectables") def _copy_internals(self, clone=_clone, **kw): + super(CompoundSelect, self)._copy_internals(clone, **kw) self._reset_exported() self.selects = [clone(s, **kw) for s in self.selects] if hasattr(self, '_col_map'): @@ -2380,6 +2440,7 @@ class Select(HasPrefixes, GenerativeSelect): return False def _copy_internals(self, clone=_clone, **kw): + super(Select, self)._copy_internals(clone, **kw) # Select() object has been cloned and probably adapted by the # given clone function. Apply the cloning function to internal diff --git a/test/orm/test_query.py b/test/orm/test_query.py index bb731db5c..b4418042e 100644 --- a/test/orm/test_query.py +++ b/test/orm/test_query.py @@ -1275,6 +1275,19 @@ class FilterTest(QueryTest, AssertsCompiledSQL): assert [] == sess.query(User).order_by(User.id)[3:3] assert [] == sess.query(User).order_by(User.id)[0:0] + def test_select_with_bindparam_offset_limit(self): + """Does a query allow bindparam for the limit?""" + User = self.classes.User + sess = create_session() + users = [] + + q1 = sess.query(self.classes.User).order_by(self.classes.User.id).limit(bindparam('n')) + for n in xrange(1,4): + users[:] = q1.params(n=n).all() + assert len(users) == n + + assert [User(id=8), User(id=9)] == sess.query(User).order_by(User.id).limit(bindparam('limit')).offset(bindparam('offset')).params(limit=2, offset=1).all() + assert [User(id=8), User(id=9)] == list(sess.query(User).params(a=1, b=3).order_by(User.id)[bindparam('a'):bindparam('b')]) @testing.requires.boolean_col_expressions def test_exists(self): |