示例#1
0
 def test_active_connections(self):
     """connecting to a database on a specified server should be done once
     only, but same database on a different server should be done"""
     ensembl_acct = get_ensembl_account(release='46')
     engine1 = DbConnection(account=ensembl_acct,
                            db_name="homo_sapiens_core_46_36h")
     engine2 = DbConnection(account=ensembl_acct,
                            db_name="homo_sapiens_core_46_36h")
     self.assertEqual(engine1, engine2)
示例#2
0
 def __init__(self, account, species=None, db_type=None, release=None,
         pool_recycle=None, division=None):
     self._tables = {}
     self.db_name = get_db_name(account=account, species=species,
                       release=release, db_type=db_type, division=division)
     if not self.db_name:
         raise RuntimeError("%s db doesn't exist for '%s' on '%s'" % \
                                     (db_type, species, account.host))
     else:
         self.db_name = self.db_name[0]
     self._db = DbConnection(account=account, db_name=self.db_name,
                             pool_recycle=pool_recycle)
     self._meta = sql.MetaData(self._db)
     self.Type = db_type
示例#3
0
 def __init__(self, account, species=None, db_type=None, release=None,
         pool_recycle=None, division=None):
     self._tables = {}
     self.db_name = get_db_name(account=account, species=species,
                       release=release, db_type=db_type, division=division)
     if not self.db_name:
         raise RuntimeError, "%s db doesn't exist for '%s' on '%s'" % \
                                     (db_type, species, account.host)
     else:
         self.db_name = self.db_name[0]
     self._db = DbConnection(account=account, db_name=self.db_name,
                             pool_recycle=pool_recycle)
     self._meta = sql.MetaData(self._db)
     self.Type = db_type
示例#4
0
 def test_pool_recycle_option(self):
     """excercising ability to specify a pool recycle option"""
     ensembl_acct = get_ensembl_account(release='56')
     engine1 = DbConnection(account=ensembl_acct,
                            db_name="homo_sapiens_core_46_36h",
                            pool_recycle=1000)
示例#5
0
class Database(object):
    """holds the data-base connection and table attributes"""
    def __init__(self, account, species=None, db_type=None, release=None,
            pool_recycle=None, division=None):
        self._tables = {}
        self.db_name = get_db_name(account=account, species=species,
                          release=release, db_type=db_type, division=division)
        if not self.db_name:
            raise RuntimeError, "%s db doesn't exist for '%s' on '%s'" % \
                                        (db_type, species, account.host)
        else:
            self.db_name = self.db_name[0]
        self._db = DbConnection(account=account, db_name=self.db_name,
                                pool_recycle=pool_recycle)
        self._meta = sql.MetaData(self._db)
        self.Type = db_type
        
    def __str__(self):
        return str(self.db_name)
    
    def __cmp__(self, other):
        return cmp(self._db, other._db)
    
    def getTable(self, name):
        """returns the SQLalchemy table instance"""
        table = self._tables.get(name, None)
        if table is None:
            c = self._db.execute("DESCRIBE %s" % name)
            custom_columns = []
            for r in c.fetchall():
                Field = r["Field"]
                Type = r["Type"]
                if "tinyint" in Type:
                    custom_columns.append(sql.Column(Field, sql.Integer))
            try:
                table = sql.Table(name, self._meta, autoload=True,
                                    extend_existing=True, *custom_columns)
            except TypeError:
                # new arg name not supported, try old
                table = sql.Table(name, self._meta, autoload=True,
                                    useexisting=True, *custom_columns)
            
            self._tables[name] = table
        return table
    
    def getDistinct(self, table_name, column):
        """returns the Ensembl data-bases distinct values for the named
        property_type.
        
        Arguments:
            - table_name: the data base table name
            - column: valid values are biotype, status"""
        table = self.getTable(table_name)
        query = sql.select([table.c[column]], distinct=True)
        records = set()
        string_types = str, unicode
        for record in query.execute():
            if type(record) not in string_types and \
                type(record[0]) not in string_types:
                # multi-dimensioned list/tuple
                record = flatten(record)
            elif type(record) not in string_types:
                # list/tuple of strings
                record = tuple(record)
            else:
                # a string
                record = [record]
            
            records.update(record)
        return records
    
    def tableHasColumn(self, table_name, column):
        """returns True if table has column"""
        table = self.getTable(table_name)
        return hasattr(table.c, column)
    
    def getTablesRowCount(self, table_name=None):
        """returns a cogent Table object with the row count for each table
        in the database
        
        Arguments:
            - table_name: database table name. If none, all database tables
              assessed."""
        if type(table_name) == str:
            table_name = (table_name,)
        elif table_name is None:
            self._meta.reflect()
            table_name = self._meta.tables.keys()
        rows = []
        for name in table_name:
            table = self.getTable(name)
            count = table.count().execute().fetchone()[0]
            rows.append(['%s.%s' % (self.db_name, name), count])
        
        return cogent_table.Table(header=['name', 'count'], rows=rows)
