Repository: kayak/pypika
Pull request #409 (2020-04-17 19:45:50)

Add handler for MySQL ON CONFLICT IGNORE statement
Also added a couple of PostgreSQL ON CONFLICT statement examples to README. Not sure about where to place tests for this feature as many PostgreSQL specific modifier tests are in `test_inserts` module.


Patch:
diff --git a/README.rst b/README.rst
index 49db1d3..29f135b 100644
--- a/README.rst
+++ b/README.rst
@@ -772,8 +772,23 @@ 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
-"""""""""""""""""""""""""""""""""""
+Insert with constraint violation handling
+"""""""""""""""""""""""""""""""""""""""""
+
+MySQL
+~~~~~
+
+.. code-block:: python
+
+    customers = Table('customers')
+
+    q = Query.into(customers)\
+        .insert(1, 'Jane', 'Doe', 'jane@example.com')\
+        .on_duplicate_key_ignore())
+
+.. code-block:: sql
+
+    INSERT INTO customers VALUES (1,'Jane','Doe','jane@example.com') ON DUPLICATE KEY IGNORE
 
 .. code-block:: python
 
@@ -790,13 +805,42 @@ Insert with on Duplicate Key Update
 ``.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.
 
+PostgreSQL
+~~~~~~~~~~
+
+.. code-block:: python
+
+    customers = Table('customers')
+
+    q = Query.into(customers)\
+        .insert(1, 'Jane', 'Doe', 'jane@example.com')\
+        .on_conflict(customers.email)
+        .do_nothing()
+
+.. code-block:: sql
+
+    INSERT INTO "abc" VALUES (1,'Jane','Doe','jane@example.com') ON CONFLICT ("email") DO NOTHING
+
+.. code-block:: python
+
+    customers = Table('customers')
+
+    q = Query.into(customers)\
+        .insert(1, 'Jane', 'Doe', 'jane@example.com')\
+        .on_conflict(customers.email)
+        .do_update(customers.email, 'bob@example.com')
+
+.. code-block:: sql
+
+    INSERT INTO "customers" VALUES (1,'Jane','Doe','jane@example.com') ON CONFLICT ("email") DO UPDATE SET "email"='bob@example.com'
+
 
 Insert from a SELECT Sub-query
 """"""""""""""""""""""""""""""
 
 .. code-block:: sql
 
-    INSERT INTO customers VALUES (1,'Jane','Doe','jane@example.com'),(2,'John','Doe','john@example.com')
+    INSERT INTO "customers" VALUES (1,'Jane','Doe','jane@example.com'),(2,'John','Doe','john@example.com')
 
 
 To specify the columns and the order, use the ``columns`` function.
diff --git a/pypika/dialects.py b/pypika/dialects.py
index 7d3288a..9f5e99f 100644
--- a/pypika/dialects.py
+++ b/pypika/dialects.py
@@ -50,23 +50,38 @@ class MySQLQueryBuilder(QueryBuilder):
               dialect=Dialects.MYSQL, wrap_union_queries=False, **kwargs
         )
         self._duplicate_updates = []
+        self._ignore_duplicates = False
         self._modifiers = []
 
     def __copy__(self):
         newone = super(MySQLQueryBuilder, self).__copy__()
         newone._duplicate_updates = copy(self._duplicate_updates)
+        newone._ignore_duplicates = copy(self._ignore_duplicates)
         return newone
 
     @builder
     def on_duplicate_key_update(self, field, value):
+        if self._ignore_duplicates:
+            raise QueryException("Can not have two conflict handlers")
+
         field = Field(field) if not isinstance(field, Field) else field
         self._duplicate_updates.append((field, ValueWrapper(value)))
 
+    @builder
+    def on_duplicate_key_ignore(self):
+        if self._duplicate_updates:
+            raise QueryException("Can not have two conflict handlers")
+
+        self._ignore_duplicates = True
+
     def get_sql(self, **kwargs):
         self._set_kwargs_defaults(kwargs)
         querystring = super(MySQLQueryBuilder, self).get_sql(**kwargs)
-        if querystring and self._duplicate_updates:
-            querystring += self._on_duplicate_key_update_sql(**kwargs)
+        if querystring:
+            if self._duplicate_updates:
+                querystring += self._on_duplicate_key_update_sql(**kwargs)
+            elif self._ignore_duplicates:
+                querystring += self._on_duplicate_key_ignore_sql()
         return querystring
 
     def _on_duplicate_key_update_sql(self, **kwargs):
@@ -79,6 +94,9 @@ class MySQLQueryBuilder(QueryBuilder):
               )
         )
 
+    def _on_duplicate_key_ignore_sql(self):
+        return " ON DUPLICATE KEY IGNORE"
+
     @builder
     def modifier(self, value):
         """


Test patch:
diff --git a/pypika/tests/dialects/test_mysql.py b/pypika/tests/dialects/test_mysql.py
index 57c9353..d226629 100644
--- a/pypika/tests/dialects/test_mysql.py
+++ b/pypika/tests/dialects/test_mysql.py
@@ -3,6 +3,7 @@ import unittest
 from pypika import (
     Table,
     MySQLQuery,
+    QueryException,
 )
 
 
@@ -42,6 +43,51 @@ class SelectTests(unittest.TestCase):
         )
 
 
+class UpdateTests(unittest.TestCase):
+    table_abc = Table("abc")
+
+    def test_update(self):
+        q = (
+            MySQLQuery.into("abc")
+            .insert(1, [1, "a", True])
+        )
+
+        self.assertEqual(
+            "INSERT INTO `abc` VALUES (1,[1,'a',true])", str(q)
+        )
+
+    def test_on_duplicate_key_ignore_update(self):
+        q = (
+            MySQLQuery.into("abc")
+            .insert(1, [1, "a", True])
+            .on_duplicate_key_ignore()
+        )
+
+        self.assertEqual(
+            "INSERT INTO `abc` VALUES (1,[1,'a',true]) ON DUPLICATE KEY IGNORE", str(q)
+        )
+
+    def test_on_duplicate_key_update_update(self):
+        q = (
+            MySQLQuery.into("abc")
+            .insert(1, [1, "a", True])
+            .on_duplicate_key_update(self.table_abc.a, 'b')
+        )
+
+        self.assertEqual(
+            "INSERT INTO `abc` VALUES (1,[1,'a',true]) ON DUPLICATE KEY UPDATE `a`='b'", str(q)
+        )
+
+    def test_conflict_handlers_update(self):
+        with self.assertRaises(QueryException):
+            (
+                MySQLQuery.into("abc")
+                .insert(1, [1, "a", True])
+                .on_duplicate_key_ignore()
+                .on_duplicate_key_update(self.table_abc.a, 'b')
+            )
+
+
 class LoadCSVTests(unittest.TestCase):
     table_abc = Table("abc")
 
diff --git a/pypika/tests/test_inserts.py b/pypika/tests/test_inserts.py
index b568830..7a3ada7 100644
--- a/pypika/tests/test_inserts.py
+++ b/pypika/tests/test_inserts.py
@@ -192,9 +192,9 @@ class PostgresInsertIntoOnConflictTests(unittest.TestCase):
     def test_insert_on_conflict_do_nothing_multiple_fields(self):
         query = (
             PostgreSQLQuery.into(self.table_abc)
-                .insert(1)
-                .on_conflict(self.table_abc.id, self.table_abc.sub_id)
-                .do_nothing()
+            .insert(1)
+            .on_conflict(self.table_abc.id, self.table_abc.sub_id)
+            .do_nothing()
         )
 
         self.assertEqual(