From bfad6e6bdfb7d6dd5176deaf30d875f3d0f15e06 Mon Sep 17 00:00:00 2001 From: Lukas Banic Date: Mon, 10 Jun 2019 15:11:41 +0200 Subject: [PATCH] fix for: unable to set column to NULL within ON DUPLICATE KEY UPDATE --- lib/sqlalchemy/dialects/mysql/base.py | 8 +++++--- test/dialect/mysql/test_on_duplicate.py | 15 +++++++++++++++ 2 files changed, 20 insertions(+), 3 deletions(-) diff --git a/lib/sqlalchemy/dialects/mysql/base.py b/lib/sqlalchemy/dialects/mysql/base.py index ad5ab288ce..fe9d882f75 100644 --- a/lib/sqlalchemy/dialects/mysql/base.py +++ b/lib/sqlalchemy/dialects/mysql/base.py @@ -1237,10 +1237,12 @@ class MySQLCompiler(compiler.SQLCompiler): clauses = [] for column in cols: - val = on_duplicate.update.get(column.key) - if val is None: + try: + val = on_duplicate.update[column.key] + except KeyError: continue - elif coercions._is_literal(val): + + if coercions._is_literal(val): val = elements.BindParameter(None, val, type_=column.type) value_text = self.process(val.self_group(), use_schema=False) elif isinstance(val, elements.BindParameter) and val.type._isnull: diff --git a/test/dialect/mysql/test_on_duplicate.py b/test/dialect/mysql/test_on_duplicate.py index 0c6f479290..077e5ba98c 100644 --- a/test/dialect/mysql/test_on_duplicate.py +++ b/test/dialect/mysql/test_on_duplicate.py @@ -62,6 +62,21 @@ class OnDuplicateTest(fixtures.TablesTest): [(1, "ab", "bz", False)], ) + def test_on_duplicate_key_update_null(self): + foos = self.tables.foos + with testing.db.connect() as conn: + conn.execute(insert(foos, dict(id=1, bar="b", baz="bz"))) + stmt = insert(foos).values( + [dict(id=1, bar="ab"), dict(id=2, bar="b")] + ) + stmt = stmt.on_duplicate_key_update(updated_once=None) + result = conn.execute(stmt) + eq_(result.inserted_primary_key, [2]) + eq_( + conn.execute(foos.select().where(foos.c.id == 1)).fetchall(), + [(1, "b", "bz", None)], + ) + def test_on_duplicate_key_update_preserve_order(self): foos = self.tables.foos with testing.db.connect() as conn: -- 2.47.3