(self, cmd)
autoimport FILENAME ?TABLE?: Imports filename creating a table and automatically working out separators and data types (alternative to .import command)

        The import command requires that you precisely pre-setup the
        table and schema, and set the data separators (eg commas or
        tabs).  In many cases this information can be automatically
        deduced from the file contents which is what this command
        does.  There must be at least two columns and two rows.

        If the table is not specified then the basename of the file
        will be used.

        Additionally the type of the contents of each column is also
        deduced - for example if it is a number or date.  Empty values
        are turned into nulls.  Dates are normalized into YYYY-MM-DD
        format and DateTime are normalized into ISO8601 format to
        allow easy sorting and searching.  4 digit years must be used
        to detect dates.  US (swapped day and month) versus rest of
        the world is also detected providing there is at least one
        value that resolves the ambiguity.

        Care is taken to ensure that columns looking like numbers are
        only treated as numbers if they do not have unnecessary
        leading zeroes or plus signs.  This is to avoid treating phone
        numbers and similar number like strings as integers.

        This command can take quite some time on large files as they
        are effectively imported twice.  The first time is to
        determine the format and the types for each column while the
        second pass actually imports the data.
autoimport FILENAME ?TABLE?: Imports filename creating a table and automatically working out separators and data types (alternative to .import command)
autoimport FILENAME ?TABLE? : Imports filename creating a table and automatically working out separators and data types ( alternative to . import command )
def command_autoimport(self, cmd):
        """autoimport FILENAME ?TABLE?: Imports filename creating a table and automatically working out separators and data types (alternative to .import command)

        The import command requires that you precisely pre-setup the
        table and schema, and set the data separators (eg commas or
        tabs).  In many cases this information can be automatically
        deduced from the file contents which is what this command
        does.  There must be at least two columns and two rows.

        If the table is not specified then the basename of the file
        will be used.

        Additionally the type of the contents of each column is also
        deduced - for example if it is a number or date.  Empty values
        are turned into nulls.  Dates are normalized into YYYY-MM-DD
        format and DateTime are normalized into ISO8601 format to
        allow easy sorting and searching.  4 digit years must be used
        to detect dates.  US (swapped day and month) versus rest of
        the world is also detected providing there is at least one
        value that resolves the ambiguity.

        Care is taken to ensure that columns looking like numbers are
        only treated as numbers if they do not have unnecessary
        leading zeroes or plus signs.  This is to avoid treating phone
        numbers and similar number like strings as integers.

        This command can take quite some time on large files as they
        are effectively imported twice.  The first time is to
        determine the format and the types for each column while the
        second pass actually imports the data.
        """
        if len(cmd) < 1 or len(cmd) > 2:
            raise self.Error("Expected one or two parameters")
        if not os.path.exists(cmd[0]):
            raise self.Error("File \"%s\" does not exist" % (cmd[0], ))
        if len(cmd) == 2:
            tablename = cmd[1]
        else:
            tablename = None
        try:
            final = None
            c = self.db.cursor()
            c.execute("BEGIN IMMEDIATE")
            final = "ROLLBACK"

            if not tablename:
                tablename = os.path.splitext(os.path.basename(cmd[0]))[0]

            if c.execute("pragma table_info(%s)" % (self._fmt_sql_identifier(tablename), )).fetchall():
                raise self.Error("Table \"%s\" already exists" % (tablename, ))

            # The types we support deducing
            def DateUS(v):  # US formatted date with wrong ordering of day and month
                return DateWorld(v, switchdm=True)

            def DateWorld(v, switchdm=False):  # Sensibly formatted date as used anywhere else in the world
                y, m, d = self._getdate(v)
                if switchdm: m, d = d, m
                if m < 1 or m > 12 or d < 1 or d > 31:
                    raise ValueError
                return "%d-%02d-%02d" % (y, m, d)

            def DateTimeUS(v):  # US date and time
                return DateTimeWorld(v, switchdm=True)

            def DateTimeWorld(v, switchdm=False):  # Sensible date and time
                y, m, d, h, M, s = self._getdatetime(v)
                if switchdm: m, d = d, m
                if m < 1 or m > 12 or d < 1 or d > 31 or h < 0 or h > 23 or M < 0 or M > 59 or s < 0 or s > 65:
                    raise ValueError
                return "%d-%02d-%02dT%02d:%02d:%02d" % (y, m, d, h, M, s)

            def Number(v):  # we really don't want phone numbers etc to match
                # Python's float & int constructors allow whitespace which we don't
                if re.search(r"\s", v):
                    raise ValueError
                if v == "0": return 0
                if v[0] == "+":  # idd prefix
                    raise ValueError
                if re.match("^[0-9]+$", v):
                    if v[0] == "0": raise ValueError  # also a phone number
                    return int(v)
                if v[0] == "0" and not v.startswith("0."):  # deceptive not a number
                    raise ValueError
                return float(v)

            # Work out the file format
            formats = [{"dialect": "excel"}, {"dialect": "excel-tab"}]
            seps = ["|", ";", ":"]
            if self.separator not in seps:
                seps.append(self.separator)
            for sep in seps:
                formats.append({"quoting": csv.QUOTE_NONE, "delimiter": sep, "doublequote": False, "quotechar": "\x00"})
            possibles = []
            errors = []
            encodingissue = False
            # format is copy() on every use.  This appears bizarre and
            # unnecessary.  However Python 2.3 and 2.4 somehow manage
            # to empty it if not copied.
            for format in formats:
                ncols = -1
                lines = 0
                try:
                    for line in self._csvin_wrapper(cmd[0], format.copy()):
                        if lines == 0:
                            lines = 1
                            ncols = len(line)
                            # data type guess setup
                            datas = []
                            for i in range(ncols):
                                datas.append([DateUS, DateWorld, DateTimeUS, DateTimeWorld, Number])
                            allblanks = [True] * ncols
                            continue
                        if len(line) != ncols:
                            raise ValueError("Expected %d columns - got %d" % (ncols, len(line)))
                        lines += 1
                        for i in range(ncols):
                            if not line[i]:
                                continue
                            allblanks[i] = False
                            if not datas[i]:
                                continue
                            # remove datas that give ValueError
                            d = []
                            for dd in datas[i]:
                                try:
                                    dd(line[i])
                                    d.append(dd)
                                except ValueError:
                                    pass
                            datas[i] = d
                    if ncols > 1 and lines > 1:
                        # if a particular column was allblank then clear datas for it
                        for i in range(ncols):
                            if allblanks[i]:
                                datas[i] = []
                        possibles.append((format.copy(), ncols, lines, datas))
                except UnicodeDecodeError:
                    encodingissue = True
                except:
                    s = str(sys.exc_info()[1])
                    if s not in errors:
                        errors.append(s)

            if len(possibles) == 0:
                if encodingissue:
                    raise self.Error(
                        "The file is probably not in the current encoding \"%s\" and didn't match a known file format" %
                        (self.encoding[0], ))
                v = "File doesn't appear to match a known type."
                if len(errors):
                    v += "  Errors reported:\n" + "\n".join(["  " + e for e in errors])
                raise self.Error(v)
            if len(possibles) > 1:
                raise self.Error("File matches more than one type!")
            format, ncols, lines, datas = possibles[0]
            fmt = format.get("dialect", None)
            if fmt is None:
                fmt = "(delimited by \"%s\")" % (format["delimiter"], )
            self.write(self.stdout, "Detected Format %s  Columns %d  Rows %d\n" % (fmt, ncols, lines))
            # Header row
            reader = self._csvin_wrapper(cmd[0], format)
            for header in reader:
                break
            # Check schema
            identity = lambda x: x
            for i in range(ncols):
                if len(datas[i]) > 1:
                    raise self.Error("Column #%d \"%s\" has ambiguous data format - %s" %
                                     (i + 1, header[i], ", ".join([d.__name__ for d in datas[i]])))
                if datas[i]:
                    datas[i] = datas[i][0]
                else:
                    datas[i] = identity
            # Make the table
            sql = "CREATE TABLE %s(%s)" % (self._fmt_sql_identifier(tablename), ", ".join(
                [self._fmt_sql_identifier(h) for h in header]))
            c.execute(sql)
            # prep work for each row
            sql = "INSERT INTO %s VALUES(%s)" % (self._fmt_sql_identifier(tablename), ",".join(["?"] * ncols))
            for line in reader:
                vals = []
                for i in range(ncols):
                    l = line[i]
                    if not l:
                        vals.append(None)
                    else:
                        vals.append(datas[i](l))
                c.execute(sql, vals)

            c.execute("COMMIT")
            self.write(self.stdout, "Auto-import into table \"%s\" complete\n" % (tablename, ))
        except:
            if final:
                self.db.cursor().execute(final)
            raise
