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