Repository: kayak/pypika
Pull request #116 (2018-04-03 08:01:58)

implement ON DUPLICATE KEY UPDATE for MySQL
* I've chosen to have a similar syntax to `.set` for `.on_duplicate_key_update` to keep updating values consistent
* there's a new `Values` wrapper to represent the `VALUES` term in the new clause


coveralls: 
[![Coverage Status](https://coveralls.io/builds/16310123/badge)](https://coveralls.io/builds/16310123)

Coverage decreased (-0.1%) to 97.811% when pulling **2438b9bd163f6e6cad1de88fc547bffa6322d949 on inkmix:implement-on-duplicate-key-update** into **eeea26c0d4d2e1a162cd45ae9e2484d61cd9d585 on kayak:master**.


Patch:
diff --git a/README.rst b/README.rst
index 9d4d015..84ac8a0 100644
--- a/README.rst
+++ b/README.rst
@@ -581,6 +581,25 @@ Multiple rows of data can be inserted either by chaining the ``insert`` function
     q = Query.into(customers).insert((1, 'Jane', 'Doe', 'jane@example.com'),
                                      (2, 'John', 'Doe', 'john@example.com'))
 
+Insert with on Duplicate Key Update
+"""""""""""""""""""""""""""""""""""
+
+.. code-block:: python
+
+    customers = Table('customers')
+
+    q = Query.into(customers)\
+        .insert(1, 'Jane', 'Doe', 'jane@example.com')\
+        .on_duplicate_key_update(customers.email, Values(customers.email))
+
+.. code-block:: sql
+
+    INSERT INTO customers VALUES (1,'Jane','Doe','jane@example.com') ON DUPLICATE KEY UPDATE `email`=VALUES(`email`)
+
+``.on_duplicate_key_update`` works similar to ``.set`` for updating rows, additionally it provides the ``Values``
+wrapper to update to the value specified in the ``INSERT`` clause.
+
+
 Insert with a SELECT Query
 """"""""""""""""""""""""""
 
@@ -683,4 +702,4 @@ Crafted with ♥ in Berlin.
 .. |License| image:: https://img.shields.io/hexpm/l/plug.svg?maxAge=2592000
    :target: http://www.apache.org/licenses/LICENSE-2.0
 
-.. _available_badges_end:
\ No newline at end of file
+.. _available_badges_end:
diff --git a/pypika/dialects.py b/pypika/dialects.py
index b2ea3b0..a615bb9 100644
--- a/pypika/dialects.py
+++ b/pypika/dialects.py
@@ -1,9 +1,41 @@
-from pypika.terms import ArithmeticExpression, Function, Field, Star
+from pypika.terms import (
+    ArithmeticExpression,
+    Function,
+    Field,
+    Star,
+    ValueWrapper,
+)
 from pypika.utils import builder, QueryException
 from pypika.enums import Dialects
 from pypika.queries import Query, QueryBuilder
 
 
+class MySQLQueryBuilder(QueryBuilder):
+    def __init__(self):
+        super(MySQLQueryBuilder, self).__init__(quote_char='`', dialect=Dialects.MYSQL, wrap_union_queries=False)
+        self._duplicate_updates = []
+
+    @builder
+    def on_duplicate_key_update(self, field, value):
+        field = Field(field) if not isinstance(field, Field) else field
+        self._duplicate_updates.append((field, ValueWrapper(value)))
+
+    def get_sql(self, with_alias=False, subquery=False, **kwargs):
+        querystring = super(MySQLQueryBuilder, self).get_sql(with_alias, subquery, **kwargs)
+        if querystring and self._duplicate_updates:
+            querystring += self._on_duplicate_key_update_sql(**kwargs)
+        return querystring
+
+    def _on_duplicate_key_update_sql(self, **kwargs):
+        return ' ON DUPLICATE KEY UPDATE {updates}'.format(
+            updates=','.join(
+                '{field}={value}'.format(
+                    field=field.get_sql(**kwargs),
+                    value=value.get_sql(**kwargs)) for field, value in self._duplicate_updates
+            )
+        )
+
+
 class MySQLQuery(Query):
     """
     Defines a query class for use with MySQL.
@@ -11,7 +43,7 @@ class MySQLQuery(Query):
 
     @classmethod
     def _builder(cls):
-        return QueryBuilder(quote_char='`', dialect=Dialects.MYSQL, wrap_union_queries=False)
+        return MySQLQueryBuilder()
 
 
 class VerticaQueryBuilder(QueryBuilder):
diff --git a/pypika/queries.py b/pypika/queries.py
index 00c620e..b32f1a7 100644
--- a/pypika/queries.py
+++ b/pypika/queries.py
@@ -519,6 +519,7 @@ class QueryBuilder(Selectable, Term):
     def union(self, other):
         return _UnionQuery(self, other, UnionType.distinct)
 
+    @builder
     def union_all(self, other):
         return _UnionQuery(self, other, UnionType.all)
 
diff --git a/pypika/terms.py b/pypika/terms.py
index 326babd..d90e3a5 100644
--- a/pypika/terms.py
+++ b/pypika/terms.py
@@ -210,13 +210,15 @@ class ValueWrapper(Term):
         return []
 
     def get_sql(self, quote_char=None, **kwargs):
-        sql = self._get_value_sql()
+        sql = self._get_value_sql(quote_char=quote_char, **kwargs)
         if self.alias is None:
             return sql
         return alias_sql(sql, self.alias, quote_char)
 
-    def _get_value_sql(self):
+    def _get_value_sql(self, quote_char=None, **kwargs):
         # FIXME escape values
+        if isinstance(self.value, Term):
+            return self.value.get_sql(quote_char=quote_char, **kwargs)
         if isinstance(self.value, Enum):
             return self.value.value
         if isinstance(self.value, date):
@@ -231,6 +233,15 @@ class ValueWrapper(Term):
         return str(self.value)
 
 
+class Values(Term):
+    def __init__(self, field,):
+        super(Values, self).__init__(None)
+        self.field = Field(field) if not isinstance(field, Field) else field
+
+    def get_sql(self, quote_char=None, **kwargs):
+        return 'VALUES({value})'.format(value=self.field.get_sql(quote_char=quote_char, **kwargs))
+
+
 class NullValue(Term):
     def fields(self):
         return []


Test patch:
diff --git a/pypika/tests/test_inserts.py b/pypika/tests/test_inserts.py
index 1712d4a..4cc5d83 100644
--- a/pypika/tests/test_inserts.py
+++ b/pypika/tests/test_inserts.py
@@ -1,8 +1,17 @@
 # coding: utf8
 import unittest
 
-from pypika import Table, Tables, Query, PostgreSQLQuery
+from pypika import (
+    Field as F,
+    MySQLQuery,
+    PostgreSQLQuery,
+    Query,
+    Table,
+    Tables,
+    functions as fn,
+)
 from pypika.functions import Avg
+from pypika.terms import Values
 from pypika.utils import QueryException
 
 __author__ = "Timothy Heys"
@@ -171,6 +180,135 @@ class PostgresInsertIntoReturningTests(unittest.TestCase):
             PostgreSQLQuery.into(self.table_abc).insert(1).returning(table_cba.id)
 
 
+class InsertIntoOnDuplicateTests(unittest.TestCase):
+    table_abc = Table('abc')
+
+    def test_insert_one_column(self):
+        query = MySQLQuery\
+            .into(self.table_abc).insert(1)\
+            .on_duplicate_key_update(self.table_abc.foo, self.table_abc.foo)
+        self.assertEqual('INSERT INTO `abc` VALUES (1) ON DUPLICATE KEY UPDATE `foo`=`foo`', str(query))
+
+    def test_insert_one_column_using_values(self):
+        query = MySQLQuery\
+            .into(self.table_abc).insert(1)\
+            .on_duplicate_key_update(self.table_abc.foo, Values(self.table_abc.foo))
+        self.assertEqual('INSERT INTO `abc` VALUES (1) ON DUPLICATE KEY UPDATE `foo`=VALUES(`foo`)', str(query))
+
+    def test_insert_one_column_single_element_array(self):
+        query = MySQLQuery\
+            .into(self.table_abc).insert((1,))\
+            .on_duplicate_key_update(self.table_abc.foo, self.table_abc.foo)
+
+        self.assertEqual('INSERT INTO `abc` VALUES (1) ON DUPLICATE KEY UPDATE `foo`=`foo`', str(query))
+
+    def test_insert_one_column_multi_element_array(self):
+        query = MySQLQuery\
+            .into(self.table_abc).insert((1,), (2,))\
+            .on_duplicate_key_update(self.table_abc.foo, self.table_abc.foo)
+
+        self.assertEqual('INSERT INTO `abc` VALUES (1),(2) ON DUPLICATE KEY UPDATE `foo`=`foo`', str(query))
+
+    def test_insert_multiple_columns_on_duplicate_update_one_with_same_value(self):
+        query = MySQLQuery\
+            .into(self.table_abc).insert(1, 'a')\
+            .on_duplicate_key_update(self.table_abc.bar, Values(self.table_abc.bar))
+
+        self.assertEqual('INSERT INTO `abc` VALUES (1,\'a\') ON DUPLICATE KEY UPDATE `bar`=VALUES(`bar`)', str(query))
+
+    def test_insert_multiple_columns_on_duplicate_update_one_with_different_value(self):
+        query = MySQLQuery\
+            .into(self.table_abc).insert(1, 'a') \
+            .on_duplicate_key_update(self.table_abc.bar, 'b')
+
+        self.assertEqual('INSERT INTO `abc` VALUES (1,\'a\') ON DUPLICATE KEY UPDATE `bar`=\'b\'', str(query))
+
+    def test_insert_multiple_columns_on_duplicate_update_one_with_expression(self):
+        query = MySQLQuery\
+            .into(self.table_abc).insert(1, 2) \
+            .on_duplicate_key_update(self.table_abc.bar, 4+F('bar'))  # todo sql expression? not python
+
+        self.assertEqual('INSERT INTO `abc` VALUES (1,2) ON DUPLICATE KEY UPDATE `bar`=4+`bar`', str(query))
+
+    def test_insert_multiple_columns_on_duplicate_update_one_with_expression_using_original_field_value(self):
+        query = MySQLQuery\
+            .into(self.table_abc).insert(1, 'a')\
+            .on_duplicate_key_update(self.table_abc.bar, fn.Concat(self.table_abc.bar, 'update'))
+
+        self.assertEqual(
+            'INSERT INTO `abc` VALUES (1,\'a\') ON DUPLICATE KEY UPDATE `bar`=CONCAT(`bar`,\'update\')',
+            str(query)
+        )
+
+    def test_insert_multiple_columns_on_duplicate_update_one_with_expression_using_values(self):
+        query = MySQLQuery\
+            .into(self.table_abc).insert(1, 'a')\
+            .on_duplicate_key_update(self.table_abc.bar, fn.Concat(Values(self.table_abc.bar), 'update'))
+
+        self.assertEqual(
+            'INSERT INTO `abc` VALUES (1,\'a\') ON DUPLICATE KEY UPDATE `bar`=CONCAT(VALUES(`bar`),\'update\')',
+            str(query)
+        )
+
+    def test_insert_multiple_columns_on_duplicate_update_multiple(self):
+        query = MySQLQuery \
+            .into(self.table_abc).insert(1, 'a', 'b') \
+            .on_duplicate_key_update(self.table_abc.bar, 'b') \
+            .on_duplicate_key_update(self.table_abc.baz, 'c')
+
+        self.assertEqual(
+            'INSERT INTO `abc` VALUES (1,\'a\',\'b\') ON DUPLICATE KEY UPDATE `bar`=\'b\',`baz`=\'c\'',
+            str(query)
+        )
+
+    def test_insert_multi_rows_chained_mixed_on_duplicate_update_multiple(self):
+        query = MySQLQuery.into(self.table_abc)\
+            .insert((1, 'a', True), (2, 'b', False))\
+            .insert(3, 'c', True)\
+            .on_duplicate_key_update(self.table_abc.foo, self.table_abc.foo)\
+            .on_duplicate_key_update(self.table_abc.bar, Values(self.table_abc.bar))
+
+        self.assertEqual(
+            'INSERT INTO `abc` VALUES (1,\'a\',true),(2,\'b\',false),(3,\'c\',true) '
+            'ON DUPLICATE KEY UPDATE `foo`=`foo`,`bar`=VALUES(`bar`)',
+            str(query)
+        )
+
+    def test_insert_selected_columns_on_duplicate_update_one(self):
+        query = MySQLQuery.into(self.table_abc)\
+            .columns(self.table_abc.foo, self.table_abc.bar, self.table_abc.baz)\
+            .insert(1, 'a', True)\
+            .on_duplicate_key_update(self.table_abc.baz, False)
+
+        self.assertEqual(
+            'INSERT INTO `abc` (`foo`,`bar`,`baz`) VALUES (1,\'a\',true) ON DUPLICATE KEY UPDATE `baz`=false',
+            str(query)
+        )
+
+    def test_insert_selected_columns_on_duplicate_update_multiple(self):
+        query = MySQLQuery.into(self.table_abc)\
+            .columns(self.table_abc.foo, self.table_abc.bar, self.table_abc.baz)\
+            .insert(1, 'a', True)\
+            .on_duplicate_key_update(self.table_abc.baz, False)\
+            .on_duplicate_key_update(self.table_abc.bar, Values(self.table_abc.bar))
+
+        self.assertEqual(
+            'INSERT INTO `abc` (`foo`,`bar`,`baz`) VALUES (1,\'a\',true) '
+            'ON DUPLICATE KEY UPDATE `baz`=false,`bar`=VALUES(`bar`)',
+            str(query)
+        )
+
+    def test_insert_none_skipped(self):
+        query = MySQLQuery.into(self.table_abc).insert().on_duplicate_key_update(self.table_abc.baz, False)
+
+        self.assertEqual('', str(query))
+
+    def test_insert_ignore(self):
+        query = MySQLQuery.into(self.table_abc).insert(1).ignore().on_duplicate_key_update(self.table_abc.baz, False)
+
+        self.assertEqual('INSERT IGNORE INTO `abc` VALUES (1) ON DUPLICATE KEY UPDATE `baz`=false', str(query))
+
+
 class InsertSelectFromTests(unittest.TestCase):
     table_abc, table_efg, table_hij = Tables('abc', 'efg', 'hij')
 
diff --git a/pypika/tests/test_updates.py b/pypika/tests/test_updates.py
index 4866893..a3b858e 100644
--- a/pypika/tests/test_updates.py
+++ b/pypika/tests/test_updates.py
@@ -36,7 +36,7 @@ class UpdateTests(unittest.TestCase):
         self.assertEqual('UPDATE "abc" SET "foo"=null', str(q))
 
 
-class PostgesUpdateTests(unittest.TestCase):
+class PostgresUpdateTests(unittest.TestCase):
     table_abc = Table('abc')
 
     def test_update_returning_str(self):
@@ -46,10 +46,6 @@ class PostgesUpdateTests(unittest.TestCase):
 
         self.assertEqual('UPDATE "abc" SET "foo"=\'bar\' WHERE "foo"=0 RETURNING id', str(q))
 
-
-class PostgresUpdateTests(unittest.TestCase):
-    table_abc = Table('abc')
-
     def test_update_returning(self):
         q = PostgreSQLQuery.update(self.table_abc).where(
             self.table_abc.foo == 0