def command_autoimport ( self , cmd ) : if len ( cmd ) < 1 or len ( cmd ) > 2 : raise self . Error ( "Expected one or two parameters" ) if not os . path . exists ( cmd [ 0 ] ) : raise self . Error ( "File \"%s\" does not exist" % ( cmd [ 0 ] , ) ) if len ( cmd ) == 2 : tablename = cmd [ 1 ] else : tablename = None try : final = None c = self . db . cursor ( ) c . execute ( "BEGIN IMMEDIATE" ) final = "ROLLBACK" if not tablename : tablename = os . path . splitext ( os . path . basename ( cmd [ 0 ] ) ) [ 0 ] if c . execute ( "pragma table_info(%s)" % ( self . _fmt_sql_identifier ( tablename ) , ) ) . fetchall ( ) : raise self . Error ( "Table \"%s\" already exists" % ( tablename , ) ) # The types we support deducing def DateUS ( v ) : # US formatted date with wrong ordering of day and month return DateWorld ( v , switchdm = True ) def DateWorld ( v , switchdm = False ) : # Sensibly formatted date as used anywhere else in the world y , m , d = self . _getdate ( v ) if switchdm : m , d = d , m if m < 1 or m > 12 or d < 1 or d > 31 : raise ValueError return "%d-%02d-%02d" % ( y , m , d ) def DateTimeUS ( v ) : # US date and time return DateTimeWorld ( v , switchdm = True ) def DateTimeWorld ( v , switchdm = False ) : # Sensible date and time y , m , d , h , M , s = self . _getdatetime ( v ) if switchdm : m , d = d , m if m < 1 or m > 12 or d < 1 or d > 31 or h < 0 or h > 23 or M < 0 or M > 59 or s < 0 or s > 65 : raise ValueError return "%d-%02d-%02dT%02d:%02d:%02d" % ( y , m , d , h , M , s ) def Number ( v ) : # we really don't want phone numbers etc to match # Python's float & int constructors allow whitespace which we don't if re . search ( r"\s" , v ) : raise ValueError if v == "0" : return 0 if v [ 0 ] == "+" : # idd prefix raise ValueError if re . match ( "^[0-9]+$" , v ) : if v [ 0 ] == "0" : raise ValueError # also a phone number return int ( v ) if v [ 0 ] == "0" and not v . startswith ( "0." ) : # deceptive not a number raise ValueError return float ( v ) # Work out the file format formats = [ { "dialect" : "excel" } , { "dialect" : "excel-tab" } ] seps = [ "|" , ";" , ":" ] if self . separator not in seps : seps . append ( self . separator ) for sep in seps : formats . append ( { "quoting" : csv . QUOTE_NONE , "delimiter" : sep , "doublequote" : False , "quotechar" : "\x00" } ) possibles = [ ] errors = [ ] encodingissue = False # format is copy() on every use.  This appears bizarre and # unnecessary.  However Python 2.3 and 2.4 somehow manage # to empty it if not copied. for format in formats : ncols = - 1 lines = 0 try : for line in self . _csvin_wrapper ( cmd [ 0 ] , format . copy ( ) ) : if lines == 0 : lines = 1 ncols = len ( line ) # data type guess setup datas = [ ] for i in range ( ncols ) : datas . append ( [ DateUS , DateWorld , DateTimeUS , DateTimeWorld , Number ] ) allblanks = [ True ] * ncols continue if len ( line ) != ncols : raise ValueError ( "Expected %d columns - got %d" % ( ncols , len ( line ) ) ) lines += 1 for i in range ( ncols ) : if not line [ i ] : continue allblanks [ i ] = False if not datas [ i ] : continue # remove datas that give ValueError d = [ ] for dd in datas [ i ] : try : dd ( line [ i ] ) d . append ( dd ) except ValueError : pass datas [ i ] = d if ncols > 1 and lines > 1 : # if a particular column was allblank then clear datas for it for i in range ( ncols ) : if allblanks [ i ] : datas [ i ] = [ ] possibles . append ( ( format . copy ( ) , ncols , lines , datas ) ) except UnicodeDecodeError : encodingissue = True except : s = str ( sys . exc_info ( ) [ 1 ] ) if s not in errors : errors . append ( s ) if len ( possibles ) == 0 : if encodingissue : raise self . Error ( "The file is probably not in the current encoding \"%s\" and didn't match a known file format" % ( self . encoding [ 0 ] , ) ) v = "File doesn't appear to match a known type." if len ( errors ) : v += "  Errors reported:\n" + "\n" . join ( [ "  " + e for e in errors ] ) raise self . Error ( v ) if len ( possibles ) > 1 : raise self . Error ( "File matches more than one type!" ) format , ncols , lines , datas = possibles [ 0 ] fmt = format . get ( "dialect" , None ) if fmt is None : fmt = "(delimited by \"%s\")" % ( format [ "delimiter" ] , ) self . write ( self . stdout , "Detected Format %s  Columns %d  Rows %d\n" % ( fmt , ncols , lines ) ) # Header row reader = self . _csvin_wrapper ( cmd [ 0 ] , format ) for header in reader : break # Check schema identity = lambda x : x for i in range ( ncols ) : if len ( datas [ i ] ) > 1 : raise self . Error ( "Column #%d \"%s\" has ambiguous data format - %s" % ( i + 1 , header [ i ] , ", " . join ( [ d . __name__ for d in datas [ i ] ] ) ) ) if datas [ i ] : datas [ i ] = datas [ i ] [ 0 ] else : datas [ i ] = identity # Make the table sql = "CREATE TABLE %s(%s)" % ( self . _fmt_sql_identifier ( tablename ) , ", " . join ( [ self . _fmt_sql_identifier ( h ) for h in header ] ) ) c . execute ( sql ) # prep work for each row sql = "INSERT INTO %s VALUES(%s)" % ( self . _fmt_sql_identifier ( tablename ) , "," . join ( [ "?" ] * ncols ) ) for line in reader : vals = [ ] for i in range ( ncols ) : l = line [ i ] if not l : vals . append ( None ) else : vals . append ( datas [ i ] ( l ) ) c . execute ( sql , vals ) c . execute ( "COMMIT" ) self . write ( self . stdout , "Auto-import into table \"%s\" complete\n" % ( tablename , ) ) except : if final : self . db . cursor ( ) . execute ( final ) raise