Repository: dbcli/litecli

Issue:
Support CSV import
SQLite [allows users to import data from CSV files][1]:

```sh
$ cat foo.csv
one,two,three
1,2,3
4,5,6

$ sqlite3 foo.db
SQLite version 3.22.0 2018-01-22 18:45:57
Enter ".help" for usage hints.
sqlite> .mode csv
sqlite> .import foo.csv foo
sqlite> .schema foo
CREATE TABLE foo(
  "one" TEXT,
  "two" TEXT,
  "three" TEXT
);
sqlite> select * from foo;
1,2,3
4,5,6
sqlite>
```

However, LiteCLI gives a syntax error:

```
$ litecli foo.db
Version: 1.2.0
Mail: https://groups.google.com/forum/#!forum/litecli-users
GitHub: https://github.com/dbcli/litecli
foo.db> .mode csv
Changed table format to csv
Time: 0.000s
foo.db> .import foo.csv foo
near ".": syntax error
foo.db>
```

Aside from `.`, CSV paths may include other unexpected characters like `:` and `/`, e.g. in this example from the official documentation:

```
sqlite> .mode csv
sqlite> .import C:/work/somedata.csv tab1
```

This likely affects [CSV export][2] as well.

Can CSV import (and, ideally, output) support be added to LiteCLI?

[1]: https://sqlite.org/cli.html#csv_import
[2]: https://sqlite.org/cli.html#csv_export


Discussion:

Patch:
diff --git a/.gitignore b/.gitignore
index ab7d02e..63c3eb6 100644
--- a/.gitignore
+++ b/.gitignore
@@ -6,6 +6,7 @@
 /src
 /test/behave.ini
 /litecli_env
+/.venv
 /.eggs
 
 .vagrant
diff --git a/changelog.md b/changelog.md
index 7ef61d6..da703e3 100644
--- a/changelog.md
+++ b/changelog.md
@@ -1,3 +1,10 @@
+Upcomming:
+==========
+
+Features:
+---------
+* Added `.import` command for importing data from file into table. (Thanks: [Zhaolong Zhu])
+
 1.2.0
 =====
 
@@ -34,4 +41,5 @@ Features:
 [Amjith]: https://blog.amjith.com
 [Zhiming Wang]: https://github.com/zmwangx
 [Irina Truong]: https://github.com/j-bennet
-[Shawn Chapla]: https://github.com/shwnchpl 
+[Shawn Chapla]: https://github.com/shwnchpl
+[Zhaolong Zhu]: https://github.com/zzl0
diff --git a/litecli/packages/completion_engine.py b/litecli/packages/completion_engine.py
index 21ca81d..aa24349 100644
--- a/litecli/packages/completion_engine.py
+++ b/litecli/packages/completion_engine.py
@@ -1,6 +1,7 @@
 from __future__ import print_function
 import os
 import sys
+import shlex
 import sqlparse
 from sqlparse.sql import Comparison, Identifier, Where
 from sqlparse.compat import text_type
@@ -116,12 +117,36 @@ def suggest_special(text):
             {"type": "view", "schema": []},
             {"type": "schema"},
         ]
-    elif cmd in ["\\.", "source", ".open"]:
+
+    if cmd in ["\\.", "source", ".open"]:
         return [{"type": "file_name"}]
 
+    if cmd in [".import"]:
+        # Usage: .import filename table
+        if _expecting_arg_idx(arg, text) == 1:
+            return [{"type": "file_name"}]
+        else:
+            return [{"type": "table", "schema": []}]
+
     return [{"type": "keyword"}, {"type": "special"}]
 
 
+def _expecting_arg_idx(arg, text):
+    """Return the index of expecting argument.
+
+    >>> _expecting_arg_idx("./da", ".import ./da")
+    1
+    >>> _expecting_arg_idx("./data.csv", ".import ./data.csv")
+    1
+    >>> _expecting_arg_idx("./data.csv", ".import ./data.csv ")
+    2
+    >>> _expecting_arg_idx("./data.csv t", ".import ./data.csv t")
+    2
+    """
+    args = shlex.split(arg)
+    return len(args) + int(text[-1].isspace())
+
+
 def suggest_based_on_last_token(token, text_before_cursor, full_text, identifier):
     if isinstance(token, string_types):
         token_v = token.lower()
