diff options
| -rw-r--r-- | doc/build/changelog/migration_13.rst | 24 | ||||
| -rw-r--r-- | doc/build/changelog/unreleased_13/pr462.rst | 11 | ||||
| -rw-r--r-- | doc/build/core/tutorial.rst | 5 | ||||
| -rw-r--r-- | lib/sqlalchemy/dialects/mysql/base.py | 50 | ||||
| -rw-r--r-- | lib/sqlalchemy/dialects/mysql/dml.py | 52 | ||||
| -rw-r--r-- | test/dialect/mysql/test_on_duplicate.py | 56 |
6 files changed, 184 insertions, 14 deletions
diff --git a/doc/build/changelog/migration_13.rst b/doc/build/changelog/migration_13.rst index c81d95ed9..23a14fca6 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 000000000..7d714cf99 --- /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 65410f7ba..baddfc459 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 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 diff --git a/test/dialect/mysql/test_on_duplicate.py b/test/dialect/mysql/test_on_duplicate.py index 9a026f9ed..376f9a9af 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]) - - - |