diff options
Diffstat (limited to 'lib/sqlalchemy')
| -rw-r--r-- | lib/sqlalchemy/dialects/mysql/base.py | 50 | ||||
| -rw-r--r-- | lib/sqlalchemy/dialects/mysql/dml.py | 52 |
2 files changed, 94 insertions, 8 deletions
diff --git a/lib/sqlalchemy/dialects/mysql/base.py b/lib/sqlalchemy/dialects/mysql/base.py index 2bb5b5beb..65a75f9b7 100644 --- a/lib/sqlalchemy/dialects/mysql/base.py +++ b/lib/sqlalchemy/dialects/mysql/base.py @@ -339,7 +339,7 @@ an error or to skip performing an UPDATE. ``ON DUPLICATE KEY UPDATE`` is used to perform an update of the already existing row, using any combination of new values as well as values -from the proposed insertion. These values are specified using +from the proposed insertion. These values are normally specified using keyword arguments passed to the :meth:`~.mysql.dml.Insert.on_duplicate_key_update` given column key values (usually the name of the column, unless it @@ -347,10 +347,33 @@ specifies :paramref:`.Column.key`) as keys and literal or SQL expressions as values:: on_duplicate_key_stmt = insert_stmt.on_duplicate_key_update( - data="some data" - updated_at=func.current_timestamp() + data="some data", + updated_at=func.current_timestamp(), ) +In a manner similar to that of :meth:`.UpdateBase.values`, other parameter +forms are accepted, including a single dictionary:: + + on_duplicate_key_stmt = insert_stmt.on_duplicate_key_update( + {"data": "some data", "updated_at": func.current_timestamp()}, + ) + +as well as a list of 2-tuples, which will automatically provide +a parameter-ordered UPDATE statement in a manner similar to that described +at :ref:`updates_order_parameters`. Unlike the :class:`.Update` object, +no special flag is needed to specify the intent since the argument form is +this context is unambiguous:: + + on_duplicate_key_stmt = insert_stmt.on_duplicate_key_update( + [ + ("data", "some data"), + ("updated_at", func.current_timestamp()), + ], + ) + +.. versionchanged:: 1.3 support for parameter-ordered UPDATE clause within + MySQL ON DUPLICATE KEY UPDATE + .. warning:: The :meth:`.Insert.on_duplicate_key_update` method does **not** take into @@ -359,6 +382,8 @@ as values:: These values will not be exercised for an ON DUPLICATE KEY style of UPDATE, unless they are manually specified explicitly in the parameters. + + In order to refer to the proposed insertion row, the special alias :attr:`~.mysql.dml.Insert.inserted` is available as an attribute on the :class:`.mysql.dml.Insert` object; this object is a @@ -899,10 +924,23 @@ class MySQLCompiler(compiler.SQLCompiler): self.process(binary.right, **kw)) def visit_on_duplicate_key_update(self, on_duplicate, **kw): - cols = self.statement.table.c + if on_duplicate._parameter_ordering: + parameter_ordering = [ + elements._column_as_key(key) + for key in on_duplicate._parameter_ordering + ] + ordered_keys = set(parameter_ordering) + cols = [ + self.statement.table.c[key] for key in parameter_ordering + if key in self.statement.table.c + ] + [ + c for c in self.statement.table.c if c.key not in ordered_keys + ] + else: + # traverse in table column order + cols = self.statement.table.c clauses = [] - # traverse in table column order for column in cols: val = on_duplicate.update.get(column.key) if val is None: @@ -922,7 +960,7 @@ class MySQLCompiler(compiler.SQLCompiler): name_text = self.preparer.quote(column.name) clauses.append("%s = %s" % (name_text, value_text)) - non_matching = set(on_duplicate.update) - set(cols.keys()) + non_matching = set(on_duplicate.update) - set(c.key for c in cols) if non_matching: util.warn( 'Additional column names not matching ' diff --git a/lib/sqlalchemy/dialects/mysql/dml.py b/lib/sqlalchemy/dialects/mysql/dml.py index 217dc7a2a..130ef2347 100644 --- a/lib/sqlalchemy/dialects/mysql/dml.py +++ b/lib/sqlalchemy/dialects/mysql/dml.py @@ -3,6 +3,7 @@ from ...sql.dml import Insert as StandardInsert from ...sql.expression import alias from ...util.langhelpers import public_factory from ...sql.base import _generative +from ... import exc from ... import util __all__ = ('Insert', 'insert') @@ -41,7 +42,7 @@ class Insert(StandardInsert): return alias(self.table, name='inserted') @_generative - def on_duplicate_key_update(self, **kw): + def on_duplicate_key_update(self, *args, **kw): r""" Specifies the ON DUPLICATE KEY UPDATE clause. @@ -55,6 +56,28 @@ class Insert(StandardInsert): These values will not be exercised for an ON DUPLICATE KEY UPDATE style of UPDATE, unless values are manually specified here. + :param \*args: As an alternative to passing key/value parameters, + a dictionary or list of 2-tuples can be passed as a single positional + argument. + + Passing a single dictionary is equivalent to the keyword argument + form:: + + insert().on_duplicate_key_update({"name": "some name"}) + + Passing a list of 2-tuples indicates that the parameter assignments + in the UPDATE clause should be ordered as sent, in a manner similar + to that described for the :class:`.Update` contruct overall + in :ref:`updates_order_parameters`:: + + insert().on_duplicate_key_update( + [("name": "some name"), ("value", "some value")]) + + .. versionchanged:: 1.3 parameters can be specified as a dictionary + or list of 2-tuples; the latter form provides for parameter + ordering. + + .. versionadded:: 1.2 .. seealso:: @@ -62,8 +85,21 @@ class Insert(StandardInsert): :ref:`mysql_insert_on_duplicate_key_update` """ + if args and kw: + raise exc.ArgumentError( + "Can't pass kwargs and positional arguments simultaneously") + + if args: + if len(args) > 1: + raise exc.ArgumentError( + "Only a single dictionary or list of tuples " + "is accepted positionally.") + values = args[0] + else: + values = kw + inserted_alias = getattr(self, 'inserted_alias', None) - self._post_values_clause = OnDuplicateClause(inserted_alias, kw) + self._post_values_clause = OnDuplicateClause(inserted_alias, values) return self @@ -73,8 +109,20 @@ insert = public_factory(Insert, '.dialects.mysql.insert') class OnDuplicateClause(ClauseElement): __visit_name__ = 'on_duplicate_key_update' + _parameter_ordering = None + def __init__(self, inserted_alias, update): self.inserted_alias = inserted_alias + + # auto-detect that parameters should be ordered. This is copied from + # Update._proces_colparams(), however we don't look for a special flag + # in this case since we are not disambiguating from other use cases as + # we are in Update.values(). + if isinstance(update, list) and \ + (update and isinstance(update[0], tuple)): + self._parameter_ordering = [key for key, value in update] + update = dict(update) + if not update or not isinstance(update, dict): raise ValueError('update parameter must be a non-empty dictionary') self.update = update |