示例#6
0
class Database(object):
    """holds the data-base connection and table attributes"""
    def __init__(self,
                 account,
                 species=None,
                 db_type=None,
                 release=None,
                 pool_recycle=None,
                 division=None):
        self._tables = {}
        self.db_name = get_db_name(account=account,
                                   species=species,
                                   release=release,
                                   db_type=db_type,
                                   division=division)
        if not self.db_name:
            raise RuntimeError, "%s db doesn't exist for '%s' on '%s'" % \
                                        (db_type, species, account.host)
        else:
            self.db_name = self.db_name[0]
        self._db = DbConnection(account=account,
                                db_name=self.db_name,
                                pool_recycle=pool_recycle)
        self._meta = sql.MetaData(self._db)
        self.Type = db_type

    def __str__(self):
        return str(self.db_name)

    def __cmp__(self, other):
        return cmp(self._db, other._db)

    def getTable(self, name):
        """returns the SQLalchemy table instance"""
        table = self._tables.get(name, None)
        if table is None:
            c = self._db.execute("DESCRIBE %s" % name)
            custom_columns = []
            for r in c.fetchall():
                Field = r["Field"]
                Type = r["Type"]
                if "tinyint" in Type:
                    custom_columns.append(sql.Column(Field, sql.Integer))
            try:
                table = sql.Table(name,
                                  self._meta,
                                  autoload=True,
                                  extend_existing=True,
                                  *custom_columns)
            except TypeError:
                # new arg name not supported, try old
                table = sql.Table(name,
                                  self._meta,
                                  autoload=True,
                                  useexisting=True,
                                  *custom_columns)

            self._tables[name] = table
        return table

    def getDistinct(self, table_name, column):
        """returns the Ensembl data-bases distinct values for the named
        property_type.
        
        Arguments:
            - table_name: the data base table name
            - column: valid values are biotype, status"""
        table = self.getTable(table_name)
        query = sql.select([table.c[column]], distinct=True)
        records = set()
        string_types = str, unicode
        for record in query.execute():
            if type(record) not in string_types and \
                type(record[0]) not in string_types:
                # multi-dimensioned list/tuple
                record = flatten(record)
            elif type(record) not in string_types:
                # list/tuple of strings
                record = tuple(record)
            else:
                # a string
                record = [record]

            records.update(record)
        return records

    def tableHasColumn(self, table_name, column):
        """returns True if table has column"""
        table = self.getTable(table_name)
        return hasattr(table.c, column)

    def getTablesRowCount(self, table_name=None):
        """returns a cogent Table object with the row count for each table
        in the database
        
        Arguments:
            - table_name: database table name. If none, all database tables
              assessed."""
        if type(table_name) == str:
            table_name = (table_name, )
        elif table_name is None:
            self._meta.reflect()
            table_name = self._meta.tables.keys()
        rows = []
        for name in table_name:
            table = self.getTable(name)
            count = table.count().execute().fetchone()[0]
            rows.append(['%s.%s' % (self.db_name, name), count])

        return cogent_table.Table(header=['name', 'count'], rows=rows)