From dfa47b454a1d873b5746263f638d757c70edd3e1 Mon Sep 17 00:00:00 2001 From: Maxim Bublis Date: Wed, 18 Jul 2018 14:06:07 -0400 Subject: [PATCH] Add ability to preserve order in MySQL ON DUPLICATE KEY UPDATE. Added support for the parameters in an ON DUPLICATE KEY UPDATE statement on MySQL to be ordered, since parameter order in a MySQL UPDATE clause is significant, in a similar manner as that described at :ref:`updates_order_parameters`. Pull request courtesy Maxim Bublis. Pull-request: https://github.com/zzzeek/sqlalchemy/pull/462 Change-Id: If508d8e26dbd3c55ab1e83cf573fb4021e9d091e --- doc/build/changelog/migration_13.rst | 24 +++++++++ doc/build/changelog/unreleased_13/pr462.rst | 11 ++++ doc/build/core/tutorial.rst | 5 ++ lib/sqlalchemy/dialects/mysql/base.py | 50 +++++++++++++++--- lib/sqlalchemy/dialects/mysql/dml.py | 52 ++++++++++++++++++- test/dialect/mysql/test_on_duplicate.py | 56 ++++++++++++++++++--- 6 files changed, 184 insertions(+), 14 deletions(-) create mode 100644 doc/build/changelog/unreleased_13/pr462.rst diff --git a/doc/build/changelog/migration_13.rst b/doc/build/changelog/migration_13.rst index c81d95ed9c..23a14fca68 100644 --- a/doc/build/changelog/migration_13.rst +++ b/doc/build/changelog/migration_13.rst @@ -333,6 +333,30 @@ pool pre-ping feature, described at :ref:`pool_disconnects_pessimistic`. This is a much more lightweight ping than the previous method of emitting "SELECT 1" on the connection. +.. _change_mysql_ondupordering: + +Control of parameter ordering within ON DUPLICATE KEY UPDATE +------------------------------------------------------------ + +The order of UPDATE parameters in the ``ON DUPLICATE KEY UPDATE`` clause +can now be explcitly ordered by passing a list of 2-tuples:: + + from sqlalchemy.dialects.mysql import insert + + insert_stmt = insert(my_table).values( + id='some_existing_id', + data='inserted value') + + on_duplicate_key_stmt = insert_stmt.on_duplicate_key_update( + [ + ("data", "some data"), + ("updated_at", func.current_timestamp()), + ], + ) + +.. seealso:: + + :ref:`mysql_insert_on_duplicate_key_update` Dialect Improvements and Changes - SQLite ============================================= diff --git a/doc/build/changelog/unreleased_13/pr462.rst b/doc/build/changelog/unreleased_13/pr462.rst new file mode 100644 index 0000000000..7d714cf996 --- /dev/null +++ b/doc/build/changelog/unreleased_13/pr462.rst @@ -0,0 +1,11 @@ +.. change:: + :tags: feature, mysql + + Added support for the parameters in an ON DUPLICATE KEY UPDATE statement on + MySQL to be ordered, since parameter order in a MySQL UPDATE clause is + significant, in a similar manner as that described at + :ref:`updates_order_parameters`. Pull request courtesy Maxim Bublis. + + .. seealso:: + + :ref:`change_mysql_ondupordering` diff --git a/doc/build/core/tutorial.rst b/doc/build/core/tutorial.rst index 65410f7bae..baddfc459e 100644 --- a/doc/build/core/tutorial.rst +++ b/doc/build/core/tutorial.rst @@ -2108,6 +2108,11 @@ except it is ordered. Using the above form, we are assured that the parameters using the :paramref:`~sqlalchemy.sql.expression.update.preserve_parameter_order` flag. +.. seealso:: + + :ref:`mysql_insert_on_duplicate_key_update` - background on the MySQL + ``ON DUPLICATE KEY UPDATE`` clause and how to support parameter ordering. + .. _deletes: Deletes diff --git a/lib/sqlalchemy/dialects/mysql/base.py b/lib/sqlalchemy/dialects/mysql/base.py index 2bb5b5beb7..65a75f9b7a 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 217dc7a2a0..130ef23477 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 diff --git a/test/dialect/mysql/test_on_duplicate.py b/test/dialect/mysql/test_on_duplicate.py index 9a026f9edc..376f9a9af6 100644 --- a/test/dialect/mysql/test_on_duplicate.py +++ b/test/dialect/mysql/test_on_duplicate.py @@ -1,8 +1,8 @@ from sqlalchemy.testing.assertions import eq_, assert_raises from sqlalchemy.testing import fixtures -from sqlalchemy import testing +from sqlalchemy import exc, testing from sqlalchemy.dialects.mysql import insert -from sqlalchemy import Table, Column, Integer, String +from sqlalchemy import Table, Column, Boolean, Integer, String, func class OnDuplicateTest(fixtures.TablesTest): @@ -17,6 +17,7 @@ class OnDuplicateTest(fixtures.TablesTest): Column('id', Integer, primary_key=True, autoincrement=True), Column('bar', String(10)), Column('baz', String(10)), + Column('updated_once', Boolean, default=False), ) def test_bad_args(self): @@ -24,6 +25,19 @@ class OnDuplicateTest(fixtures.TablesTest): ValueError, insert(self.tables.foos, values={}).on_duplicate_key_update ) + assert_raises( + exc.ArgumentError, + insert(self.tables.foos, values={}).on_duplicate_key_update, + {'id': 1, 'bar': 'b'}, + id=1, + bar='b', + ) + assert_raises( + exc.ArgumentError, + insert(self.tables.foos, values={}).on_duplicate_key_update, + {'id': 1, 'bar': 'b'}, + {'id': 2, 'bar': 'baz'}, + ) def test_on_duplicate_key_update(self): foos = self.tables.foos @@ -36,7 +50,40 @@ class OnDuplicateTest(fixtures.TablesTest): eq_(result.inserted_primary_key, [2]) eq_( conn.execute(foos.select().where(foos.c.id == 1)).fetchall(), - [(1, 'ab', 'bz')] + [(1, 'ab', 'bz', False)] + ) + + def test_on_duplicate_key_update_preserve_order(self): + foos = self.tables.foos + with testing.db.connect() as conn: + conn.execute(insert(foos, + [dict(id=1, bar='b', baz='bz'), dict(id=2, bar='b', baz='bz2')])) + + stmt = insert(foos) + update_condition = (foos.c.updated_once == False) + + # The following statements show importance of the columns update ordering + # as old values being referenced in UPDATE clause are getting replaced one + # by one from left to right with their new values. + stmt1 = stmt.on_duplicate_key_update([ + ('bar', func.if_(update_condition, func.values(foos.c.bar), foos.c.bar)), + ('updated_once', func.if_(update_condition, True, foos.c.updated_once)), + ]) + stmt2 = stmt.on_duplicate_key_update([ + ('updated_once', func.if_(update_condition, True, foos.c.updated_once)), + ('bar', func.if_(update_condition, func.values(foos.c.bar), foos.c.bar)), + ]) + # First statement should succeed updating column bar + conn.execute(stmt1, dict(id=1, bar='ab')) + eq_( + conn.execute(foos.select().where(foos.c.id == 1)).fetchall(), + [(1, 'ab', 'bz', True)], + ) + # Second statement will do noop update of column bar + conn.execute(stmt2, dict(id=2, bar='ab')) + eq_( + conn.execute(foos.select().where(foos.c.id == 2)).fetchall(), + [(2, 'b', 'bz2', True)] ) def test_last_inserted_id(self): @@ -55,6 +102,3 @@ class OnDuplicateTest(fixtures.TablesTest): bar=stmt.inserted.bar, baz="newbz") ) eq_(result.inserted_primary_key, [1]) - - - -- 2.47.2