Repository: kayak/pypika
Pull request #258 (2019-06-17 15:43:42)

Support REPLACE statement Issue #144
Initial PR to support `REPLACE` statement to substitute `INSERT`


Patch:
diff --git a/pypika/queries.py b/pypika/queries.py
index ab63847..a48a5c3 100644
--- a/pypika/queries.py
+++ b/pypika/queries.py
@@ -372,6 +372,7 @@ class QueryBuilder(Selectable, Term):
         self._insert_table = None
         self._update_table = None
         self._delete_from = False
+        self._replace = False
 
         self._with = []
         self._selects = []
@@ -509,15 +510,20 @@ class QueryBuilder(Selectable, Term):
 
         if not terms:
             return
+        else:
+            self._validate_terms_and_append(*terms)
+        self._replace = False
 
-        if not isinstance(terms[0], (list, tuple, set)):
-            terms = [terms]
+    @builder
+    def replace(self, *terms):
+        if self._insert_table is None:
+            raise AttributeError("'Query' object has no attribute '%s'" % 'insert')
 
-        for values in terms:
-            self._values.append([value
-                                 if isinstance(value, Term)
-                                 else self.wrap_constant(value)
-                                 for value in values])
+        if not terms:
+            return
+        else:
+            self._validate_terms_and_append(*terms)
+        self._replace = True
 
     @builder
     def distinct(self):
@@ -734,6 +740,21 @@ class QueryBuilder(Selectable, Term):
         subquery.alias = 'sq%d' % self._subquery_count
         self._subquery_count += 1
 
+    def _validate_terms_and_append(self, *terms):
+        """
+        Handy function for INSERT and REPLACE statements in order to check if 
+        terms are introduced and how append them to `self._values`
+        """
+        if not isinstance(terms[0], (list, tuple, set)):
+            terms = [terms]
+
+        for values in terms:
+            self._values.append([value
+                                 if isinstance(value, Term)
+                                 else self.wrap_constant(value)
+                                 for value in values])
+
+
     def __str__(self):
         return self.get_sql(quote_char=self.quote_char, dialect=self.dialect)
 
@@ -795,7 +816,10 @@ class QueryBuilder(Selectable, Term):
             querystring = self._delete_sql(**kwargs)
 
         elif not self._select_into and self._insert_table:
-            querystring = self._insert_sql(**kwargs)
+            if self._replace:
+                querystring = self._replace_sql(**kwargs)
+            else:
+                querystring = self._insert_sql(**kwargs)
 
             if self._columns:
                 querystring += self._columns_sql(**kwargs)
@@ -877,6 +901,11 @@ class QueryBuilder(Selectable, Term):
               ignore='IGNORE ' if self._ignore else ''
         )
 
+    def _replace_sql(self, **kwargs):
+        return 'REPLACE INTO {table}'.format(
+              table=self._insert_table.get_sql(**kwargs),
+        )
+
     @staticmethod
     def _delete_sql(**kwargs):
         return 'DELETE'


Test patch:
diff --git a/pypika/tests/test_inserts.py b/pypika/tests/test_inserts.py
index 682140a..574e309 100644
--- a/pypika/tests/test_inserts.py
+++ b/pypika/tests/test_inserts.py
@@ -469,3 +469,19 @@ class SelectIntoTests(unittest.TestCase):
         self.assertEqual('SELECT "abc"."foo","abc"."bar","hij"."fiz","hij"."buz" '
                          'INTO "efg" FROM "abc" '
                          'JOIN "hij" ON "abc"."id"="hij"."abc_id"', str(query))
+
+
+class ReplaceTests(unittest.TestCase):
+    table_abc, table_def = Tables('abc', 'efg')
+
+    def test_replace_simple(self):
+        query = Query.into(self.table_abc).replace('v1', 'v2', 'v3')
+        expected_output = 'REPLACE INTO "abc" VALUES (\'v1\',\'v2\',\'v3\')'
+        self.assertEqual(str(query), expected_output)
+
+    def test_replace_subquery(self):
+        query = Query.into(self.table_abc).replace(
+            Query.from_(self.table_def).select('f1', 'f2')
+        )
+        expected_output = 'REPLACE INTO "abc" VALUES ((SELECT "f1","f2" FROM "efg"))'
+        self.assertEqual(str(query), expected_output)