diff options
Diffstat (limited to 'lib/sqlalchemy')
| -rw-r--r-- | lib/sqlalchemy/dialects/mssql/base.py | 30 | ||||
| -rw-r--r-- | lib/sqlalchemy/dialects/oracle/base.py | 109 | ||||
| -rw-r--r-- | lib/sqlalchemy/dialects/oracle/cx_oracle.py | 12 | ||||
| -rw-r--r-- | lib/sqlalchemy/dialects/sybase/base.py | 17 | ||||
| -rw-r--r-- | lib/sqlalchemy/engine/default.py | 123 | ||||
| -rw-r--r-- | lib/sqlalchemy/sql/coercions.py | 7 | ||||
| -rw-r--r-- | lib/sqlalchemy/sql/compiler.py | 158 | ||||
| -rw-r--r-- | lib/sqlalchemy/sql/elements.py | 52 | ||||
| -rw-r--r-- | lib/sqlalchemy/sql/sqltypes.py | 2 | ||||
| -rw-r--r-- | lib/sqlalchemy/testing/assertsql.py | 5 | ||||
| -rw-r--r-- | lib/sqlalchemy/testing/requirements.py | 20 | ||||
| -rw-r--r-- | lib/sqlalchemy/testing/suite/test_select.py | 190 |
12 files changed, 558 insertions, 167 deletions
diff --git a/lib/sqlalchemy/dialects/mssql/base.py b/lib/sqlalchemy/dialects/mssql/base.py index 87b675c5f..7ab680ca4 100644 --- a/lib/sqlalchemy/dialects/mssql/base.py +++ b/lib/sqlalchemy/dialects/mssql/base.py @@ -246,17 +246,29 @@ LIMIT/OFFSET Support -------------------- MSSQL has no support for the LIMIT or OFFSET keywords. LIMIT is -supported directly through the ``TOP`` Transact SQL keyword:: +supported directly through the ``TOP`` Transact SQL keyword. A statement +such as:: - select.limit + select([some_table]).limit(5) -will yield:: +will render similarly to:: - SELECT TOP n + SELECT TOP 5 col1, col2.. FROM table -If using SQL Server 2005 or above, LIMIT with OFFSET -support is available through the ``ROW_NUMBER OVER`` construct. -For versions below 2005, LIMIT with OFFSET usage will fail. +LIMIT with OFFSET support is implemented using the using the ``ROW_NUMBER()`` +window function. A statement such as:: + + select([some_table]).order_by(some_table.c.col3).limit(5).offset(10) + +will render similarly to:: + + SELECT anon_1.col1, anon_1.col2 FROM (SELECT col1, col2, + ROW_NUMBER() OVER (ORDER BY col3) AS + mssql_rn FROM table WHERE t.x = :x_1) AS + anon_1 WHERE mssql_rn > :param_1 AND mssql_rn <= :param_2 + :param_1 + +Note that when using LIMIT and OFFSET together, the statement must have +an ORDER BY as well. .. _mssql_isolation_level: @@ -1603,8 +1615,8 @@ class MSSQLCompiler(compiler.SQLCompiler): # ODBC drivers and possibly others # don't support bind params in the SELECT clause on SQL Server. # so have to use literal here. - s += "TOP %d " % select._limit - + kw["literal_execute"] = True + s += "TOP %s " % self.process(select._limit_clause, **kw) if s: return s else: diff --git a/lib/sqlalchemy/dialects/oracle/base.py b/lib/sqlalchemy/dialects/oracle/base.py index 868c64ed3..768df7426 100644 --- a/lib/sqlalchemy/dialects/oracle/base.py +++ b/lib/sqlalchemy/dialects/oracle/base.py @@ -25,7 +25,7 @@ which affect the behavior of the dialect regardless of driver in use. * ``optimize_limits`` - defaults to ``False``. see the section on LIMIT/OFFSET. -* ``use_binds_for_limits`` - defaults to ``True``. see the section on +* ``use_binds_for_limits`` - deprecated. see the section on LIMIT/OFFSET. Auto Increment Behavior @@ -71,30 +71,33 @@ lowercase names should be used on the SQLAlchemy side. LIMIT/OFFSET Support -------------------- -Oracle has no support for the LIMIT or OFFSET keywords. SQLAlchemy uses -a wrapped subquery approach in conjunction with ROWNUM. The exact methodology -is taken from -http://www.oracle.com/technetwork/issue-archive/2006/06-sep/o56asktom-086197.html . +Oracle has no direct support for LIMIT and OFFSET until version 12c. +To achieve this behavior across all widely used versions of Oracle starting +with the 8 series, SQLAlchemy currently makes use of ROWNUM to achieve +LIMIT/OFFSET; the exact methodology is taken from +https://blogs.oracle.com/oraclemagazine/on-rownum-and-limiting-results . -There are two options which affect its behavior: +There is currently a single option to affect its behavior: -* the "FIRST ROWS()" optimization keyword is not used by default. To enable +* the "FIRST_ROWS()" optimization keyword is not used by default. To enable the usage of this optimization directive, specify ``optimize_limits=True`` to :func:`.create_engine`. -* the values passed for the limit/offset are sent as bound parameters. Some - users have observed that Oracle produces a poor query plan when the values - are sent as binds and not rendered literally. To render the limit/offset - values literally within the SQL statement, specify - ``use_binds_for_limits=False`` to :func:`.create_engine`. - -Some users have reported better performance when the entirely different -approach of a window query is used, i.e. ROW_NUMBER() OVER (ORDER BY), to -provide LIMIT/OFFSET (note that the majority of users don't observe this). -To suit this case the method used for LIMIT/OFFSET can be replaced entirely. -See the recipe at -http://www.sqlalchemy.org/trac/wiki/UsageRecipes/WindowFunctionsByDefault -which installs a select compiler that overrides the generation of limit/offset -with a window function. + +.. versionchanged:: 1.4 + The Oracle dialect renders limit/offset integer values using a "post + compile" scheme which renders the integer directly before passing the + statement to the cursor for execution. The ``use_binds_for_limits`` flag + no longer has an effect. + + .. seealso:: + + :ref:`change_4808`. + +Support for changing the row number strategy, which would include one that +makes use of the ``row_number()`` window function as well as one that makes +use of the Oracle 12c "FETCH FIRST N ROW / OFFSET N ROWS" keywords may be +added in a future release. + .. _oracle_returning: @@ -364,6 +367,7 @@ from ...types import CHAR from ...types import CLOB from ...types import FLOAT from ...types import INTEGER +from ...types import Integer from ...types import NCHAR from ...types import NVARCHAR from ...types import TIMESTAMP @@ -855,17 +859,9 @@ class OracleCompiler(compiler.SQLCompiler): limit_clause = select._limit_clause offset_clause = select._offset_clause if limit_clause is not None or offset_clause is not None: - # See http://www.oracle.com/technology/oramag/oracle/06-sep/\ - # o56asktom.html - # - # Generalized form of an Oracle pagination query: - # select ... from ( - # select /*+ FIRST_ROWS(N) */ ...., rownum as ora_rn from - # ( select distinct ... where ... order by ... - # ) where ROWNUM <= :limit+:offset - # ) where ora_rn > :offset - # Outer select and "ROWNUM as ora_rn" can be dropped if - # limit=0 + # currently using form at: + # https://blogs.oracle.com/oraclemagazine/\ + # on-rownum-and-limiting-results kwargs["select_wraps_for"] = orig_select = select select = select._generate() @@ -896,8 +892,17 @@ class OracleCompiler(compiler.SQLCompiler): and self.dialect.optimize_limits and select._simple_int_limit ): + param = sql.bindparam( + "_ora_frow", + select._limit, + type_=Integer, + literal_execute=True, + unique=True, + ) limitselect = limitselect.prefix_with( - "/*+ FIRST_ROWS(%d) */" % select._limit + expression.text( + "/*+ FIRST_ROWS(:_ora_frow) */" + ).bindparams(param) ) limitselect._oracle_visit = True @@ -913,14 +918,20 @@ class OracleCompiler(compiler.SQLCompiler): # If needed, add the limiting clause if limit_clause is not None: - if not self.dialect.use_binds_for_limits: - # use simple int limits, will raise an exception - # if the limit isn't specified this way + if select._simple_int_limit and ( + offset_clause is None or select._simple_int_offset + ): max_row = select._limit if offset_clause is not None: max_row += select._offset - max_row = sql.literal_column("%d" % max_row) + max_row = sql.bindparam( + None, + max_row, + type_=Integer, + literal_execute=True, + unique=True, + ) else: max_row = limit_clause if offset_clause is not None: @@ -969,10 +980,15 @@ class OracleCompiler(compiler.SQLCompiler): adapter.traverse(elem) for elem in for_update.of ] - if not self.dialect.use_binds_for_limits: - offset_clause = sql.literal_column( - "%d" % select._offset + if select._simple_int_offset: + offset_clause = sql.bindparam( + None, + select._offset, + Integer, + literal_execute=True, + unique=True, ) + offsetselect = offsetselect.where( sql.literal_column("ora_rn") > offset_clause ) @@ -1150,11 +1166,21 @@ class OracleDialect(default.DefaultDialect): (sa_schema.Index, {"bitmap": False, "compress": False}), ] + @util.deprecated_params( + use_binds_for_limits=( + "1.4", + "The ``use_binds_for_limits`` Oracle dialect parameter is " + "deprecated. The dialect now renders LIMIT /OFFSET integers " + "inline in all cases using a post-compilation hook, so that the " + "value is still represented by a 'bound parameter' on the Core " + "Expression side.", + ) + ) def __init__( self, use_ansi=True, optimize_limits=False, - use_binds_for_limits=True, + use_binds_for_limits=None, use_nchar_for_unicode=False, exclude_tablespaces=("SYSTEM", "SYSAUX"), **kwargs @@ -1163,7 +1189,6 @@ class OracleDialect(default.DefaultDialect): self._use_nchar_for_unicode = use_nchar_for_unicode self.use_ansi = use_ansi self.optimize_limits = optimize_limits - self.use_binds_for_limits = use_binds_for_limits self.exclude_tablespaces = exclude_tablespaces def initialize(self, connection): diff --git a/lib/sqlalchemy/dialects/oracle/cx_oracle.py b/lib/sqlalchemy/dialects/oracle/cx_oracle.py index 90d049aa0..fbacb622e 100644 --- a/lib/sqlalchemy/dialects/oracle/cx_oracle.py +++ b/lib/sqlalchemy/dialects/oracle/cx_oracle.py @@ -518,13 +518,13 @@ class OracleCompiler_cx_oracle(OracleCompiler): quote is True or quote is not False and self.preparer._bindparam_requires_quotes(name) + and not kw.get("post_compile", False) ): - if kw.get("expanding", False): - raise exc.CompileError( - "Can't use expanding feature with parameter name " - "%r on Oracle; it requires quoting which is not supported " - "in this context." % name - ) + # interesting to note about expanding parameters - since the + # new parameters take the form <paramname>_<int>, at least if + # they are originally formed from reserved words, they no longer + # need quoting :). names that include illegal characters + # won't work however. quoted_name = '"%s"' % name self._quoted_bind_names[name] = quoted_name return OracleCompiler.bindparam_string(self, quoted_name, **kw) diff --git a/lib/sqlalchemy/dialects/sybase/base.py b/lib/sqlalchemy/dialects/sybase/base.py index 71ff62976..532affb4a 100644 --- a/lib/sqlalchemy/dialects/sybase/base.py +++ b/lib/sqlalchemy/dialects/sybase/base.py @@ -513,17 +513,12 @@ class SybaseSQLCompiler(compiler.SQLCompiler): def get_select_precolumns(self, select, **kw): s = select._distinct and "DISTINCT " or "" - # TODO: don't think Sybase supports - # bind params for FIRST / TOP - limit = select._limit - if limit: - # if select._limit == 1: - # s += "FIRST " - # else: - # s += "TOP %s " % (select._limit,) - s += "TOP %s " % (limit,) - offset = select._offset - if offset: + + if select._simple_int_limit and not select._offset: + kw["literal_execute"] = True + s += "TOP %s " % self.process(select._limit_clause, **kw) + + if select._offset: raise NotImplementedError("Sybase ASE does not support OFFSET") return s diff --git a/lib/sqlalchemy/engine/default.py b/lib/sqlalchemy/engine/default.py index fb1728eab..eac593125 100644 --- a/lib/sqlalchemy/engine/default.py +++ b/lib/sqlalchemy/engine/default.py @@ -695,8 +695,10 @@ class DefaultExecutionContext(interfaces.ExecutionContext): processors = compiled._bind_processors - if compiled.contains_expanding_parameters: - positiontup = self._expand_in_parameters(compiled, processors) + if compiled.literal_execute_params: + positiontup = self._literal_execute_parameters( + compiled, processors + ) elif compiled.positional: positiontup = self.compiled.positiontup @@ -744,21 +746,34 @@ class DefaultExecutionContext(interfaces.ExecutionContext): return self - def _expand_in_parameters(self, compiled, processors): - """handle special 'expanding' parameters, IN tuples that are rendered - on a per-parameter basis for an otherwise fixed SQL statement string. + def _literal_execute_parameters(self, compiled, processors): + """handle special post compile parameters. + + These include: + + * "expanding" parameters -typically IN tuples that are rendered + on a per-parameter basis for an otherwise fixed SQL statement string. + + * literal_binds compiled with the literal_execute flag. Used for + things like SQL Server "TOP N" where the driver does not accommodate + N as a bound parameter. """ if self.executemany: raise exc.InvalidRequestError( - "'expanding' parameters can't be used with " "executemany()" + "'literal_execute' or 'expanding' parameters can't be " + "used with executemany()" ) - if self.compiled.positional and self.compiled._numeric_binds: - # I'm not familiar with any DBAPI that uses 'numeric' + if compiled.positional and compiled._numeric_binds: + # I'm not familiar with any DBAPI that uses 'numeric'. + # strategy would likely be to make use of numbers greater than + # the highest number present; then for expanding parameters, + # append them to the end of the parameter list. that way + # we avoid having to renumber all the existing parameters. raise NotImplementedError( - "'expanding' bind parameters not supported with " - "'numeric' paramstyle at this time." + "'post-compile' bind parameters are not supported with " + "the 'numeric' paramstyle at this time." ) self._expanded_parameters = {} @@ -773,12 +788,21 @@ class DefaultExecutionContext(interfaces.ExecutionContext): to_update_sets = {} for name in ( - self.compiled.positiontup + compiled.positiontup if compiled.positional - else self.compiled.binds + else compiled.bind_names.values() ): - parameter = self.compiled.binds[name] - if parameter.expanding: + parameter = compiled.binds[name] + if parameter in compiled.literal_execute_params: + + if not parameter.expanding: + value = compiled_params.pop(name) + replacement_expressions[ + name + ] = compiled.render_literal_bindparam( + parameter, render_literal_value=value + ) + continue if name in replacement_expressions: to_update = to_update_sets[name] @@ -791,58 +815,25 @@ class DefaultExecutionContext(interfaces.ExecutionContext): # param. values = compiled_params.pop(name) - if not values: - to_update = to_update_sets[name] = [] - replacement_expressions[ - name - ] = self.compiled.visit_empty_set_expr( - parameter._expanding_in_types - if parameter._expanding_in_types - else [parameter.type] - ) + leep = compiled._literal_execute_expanding_parameter + to_update, replacement_expr = leep(name, parameter, values) - elif isinstance(values[0], (tuple, list)): - to_update = to_update_sets[name] = [ - ("%s_%s_%s" % (name, i, j), value) - for i, tuple_element in enumerate(values, 1) - for j, value in enumerate(tuple_element, 1) - ] - replacement_expressions[name] = ( - "VALUES " if self.dialect.tuple_in_values else "" - ) + ", ".join( - "(%s)" - % ", ".join( - self.compiled.bindtemplate - % { - "name": to_update[ - i * len(tuple_element) + j - ][0] - } - for j, value in enumerate(tuple_element) - ) - for i, tuple_element in enumerate(values) - ) - else: - to_update = to_update_sets[name] = [ - ("%s_%s" % (name, i), value) - for i, value in enumerate(values, 1) - ] - replacement_expressions[name] = ", ".join( - self.compiled.bindtemplate % {"name": key} - for key, value in to_update - ) + to_update_sets[name] = to_update + replacement_expressions[name] = replacement_expr - compiled_params.update(to_update) - processors.update( - (key, processors[name]) - for key, value in to_update - if name in processors - ) - if compiled.positional: - positiontup.extend(name for name, value in to_update) - self._expanded_parameters[name] = [ - expand_key for expand_key, value in to_update - ] + if not parameter.literal_execute: + compiled_params.update(to_update) + + processors.update( + (key, processors[name]) + for key, value in to_update + if name in processors + ) + if compiled.positional: + positiontup.extend(name for name, value in to_update) + self._expanded_parameters[name] = [ + expand_key for expand_key, value in to_update + ] elif compiled.positional: positiontup.append(name) @@ -850,7 +841,7 @@ class DefaultExecutionContext(interfaces.ExecutionContext): return replacement_expressions[m.group(1)] self.statement = re.sub( - r"\[EXPANDING_(\S+)\]", process_expanding, self.statement + r"\[POSTCOMPILE_(\S+)\]", process_expanding, self.statement ) return positiontup @@ -1214,6 +1205,8 @@ class DefaultExecutionContext(interfaces.ExecutionContext): inputsizes = {} for bindparam in self.compiled.bind_names: + if bindparam in self.compiled.literal_execute_params: + continue dialect_impl = bindparam.type._unwrapped_dialect_impl(self.dialect) dialect_impl_cls = type(dialect_impl) diff --git a/lib/sqlalchemy/sql/coercions.py b/lib/sqlalchemy/sql/coercions.py index 8a9f0b979..a7a856bba 100644 --- a/lib/sqlalchemy/sql/coercions.py +++ b/lib/sqlalchemy/sql/coercions.py @@ -294,11 +294,11 @@ class BinaryElementImpl( def _post_coercion(self, resolved, expr, **kw): if ( - isinstance(resolved, elements.BindParameter) + isinstance(resolved, (elements.Grouping, elements.BindParameter)) and resolved.type._isnull + and not expr.type._isnull ): - resolved = resolved._clone() - resolved.type = expr.type + resolved = resolved._with_binary_element_type(expr.type) return resolved @@ -360,6 +360,7 @@ class InElementImpl(RoleImpl, roles.InElementRole): element = element._with_expanding_in_types( [elem.type for elem in expr] ) + return element else: return element diff --git a/lib/sqlalchemy/sql/compiler.py b/lib/sqlalchemy/sql/compiler.py index fa7eeaecf..8df93a60b 100644 --- a/lib/sqlalchemy/sql/compiler.py +++ b/lib/sqlalchemy/sql/compiler.py @@ -36,6 +36,7 @@ from . import roles from . import schema from . import selectable from . import sqltypes +from .base import NO_ARG from .. import exc from .. import util @@ -463,14 +464,6 @@ class SQLCompiler(Compiled): columns with the table name (i.e. MySQL only) """ - contains_expanding_parameters = False - """True if we've encountered bindparam(..., expanding=True). - - These need to be converted before execution time against the - string statement. - - """ - ansi_bind_rules = False """SQL 92 doesn't allow bind parameters to be used in the columns clause of a SELECT, nor does it allow @@ -507,6 +500,8 @@ class SQLCompiler(Compiled): """ + literal_execute_params = frozenset() + insert_prefetch = update_prefetch = () def __init__( @@ -1267,6 +1262,81 @@ class SQLCompiler(Compiled): % self.dialect.name ) + def _literal_execute_expanding_parameter_literal_binds( + self, parameter, values + ): + if not values: + replacement_expression = self.visit_empty_set_expr( + parameter._expanding_in_types + if parameter._expanding_in_types + else [parameter.type] + ) + + elif isinstance(values[0], (tuple, list)): + replacement_expression = ( + "VALUES " if self.dialect.tuple_in_values else "" + ) + ", ".join( + "(%s)" + % ( + ", ".join( + self.render_literal_value(value, parameter.type) + for value in tuple_element + ) + ) + for i, tuple_element in enumerate(values) + ) + else: + replacement_expression = ", ".join( + self.render_literal_value(value, parameter.type) + for value in values + ) + + return (), replacement_expression + + def _literal_execute_expanding_parameter(self, name, parameter, values): + if parameter.literal_execute: + return self._literal_execute_expanding_parameter_literal_binds( + parameter, values + ) + + if not values: + to_update = [] + replacement_expression = self.visit_empty_set_expr( + parameter._expanding_in_types + if parameter._expanding_in_types + else [parameter.type] + ) + + elif isinstance(values[0], (tuple, list)): + to_update = [ + ("%s_%s_%s" % (name, i, j), value) + for i, tuple_element in enumerate(values, 1) + for j, value in enumerate(tuple_element, 1) + ] + replacement_expression = ( + "VALUES " if self.dialect.tuple_in_values else "" + ) + ", ".join( + "(%s)" + % ( + ", ".join( + self.bindtemplate + % {"name": to_update[i * len(tuple_element) + j][0]} + for j, value in enumerate(tuple_element) + ) + ) + for i, tuple_element in enumerate(values) + ) + else: + to_update = [ + ("%s_%s" % (name, i), value) + for i, value in enumerate(values, 1) + ] + replacement_expression = ", ".join( + self.bindtemplate % {"name": key} for key, value in to_update + ) + + return to_update, replacement_expression + def visit_binary( self, binary, override_operator=None, eager_grouping=False, **kw ): @@ -1457,6 +1527,7 @@ class SQLCompiler(Compiled): within_columns_clause=False, literal_binds=False, skip_bind_expression=False, + literal_execute=False, **kwargs ): @@ -1469,18 +1540,28 @@ class SQLCompiler(Compiled): skip_bind_expression=True, within_columns_clause=within_columns_clause, literal_binds=literal_binds, + literal_execute=literal_execute, **kwargs ) - if literal_binds or (within_columns_clause and self.ansi_bind_rules): - if bindparam.value is None and bindparam.callable is None: - raise exc.CompileError( - "Bind parameter '%s' without a " - "renderable value not allowed here." % bindparam.key - ) - return self.render_literal_bindparam( + if not literal_binds: + post_compile = ( + literal_execute + or bindparam.literal_execute + or bindparam.expanding + ) + else: + post_compile = False + + if not literal_execute and ( + literal_binds or (within_columns_clause and self.ansi_bind_rules) + ): + ret = self.render_literal_bindparam( bindparam, within_columns_clause=True, **kwargs ) + if bindparam.expanding: + ret = "(%s)" % ret + return ret name = self._truncate_bindparam(bindparam) @@ -1508,13 +1589,38 @@ class SQLCompiler(Compiled): self.binds[bindparam.key] = self.binds[name] = bindparam - return self.bindparam_string( - name, expanding=bindparam.expanding, **kwargs + if post_compile: + self.literal_execute_params |= {bindparam} + + ret = self.bindparam_string( + name, + post_compile=post_compile, + expanding=bindparam.expanding, + **kwargs ) + if bindparam.expanding: + ret = "(%s)" % ret + return ret + + def render_literal_bindparam( + self, bindparam, render_literal_value=NO_ARG, **kw + ): + if render_literal_value is not NO_ARG: + value = render_literal_value + else: + if bindparam.value is None and bindparam.callable is None: + raise exc.CompileError( + "Bind parameter '%s' without a " + "renderable value not allowed here." % bindparam.key + ) + value = bindparam.effective_value - def render_literal_bindparam(self, bindparam, **kw): - value = bindparam.effective_value - return self.render_literal_value(value, bindparam.type) + if bindparam.expanding: + leep = self._literal_execute_expanding_parameter_literal_binds + to_update, replacement_expr = leep(bindparam, value) + return replacement_expr + else: + return self.render_literal_value(value, bindparam.type) def render_literal_value(self, value, type_): """Render the value of a bind parameter as a quoted literal. @@ -1577,16 +1683,20 @@ class SQLCompiler(Compiled): return derived + "_" + str(anonymous_counter) def bindparam_string( - self, name, positional_names=None, expanding=False, **kw + self, + name, + positional_names=None, + post_compile=False, + expanding=False, + **kw ): if self.positional: if positional_names is not None: positional_names.append(name) else: self.positiontup.append(name) - if expanding: - self.contains_expanding_parameters = True - return "([EXPANDING_%s])" % name + if post_compile: + return "[POSTCOMPILE_%s]" % name else: return self.bindtemplate % {"name": name} diff --git a/lib/sqlalchemy/sql/elements.py b/lib/sqlalchemy/sql/elements.py index 669519d1a..42e7522ae 100644 --- a/lib/sqlalchemy/sql/elements.py +++ b/lib/sqlalchemy/sql/elements.py @@ -211,6 +211,15 @@ class ClauseElement(roles.SQLRole, Visitable): return c + def _with_binary_element_type(self, type_): + """in the context of binary expression, convert the type of this + object to the one given. + + applies only to :class:`.ColumnElement` classes. + + """ + return self + def _cache_key(self, **kw): """return an optional cache key. @@ -732,6 +741,14 @@ class ColumnElement( def type(self): return type_api.NULLTYPE + def _with_binary_element_type(self, type_): + cloned = self._clone() + cloned._copy_internals( + clone=lambda element: element._with_binary_element_type(type_) + ) + cloned.type = type_ + return cloned + @util.memoized_property def comparator(self): try: @@ -986,6 +1003,7 @@ class BindParameter(roles.InElementRole, ColumnElement): callable_=None, expanding=False, isoutparam=False, + literal_execute=False, _compared_to_operator=None, _compared_to_type=None, ): @@ -1198,6 +1216,30 @@ class BindParameter(roles.InElementRole, ColumnElement): :func:`.outparam` + :param literal_execute: + if True, the bound parameter will be rendered in the compile phase + with a special "POSTCOMPILE" token, and the SQLAlchemy compiler will + render the final value of the parameter into the SQL statement at + statement execution time, omitting the value from the parameter + dictionary / list passed to DBAPI ``cursor.execute()``. This + produces a similar effect as that of using the ``literal_binds``, + compilation flag, however takes place as the statement is sent to + the DBAPI ``cursor.execute()`` method, rather than when the statement + is compiled. The primary use of this + capability is for rendering LIMIT / OFFSET clauses for database + drivers that can't accommodate for bound parameters in these + contexts, while allowing SQL constructs to be cacheable at the + compilation level. + + .. versionadded:: 1.4 Added "post compile" bound parameters + + .. seealso:: + + :ref:`change_4808`. + + + + """ if isinstance(key, ColumnClause): type_ = key.type @@ -1235,6 +1277,7 @@ class BindParameter(roles.InElementRole, ColumnElement): self.isoutparam = isoutparam self.required = required self.expanding = expanding + self.literal_execute = literal_execute if type_ is None: if _compared_to_type is not None: @@ -1643,14 +1686,17 @@ class TextClause( for bind in binds: try: - existing = new_params[bind.key] + # the regex used for text() currently will not match + # a unique/anonymous key in any case, so use the _orig_key + # so that a text() construct can support unique parameters + existing = new_params[bind._orig_key] except KeyError: raise exc.ArgumentError( "This text() construct doesn't define a " - "bound parameter named %r" % bind.key + "bound parameter named %r" % bind._orig_key ) else: - new_params[existing.key] = bind + new_params[existing._orig_key] = bind for key, value in names_to_values.items(): try: diff --git a/lib/sqlalchemy/sql/sqltypes.py b/lib/sqlalchemy/sql/sqltypes.py index fd15d7c79..7829eb4d0 100644 --- a/lib/sqlalchemy/sql/sqltypes.py +++ b/lib/sqlalchemy/sql/sqltypes.py @@ -471,7 +471,7 @@ class Integer(_LookupExpressionAdapter, TypeEngine): def literal_processor(self, dialect): def process(value): - return str(value) + return str(int(value)) return process diff --git a/lib/sqlalchemy/testing/assertsql.py b/lib/sqlalchemy/testing/assertsql.py index 00496d549..55f4dc2ab 100644 --- a/lib/sqlalchemy/testing/assertsql.py +++ b/lib/sqlalchemy/testing/assertsql.py @@ -38,11 +38,10 @@ class SQLMatchRule(AssertRule): class CursorSQL(SQLMatchRule): - consume_statement = False - - def __init__(self, statement, params=None): + def __init__(self, statement, params=None, consume_statement=True): self.statement = statement self.params = params + self.consume_statement = consume_statement def process_statement(self, execute_observed): stmt = execute_observed.statements[0] diff --git a/lib/sqlalchemy/testing/requirements.py b/lib/sqlalchemy/testing/requirements.py index 62e600eaa..27def1004 100644 --- a/lib/sqlalchemy/testing/requirements.py +++ b/lib/sqlalchemy/testing/requirements.py @@ -45,6 +45,26 @@ class SuiteRequirements(Requirements): return exclusions.open() @property + def standard_cursor_sql(self): + """Target database passes SQL-92 style statements to cursor.execute() + when a statement like select() or insert() is run. + + A very small portion of dialect-level tests will ensure that certain + conditions are present in SQL strings, and these tests use very basic + SQL that will work on any SQL-like platform in order to assert results. + + It's normally a given for any pep-249 DBAPI that a statement like + "SELECT id, name FROM table WHERE some_table.id=5" will work. + However, there are dialects that don't actually produce SQL Strings + and instead may work with symbolic objects instead, or dialects that + aren't working with SQL, so for those this requirement can be marked + as excluded. + + """ + + return exclusions.open() + + @property def on_update_cascade(self): """"target database must support ON UPDATE..CASCADE behavior in foreign keys.""" diff --git a/lib/sqlalchemy/testing/suite/test_select.py b/lib/sqlalchemy/testing/suite/test_select.py index dabb30f9b..02cdcf4f5 100644 --- a/lib/sqlalchemy/testing/suite/test_select.py +++ b/lib/sqlalchemy/testing/suite/test_select.py @@ -1,7 +1,10 @@ +from .. import AssertsCompiledSQL +from .. import AssertsExecutionResults from .. import config from .. import fixtures from ..assertions import eq_ from ..assertions import in_ +from ..assertsql import CursorSQL from ..schema import Column from ..schema import Table from ... import bindparam @@ -14,6 +17,7 @@ from ... import null from ... import select from ... import String from ... import testing +from ... import text from ... import true from ... import tuple_ from ... import union @@ -233,6 +237,59 @@ class LimitOffsetTest(fixtures.TablesTest): params={"l": 2, "o": 1}, ) + @testing.requires.sql_expression_limit_offset + def test_expr_offset(self): + table = self.tables.some_table + self._assert_result( + select([table]) + .order_by(table.c.id) + .offset(literal_column("1") + literal_column("2")), + [(4, 4, 5)], + ) + + @testing.requires.sql_expression_limit_offset + def test_expr_limit(self): + table = self.tables.some_table + self._assert_result( + select([table]) + .order_by(table.c.id) + .limit(literal_column("1") + literal_column("2")), + [(1, 1, 2), (2, 2, 3), (3, 3, 4)], + ) + + @testing.requires.sql_expression_limit_offset + def test_expr_limit_offset(self): + table = self.tables.some_table + self._assert_result( + select([table]) + .order_by(table.c.id) + .limit(literal_column("1") + literal_column("1")) + .offset(literal_column("1") + literal_column("1")), + [(3, 3, 4), (4, 4, 5)], + ) + + @testing.requires.sql_expression_limit_offset + def test_simple_limit_expr_offset(self): + table = self.tables.some_table + self._assert_result( + select([table]) + .order_by(table.c.id) + .limit(2) + .offset(literal_column("1") + literal_column("1")), + [(3, 3, 4), (4, 4, 5)], + ) + + @testing.requires.sql_expression_limit_offset + def test_expr_limit_simple_offset(self): + table = self.tables.some_table + self._assert_result( + select([table]) + .order_by(table.c.id) + .limit(literal_column("1") + literal_column("1")) + .offset(2), + [(3, 3, 4), (4, 4, 5)], + ) + class CompoundSelectTest(fixtures.TablesTest): __backend__ = True @@ -372,6 +429,127 @@ class CompoundSelectTest(fixtures.TablesTest): ) +class PostCompileParamsTest( + AssertsExecutionResults, AssertsCompiledSQL, fixtures.TablesTest +): + __backend__ = True + + __requires__ = ("standard_cursor_sql",) + + @classmethod + def define_tables(cls, metadata): + Table( + "some_table", + metadata, + Column("id", Integer, primary_key=True), + Column("x", Integer), + Column("y", Integer), + Column("z", String(50)), + ) + + @classmethod + def insert_data(cls): + config.db.execute( + cls.tables.some_table.insert(), + [ + {"id": 1, "x": 1, "y": 2, "z": "z1"}, + {"id": 2, "x": 2, "y": 3, "z": "z2"}, + {"id": 3, "x": 3, "y": 4, "z": "z3"}, + {"id": 4, "x": 4, "y": 5, "z": "z4"}, + ], + ) + + def test_compile(self): + table = self.tables.some_table + + stmt = select([table.c.id]).where( + table.c.x == bindparam("q", literal_execute=True) + ) + + self.assert_compile( + stmt, + "SELECT some_table.id FROM some_table " + "WHERE some_table.x = [POSTCOMPILE_q]", + {}, + ) + + def test_compile_literal_binds(self): + table = self.tables.some_table + + stmt = select([table.c.id]).where( + table.c.x == bindparam("q", 10, literal_execute=True) + ) + + self.assert_compile( + stmt, + "SELECT some_table.id FROM some_table WHERE some_table.x = 10", + {}, + literal_binds=True, + ) + + def test_execute(self): + table = self.tables.some_table + + stmt = select([table.c.id]).where( + table.c.x == bindparam("q", literal_execute=True) + ) + + with self.sql_execution_asserter() as asserter: + with config.db.connect() as conn: + conn.execute(stmt, q=10) + + asserter.assert_( + CursorSQL( + "SELECT some_table.id \nFROM some_table " + "\nWHERE some_table.x = 10", + () if config.db.dialect.positional else {}, + ) + ) + + def test_execute_expanding_plus_literal_execute(self): + table = self.tables.some_table + + stmt = select([table.c.id]).where( + table.c.x.in_(bindparam("q", expanding=True, literal_execute=True)) + ) + + with self.sql_execution_asserter() as asserter: + with config.db.connect() as conn: + conn.execute(stmt, q=[5, 6, 7]) + + asserter.assert_( + CursorSQL( + "SELECT some_table.id \nFROM some_table " + "\nWHERE some_table.x IN (5, 6, 7)", + () if config.db.dialect.positional else {}, + ) + ) + + @testing.requires.tuple_in + def test_execute_tuple_expanding_plus_literal_execute(self): + table = self.tables.some_table + + stmt = select([table.c.id]).where( + tuple_(table.c.x, table.c.y).in_( + bindparam("q", expanding=True, literal_execute=True) + ) + ) + + with self.sql_execution_asserter() as asserter: + with config.db.connect() as conn: + conn.execute(stmt, q=[(5, 10), (12, 18)]) + + asserter.assert_( + CursorSQL( + "SELECT some_table.id \nFROM some_table " + "\nWHERE (some_table.x, some_table.y) " + "IN (%s(5, 10), (12, 18))" + % ("VALUES " if config.db.dialect.tuple_in_values else ""), + () if config.db.dialect.positional else {}, + ) + ) + + class ExpandingBoundInTest(fixtures.TablesTest): __backend__ = True @@ -496,6 +674,18 @@ class ExpandingBoundInTest(fixtures.TablesTest): params={"q": [(2, "z2"), (3, "z3"), (4, "z4")]}, ) + @testing.requires.tuple_in + def test_bound_in_heterogeneous_two_tuple_text(self): + stmt = text( + "select id FROM some_table WHERE (x, z) IN :q ORDER BY id" + ).bindparams(bindparam("q", expanding=True)) + + self._assert_result( + stmt, + [(2,), (3,), (4,)], + params={"q": [(2, "z2"), (3, "z3"), (4, "z4")]}, + ) + def test_empty_set_against_integer(self): table = self.tables.some_table |