Make a sqlite database for fast retrieval of features.
433988
def make_index(gff_file):
    
    import gffutils
    db_file = gff_file + ".db"

    if need_update(gff_file, db_file):
        if op.exists(db_file):
            os.remove(db_file)
        logging.debug("Indexing `{0}`".format(gff_file))
        gffutils.create_db(gff_file, db_file, merge_strategy="create_unique")
    else:
        logging.debug("Load index `{0}`".format(gff_file))

    return gffutils.FeatureDB(db_file)
489557
async def fetchone(self) -> Optional[sqlite3.Row]:
        """"""
        return await self._execute(self._cursor.fetchone)
954516
def _get_sql(filename):
    """"""
    with open(os.path.join(SQL_DIR, filename), 'r') as f:
        return f.read()
385648
def _load_features(root):
    """"""
    out = collections.OrderedDict()
    for elem in root.findall('feature'):
        name = elem.attrib['name']
        api = elem.attrib['api']
        number = tuple([int(x) for x in elem.attrib['number'].split('.')])
        requires = [_load_require(x) for x in elem.findall('require')]
        removes = [_load_remove(x) for x in elem.findall('remove')]
        comment = elem.get('comment')
        out[name] = Feature(name, api, number, requires, removes, comment)
    return out
1223526
def from_database(cls, database):
        """"""
        if isinstance(database, PostgresqlDatabase):
            return PostgresqlMigrator(database)
        if isinstance(database, SqliteDatabase):
            return SqliteMigrator(database)
        if isinstance(database, MySQLDatabase):
            return MySQLMigrator(database)
        return super(SchemaMigrator, cls).from_database(database)
156536
def prepare(self):
        """"""
        if not os.path.exists(self.path):
            if self.retrieve:
                print("Downloading SQLite3 database...")
                download_from_url(self.retrieve, self.path, progress=True)
            else: raise Exception("The file '" + self.path + "' does not exist.")
        self.check_format()
        if self.known_md5: assert self.known_md5 == self.md5
        self.prepared = True
580679
def tables(db=None):
    '''
    
    '''
    cur = _connect(db)

    if not cur:
        return False

    cur.execute(
        "SELECT name FROM sqlite_master WHERE type='table' ORDER BY name;"
    )
    rows = cur.fetchall()
    return rows
215041
def get_db(cls):
        """"""
        if cls._db:
            return getattr(cls._client, cls._db)
        return cls._client.get_default_database()
388450
def _get_indexes(self, schema, **kwargs):
        """"""
        # http://www.sqlite.org/pragma.html#schema
        # http://www.mail-archive.com/sqlite-users@sqlite.org/msg22055.html
        # http://stackoverflow.com/questions/604939/
        ret = {}
        rs = self._query('PRAGMA index_list({})'.format(self._normalize_table_name(schema)), **kwargs)
        if rs:
            for r in rs:
                iname = r['name']
                ret.setdefault(iname, [])
                indexes = self._query('PRAGMA index_info({})'.format(r['name']), **kwargs)
                for idict in indexes:
                    ret[iname].append(idict['name'])

        return ret
240207
def _loadDB(self):
        """"""
        if not self._validID():
            raise NotFound, self._getID()
        (sql, fields) = self._prepareSQL("SELECT")
        curs = self.cursor()
        curs.execute(sql, self._getID())
        result = curs.fetchone()
        if not result:
            curs.close()
            raise NotFound, self._getID()
        self._loadFromRow(result, fields, curs)
        curs.close()
        self._updated = time.time()
398029
def sql(self, sql, params=()):
        ''' 
        '''
        def _items(items):
            for item in items:
                yield self._item_class(item)

        sql = sql.strip()
        try:
            self._cursor.execute(sql, params)
        except sqlite3.OperationalError, err:
            raise SQLError('%s, SQL: %s, params: %s' % (err, sql, params) )
        except sqlite3.IntegrityError:
            raise DuplicateItem('Duplicate item, %s' % item)

        if sql.upper().startswith('SELECT'):
            return _items(self._cursor.fetchall())
        else:
            return None
