s_mart: smart_toolShed/SMART/Java/Python/mySql/MySqlTable.py annotate

annotate smart_toolShed/SMART/Java/Python/mySql/MySqlTable.py @ 1:30c5431d8ce4

Uploaded

author	yufei-luo
date	Thu, 17 Jan 2013 11:01:51 -0500
parents	e0f8dcca02ed
children

rev	line source
0 e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	1 #
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	2 # Copyright INRA-URGI 2009-2010
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	3 #
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	4 # This software is governed by the CeCILL license under French law and
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	5 # abiding by the rules of distribution of free software. You can use,
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	6 # modify and/ or redistribute the software under the terms of the CeCILL
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	7 # license as circulated by CEA, CNRS and INRIA at the following URL
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	8 # "http://www.cecill.info".
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	9 #
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	10 # As a counterpart to the access to the source code and rights to copy,
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	11 # modify and redistribute granted by the license, users are provided only
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	12 # with a limited warranty and the software's author, the holder of the
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	13 # economic rights, and the successive licensors have only limited
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	14 # liability.
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	15 #
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	16 # In this respect, the user's attention is drawn to the risks associated
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	17 # with loading, using, modifying and/or developing or reproducing the
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	18 # software by the user in light of its specific status of free software,
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	19 # that may mean that it is complicated to manipulate, and that also
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	20 # therefore means that it is reserved for developers and experienced
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	21 # professionals having in-depth computer knowledge. Users are therefore
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	22 # encouraged to load and test the software's suitability as regards their
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	23 # requirements in conditions enabling the security of their systems and/or
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	24 # data to be ensured and, more generally, to use and operate it in the
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	25 # same conditions as regards security.
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	26 #
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	27 # The fact that you are presently reading this means that you have had
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	28 # knowledge of the CeCILL license and that you accept its terms.
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	29 #
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	30 import re
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	31 import sys
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	32
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	33 class MySqlTable(object):
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	34 """
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	35 Store a table of a mySQL database, used for transcripts or exons
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	36 Record a a name and a type (int, float, double) for each column
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	37 @ivar name: name of the table
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	38 @type name: string
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	39 @ivar variables: name of the columns
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	40 @type variables: list of string
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	41 @ivar types: type of the columns
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	42 @type types: dict of string
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	43 @ivar mySqlConnection: connection to a database
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	44 @type mySqlConnection: class L{MySqlConnection<MySqlConnection>}
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	45 @ivar nbLines: number of rows
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	46 @type nbLines: int
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	47 @ivar verbosity: verbosity
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	48 @type verbosity: int
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	49 """
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	50
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	51 def __init__(self, connection, name, verbosity = 0):
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	52 """
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	53 Constructor
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	54 Possibly retrieve column names and types if table exists
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	55 @param mySqlConnection: connection to a databas
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	56 @type mySqlConnection: class L{MySqlConnection<MySqlConnection>}
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	57 @param name: name of the table
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	58 @type name: string
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	59 @param verbosity: verbosity
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	60 @type verbosity: int
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	61 """
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	62 self.name = name
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	63 self.variables = []
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	64 self.types = {}
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	65 self.sizes = {}
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	66 self.nbLines = None
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	67 self.verbosity = verbosity
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	68 self.mySqlConnection = connection
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	69 queryTables = self.mySqlConnection.executeQuery("SELECT name FROM sqlite_master WHERE type LIKE 'table' AND name LIKE '%s'" % (self.name))
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	70 self.created = not queryTables.isEmpty()
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	71 if self.created:
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	72 queryFields = self.mySqlConnection.executeQuery("PRAGMA table_info('%s')" % (name))
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	73 for field in queryFields.getIterator():
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	74 if field[1] != "id":
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	75 self.variables.append(field[1])
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	76 self.types[field[1]] = field[2]
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	77 self.sizes[field[1]] = field[3]
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	78
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	79
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	80 def getName(self):
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	81 return self.name
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	82
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	83
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	84 def create(self, variables, types, sizes):
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	85 """
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	86 Create a table using give column names and types
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	87 @param variables: names of the columns
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	88 @type variables: list of string
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	89 @param types: types of the columns
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	90 @type types: dict of string
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	91 @param sizes: sizes of the types
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	92 @type sizes: dict of int
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	93 """
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	94 self.variables = variables
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	95 self.types = types
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	96 self.sizes = sizes
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	97 if self.created:
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	98 self.remove()
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	99 query = "CREATE TABLE '%s' (id INTEGER PRIMARY KEY" % (self.name)
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	100 for variable in variables:
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	101 query = "%s, %s %s(%d)" % (query, variable, types[variable], sizes[variable])
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	102 query += ")"
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	103 self.mySqlConnection.executeQuery(query)
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	104 self.created = True
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	105
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	106
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	107 def insertMany(self, lines):
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	108 """
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	109 Insert many lines
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	110 @param lines: the list of values
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	111 @type lines: list of lists
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	112 """
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	113 commands = []
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	114 for values in lines:
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	115 commands.append("INSERT INTO '%s' (%s) VALUES (%s)" % (self.name, ", ".join(self.variables), ", ".join([MySqlTable.formatSql(values[variable], self.types[variable], self.sizes[variable]) for variable in self.variables])))
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	116 self.mySqlConnection.executeManyQueries(commands)
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	117
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	118
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	119 def rename(self, name):
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	120 """
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	121 Rename the table
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	122 @param name: the new name
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	123 @type name: string
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	124 """
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	125 self.mySqlConnection.executeQuery("RENAME TABLE '%s' TO '%s'" % (self.name, name))
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	126 self.name = name
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	127
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	128
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	129 def copy(self, table):
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	130 """
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	131 Copy the given table this one
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	132 @param table: the table to be copied
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	133 @type table: class L{MySqlTable<MySqlTable>}
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	134 """
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	135 variables = []
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	136 types = {}
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	137 sizes = {}
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	138 fields = self.mySqlConnection.executeQuery("PRAGMA table_info(%s)" % (table.name))
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	139 for field in fields.getIterator():
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	140 if field[1] != "id":
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	141 variables.append(field[1])
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	142 m = re.search(r"(\w+)\((\d+)\)", field[2])
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	143 if m == None:
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	144 raise Exception("\nFormat %s in table %s is strange." % (field[2], table.name))
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	145 types[field[1]] = m.group(1)
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	146 sizes[field[1]] = int(m.group(2))
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	147 self.create(variables, types, sizes)
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	148 self.mySqlConnection.executeQuery("INSERT INTO '%s' SELECT * FROM %s" % (self.name, table.name))
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	149
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	150
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	151 def add(self, table):
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	152 """
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	153 Add the content of a table to this one
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	154 @param table: the table to be added
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	155 @type table: class L{MySqlTable<MySqlTable>}
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	156 """
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	157 self.mySqlConnection.executeQuery("INSERT INTO '%s' SELECT * FROM %s" % (self.name, table.name))
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	158 self.created = True
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	159
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	160
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	161 def exists(self):
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	162 """
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	163 Check if the table exists in mySQL
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	164 @return: true if it exits
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	165 """
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	166 return self.created
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	167
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	168
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	169 def remove(self):
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	170 """
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	171 Remove this table
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	172 """
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	173 if self.exists():
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	174 query = "DROP TABLE IF EXISTS '%s'" % (self.name)
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	175 self.mySqlConnection.executeQuery(query)
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	176 self.created = False
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	177
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	178
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	179 def clear(self):
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	180 """
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	181 Clear the content of this table
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	182 """
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	183 self.mySqlConnection.executeQuery("DELETE FROM '%s'" % (self.name))
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	184
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	185
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	186 def getNbElements(self):
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	187 """
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	188 Count the number of rows in the table
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	189 """
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	190 command = "SELECT COUNT(id) FROM '%s'" % (self.name)
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	191 query = self.mySqlConnection.executeQuery(command)
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	192 return int(query.getLine()[0])
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	193
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	194
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	195 def formatSql(self, value, type, size):
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	196 """
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	197 Format a value using MySQL encapsulation
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	198 """
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	199 if type.find("int") != -1:
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	200 return "%d" % value
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	201 if type.find("float") != -1:
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	202 return "%.10f" % value
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	203 if type.find("double") != -1:
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	204 return "%.20f" % value
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	205 if type.find("varchar") != -1:
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	206 if len(value) > size:
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	207 return "'%s'" % value[0:size]
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	208 return "'%s'" % value
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	209 raise Exception("Do not understand type %s" % (type))
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	210 formatSql = classmethod(formatSql)
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	211
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	212
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	213 def addLine(self, values):
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	214 """
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	215 Add a row to this table
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	216 @param values: the values of the row
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	217 @type values: dict
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	218 @return: the id of the added row
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	219 """
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	220 sqlValues = []
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	221 for variable in self.variables:
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	222 sqlValues.append(self.formatSql(values[variable], self.types[variable], self.sizes[variable]))
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	223 command = "INSERT INTO '%s' (%s) VALUES (%s)" % (self.name, ", ".join(self.variables), ", ".join(sqlValues))
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	224 id = self.mySqlConnection.executeQuery(command, True)
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	225 return id
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	226
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	227
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	228 def retrieveFromId(self, id):
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	229 """
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	230 Retrieve a row from its id
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	231 @param id: the id of the row
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	232 @type id: int
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	233 @return: the row
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	234 """
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	235 query = self.mySqlConnection.executeQuery("SELECT * FROM '%s' WHERE id = %d" % (self.name, id))
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	236 result = query.getLine()
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	237 if result == None:
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	238 raise Exception("Error! Id %d is not in the table %s!" % (id, self.name))
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	239 return result
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	240
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	241
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	242 def retrieveBulkFromId(self, ids):
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	243 """
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	244 Retrieve a row from its id
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	245 @param id: the ids of the row
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	246 @type id: list of int
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	247 @return: the row
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	248 """
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	249 if not ids:
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	250 return []
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	251 MAXSIZE = 1000
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	252 results = []
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	253 for batch in range(len(ids) / MAXSIZE + 1):
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	254 theseIds = ids[batch * MAXSIZE : (batch+1) * MAXSIZE]
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	255 if theseIds:
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	256 query = self.mySqlConnection.executeQuery("SELECT * FROM '%s' WHERE id IN (%s)" % (self.name, ", ".join(["%d" % (id) for id in theseIds])))
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	257 lines = query.getLines()
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	258 if len(lines) != len(theseIds):
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	259 raise Exception("Error! Some Ids of (%s) is are missing in the table '%s' (got %d instead of %d)!" % (", ".join(["%d" % (id) for id in theseIds]), self.name, len(lines)), len(theseIds))
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	260 results.extend(lines)
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	261 return results
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	262
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	263
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	264 def removeFromId(self, id):
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	265 """
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	266 Remove a row from its id
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	267 @param id: the id of the row
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	268 @type id: int
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	269 """
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	270 self.mySqlConnection.executeQuery("DELETE FROM '%s' WHERE id = %d" % (self.name, id))
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	271
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	272
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	273 def getIterator(self):
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	274 """
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	275 Iterate on the content of table
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	276 @return: iterator to the rows of the table
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	277 """
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	278 if not self.created:
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	279 return
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	280 MAXSIZE = 1000
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	281 query = self.mySqlConnection.executeQuery("SELECT count(id) FROM '%s'" % (self.name))
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	282 nbRows = int(query.getLine()[0])
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	283 for chunk in range((nbRows / MAXSIZE) + 1):
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	284 query = self.mySqlConnection.executeQuery("SELECT * FROM '%s' LIMIT %d, %d" % (self.name, chunk * MAXSIZE, MAXSIZE))
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	285 for line in query.getIterator():
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	286 yield line
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	287
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	288
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	289 def createIndex(self, indexName, values, unique = False, fullText = False):
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	290 """
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	291 Add an index on the table
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	292 @param indexName: name of the index
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	293 @type indexName: string
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	294 @param values: values to be indexed
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	295 @type values: string
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	296 @param unique: if the index is unique
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	297 @type unique: boolean
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	298 @param fullText: whether full text should be indexed
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	299 @type fullText: boolean
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	300 """
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	301 self.mySqlConnection.executeQuery("CREATE %s%sINDEX '%s' ON '%s' (%s)" % ("UNIQUE " if unique else "", "FULLTEXT " if fullText else "", indexName, self.name, ", ".join(values)))
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	302
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	303
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	304 def setDefaultTagValue(self, field, name, value):
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	305 """
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	306 Add a tag value
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	307 @param name: name of the tag
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	308 @type name: string
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	309 @param value: value of the tag
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	310 @type value: string or int
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	311 """
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	312 newData = {}
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	313 for line in MySqlTable.getIterator(self):
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	314 id = line[0]
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	315 tags = line[field]
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	316 if tags == '':
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	317 newTag = "%s=%s" % (name, value)
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	318 else:
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	319 newTag = "%s;%s=%s" % (tags, name, value)
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	320 if name not in [tag.split("=")[0] for tag in tags.split(";")]:
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	321 newData[id] = newTag
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	322 for id, tag in newData.iteritems():
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	323 query = self.mySqlConnection.executeQuery("UPDATE '%s' SET tags = '%s' WHERE id = %i" % (self.name, tag, id))
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	324
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	325
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	326
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	327 def show(self):
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	328 """
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	329 Drop the content of the current table
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	330 """
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	331 query = self.mySqlConnection.executeQuery("SELECT * FROM '%s'" % (self.name))
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	332 print query.getLines()
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	333
e0f8dcca02ed Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data. yufei-luo parents: diff changeset	334

Mercurial > repos > yufei-luo > s_mart

annotate smart_toolShed/SMART/Java/Python/mySql/MySqlTable.py @ 1:30c5431d8ce4