def read(self, Host=None, User=None, Db=None, annotation=None, columns=('name','chrom','strand','txStart','txEnd','cdsStart','cdsEnd','exonCount','exonStarts', 'exonEnds'), which='', where=()): 
        """read a gene annotation table from a database (UCSC or local sqlite3)
        
        Parameters:
        When ucsc genome database is used:
        1. Host: "genome-mysql.cse.ucsc.edu"
        2. User: "genome"
        3. Db: organism name (e.g., "ce4" (C elegans))
        4. annotation: the name of the table to read ("refGene" (refSeq genes))
        
        When a local sqlite3 db file is used:
        1. Db: the local sqlite3 db file name
        2. annotation: the name of the table to read (e.g., "refGene") 
        
        Other parameters:
        1. columns: the table column names to read
        2. which: the column name that is used to read certain specific genes from the table
        3. where: a list of values for the column specified by 'which'.
        """     
        
        # connect and read the database table
        if Host and User:
            try:
                dbconnect=MySQLdb.connect(host=Host,user=User)
                cursor=dbconnect.cursor()
                cursor.execute('use '+Db)
            except NameError:
                dbconnect=sqlite3.connect(Db)
                cursor.execute()
        else:
            dbconnect=sqlite3.connect(Db)
            cursor=dbconnect.cursor()
        
        if which and (which not in columns): columns=columns+(which,)
        
        # store the columns to read as an attribute
        self.columns=columns
        
        # get the schema and their indicies
        schema={}
        for i,x in enumerate(columns): schema[x]=i
        if which and where:
            num_where=len(where)
            if num_where>500:
                table=[]
                sqlcmd="SELECT "+ ", ".join(columns)+" FROM "+annotation+" WHERE "
                for j in range(0,num_where/500+1):
                    sqlcmd="SELECT "+ ", ".join(columns)+" FROM "+annotation+" WHERE "
                    it=iter(where[j*500:(j+1)*500])
                    for i in it:
                        if type(i).__name__=='str': 
                            sqlcmd+= which + "='" + i+"'" + " or "
                        elif type(i).__name__=='list' or type(i).__name__=='tuple' or type(i).__name__=='array':
                            sqlcmd+=which + "=" + str(i)[1:-1]+ " or " 
                        else: sqlcmd+=which + "=" + str(i)+ " or " 
                    sqlcmd=sqlcmd[:-4]
                    cursor.execute(sqlcmd)
                    table+=cursor.fetchall()
            else:
                sqlcmd="SELECT "+ ", ".join(columns)+" FROM "+annotation+" WHERE "
                it=iter(where)
                for i in it:
                    if type(i).__name__=='str': 
                        sqlcmd+= which + "='" + i+"'" + " or "
                    elif type(i).__name__=='list' or type(i).__name__=='tuple' or type(i).__name__=='array':
                        sqlcmd+=which + "=" + str(i)[1:-1]+ " or " 
                    else: sqlcmd+=which + "=" + str(i)+ " or " 
                sqlcmd=sqlcmd[:-4]
                cursor.execute(sqlcmd)
                table=cursor.fetchall()
        else:  
            sqlcmd="SELECT "+ ", ".join(columns)+" FROM "+annotation
            cursor.execute(sqlcmd)
            table=cursor.fetchall()
    
        # parse the database table.
        # lambda expression for parsing exonStarts and exonEnds
        parser_commasep_array=lambda strarray: array('l',[int(k) for k in strarray.tostring().rstrip(',').split(',')])
        parser_commasep_str=lambda strlist: array('l',[int(k) for k in strlist])
        
        # get where 'chrom' is. 'chrom' must be included
        try:
            i=schema['chrom']
        except KeyError:
            raise Exception("'chrom' must be included in 'columns'")
        
        # extract the 'chrom' column and get the unique chromosome names
        chroms=set(map(operator.itemgetter(i),table))
        chroms=list(chroms)
        chroms.sort()
        
        # set the table up with the chromosome names
        self.table={}
        for chrom in chroms: self.table[str(chrom)]={}
        
        # run through the table
        for tb in table:
            for column in columns:
                if column=='chrom': continue
                value=tb[schema[column]]
                ctype=type(tb[schema[column]]).__name__
                
                # if blob (array in the case of MySQLdb, a comma separate string in the case of sqlite3), parse them
                if ctype=='array':
                    value=parser_commasep_array(value)
                elif ctype=='str' or ctype=='unicode': 
                    try:                    # try block for checking if the string (value) is empty
                        if value[-1]==',':    # this is comma-separated blob
                            try:
                                temp=value.rstrip(',').split(',')
                                value=parser_commasep_str(temp)
                            except ValueError:
                                pass
                        else: value=str(value)
                    except IndexError:     
                        pass
                # when no array or list was formed, make. Otherwise, just append the new data to the existing array or list
                try:
                    self.table[tb[schema['chrom']]][column].append(value)
                except KeyError:
                    if ctype=='int' or ctype=='long': self.table[tb[schema['chrom']]][column]=array('l',[value])
                    elif ctype=='float': self.table[tb[schema['chrom']]][column]=array('d',[value])
                    else: self.table[tb[schema['chrom']]][column]=[value]
                
        #close cursor and connection
        cursor.close()
        dbconnect.close()
dataset/ETHPy150Open taoliu/taolib/Assoc/inout.py/GeneTable.read/original