Repository: kayak/pypika
Pull request #481 (2020-09-07 10:43:27)

Support ClickHouse DELETE and UPDATE


Patch:
diff --git a/pypika/dialects.py b/pypika/dialects.py
index a356818..51cb01c 100644
--- a/pypika/dialects.py
+++ b/pypika/dialects.py
@@ -632,13 +632,46 @@ class MSSQLQuery(Query):
         return MSSQLQueryBuilder(**kwargs)
 
 
+class ClickHouseQueryBuilder(QueryBuilder):
+
+    @staticmethod
+    def _delete_sql(**kwargs: Any) -> str:
+        return 'ALTER TABLE'
+
+    def _update_sql(self, **kwargs: Any) -> str:
+        return "ALTER TABLE {table}".format(table=self._update_table.get_sql(**kwargs))
+
+    def _from_sql(self, with_namespace: bool = False, **kwargs: Any) -> str:
+        selectable = ",".join(
+            clause.get_sql(subquery=True, with_alias=True, **kwargs)
+            for clause in self._from
+        )
+        if self._delete_from:
+            return " {selectable} DELETE".format(
+                selectable=selectable
+            )
+        return " FROM {selectable}".format(
+            selectable=selectable
+        )
+
+    def _set_sql(self, **kwargs: Any) -> str:
+        return " UPDATE {set}".format(
+            set=",".join(
+                "{field}={value}".format(
+                    field=field.get_sql(**dict(kwargs, with_namespace=False)), value=value.get_sql(**kwargs)
+                )
+                for field, value in self._updates
+            )
+        )
+
+
 class ClickHouseQuery(Query):
     """
     Defines a query class for use with Yandex ClickHouse.
     """
     @classmethod
     def _builder(cls, **kwargs: Any) -> QueryBuilder:
-        return QueryBuilder(dialect=Dialects.CLICKHOUSE, wrap_set_operation_queries=False, as_keyword=True, **kwargs)
+        return ClickHouseQueryBuilder(dialect=Dialects.CLICKHOUSE, wrap_set_operation_queries=False, as_keyword=True, **kwargs)
 
 
 class SQLLiteValueWrapper(ValueWrapper):


Test patch:
diff --git a/pypika/tests/dialects/test_clickhouse.py b/pypika/tests/dialects/test_clickhouse.py
index 45315ea..2a705a1 100644
--- a/pypika/tests/dialects/test_clickhouse.py
+++ b/pypika/tests/dialects/test_clickhouse.py
@@ -11,3 +11,45 @@ class ClickHouseQueryTests(TestCase):
         t = Table('abc')
         query = ClickHouseQuery.from_(t).select(t.foo.as_('f1'), t.bar.as_('f2'))
         self.assertEqual(str(query), 'SELECT "foo" AS "f1","bar" AS "f2" FROM "abc"')
+
+
+class ClickHouseDeleteTests(TestCase):
+    table_abc = Table("abc")
+
+    def test_omit_where(self):
+        q = ClickHouseQuery.from_("abc").delete()
+
+        self.assertEqual('ALTER TABLE "abc" DELETE', str(q))
+
+    def test_omit_where__table_schema(self):
+        q = ClickHouseQuery.from_(Table("abc", "schema1")).delete()
+
+        self.assertEqual('ALTER TABLE "schema1"."abc" DELETE', str(q))
+
+    def test_where_field_equals(self):
+        q1 = (
+            ClickHouseQuery.from_(self.table_abc)
+            .where(self.table_abc.foo == self.table_abc.bar)
+            .delete()
+        )
+        q2 = (
+            ClickHouseQuery.from_(self.table_abc)
+            .where(self.table_abc.foo.eq(self.table_abc.bar))
+            .delete()
+        )
+
+        self.assertEqual('ALTER TABLE "abc" DELETE WHERE "foo"="bar"', str(q1))
+        self.assertEqual('ALTER TABLE "abc" DELETE WHERE "foo"="bar"', str(q2))
+
+
+class ClickHouseUpdateTests(TestCase):
+    table_abc = Table("abc")
+
+    def test_update(self):
+        q = (
+            ClickHouseQuery.update(self.table_abc).where(self.table_abc.foo == 0).set("foo", "bar")
+        )
+
+        self.assertEqual(
+            'ALTER TABLE "abc" UPDATE "foo"=\'bar\' WHERE "foo"=0', str(q)
+        )