54804
def execute_sql(self, sql):
        """"""
        cursor = self.get_cursor()
        cursor.execute(sql)
        return cursor
351338
def create_database(self, instance, name, character_set=None,
            collate=None):
        """"""
        return instance.create_database(name, character_set=character_set,
                collate=collate)
82981
def makeblastdb(self):
        """"""
        while True:  # while daemon
            fastapath = self.dqueue.get()  # grabs fastapath from dqueue
            # remove the path and the file extension for easier future globbing
            db = fastapath.split('.')[0]
            nhr = '{}.nhr'.format(db)  # add nhr for searching
            if not os.path.isfile(str(nhr)):  # if check for already existing dbs
                # Create the databases
                # TODO use MakeBLASTdb class
                subprocess.call(shlex.split('makeblastdb -in {} -parse_seqids -max_file_sz 2GB -dbtype nucl -out {}'
                                            .format(fastapath, db)), stdout=self.fnull, stderr=self.fnull)
            dotter()
            self.dqueue.task_done()
612871
def query(self, sql: str, args: tuple = None):
        """"""

        with self._cursor() as cursor:
            log.debug('Running SQL: ' + str((sql, args)))
            cursor.execute(sql, args)
            return cursor.fetchall()
200227
def load_keys(database, parameters):
    """"""
    keys = []
    with database.snapshot() as snapshot:
        results = snapshot.execute_sql(
            'SELECT u.id FROM %s u' % parameters['table'])

    for row in results:
        keys.append(row[0])

    return keys