diff --git a/litecli/packages/special/dbcommands.py b/litecli/packages/special/dbcommands.py
index 9d5d84e..ce09b13 100644
--- a/litecli/packages/special/dbcommands.py
+++ b/litecli/packages/special/dbcommands.py
@@ -1,6 +1,8 @@
-from __future__ import unicode_literals
+from __future__ import unicode_literals, print_function
+import csv
 import logging
 import os
+import sys
 import platform
 import shlex
 from sqlite3 import ProgrammingError
@@ -216,3 +218,46 @@ def read_script(cur, arg, **_):
         script = f.read()
         cur.executescript(script)
     return [(None, None, None, "")]
+
+
+@special_command(
+    ".import",
+    ".import filename table",
+    "Import data from filename into an existing table",
+    arg_type=PARSED_QUERY,
+    case_sensitive=True,
+)
+def import_file(cur, arg=None, **_):
+    args = shlex.split(arg)
+    if len(args) != 2:
+        raise TypeError("Usage: .import filename table")
+
+    filename, table = args
+    cur.execute("PRAGMA table_info(%s)" % table)
+    ncols = len(cur.fetchall())
+    insert_tmpl = 'INSERT INTO "%s" VALUES (?%s)' % (table, ",?" * (ncols - 1))
+
+    with open(filename, "r") as csvfile:
+        dialect = csv.Sniffer().sniff(csvfile.read(1024))
+        csvfile.seek(0)
+        reader = csv.reader(csvfile, dialect)
+
+        cur.execute("BEGIN")
+        ninserted, nignored = 0, 0
+        for i, row in enumerate(reader):
+            if len(row) != ncols:
+                print(
+                    "%s:%d expected %d columns but found %d - ignored"
+                    % (filename, i, ncols, len(row)),
+                    file=sys.stderr,
+                )
+                nignored += 1
+                continue
+            cur.execute(insert_tmpl, row)
+            ninserted += 1
+        cur.execute("COMMIT")
+
+    status = "Inserted %d rows into %s" % (ninserted, table)
+    if nignored > 0:
+        status += " (%d rows are ignored)" % nignored
+    return [(None, None, None, status)]


Test patch:
diff --git a/tests/data/import_data.csv b/tests/data/import_data.csv
new file mode 100644
index 0000000..d68d655
--- /dev/null
+++ b/tests/data/import_data.csv
@@ -0,0 +1,2 @@
+t1,11
+t2,22
diff --git a/tests/test_dbspecial.py b/tests/test_dbspecial.py
index b0ca402..18e7f1d 100644
--- a/tests/test_dbspecial.py
+++ b/tests/test_dbspecial.py
@@ -3,6 +3,22 @@ from test_completion_engine import sorted_dicts
 from litecli.packages.special.utils import format_uptime
 
 
+def test_import_first_argument():
+    test_cases = [
+        # text, expecting_arg_idx
+        [".import ", 1],
+        [".import ./da", 1],
+        [".import ./data.csv ", 2],
+        [".import ./data.csv t", 2],
+    ]
+    for text, expecting_arg_idx in test_cases:
+        suggestions = suggest_type(text, text)
+        if expecting_arg_idx == 1:
+            assert suggestions == [{"type": "file_name"}]
+        else:
+            assert suggestions == [{"type": "table", "schema": []}]
+
+
 def test_u_suggests_databases():
     suggestions = suggest_type("\\u ", "\\u ")
     assert sorted_dicts(suggestions) == sorted_dicts([{"type": "database"}])
diff --git a/tests/test_main.py b/tests/test_main.py
index 0b926f4..90132f1 100644
--- a/tests/test_main.py
+++ b/tests/test_main.py
@@ -238,3 +238,24 @@ def test_reserved_space_is_integer():
     lc = LiteCli()
     assert isinstance(lc.get_reserved_space(), int)
     click.get_terminal_size = old_func
+
+
+@dbtest
+def test_import_command(executor):
+    data_file = os.path.join(project_dir, "tests", "data", "import_data.csv")
+    run(executor, """create table tbl1(one varchar(10), two smallint)""")
+
+    # execute
+    run(executor, """.import %s tbl1""" % data_file)
+
+    # verify
+    sql = "select * from tbl1;"
+    runner = CliRunner()
+    result = runner.invoke(cli, args=CLI_ARGS + ["--csv"], input=sql)
+
+    expected = """one","two"
+"t1","11"
+"t2","22"
+"""
+    assert result.exit_code == 0
+    assert expected in "".join(result.output)