Repository: kayak/pypika
Pull request #331 (2019-10-15 09:05:54)

Add copy/load from csv for mysql and vertica
Add query syntax to mysql and vertica for loading csv files into tables

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

Coverage decreased (-0.01%) to 97.418% when pulling **8ea81471f873b845d2c6848f8f1323bc23613d99 on RobinPapke:master** into **01a17cd73a81d1e0ec3601606687a9011c55f426 on kayak:master**.

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

Coverage decreased (-0.01%) to 97.418% when pulling **8ea81471f873b845d2c6848f8f1323bc23613d99 on RobinPapke:master** into **01a17cd73a81d1e0ec3601606687a9011c55f426 on kayak:master**.


Patch:
diff --git a/pypika/dialects.py b/pypika/dialects.py
index 9cf44b3..259bae5 100644
--- a/pypika/dialects.py
+++ b/pypika/dialects.py
@@ -4,6 +4,7 @@ from pypika.enums import Dialects
 from pypika.queries import (
     Query,
     QueryBuilder,
+    Table,
 )
 from pypika.terms import (
     ArithmeticExpression,
@@ -92,6 +93,41 @@ class MySQLQueryBuilder(QueryBuilder):
         )
 
 
+class MySQLLoadQueryBuilder:
+    def __init__(self):
+        self._load_file = None
+        self._into_table = None
+
+    @builder
+    def load(self, fp):
+        self._load_file = fp
+
+    @builder
+    def into(self, table):
+        self._into_table = table if isinstance(table, Table) else Table(table)
+
+    def get_sql(self, *args, **kwargs):
+        querystring = ''
+        if self._load_file and self._into_table:
+            querystring += self._load_file_sql(**kwargs)
+            querystring += self._into_table_sql(**kwargs)
+            querystring += self._options_sql(**kwargs)
+
+        return querystring
+
+    def _load_file_sql(self, **kwargs):
+        return 'LOAD DATA LOCAL INFILE \'{}\''.format(self._load_file)
+
+    def _into_table_sql(self, **kwargs):
+        return ' INTO TABLE `{}`'.format(self._into_table.get_sql(**kwargs))
+
+    def _options_sql(self, **kwargs):
+        return ' FIELDS TERMINATED BY \',\''
+
+    def __str__(self):
+        return self.get_sql()
+
+
 class MySQLQuery(Query):
     """
     Defines a query class for use with MySQL.
@@ -101,6 +137,10 @@ class MySQLQuery(Query):
     def _builder(cls):
         return MySQLQueryBuilder()
 
+    @classmethod
+    def load(cls, fp):
+        return MySQLLoadQueryBuilder().load(fp)
+
 
 class VerticaQueryBuilder(QueryBuilder):
     def __init__(self):
@@ -122,6 +162,41 @@ class VerticaQueryBuilder(QueryBuilder):
         return sql
 
 
+class VerticaCopyQueryBuilder:
+    def __init__(self):
+        self._copy_table = None
+        self._from_file = None
+
+    @builder
+    def from_file(self, fp):
+        self._from_file = fp
+
+    @builder
+    def copy_(self, table):
+        self._copy_table = table if isinstance(table, Table) else Table(table)
+
+    def get_sql(self, *args, **kwargs):
+        querystring = ''
+        if self._copy_table and self._from_file:
+            querystring += self._copy_table_sql(**kwargs)
+            querystring += self._from_file_sql(**kwargs)
+            querystring += self._options_sql(**kwargs)
+
+        return querystring
+
+    def _copy_table_sql(self, **kwargs):
+        return 'COPY \"{}\"'.format(self._copy_table.get_sql(**kwargs))
+
+    def _from_file_sql(self, **kwargs):
+        return ' FROM \"{}\"'.format(self._from_file)
+
+    def _options_sql(self, **kwargs):
+        return ' PARSER fcsvparser()'
+
+    def __str__(self):
+        return self.get_sql()
+
+
 class VerticaQuery(Query):
     """
     Defines a query class for use with Vertica.
@@ -131,6 +206,10 @@ class VerticaQuery(Query):
     def _builder(cls):
         return VerticaQueryBuilder()
 
+    @classmethod
+    def from_file(cls, fp):
+        return VerticaCopyQueryBuilder().from_file(fp)
+
 
 class OracleQueryBuilder(QueryBuilder):
     def __init__(self):


Test patch:
diff --git a/pypika/tests/dialects/test_mysql.py b/pypika/tests/dialects/test_mysql.py
index 84d37c2..4923959 100644
--- a/pypika/tests/dialects/test_mysql.py
+++ b/pypika/tests/dialects/test_mysql.py
@@ -26,3 +26,21 @@ class SelectTests(unittest.TestCase):
         q = MySQLQuery.from_('abc').select('def').modifier('HIGH_PRIORITY').modifier('SQL_CALC_FOUND_ROWS')
 
         self.assertEqual('SELECT HIGH_PRIORITY SQL_CALC_FOUND_ROWS `def` FROM `abc`', str(q))
+
+
+class LoadCSVTests(unittest.TestCase):
+    table_abc = Table('abc')
+
+    def test_load_from_file(self):
+        q1 = MySQLQuery \
+            .load('/path/to/file') \
+            .into('abc')
+
+        q2 = MySQLQuery \
+            .load('/path/to/file') \
+            .into(self.table_abc)
+
+        self.assertEqual('LOAD DATA LOCAL INFILE \'/path/to/file\' INTO TABLE `abc` FIELDS TERMINATED BY \',\'',
+                         str(q1))
+        self.assertEqual('LOAD DATA LOCAL INFILE \'/path/to/file\' INTO TABLE `abc` FIELDS TERMINATED BY \',\'',
+                         str(q2))
diff --git a/pypika/tests/dialects/test_vertica.py b/pypika/tests/dialects/test_vertica.py
new file mode 100644
index 0000000..9efef71
--- /dev/null
+++ b/pypika/tests/dialects/test_vertica.py
@@ -0,0 +1,20 @@
+import unittest
+
+from pypika import Table
+from pypika.dialects import VerticaQuery
+
+
+class CopyCSVTests(unittest.TestCase):
+    table_abc = Table('abc')
+
+    def test_copy_from_file(self):
+        q1 = VerticaQuery \
+            .from_file('/path/to/file') \
+            .copy_('abc')
+
+        q2 = VerticaQuery \
+            .from_file('/path/to/file') \
+            .copy_(self.table_abc)
+
+        self.assertEqual('COPY "abc" FROM "/path/to/file" PARSER fcsvparser()', str(q1))
+        self.assertEqual('COPY "abc" FROM "/path/to/file" PARSER fcsvparser()', str(q2))