Package install :: Package MoSTBioDat :: Package DataBase :: Package ImportData :: Package Data2DB :: Module TaBuilder
[hide private]
[frames] | no frames]

Source Code for Module install.MoSTBioDat.DataBase.ImportData.Data2DB.TaBuilder

  1  #!/usr/bin/env python 
  2  ######################## 
  3  # TaBuilder.py         # 
  4  # Table class builder  # 
  5  ######################## 
  6   
  7  ###################################################### 
  8  # Copyright (c) 2007-2008 Andrzej Bak                # 
  9  # ARC Seibersdorf & University of Silesia            # 
 10  # Author: Andrzej Bak <Andrzej.Bak@us.edu.pl>        # 
 11  # License: GNU General Public License, version: 3    # 
 12  # URL: http://chemoinformatyka.us.edu.pl/mostbiodat/ # 
 13  # Version: 1, 06.01.2010                             # 
 14  ###################################################### 
 15   
 16  try: 
 17      import sys 
 18      import os 
 19      from MoSTBioDat.DataBase.Scheme.MoSTBioDatScheme import MoSTBioDatScheme 
 20  except ImportError,e: 
 21      print 'Error: %s' %e 
 22      sys.exit(1) 
 23  ###############  Table Builder class ################# 
24 -class TaBuilder(MoSTBioDatScheme):
25 """ 26 Build Table subclasses by investigating current Database Scheme 27 INPUT: 28 host - string, host to connect 29 user - string, user to connect as 30 passwd - string, password to use 31 db - string, database to use 32 port - integer, TCP/IP port to connect 33 log - boolean, logging flag 34 unix_socket - string, location of unix_socket to use 35 conv - conversion dictionary, see MySQLdb.converters 36 connect_timeout - number of seconds to wait before the connection attempt fails. 37 compress - if set, compression is enabled 38 named_pipe - if set, a named pipe is used to connect (Windows only) 39 init_command - command which is run once the connection is created 40 read_default_file - file from which default client values are read 41 read_default_group - configuration group to use from the default file 42 cursorclass - class object, used to create cursors (keyword only) 43 use_unicode - if True, text-like columns are returned as unicode objects 44 using the connection's character set. Otherwise, text-like 45 columns are returned as strings. columns are returned as 46 normal strings. Unicode objects will always be encoded to 47 the connection's character set regardless of this setting. 48 charset - if supplied, the connection character set will be changed 49 to this character set (MySQL-4.1 and newer). This implies 50 use_unicode=True. 51 sql_mode - if supplied, the session SQL mode will be changed to this 52 setting (MySQL-4.1 and newer). For more details and legal 53 values, see the MySQL documentation. 54 client_flag - integer, flags to use or 0 55 (see MySQL docs or constants/CLIENTS.py) 56 ssl - dictionary or mapping, contains SSL connection parameters; 57 see the MySQL documentation for more details 58 (mysql_ssl_set()). If this is set, and the client does not 59 support SSL, NotSupportedError will be raised. 60 local_infile - integer, non-zero enables LOAD LOCAL INFILE; zero disables 61 format - string format for log handler 62 filter - filter object from logger object 63 datefmt - data/time format 64 path - directory path to log file 65 filename - log filename, default log 66 filemode - mode to open log file, default='a' 67 level - set root logger level to specified level 68 logfilelevel- set level to log file 69 cache - create cache for query, default=True 70 scheme2file - boolean - save database scheme to shelve file 71 log - log flag, boolean default=False 72 OUTPUT: 73 class object 74 """ 75
76 - def __init__(self,host='localhost',db='Ligand',user=None,passwd=None,port=3306,log=False,**kwargs):
77 MoSTBioDatScheme.__init__(self,host,db,user,passwd,port,scheme2file=False,log=log,**kwargs)
78
79 - def genTables(self):
80 """ 81 generate Tables dictionary 82 INPUT: 83 class object 84 OUTPUT: 85 tabobjdict - table object dictionary 86 """ 87 tabdict=self.geTablesDesc()#get table description dictionary 88 tabobjdict={} 89 if tabdict: 90 for tabname, tabdesc in tabdict.iteritems():#iterate table dictionary 91 colobjlist=map(lambda col: self.genColumn(tabname,col),tabdesc)#generate column object list for table 92 tabobj=Table(tabname,*colobjlist)#generate table object 93 tabobjdict[tabname]=tabobj#insert table object into table object dictionary 94 return tabobjdict 95 else: 96 print 'Database scheme not downloaded!' 97 sys.exit(1)
98
99 - def genTable(self,tabname):
100 """ 101 generate Table dictionary 102 INPUT: 103 tabname - table name 104 OUTPUT: 105 table object dictionary 106 """ 107 tabdict=self.geTableDesc(tabname)#get table description dictionary 108 tabobjdict={} 109 if tabdict: 110 colobjlist=map(lambda col: self.genColumn(tabname,col),tabdict[tabname])#generate column object list for table 111 tabobj=Table(tabname,*colobjlist)#generate table object 112 tabobjdict[tabname]=tabobj#insert table object into table object dictionary 113 return tabobjdict 114 else: 115 print 'Database scheme not downloaded!' 116 sys.exit(1)
117
118 - def getFKDesc(self,tabname,colname):
119 """ 120 get foreign keys 121 INPUt: 122 class object 123 tabname - str, tablename 124 colname - str, colname 125 """ 126 query=""" 127 SELECT REFERENCED_TABLE_NAME AS FKtab,REFERENCED_COLUMN_NAME AS FKol 128 FROM information_schema.KEY_COLUMN_USAGE 129 WHERE TABLE_SCHEMA='%s' 130 AND TABLE_NAME='%s' 131 AND COLUMN_NAME='%s'; 132 """ %(self.kwargs['db'],tabname,colname)#sql query on information_schema database 133 return query
134
135 - def genColumn(self,tabname,coldesc):
136 """ 137 generate Column class 138 INPUT: 139 class object 140 coldesc - column description 141 OUTPUT: 142 column class 143 """ 144 sql=self.getFKDesc(tabname,coldesc['Field'])#get foreign key description 145 result=self.executeQuery(sql) 146 if result:#if not empty 147 coldesc.update(result[0])#add result to column description dictionary 148 coltype=coldesc['Type']#get column type 149 if len(coltype)>1:# if column description longer then one word 150 coltype=coltype.split(' ') 151 coldesc['Type'],colength,fpoint= self.parseType(coltype[0])#parse type f.e FLOAT(11,0) 152 if colength: 153 coldesc['Length']=colength#get length 154 if fpoint: 155 coldesc['Fpoint']=fpoint#get floating point 156 while True: 157 try: 158 coldesc[coltype.pop(1).capitalize()]=True 159 except IndexError: break 160 else: 161 coldesc['Type'],coldesc['Lenght']= self.parseType(coltype)#column desc one word 162 coldesc['Null']=self.isNull(coldesc['Null'])#get Null 163 coldesc['Key']=self.isKey(coldesc['Key']) 164 if coldesc['Default']=='':#Default empty 165 coldesc['Default']=None 166 if coldesc['Extra']=='':#Extra empty 167 coldesc['Extra']=None 168 else: 169 coldesc['Autoincrement']=coldesc['Extra']#get autoincrement 170 171 FKol=coldesc.get('FKol',None)# get foreing key 172 if FKol:#if not None 173 coldesc['Fkey']=True 174 else:#if None 175 if self.isFK(coldesc['Field']):#check on basis of column name 176 coldesc['Fkey']=True 177 return Column(**coldesc)
178
179 - def parseType(self,col):
180 """ 181 parse Type MySQL field 182 INPUT: 183 class object 184 col - column description 185 OUTPUT: 186 coltype - column type 187 colength - column length 188 fpoint - floating point 189 """ 190 try: 191 idxo=col.index('(') 192 except ValueError: 193 return col.upper(), None, None 194 else: 195 coltype=col[:idxo].upper() 196 idxc=col.index(')') 197 colength=col[idxo+1:idxc].split(',') 198 if len(colength)>1: 199 fpoint=int(colength[1]) 200 colength=int(colength[0]) 201 return coltype, colength, fpoint 202 else: 203 return coltype,int(colength[0]),None
204
205 - def isNull(self,null):
206 """ 207 check if NULL 208 INPUT: 209 class object 210 null - null value 211 OUTPUT: 212 boolean 213 """ 214 if null=='YES': 215 return True 216 else: 217 return False
218
219 - def isKey(self,key):
220 """ 221 check if PRIMARY KEY 222 INPUT: 223 class object 224 key - key value 225 OUTPUT: 226 boolean 227 """ 228 if key=='PRI': 229 return True 230 else: 231 return False
232
233 - def isFK(self,key):
234 """ 235 check if Foreign Key 236 INPUT: 237 class object 238 key - key value 239 OUTPUT: 240 boolean 241 """ 242 if key.endswith('Fk'): 243 return True 244 else: 245 return False
246 ########### Column class #################################
247 -class Column(object):
248 """ 249 column class 250 INPUT: 251 OUTPUT: 252 """ 253
254 - def __init__(self,**kwargs):
255 self.kwargs={} 256 self.kwargs=kwargs 257 self.field=kwargs.get('Field',None)
258
259 - def getField(self):
260 """ 261 get field name 262 INPUT: 263 class object 264 OUTPUT: 265 field - field 266 """ 267 return self.field
268
269 - def geType(self):
270 """ 271 get Type 272 INPUT: 273 class object 274 OUTPUT: 275 type - type, default None 276 """ 277 return self.kwargs.get('Type',None)
278
279 - def getLength(self):
280 """ 281 get Length 282 INPUT: 283 class object 284 OUTPUT: 285 length - length, default - default 286 """ 287 return self.kwargs.get('Length','default')
288
289 - def getFpoint(self):
290 """ 291 get number of digits following decimal point 292 INPUT: 293 class object 294 OUTPUT: 295 fpoint - floating point, default - default 296 """ 297 return self.kwargs.get('Fpoint','default')
298
299 - def getExtra(self):
300 """ 301 get Extra 302 INPUT: 303 class object 304 OUTPUT: 305 extra - extra, default - None 306 """ 307 return self.kwargs.get('Extra',None)
308
309 - def getKey(self):
310 """ 311 get Key 312 INPUT: 313 class object 314 OUTPUT: 315 key - key, default - False 316 """ 317 return self.kwargs.get('Key',False)
318
319 - def getNull(self):
320 """ 321 get Null 322 INPUT: 323 class object 324 OUTPUT: 325 null - null, default - False 326 """ 327 return self.kwargs.get('Null',False)
328
329 - def getDefault(self):
330 """ 331 get Default 332 INPUT: 333 class object 334 OUTPUT: 335 default - default, default - None 336 """ 337 return self.kwargs.get('Default',None)
338
339 - def getUnsigned(self):
340 """ 341 get Unsigned 342 INPUT: 343 class object 344 OUTPUT: 345 unsigned - unsigned, default - False 346 """ 347 return self.kwargs.get('Unsigned',False)
348
349 - def getZerofill(self):
350 """ 351 get Zerofill 352 INPUT: 353 class object 354 OUTPUT: 355 zerofill - zerofill, default - False 356 """ 357 return self.kwargs.get('Zerofill',False)
358
359 - def getAutoIncrem(self):
360 """ 361 get Autoincrement 362 INPUT: 363 class object 364 OUTPUT: 365 autoincrement - autoincrement, default - False 366 """ 367 return self.kwargs.get('Autoincrement',False)
368
369 - def getFKey(self):
370 """ 371 get Foreign Key 372 INPUT: 373 class object 374 OUTPUT: 375 Fkey - Fkey, default - False 376 """ 377 return self.kwargs.get('Fkey',False)
378
379 - def getFKol(self):
380 """ 381 get Foreign Key column name 382 INPUT: 383 class object 384 OUTPUT: 385 FKol - foreign column name 386 """ 387 return self.kwargs.get('FKol',None)
388
389 - def getFKtab(self):
390 """ 391 get Foreign Key tablename 392 INPUT: 393 class object 394 OUTPUT: 395 FKtab - foreign tablename 396 """ 397 return self.kwargs.get('FKtab',None)
398 399 ############ Table class ###################################
400 -class Table(object):
401 """ 402 table class 403 INPUT: 404 OUTPUT: 405 """
406 - def __init__(self,name,*args):
407 self.name=name 408 self.columns={} 409 for colobj in args: 410 self.columns[colobj.field]=colobj
411
412 - def getColName(self):
413 """ 414 get column name 415 INPUT: 416 class object 417 OUTPUT: 418 columns keys 419 """ 420 return self.columns.keys()
421
422 - def getColumn(self,key):
423 """ 424 get column object 425 INPUT: 426 class object 427 OUTPUT: 428 columns key, default None 429 """ 430 return self.columns.get(key,None)
431 432 ################## End of class ##################################### 433 ################### MAIN ############################################ 434 ################# example of usage ################################## 435 if __name__=='__main__': 436 pass 437 # A=TaBuilder(user='',passwd='',db='Ligand',path='/tmp/Log',filename='taBuilder',log=True) 438 # a=A.genTables() 439 # c=a['ConnTab'].getColumn('ProtStatIdFk').getFKol() 440 # print c 441 # a=A.genTable('ProtStat') 442 # c=a['ProtStat'].getColumn('ChemCompIdFk').getFKol() 443 # d=a['ProtStat'].getColumn('ChemCompIdFk').getFKey() 444 # print c, d 445 # A.closeDB() 446