(self, cmd)
import FILE TABLE: Imports separated data from FILE into TABLE

        Reads data from the file into the named table using the
        current separator and encoding.  For example if the separator
        is currently a comma then the file should be CSV (comma
        separated values).

        All values read in are supplied to SQLite as strings.  If you
        want SQLite to treat them as other types then declare your
        columns appropriately.  For example declaring a column 'REAL'
        will result in the values being stored as floating point if
        they can be safely converted.  See this page for more details:

          https://sqlite.org/datatype3.html

        Another alternative is to create a tempory table, insert the
        values into that and then use casting.

          CREATE TEMPORARY TABLE import(a,b,c);

          .import filename import

          CREATE TABLE final AS SELECT cast(a as BLOB), cast(b as INTEGER), cast(c as CHAR) from import;

          DROP TABLE import;

        You can also get more sophisticated using the SQL CASE
        operator.  For example this will turn zero length strings into
        null:

          SELECT CASE col WHEN '' THEN null ELSE col END FROM ...
import FILE TABLE: Imports separated data from FILE into TABLE
import FILE TABLE : Imports separated data from FILE into TABLE
def command_import(self, cmd):
        """import FILE TABLE: Imports separated data from FILE into TABLE

        Reads data from the file into the named table using the
        current separator and encoding.  For example if the separator
        is currently a comma then the file should be CSV (comma
        separated values).

        All values read in are supplied to SQLite as strings.  If you
        want SQLite to treat them as other types then declare your
        columns appropriately.  For example declaring a column 'REAL'
        will result in the values being stored as floating point if
        they can be safely converted.  See this page for more details:

          https://sqlite.org/datatype3.html

        Another alternative is to create a tempory table, insert the
        values into that and then use casting.

          CREATE TEMPORARY TABLE import(a,b,c);

          .import filename import

          CREATE TABLE final AS SELECT cast(a as BLOB), cast(b as INTEGER), cast(c as CHAR) from import;

          DROP TABLE import;

        You can also get more sophisticated using the SQL CASE
        operator.  For example this will turn zero length strings into
        null:

          SELECT CASE col WHEN '' THEN null ELSE col END FROM ...
        """
        if len(cmd) != 2:
            raise self.Error("import takes two parameters")

        try:
            final = None
            # start transaction so database can't be changed
            # underneath us
            self.db.cursor().execute("BEGIN IMMEDIATE")
            final = "ROLLBACK"

            # how many columns?
            ncols = len(self.db.cursor().execute("pragma table_info(" + self._fmt_sql_identifier(cmd[1]) +
                                                 ")").fetchall())
            if ncols < 1:
                raise self.Error("No such table '%s'" % (cmd[1], ))

            cur = self.db.cursor()
            sql = "insert into %s values(%s)" % (self._fmt_sql_identifier(cmd[1]), ",".join("?" * ncols))

            kwargs = {}
            if self.separator == ",":
                kwargs["dialect"] = "excel"
            elif self.separator == "\t":
                kwargs["dialect"] = "excel-tab"
            else:
                kwargs["quoting"] = csv.QUOTE_NONE
                kwargs["delimiter"] = self.separator
                kwargs["doublequote"] = False
                kwargs["quotechar"] = "\x00"
            row = 1
            for line in self._csvin_wrapper(cmd[0], kwargs):
                if len(line) != ncols:
                    raise self.Error("row %d has %d columns but should have %d" % (row, len(line), ncols))
                try:
                    cur.execute(sql, line)
                except:
                    self.write(self.stderr, "Error inserting row %d" % (row, ))
                    raise
                row += 1
            self.db.cursor().execute("COMMIT")

        except:
            if final:
                self.db.cursor().execute(final)
            raise
def command_import ( self , cmd ) : if len ( cmd ) != 2 : raise self . Error ( "import takes two parameters" ) try : final = None # start transaction so database can't be changed # underneath us self . db . cursor ( ) . execute ( "BEGIN IMMEDIATE" ) final = "ROLLBACK" # how many columns? ncols = len ( self . db . cursor ( ) . execute ( "pragma table_info(" + self . _fmt_sql_identifier ( cmd [ 1 ] ) + ")" ) . fetchall ( ) ) if ncols < 1 : raise self . Error ( "No such table '%s'" % ( cmd [ 1 ] , ) ) cur = self . db . cursor ( ) sql = "insert into %s values(%s)" % ( self . _fmt_sql_identifier ( cmd [ 1 ] ) , "," . join ( "?" * ncols ) ) kwargs = { } if self . separator == "," : kwargs [ "dialect" ] = "excel" elif self . separator == "\t" : kwargs [ "dialect" ] = "excel-tab" else : kwargs [ "quoting" ] = csv . QUOTE_NONE kwargs [ "delimiter" ] = self . separator kwargs [ "doublequote" ] = False kwargs [ "quotechar" ] = "\x00" row = 1 for line in self . _csvin_wrapper ( cmd [ 0 ] , kwargs ) : if len ( line ) != ncols : raise self . Error ( "row %d has %d columns but should have %d" % ( row , len ( line ) , ncols ) ) try : cur . execute ( sql , line ) except : self . write ( self . stderr , "Error inserting row %d" % ( row , ) ) raise row += 1 self . db . cursor ( ) . execute ( "COMMIT" ) except : if final : self . db . cursor ( ) . execute ( final ) raise