From 6b09777e3d1ef35eb0ed07843b51d1a525702b79 Mon Sep 17 00:00:00 2001 From: =?utf8?q?Lo=C3=AFc=20Simon?= Date: Thu, 5 Feb 2026 14:56:26 -0500 Subject: [PATCH] PostgreSQL / SQLite / Insert.on_conflict_do_update: respect compile_kwargs MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit Fixed issue where :meth:`_postgresql.Insert.on_conflict_do_update` as well as :meth:`_sqlite.Insert.on_conflict_do_update` parameters were not respecting compilation options such as ``literal_binds=True``. Pull request courtesy Loïc Simon. Fixes: #13110 Closes: #13111 Pull-request: https://github.com/sqlalchemy/sqlalchemy/pull/13111 Pull-request-sha: 9ca251610b2eb1c5fdda6aeffa6e81dcaef23aaa Change-Id: Ice21e508210d682098104c78e77bad8d24e6c93f --- doc/build/changelog/unreleased_20/13110.rst | 16 +++++ lib/sqlalchemy/dialects/postgresql/base.py | 15 +++-- lib/sqlalchemy/dialects/sqlite/base.py | 20 ++++-- test/dialect/postgresql/test_compiler.py | 62 +++++++++++++++++ test/dialect/sqlite/test_compiler.py | 74 +++++++++++++++++++++ 5 files changed, 176 insertions(+), 11 deletions(-) create mode 100644 doc/build/changelog/unreleased_20/13110.rst diff --git a/doc/build/changelog/unreleased_20/13110.rst b/doc/build/changelog/unreleased_20/13110.rst new file mode 100644 index 0000000000..05670719bd --- /dev/null +++ b/doc/build/changelog/unreleased_20/13110.rst @@ -0,0 +1,16 @@ +.. change:: + :tags: bug, postgresql + :tickets: 13110 + + Fixed issue where :meth:`_postgresql.Insert.on_conflict_do_update` + parameters were not respecting compilation options such as + ``literal_binds=True``. Pull request courtesy Loïc Simon. + + +.. change:: + :tags: bug, sqlite + :tickets: 13110 + + Fixed issue where :meth:`_sqlite.Insert.on_conflict_do_update` + parameters were not respecting compilation options such as + ``literal_binds=True``. Pull request courtesy Loïc Simon. diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py index e54fd0a658..be50b41d96 100644 --- a/lib/sqlalchemy/dialects/postgresql/base.py +++ b/lib/sqlalchemy/dialects/postgresql/base.py @@ -2370,10 +2370,11 @@ class PGCompiler(compiler.SQLCompiler): for c in clause.inferred_target_elements ) if clause.inferred_target_whereclause is not None: + whereclause_kw = dict(kw) + whereclause_kw.update(include_table=False, use_schema=False) target_text += " WHERE %s" % self.process( clause.inferred_target_whereclause, - include_table=False, - use_schema=False, + **whereclause_kw, ) else: target_text = "" @@ -2400,6 +2401,8 @@ class PGCompiler(compiler.SQLCompiler): insert_statement = self.stack[-1]["selectable"] cols = insert_statement.table.c + set_kw = dict(kw) + set_kw.update(use_schema=False) for c in cols: col_key = c.key @@ -2416,7 +2419,7 @@ class PGCompiler(compiler.SQLCompiler): and value.type._isnull ): value = value._with_binary_element_type(c.type) - value_text = self.process(value.self_group(), use_schema=False) + value_text = self.process(value.self_group(), **set_kw) key_text = self.preparer.quote(c.name) action_set_ops.append("%s = %s" % (key_text, value_text)) @@ -2439,14 +2442,16 @@ class PGCompiler(compiler.SQLCompiler): ) value_text = self.process( coercions.expect(roles.ExpressionElementRole, v), - use_schema=False, + **set_kw, ) action_set_ops.append("%s = %s" % (key_text, value_text)) action_text = ", ".join(action_set_ops) if clause.update_whereclause is not None: + where_kw = dict(kw) + where_kw.update(include_table=True, use_schema=False) action_text += " WHERE %s" % self.process( - clause.update_whereclause, include_table=True, use_schema=False + clause.update_whereclause, **where_kw ) return "ON CONFLICT %s DO UPDATE SET %s" % (target_text, action_text) diff --git a/lib/sqlalchemy/dialects/sqlite/base.py b/lib/sqlalchemy/dialects/sqlite/base.py index 282368febc..06568a07cf 100644 --- a/lib/sqlalchemy/dialects/sqlite/base.py +++ b/lib/sqlalchemy/dialects/sqlite/base.py @@ -1610,12 +1610,16 @@ class SQLiteCompiler(compiler.SQLCompiler): for c in clause.inferred_target_elements ) if clause.inferred_target_whereclause is not None: - target_text += " WHERE %s" % self.process( - clause.inferred_target_whereclause, + whereclause_kw = dict(kw) + whereclause_kw.update( include_table=False, use_schema=False, literal_execute=True, ) + target_text += " WHERE %s" % self.process( + clause.inferred_target_whereclause, + **whereclause_kw, + ) else: target_text = "" @@ -1642,6 +1646,8 @@ class SQLiteCompiler(compiler.SQLCompiler): insert_statement = self.stack[-1]["selectable"] cols = insert_statement.table.c + set_kw = dict(kw) + set_kw.update(use_schema=False) for c in cols: col_key = c.key @@ -1657,7 +1663,7 @@ class SQLiteCompiler(compiler.SQLCompiler): and value.type._isnull ): value = value._with_binary_element_type(c.type) - value_text = self.process(value.self_group(), use_schema=False) + value_text = self.process(value.self_group(), **set_kw) key_text = self.preparer.quote(c.name) action_set_ops.append("%s = %s" % (key_text, value_text)) @@ -1676,18 +1682,20 @@ class SQLiteCompiler(compiler.SQLCompiler): key_text = ( self.preparer.quote(k) if isinstance(k, str) - else self.process(k, use_schema=False) + else self.process(k, **set_kw) ) value_text = self.process( coercions.expect(roles.ExpressionElementRole, v), - use_schema=False, + **set_kw, ) action_set_ops.append("%s = %s" % (key_text, value_text)) action_text = ", ".join(action_set_ops) if clause.update_whereclause is not None: + where_kw = dict(kw) + where_kw.update(include_table=True, use_schema=False) action_text += " WHERE %s" % self.process( - clause.update_whereclause, include_table=True, use_schema=False + clause.update_whereclause, **where_kw ) return "ON CONFLICT %s DO UPDATE SET %s" % (target_text, action_text) diff --git a/test/dialect/postgresql/test_compiler.py b/test/dialect/postgresql/test_compiler.py index 45db034042..142a7c808f 100644 --- a/test/dialect/postgresql/test_compiler.py +++ b/test/dialect/postgresql/test_compiler.py @@ -1,3 +1,4 @@ +import contextlib import random import re @@ -4103,6 +4104,67 @@ class InsertOnConflictTest( }, ) + @testing.variation( + "path", ["unknown_columns", "whereclause", "indexwhere"] + ) + def test_on_conflict_literal_binds(self, path: testing.Variation): + """test for #13110""" + + i = insert(self.table_with_metadata).values(myid=1, name="foo") + + if path.unknown_columns: + i = i.on_conflict_do_update( + index_elements=["myid"], + set_=OrderedDict( + [ + ("name", "I'm a name"), + ("other_param", literal("this too")), + ] + ), + ) + expected = ( + "ON CONFLICT (myid) DO UPDATE SET name = " + "'I''m a name', other_param = 'this too'" + ) + warnings = expect_warnings( + "Additional column names not matching any column keys" + ) + elif path.whereclause: + i = i.on_conflict_do_update( + index_elements=["myid"], + set_={"name": "I'm a name"}, + where=self.table_with_metadata.c.name == "foo", + ) + expected = ( + "ON CONFLICT (myid) DO UPDATE SET name = " + "'I''m a name' WHERE mytable.name = 'foo'" + ) + warnings = contextlib.nullcontext() + elif path.indexwhere: + i = i.on_conflict_do_update( + index_elements=["myid"], + set_={"name": "I'm a name"}, + index_where=self.goofy_index.dialect_options["postgresql"][ + "where" + ], + ) + warnings = contextlib.nullcontext() + expected = ( + "ON CONFLICT (myid) WHERE name > 'm' " + "DO UPDATE SET name = 'I''m a name'" + ) + else: + path.fail() + + with warnings: + self.assert_compile( + i, + "INSERT INTO mytable (myid, name) VALUES (1, 'foo')" + f" {expected}", + {}, + literal_binds=True, + ) + class DistinctOnTest( fixtures.MappedTest, diff --git a/test/dialect/sqlite/test_compiler.py b/test/dialect/sqlite/test_compiler.py index de01542503..43f179a6d9 100644 --- a/test/dialect/sqlite/test_compiler.py +++ b/test/dialect/sqlite/test_compiler.py @@ -1,5 +1,7 @@ """SQLite-specific tests.""" +from collections import OrderedDict +import contextlib import random from sqlalchemy import and_ @@ -828,6 +830,78 @@ class OnConflictCompileTest( "SET name = excluded.name, login_email = excluded.login_email", ) + @testing.variation( + "path", ["unknown_columns", "whereclause", "indexwhere"] + ) + def test_on_conflict_literal_binds(self, path: testing.Variation): + """test for #13110""" + + metadata = MetaData() + table_with_metadata = Table( + "mytable", + metadata, + Column("myid", Integer, primary_key=True), + Column("name", String(128)), + ) + goofy_index = Index( + "goofy_index", + table_with_metadata.c.name, + sqlite_where=table_with_metadata.c.name > "m", + ) + + i = insert(table_with_metadata).values(myid=1, name="foo") + + if path.unknown_columns: + i = i.on_conflict_do_update( + index_elements=["myid"], + set_=OrderedDict( + [ + ("name", "I'm a name"), + ("other_param", literal("this too")), + ] + ), + ) + expected = ( + "ON CONFLICT (myid) DO UPDATE SET name = " + "'I''m a name', other_param = 'this too'" + ) + warnings = testing.expect_warnings( + "Additional column names not matching any column keys" + ) + elif path.whereclause: + i = i.on_conflict_do_update( + index_elements=["myid"], + set_={"name": "I'm a name"}, + where=table_with_metadata.c.name == "foo", + ) + expected = ( + "ON CONFLICT (myid) DO UPDATE SET name = " + "'I''m a name' WHERE mytable.name = 'foo'" + ) + warnings = contextlib.nullcontext() + elif path.indexwhere: + i = i.on_conflict_do_update( + index_elements=["myid"], + set_={"name": "I'm a name"}, + index_where=goofy_index.dialect_options["sqlite"]["where"], + ) + warnings = contextlib.nullcontext() + expected = ( + "ON CONFLICT (myid) WHERE name > 'm' " + "DO UPDATE SET name = 'I''m a name'" + ) + else: + path.fail() + + with warnings: + self.assert_compile( + i, + "INSERT INTO mytable (myid, name) VALUES (1, 'foo')" + f" {expected}", + {}, + literal_binds=True, + ) + @testing.fixture def users(self): metadata = MetaData() -- 2.47.3