Support Column objects in the SET clause for upsert

Established support for :class:`_schema.Column` objects as well as ORM
instrumented attributes as keys in the ``set_`` dictionary passed to the
:meth:`_postgresql.Insert.on_conflict_do_update` and
:meth:`_sqlite.Insert.on_conflict_do_update` methods, which match to the
:class:`_schema.Column` objects in the ``.c`` collection of the target
:class:`_schema.Table`. Previously,  only string column names were
expected; a column expression would be assumed to be an out-of-table
expression that would render fully along with a warning.

Fixes: #5722
Change-Id: Ice73b501d721c28d978a0277a83cedc6aff756a9

2 files changed, 33 insertions, 20 deletions

diff --git a/lib/sqlalchemy/dialects/sqlite/base.py b/lib/sqlalchemy/dialects/sqlite/base.py
index 404a215b6..7c1bbb18e 100644
--- a/lib/sqlalchemy/dialects/sqlite/base.py
+++ b/lib/sqlalchemy/dialects/sqlite/base.py
@@ -1355,22 +1355,28 @@ class SQLiteCompiler(compiler.SQLCompiler):
         cols = insert_statement.table.c
         for c in cols:
             col_key = c.key
+
             if col_key in set_parameters:
                 value = set_parameters.pop(col_key)
-                if coercions._is_literal(value):
-                    value = elements.BindParameter(None, value, type_=c.type)
+            elif c in set_parameters:
+                value = set_parameters.pop(c)
+            else:
+                continue
 
-                else:
-                    if (
-                        isinstance(value, elements.BindParameter)
-                        and value.type._isnull
-                    ):
-                        value = value._clone()
-                        value.type = c.type
-                value_text = self.process(value.self_group(), use_schema=False)
-
-                key_text = self.preparer.quote(col_key)
-                action_set_ops.append("%s = %s" % (key_text, value_text))
+            if coercions._is_literal(value):
+                value = elements.BindParameter(None, value, type_=c.type)
+
+            else:
+                if (
+                    isinstance(value, elements.BindParameter)
+                    and value.type._isnull
+                ):
+                    value = value._clone()
+                    value.type = c.type
+            value_text = self.process(value.self_group(), use_schema=False)
+
+            key_text = self.preparer.quote(col_key)
+            action_set_ops.append("%s = %s" % (key_text, value_text))
 
         # check for names that don't match columns
         if set_parameters:
diff --git a/lib/sqlalchemy/dialects/sqlite/dml.py b/lib/sqlalchemy/dialects/sqlite/dml.py
index a4d4d560c..2d7ea6e4a 100644
--- a/lib/sqlalchemy/dialects/sqlite/dml.py
+++ b/lib/sqlalchemy/dialects/sqlite/dml.py
@@ -5,6 +5,8 @@
 # the MIT License: http://www.opensource.org/licenses/mit-license.php
 
 from ... import util
+from ...sql import coercions
+from ...sql import roles
 from ...sql.base import _generative
 from ...sql.dml import Insert as StandardInsert
 from ...sql.elements import ClauseElement
@@ -65,12 +67,16 @@ class Insert(StandardInsert):
          conditional target index.
 
         :param set\_:
-         Required argument. A dictionary or other mapping object
-         with column names as keys and expressions or literals as values,
-         specifying the ``SET`` actions to take.
-         If the target :class:`_schema.Column` specifies a ".
-         key" attribute distinct
-         from the column name, that key should be used.
+         A dictionary or other mapping object
+         where the keys are either names of columns in the target table,
+         or :class:`_schema.Column` objects or other ORM-mapped columns
+         matching that of the target table, and expressions or literals
+         as values, specifying the ``SET`` actions to take.
+
+         .. versionadded:: 1.4 The
+            :paramref:`_sqlite.Insert.on_conflict_do_update.set_`
+            parameter supports :class:`_schema.Column` objects from the target
+            :class:`_schema.Table` as keys.
 
          .. warning:: This dictionary does **not** take into account
             Python-specified default UPDATE values or generation functions,
@@ -155,6 +161,7 @@ class OnConflictDoUpdate(OnConflictClause):
         if not isinstance(set_, dict) or not set_:
             raise ValueError("set parameter must be a non-empty dictionary")
         self.update_values_to_set = [
-            (key, value) for key, value in set_.items()
+            (coercions.expect(roles.DMLColumnRole, key), value)
+            for key, value in set_.items()
         ]
         self.update_whereclause = where