1179145
def convert_sqlite_to_mysql(
            self):
        
        from fundamentals.renderer import list_of_dictionaries
        from fundamentals.mysql import directory_script_runner
        self.log.debug('starting the ``convert_sqlite_to_mysql`` method')

        con = lite.connect(self.pathToSqlite)
        con.row_factory = lite.Row
        cur = con.cursor()

        # GET ALL TABLE NAMES
        cur.execute("SELECT name FROM sqlite_master WHERE type='table';")
        tables = cur.fetchall()

        createStatements = []
        inserts = []
        for table in tables:
            table = table['name']
            if table == "sqlite_sequence":
                continue

            # CREATE TABLE collection_books (folder_id, fingerprint, primary key(folder_id, fingerprint));
            # GENEREATE THE MYSQL CREATE STATEMENTS FOR EACH TABLE
            cur.execute(
                "SELECT sql FROM sqlite_master WHERE name = '%(table)s';" % locals())
            createStatement = cur.fetchone()
            createStatement = createStatement[0].replace('"', '`') + ";"
            if "DEFAULT" not in createStatement:
                if "primary key(" in createStatement:
                    tmp = createStatement.split("primary key(")
                    tmp[0] = tmp[0].replace(
                        ",", " varchar(150) DEFAULT NULL,")
                    createStatement = ("primary key(").join(tmp)
                if "primary key," in createStatement:
                    tmp = createStatement.split("primary key,")
                    tmp[1] = tmp[1].replace(
                        ",", " varchar(150) DEFAULT NULL,")
                    tmp[1] = tmp[1].replace(
                        ");", " varchar(150) DEFAULT NULL);")
                    createStatement = ("primary key,").join(tmp)
            createStatement = createStatement.replace(
                "INTEGER PRIMARY KEY", "INTEGER AUTO_INCREMENT PRIMARY KEY")
            createStatement = createStatement.replace(
                "AUTOINCREMENT", "AUTO_INCREMENT")
            createStatement = createStatement.replace(
                "DEFAULT 't'", "DEFAULT '1'")
            createStatement = createStatement.replace(
                "DEFAULT 'f'", "DEFAULT '0'")
            createStatement = createStatement.replace(",'t'", ",'1'")
            createStatement = createStatement.replace(",'f'", ",'0'")
            if "CREATE TABLE `" in createStatement:
                createStatement = createStatement.replace(
                    "CREATE TABLE `", "CREATE TABLE IF NOT EXISTS `" + self.tablePrefix)
            else:
                createStatement = createStatement.replace(
                    "CREATE TABLE ", "CREATE TABLE IF NOT EXISTS " + self.tablePrefix)
            if ", primary key(" in createStatement:
                createStatement = createStatement.replace(", primary key(", """,
`dateCreated` datetime DEFAULT CURRENT_TIMESTAMP,
`dateLastModified` datetime DEFAULT CURRENT_TIMESTAMP,
`updated` tinyint(4) DEFAULT '0',
primary key(""")
            else:
                createStatement = createStatement.replace(");", """,
    `dateCreated` datetime DEFAULT CURRENT_TIMESTAMP,
    `dateLastModified` datetime DEFAULT CURRENT_TIMESTAMP,
    `updated` tinyint(4) DEFAULT '0');
                """)
            createStatement = createStatement.replace(
                " text primary key", " varchar(100) primary key")
            createStatement = createStatement.replace(
                "`EntryText` TEXT NOT NULL,", "`EntryText` TEXT,")
            createStatement = createStatement.replace(
                "`SelectionText` TEXT NOT NULL", "`SelectionText` TEXT")
            createStatement = createStatement.replace(
                "`Filename` INTEGER NOT NULL,", "`Filename` TEXT NOT NULL,")
            createStatement = createStatement.replace(
                "`SessionPartUUID` TEXT NOT NULL UNIQUE,", "`SessionPartUUID` VARCHAR(100) NOT NULL UNIQUE,")
            createStatement = createStatement.replace(
                "`Name` TEXT PRIMARY KEY NOT NULL", "`Name` VARCHAR(100) PRIMARY KEY NOT NULL")
            createStatement = createStatement.replace(
                " VARCHAR ", " VARCHAR(100) ")
            createStatement = createStatement.replace(
                " VARCHAR,", " VARCHAR(100),")

            # GRAB THE DATA TO ADD TO THE MYSQL DATABASE TABLES
            cur.execute(
                "SELECT * from '%(table)s';" % locals())
            rows = cur.fetchall()

            allRows = []
            for row in rows:
                allRows.append(dict(row))

            # RECURSIVELY CREATE MISSING DIRECTORIES
            if not os.path.exists("/tmp/headjack/"):
                os.makedirs("/tmp/headjack/")

            writequery(
                log=self.log,
                sqlQuery=createStatement,
                dbConn=self.dbConn,
            )

            from fundamentals.mysql import insert_list_of_dictionaries_into_database_tables
            # USE dbSettings TO ACTIVATE MULTIPROCESSING
            insert_list_of_dictionaries_into_database_tables(
                dbConn=self.dbConn,
                log=self.log,
                dictList=allRows,
                dbTableName=self.tablePrefix + table,
                uniqueKeyList=[],
                dateModified=True,
                dateCreated=True,
                batchSize=10000,
                replace=True,
                dbSettings=self.settings["database settings"]
            )

            # # DUMP THE DATA INTO A MYSQL DATABASE
            # dataSet = list_of_dictionaries(
            #     log=self.log,
            #     listOfDictionaries=allRows
            # )
            # originalList = dataSet.list
            # now = datetime.now()
            # now = now.strftime("%Y%m%dt%H%M%S%f.sql")
            # mysqlData = dataSet.mysql(
            # tableName=self.tablePrefix + table, filepath="/tmp/headjack/" +
            # now, createStatement=createStatement)

            # directory_script_runner(
            #     log=self.log,
            #     pathToScriptDirectory="/tmp/headjack/",
            #     databaseName=self.settings["database settings"]["db"],
            #     loginPath=self.settings["database settings"]["loginPath"],
            #     successRule="delete",
            #     failureRule="failed"
            # )

        con.close()

        self.log.debug('completed the ``convert_sqlite_to_mysql`` method')
        return None