- Added support for parameter-ordered SET clauses in an UPDATE

statement.  This feature is available by passing the
:paramref:`~.sqlalchemy.sql.expression.update.preserve_parameter_order`
flag either to the core :class:`.Update` construct or alternatively
adding it to the :paramref:`.Query.update.update_args` dictionary at
the ORM-level, also passing the parameters themselves as a list of 2-tuples.
Thanks to Gorka Eguileor for implementation and tests.
adapted from pullreq github:200

1 files changed, 124 insertions, 0 deletions

diff --git a/test/sql/test_update.py b/test/sql/test_update.py
index 58c86613b..3ab580b11 100644
--- a/test/sql/test_update.py
+++ b/test/sql/test_update.py
@@ -4,6 +4,7 @@ from sqlalchemy.dialects import mysql
 from sqlalchemy.engine import default
 from sqlalchemy.testing import AssertsCompiledSQL, eq_, fixtures
 from sqlalchemy.testing.schema import Table, Column
+from sqlalchemy import util
 
 
 class _UpdateFromTestBase(object):
@@ -32,6 +33,11 @@ class _UpdateFromTestBase(object):
                      test_needs_autoincrement=True),
               Column('address_id', None, ForeignKey('addresses.id')),
               Column('data', String(30)))
+        Table('update_w_default', metadata,
+              Column('id', Integer, primary_key=True),
+              Column('x', Integer),
+              Column('ycol', Integer, key='y'),
+              Column('data', String(30), onupdate=lambda: "hi"))
 
     @classmethod
     def fixtures(cls):
@@ -165,6 +171,124 @@ class UpdateTest(_UpdateFromTestBase, fixtures.TablesTest, AssertsCompiledSQL):
             table1.c.name: table1.c.name + 'lala',
             table1.c.myid: func.do_stuff(table1.c.myid, literal('hoho'))
         }
+
+        self.assert_compile(
+            update(
+                table1,
+                (table1.c.myid == func.hoho(4)) & (
+                    table1.c.name == literal('foo') +
+                    table1.c.name +
+                    literal('lala')),
+                values=values),
+            'UPDATE mytable '
+            'SET '
+            'myid=do_stuff(mytable.myid, :param_1), '
+            'name=(mytable.name || :name_1) '
+            'WHERE '
+            'mytable.myid = hoho(:hoho_1) AND '
+            'mytable.name = :param_2 || mytable.name || :param_3')
+
+    def test_update_ordered_parameters_1(self):
+        table1 = self.tables.mytable
+
+        # Confirm that we can pass values as list value pairs
+        # note these are ordered *differently* from table.c
+        values = [
+            (table1.c.name, table1.c.name + 'lala'),
+            (table1.c.myid, func.do_stuff(table1.c.myid, literal('hoho'))),
+        ]
+        self.assert_compile(
+            update(
+                table1,
+                (table1.c.myid == func.hoho(4)) & (
+                    table1.c.name == literal('foo') +
+                    table1.c.name +
+                    literal('lala')),
+                preserve_parameter_order=True,
+                values=values),
+            'UPDATE mytable '
+            'SET '
+            'name=(mytable.name || :name_1), '
+            'myid=do_stuff(mytable.myid, :param_1) '
+            'WHERE '
+            'mytable.myid = hoho(:hoho_1) AND '
+            'mytable.name = :param_2 || mytable.name || :param_3')
+
+    def test_update_ordered_parameters_2(self):
+        table1 = self.tables.mytable
+
+        # Confirm that we can pass values as list value pairs
+        # note these are ordered *differently* from table.c
+        values = [
+            (table1.c.name, table1.c.name + 'lala'),
+            ('description', 'some desc'),
+            (table1.c.myid, func.do_stuff(table1.c.myid, literal('hoho')))
+        ]
+        self.assert_compile(
+            update(
+                table1,
+                (table1.c.myid == func.hoho(4)) & (
+                    table1.c.name == literal('foo') +
+                    table1.c.name +
+                    literal('lala')),
+                preserve_parameter_order=True).values(values),
+            'UPDATE mytable '
+            'SET '
+            'name=(mytable.name || :name_1), '
+            'description=:description, '
+            'myid=do_stuff(mytable.myid, :param_1) '
+            'WHERE '
+            'mytable.myid = hoho(:hoho_1) AND '
+            'mytable.name = :param_2 || mytable.name || :param_3')
+
+    def test_update_ordered_parameters_fire_onupdate(self):
+        table = self.tables.update_w_default
+
+        values = [
+            (table.c.y, table.c.x + 5),
+            ('x', 10)
+        ]
+
+        self.assert_compile(
+            table.update(preserve_parameter_order=True).values(values),
+            "UPDATE update_w_default SET ycol=(update_w_default.x + :x_1), "
+            "x=:x, data=:data"
+        )
+
+    def test_update_ordered_parameters_override_onupdate(self):
+        table = self.tables.update_w_default
+
+        values = [
+            (table.c.y, table.c.x + 5),
+            (table.c.data, table.c.x + 10),
+            ('x', 10)
+        ]
+
+        self.assert_compile(
+            table.update(preserve_parameter_order=True).values(values),
+            "UPDATE update_w_default SET ycol=(update_w_default.x + :x_1), "
+            "data=(update_w_default.x + :x_2), x=:x"
+        )
+
+    def test_update_preserve_order_reqs_listtups(self):
+        table1 = self.tables.mytable
+        testing.assert_raises_message(
+            ValueError,
+            "When preserve_parameter_order is True, values\(\) "
+            "only accepts a list of 2-tuples",
+            table1.update(preserve_parameter_order=True).values,
+            {"description": "foo", "name": "bar"}
+        )
+
+    def test_update_ordereddict(self):
+        table1 = self.tables.mytable
+
+        # Confirm that ordered dicts are treated as normal dicts,
+        # columns sorted in table order
+        values = util.OrderedDict((
+            (table1.c.name, table1.c.name + 'lala'),
+            (table1.c.myid, func.do_stuff(table1.c.myid, literal('hoho')))))
+
         self.assert_compile(
             update(
                 table1,