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

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

   1  #!/usr/bin/env python 
   2  ###################################### 
   3  # InserTables.py                     # 
   4  # Import table into DataBase or File # 
   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      import shelve 
  20      import anydbm 
  21      import numpy 
  22      from MoSTBioDat.DataBase.ImportData.Validator.TypeValidator import TypeValidator 
  23      from MoSTBioDat.DataBase.Query.SQLBuilder.QueryBuilder import queryBuilder 
  24      from MoSTBioDat.DataBase.Query.SQLBuilder.sqlbuilder import * 
  25      from MoSTBioDat.DataBase.Query.UserQuery.UserQuery import UserQuery 
  26      from MoSTBioDat.DataBase.ImportData.Data2DB.Smile import Smile 
  27      from MoSTBioDat.DataBase.ImportData.Data2DB.DBFile import InputDB 
  28  except ImportError,e: 
  29      print 'Error: %s' %e 
  30      sys.exit(1) 
  31  ########## Insert Tables class ########################################################## 
32 -class InserTables(object):
33 - def __init__(self):
34 """ 35 Methods for inserting data from shelve dictionary into database tables 36 INPUT: 37 OUTPUT: 38 class object 39 """ 40 pass
41
42 - def UserChemComp(self,logdebug=False,tabcolvaldict={},lowercasetablenames=True,**kwarg):
43 """ 44 User Chemical Compound table import 45 INPUT: 46 logdebug - log debug in log file 47 tabcolvaldict - dict, table column value dictionary 48 lowercasetablenames - boolean, lower case table names MySQL engine settings, default True 49 OUTPUT: 50 ID - id table value 51 """ 52 ### settings #################### 53 tablename='ChemComp' 54 if lowercasetablenames: 55 tablename=tablename.lower() 56 ################################# 57 if not len(tabcolvaldict): 58 print 'Error, %s: Specify column name/value dictionary!' %tablename 59 sys.exit(1) 60 tabcol=tabcolvaldict.keys()# tablename list 61 62 if not self.isTabDesCorr(tablename,tabcol):#is table description correct 63 print 'Error, %s: Incorrect table description!'%tablename 64 self.log.error('%s: Incorrect table description',tablename) 65 sys.exit(1) 66 67 if not kwarg.has_key('anyflag'): 68 kwarg.setdefault('anyflag',False) 69 70 if not kwarg.has_key('iso'): 71 kwarg.setdefault('iso',True) 72 73 ### check if record exists in ChemComp on basis of ZINCode or IsoSmi 74 tabcol=[] 75 tabcol.append('Id') 76 if tabcolvaldict.has_key('IsoSmi'): 77 tabcol.append('IsoSmi') 78 if tabcolvaldict.has_key('ZINCode'): 79 tabcol.append('ZINCode') 80 if not (tabcolvaldict.has_key('IsoSmi') or tabcolvaldict.has_key('ZINCode')): 81 print 'Error, %s: Specify needed input values!' %tablename 82 sys.exit(1) 83 84 smiobj=Smile(smile=tabcolvaldict['IsoSmi'])#check if smile correct 85 if kwarg['iso']:#if flag iso 86 tabcolvaldict['IsoSmi']=smiobj.CanSmi(mol=smiobj.getMol(),iso=True,kek=False,verbose=True)#create isomeric smile 87 ### search for smile in database ### 88 if len(tabcol)==2: 89 select='Select([table.'+ tablename +'.Id], where=OR(table.'+tablename+'.'+tabcol[1]+'==tabcolvaldict[\''+tabcol[1]+'\']))' 90 if len(tabcol)==3: 91 select='Select([table.'+ tablename +'.Id], where=OR(table.'+tablename+'.'+tabcol[1]+'==tabcolvaldict[\''+tabcol[1]+'\'],table.'+tablename+'.'+tabcol[2]+'==tabcolvaldict[\''+tabcol[2]+'\']))' 92 93 sql=queryBuilder(eval(select))#build query 94 result=self.executeQuery(sql)#execute query 95 if result:#if record exists in table 96 ID=[]##get ID 97 for resitem in result:#iterate result dictionary 98 ID.append(resitem['Id'])#output ID list 99 return ID 100 101 ### DATA VALIDATION ### 102 inrow=self.uservalidrecord(tablename,tabcol,tabcolvaldict,ID=None,logdebug=logdebug) 103 if not inrow: 104 return 0 105 ## DATA INSERTION ### 106 inskey,insval=insPrep(**inrow)#preparation for insertion to table 107 if kwarg['anyflag']:#remove items with at least False value 108 insval=filter(checkitem,insval) 109 if not all([checkitem(val) for val in insval]):#check all values are True 110 return 0 111 if not insval:##input list is empty 112 return 0 113 114 maxId=UserQuery().getMaxId(tablename)#get maximal Id 115 result=self.executeQuery(maxId)#execute query 116 if result: 117 maxId=result[0]['maxId']#max Id value 118 if maxId:#if value not None 119 setautoincr=UserQuery().setLastId(tabname=tablename, value=maxId)#set auto_increment equals value 120 self.executeQuery(setautoincr)#execute query 121 122 IDlist=self.insertrecord(tablename,inskey,insval,logdebug) 123 if logdebug: 124 self.log.debug('%s: Insertion %s done',tablename,tabcolvaldict['IsoSmi']) 125 print '%s inserted into ChemComp Id: %s'%(tabcolvaldict['IsoSmi'],IDlist[0]) 126 return IDlist
127
128 - def getChemComponIdIsoSmiZINCode(self,logdebug=False,tabcolvaldict={},lowercasetablenames=True,**kwarg):
129 """ 130 User Chemical Compound table import 131 INPUT: 132 logdebug - log debug in log file 133 tabcolvaldict - dict, table column value dictionary 134 lowercasetablenames - boolean, lower case table names MySQL engine settings, default True 135 OUTPUT: 136 IDict - ChemComp table dictionary {Id: {tabcolname:tabcolvalue,..}} 137 """ 138 ### settings #################### 139 tablename='ChemComp' 140 if lowercasetablenames: 141 tablename=tablename.lower() 142 ################################# 143 if not len(tabcolvaldict): 144 print 'Error, %s: Specify column name/value dictionary!' %tablename 145 sys.exit(1) 146 tabcol=tabcolvaldict.keys()# tablename list 147 148 if not self.isTabDesCorr(tablename,tabcol):#is table description correct 149 print 'Error, %s: Incorrect table description!'%tablename 150 self.log.error('%s: Incorrect table description',tablename) 151 sys.exit(1) 152 153 if not kwarg.has_key('iso'): 154 kwarg.setdefault('iso',True) 155 156 ### check if record exists in ChemComp on basis of Id, ZINCode or IsoSmi (in this order) 157 id=tabcolvaldict.get('Id',None) 158 isosmi=tabcolvaldict.get('IsoSmi',None) 159 zincode=tabcolvaldict.get('ZINCode',None) 160 if id:#search table on id 161 select='Select([table.'+ tablename +'.Id,table.'+tablename+'.IsoSmi,table.'+tablename+'.ZINCode], where=OR(table.'+tablename+'.Id==id))' 162 elif zincode:#search table on zincode 163 select='Select([table.'+ tablename +'.Id,table.'+tablename+'.IsoSmi,table.'+tablename+'.ZINCode], where=OR(table.'+tablename+'.ZINCode==zincode))' 164 elif isosmi:#search table on isosmi 165 smiobj=Smile(smile=isosmi)#check if smile correct 166 if kwarg['iso']:#if flag iso 167 isosmi=smiobj.CanSmi(mol=smiobj.getMol(),iso=True,kek=False,verbose=True)#create isomeric smile 168 select='Select([table.'+ tablename +'.Id,table.'+tablename+'.IsoSmi,table.'+tablename+'.ZINCode], where=OR(table.'+tablename+'.IsoSmi==isosmi))' 169 else: 170 print 'Error, %s: Specify needed input values!' %tablename 171 sys.exit(1) 172 173 sql=queryBuilder(eval(select))#build query 174 result=self.executeQuery(sql)#execute query 175 if result:#if record exists in table 176 IDict={}##get ID 177 IDict=dict([(long(item['Id']),item)for item in result]) 178 return IDict 179 else: 180 return 0
181
182 - def getChemCompIdIsoSmi(self,logdebug=False,lowercasetablenames=True,**kwarg):
183 """ 184 get Id and Isomeric SMILE from Chemical Compound table import 185 INPUT: 186 baseImport - flag to avoid data importing 187 logdebug - log debug in log file 188 anyflag - boolean, all imported values must be validated correctly, default False 189 lowercasetablenames - boolean, lower case table names MySQL engine settings, default True 190 OUTPUT: 191 ID - id table value 192 """ 193 ########## settings ################################################################## 194 tablename='ChemComp'#table name 195 if lowercasetablenames: 196 tablename=tablename.lower() 197 tabcol=[ 'Id','IsoSmi','ZINCode']#table columname list 198 ##################################################################################### 199 if not self.isTabDesCorr(tablename,tabcol):#is table description correct 200 print 'Error, %s: Incorrect table description!' %tablename 201 self.log.error('%s: Incorrect table description',tablename) 202 sys.exit(1) 203 204 ### select Id and IsoSmi for table ### 205 select='Select([table.'+ tablename +'.Id,table.'+tablename+'.IsoSmi])' 206 sql=queryBuilder(eval(select))#build query 207 result=self.executeQuery(sql)#execute query 208 if result: 209 IDict={} 210 while result: 211 item=result.pop() 212 IDict[item['Id']]=item['IsoSmi'] 213 #IDict=dict([(item[tabcol[0]],item[tabcol[1]])for item in result] 214 del result 215 return IDict 216 else: 217 return 0
218
219 - def ChemComp(self,dictobj=None,baseImport=False,logdebug=False,lowercasetablenames=True,**kwarg):
220 """ 221 Chemical Compound table import 222 INPUT: 223 baseImport - flag to avoid data importing 224 dictobj - shelve dictionary object 225 logdebug - log debug in log file 226 anyflag - boolean, all imported values must be validated correctly, default False 227 lowercasetablenames - boolean, lower case table names MySQL engine settings, default True 228 OUTPUT: 229 ID - id table value 230 """ 231 ########## settings ################################################################## 232 tablename='ChemComp'#table name 233 if lowercasetablenames: 234 tablename=tablename.lower() 235 tabcol=[ 'Id', 'IsoSmi','ZINCode']#table columname list 236 mapdict=['autoincrement','isosmi','filename']# mapping dictionary object attributes 237 ##################################################################################### 238 if not dictobj: 239 print 'Error, %s: Missing dictionary object!' %tablename 240 self.log.error('%s: Missing dictionary object',tablename) 241 sys.exit(1) 242 if not self.isTabDesCorr(tablename,tabcol):#is table description correct 243 print 'Error, %s: Incorrect table description!' %tablename 244 self.log.error('%s: Incorrect table description',tablename) 245 sys.exit(1) 246 247 ### check if dictionary object has specified attributes 248 if not dictobj.hasAttr(mapdict[2]): 249 if logdebug: 250 self.log.debug('%s: Missing filename attribute',tablename) 251 return 0 252 if not dictobj.hasAttr(mapdict[1]): 253 if logdebug: 254 self.log.debug('%s: Missing isosmi attribute: %s',tablename,dictobj.kwargs['filename']) 255 return 0 256 257 if not kwarg.has_key('anyflag'): 258 kwarg.setdefault('anyflag',False) 259 260 ### check if record exists in ChemComp on basis of ZINCode 261 select='Select([table.'+ tablename +'.'+ tabcol[0]+'], where=OR(table.'+tablename+'.'+tabcol[1]+'==dictobj.kwargs[\''+mapdict[1]+'\'],table.'+tablename+'.'+tabcol[2]+'==dictobj.kwargs[\''+mapdict[2]+'\']))' 262 sql=queryBuilder(eval(select))#build query 263 result=self.executeQuery(sql)#execute query 264 if result:#if record exists in table 265 ID=[]##get ID 266 for resitem in result:#iterate result dictionary 267 ID.append(resitem[tabcol[0]])#output ID list 268 return ID 269 270 if not baseImport:#no import to table 271 return 0 272 273 ID=kwarg.get('ID',None) 274 if ID:##ID must be list of length=1 275 if not isinstance(ID,list) or len(ID)!=1: 276 print 'Error, %s: Incorrect ID specification!' %tablename 277 if logdebug: 278 self.log.debug('%s: Incorrect ID specification',tablename) 279 return 0 280 281 IDlist=kwarg.get('IDlist',None) 282 if IDlist:#must be list 283 if not isinstance(IDlist,list): 284 print 'Error, %s: Incorrect IDlist specification!'%tablename 285 if logdebug: 286 self.log.debug('%s: Incorrect IDlist specification',tablename) 287 return 0 288 289 IDict=kwarg.get('IDict',None) 290 if IDict:#must be dictionary 291 if not isinstance(IDict,dict): 292 print 'Error, %s: Incorrect IDict specification!'%tablename 293 if logdebug: 294 self.log.debug('%s: Incorrect ID specification',tablename) 295 return 0 296 297 ### DATA VALIDATION ### 298 inrow=self.ligvalidrecord(dictobj,tablename,tabcol,mapdict,ID,IDlist,IDict,logdebug) 299 if not inrow: 300 return 0 301 ### DATA INSERTION ### 302 inskey,insval=insPrep(**inrow)#preparation for insertion to table 303 if kwarg['anyflag']:#remove items with at least False value 304 insval=filter(checkitem,insval) 305 if not all([checkitem(val) for val in insval]):#check all values are True 306 return 0 307 if not insval:##input list is empty 308 return 0 309 IDlist=self.insertrecord(tablename,inskey,insval,logdebug) 310 if logdebug: 311 self.log.debug('%s: Insertion %s done',tablename,dictobj.kwargs['filename']) 312 return IDlist
313
314 - def UserProtStat(self,logdebug=False,tabcolvaldict={},lowercasetablenames=True,**kwarg):
315 """ 316 Protomer/Tautomer State table import 317 INPUT: 318 logdebug - log debug in log file 319 tabcolvaldict - dict, table column/value dictionary 320 lowercasetablenames - boolean, lower case table names MySQL engine settings, default True 321 **kwarg - 322 ID, IDlist, IDict - foreign key value 323 iso - boolean, isomeric SMILE code, default True 324 addH - boolean, add hydrogens, default True 325 326 OUTPUT: 327 ID - id table value 328 """ 329 ### Settings ##################### 330 tablename='ProtStat' 331 if lowercasetablenames: 332 tablename=tablename.lower() 333 ################################## 334 if not len(tabcolvaldict): 335 print 'Error, %s: Specify column name/value dictionary!' %tablename 336 sys.exit(1) 337 tabcol=tabcolvaldict.keys()# tablename list 338 339 if not self.isTabDesCorr(tablename,tabcol):#is table description correct 340 print 'Error, %s: Incorrect table description!' %tablename 341 self.log.error('%s: Incorrect table description',tablename) 342 sys.exit(1) 343 344 if not kwarg.has_key('anyflag'): 345 kwarg.setdefault('anyflag',False) 346 347 if not kwarg.has_key('iso'): 348 kwarg.setdefault('iso',True) 349 if not kwarg.has_key('addH'): 350 kwarg.setdefault('addH',True) 351 352 ###check if record exists in ChemComp on basis of ZINCode or IsoSmi 353 tabcol=[] 354 tabcol.append('Id') 355 if tabcolvaldict.has_key('IsoSmi'): 356 tabcol.append('IsoSmi') 357 if not tabcolvaldict.has_key('IsoSmi'): 358 print 'Error, %s: Specify needed input values!' %tablename 359 sys.exit(1) 360 361 smiobj=Smile(smile=tabcolvaldict['IsoSmi']) 362 if kwarg['iso']: 363 tabcolvaldict['IsoSmi']=smiobj.CanSmi(mol=smiobj.getMol(),iso=True,kek=False,verbose=True)#create isomeric smile 364 ### check if smile exists in table 365 select='Select([table.'+ tablename +'.Id], where=OR(table.'+tablename+'.'+tabcol[1]+'==tabcolvaldict[\''+tabcol[1]+'\']))' 366 sql=queryBuilder(eval(select))#build query 367 result=self.executeQuery(sql)#execute query 368 if result:#if record exists in table 369 ID=[]##get ID 370 for resitem in result:#iterate result dictionary 371 ID.append(resitem['Id'])#output ID list 372 return ID 373 374 ### get foreign key from ChemComp table 375 if tabcolvaldict.has_key('ChemCompIdFk'):#get FK if specified in table column value dictionary 376 ID=tabcolvaldict['ChemCompIdFk'] 377 if isinstance(ID,(str,int)): 378 try: 379 ID=int(ID) 380 except ValueError: 381 print 'Error, %s: Incorrect ChemCompIdFk format!' %tablename 382 sys.exit(1) 383 ID=[ID] 384 else:#get FK if not specified in table column value dictionary 385 ID=self.UserChemComp(tablename='ChemComp',tabcolvaldict={'IsoSmi':tabcolvaldict['IsoSmi']}) 386 if ID:## ID must be list of length=1 387 if not isinstance(ID,list) or len(ID)!=1: 388 print 'Error, %s: Incorrect ID specification!' %tablename 389 if logdebug: 390 self.log.debug('%s: Incorrect ID specification',tablename) 391 sys.exit(1) 392 393 tabcol.append('ChemCompIdFk')#add attribute to table column list 394 395 ### DATA VALIDATION ### 396 inrow=self.uservalidrecord(tablename,tabcol,tabcolvaldict,ID,logdebug=logdebug) 397 if not inrow: 398 return 0 399 ### DATA INSERTION ### 400 inskey,insval=insPrep(**inrow)#preparation for insertion to table 401 if kwarg['anyflag']:#remove items with at least False value 402 insval=filter(checkitem,insval) 403 if not all([checkitem(val) for val in insval]):#check all values are True 404 return 0 405 if not insval:##input list is empty 406 return 0 407 408 maxId=UserQuery().getMaxId(tablename)#get maximal Id 409 result=self.executeQuery(maxId)#execute query 410 if result: 411 maxId=result[0]['maxId']#max Id value 412 if maxId:#if value not None 413 setautoincr=UserQuery().setLastId(tabname=tablename, value=maxId)#set auto_increment equals value 414 self.executeQuery(setautoincr)#execute query 415 416 IDlist=self.insertrecord(tablename,inskey,insval,logdebug) 417 418 if logdebug: 419 self.log.debug('%s: Insertion %s done',tablename,tabcolvaldict['IsoSmi']) 420 421 print '%s inserted into ProtStat Id: %s'%(tabcolvaldict['IsoSmi'],IDlist[0]) 422 ### insertion into ConnTab table ### 423 ConnTabId=self.UserConnTab(smi=tabcolvaldict['IsoSmi'],logdebug=logdebug,lowercasetablenames=lowercasetablenames,iso=kwarg['iso'],addH=kwarg['addH'],ID=IDlist) 424 print '%s inserted into ConnTab Id: '%tabcolvaldict['IsoSmi'],ConnTabId 425 ### insertion into Atoms table ### 426 AtomsId=self.UserAtoms(smi=tabcolvaldict['IsoSmi'],logdebug=logdebug,lowercasetablenames=lowercasetablenames,iso=kwarg['iso'],addH=kwarg['addH'],ID=IDlist) 427 print '%s inserted into Atoms Id: '%tabcolvaldict['IsoSmi'],AtomsId 428 return IDlist
429
430 - def getProtStat(self,logdebug=False,tabcolvaldict={},lowercasetablenames=True,**kwarg):
431 """ 432 Check if specified SMILE code exists in ProtStat table 433 INPUT: 434 logdebug - log debug in log file 435 tabcolvaldict - dict, table column/value dictionary 436 lowercasetablenames - boolean, lower case table names MySQL engine settings, default True 437 **kwarg - 438 iso - boolean, isomeric SMILE code, default True 439 OUTPUT: 440 ID - ChemComp foreign key table value 441 """ 442 ### Settings ########################## 443 tablename='ProtStat' 444 if lowercasetablenames: 445 tablename=tablename.lower() 446 ####################################### 447 if not len(tabcolvaldict): 448 print 'Error, %s: Specify column name/value dictionary!' %tablename 449 sys.exit(1) 450 tabcol=tabcolvaldict.keys()# tablename list 451 452 if not self.isTabDesCorr(tablename,tabcol):#is table description correct 453 print 'Error, %s: Incorrect table description!' %tablename 454 self.log.error('%s: Incorrect table description',tablename) 455 sys.exit(1) 456 457 if not kwarg.has_key('iso'): 458 kwarg.setdefault('iso',True) 459 460 ###check if record exists in ProtStat on basis of ZINCode or IsoSmi 461 tabcol=[] 462 if tabcolvaldict.has_key('IsoSmi'): 463 tabcol.append('IsoSmi') 464 if not tabcolvaldict.has_key('IsoSmi'): 465 print 'Error, %s: Specify needed input values!' %tablename 466 sys.exit(1) 467 468 smiobj=Smile(smile=tabcolvaldict['IsoSmi']) 469 if kwarg['iso']: 470 tabcolvaldict['IsoSmi']=smiobj.CanSmi(mol=smiobj.getMol(),iso=True,kek=False,verbose=True)#create isomeric smile 471 ### check if smile exists in table 472 select='Select([table.'+ tablename +'.ChemCompIdFk], where=OR(table.'+tablename+'.'+tabcol[0]+'==tabcolvaldict[\''+tabcol[0]+'\']))' 473 sql=queryBuilder(eval(select))#build query 474 result=self.executeQuery(sql)#execute query 475 if result:#if record exists in table 476 ID=[]##get ID 477 for resitem in result:#iterate result dictionary 478 ID.append(resitem['ChemCompIdFk'])#output ID list 479 if len(ID)!=1: 480 print 'Error, %s: Incorrect ID specification!' %tablename 481 if logdebug: 482 self.log.debug('%s: Incorrect ID specification',tablename) 483 sys.exit(1) 484 return ID
485
486 - def getProtStatonIdFk(self,logdebug=False,tabcolvaldict={},lowercasetablenames=True,**kwarg):
487 """ 488 get isomeric SMILE/Id dictionary for specified foreign key 489 INPUT: 490 logdebug - log debug in log file 491 tabcolvaldict - dict, table column/value dictionary 492 lowercasetablenames - boolean, lower case table names MySQL engine settings, default True 493 **kwarg - 494 iso - boolean, isomeric SMILE code, default True 495 OUTPUT: 496 IDict - dictionary, smile: id 497 """ 498 ### Settings ############################# 499 tablename='ProtStat' 500 if lowercasetablenames: 501 tablename=tablename.lower() 502 ########################################## 503 if not len(tabcolvaldict): 504 print 'Error, %s: Specify column name/value dictionary!' %tablename 505 sys.exit(1) 506 tabcol=tabcolvaldict.keys()# tablename list 507 508 if not self.isTabDesCorr(tablename,tabcol):#is table description correct 509 print 'Error, %s: Incorrect table description!' %tablename 510 self.log.error('%s: Incorrect table description',tablename) 511 sys.exit(1) 512 513 ###check if record exists in ProtStat on basis of ChemCompIdFk 514 tabcol=[] 515 if tabcolvaldict.has_key('ChemCompIdFk'): 516 ID=tabcolvaldict['ChemCompIdFk'] 517 if not isinstance(ID,list) or len(ID)!=1: 518 print 'Error, %s: Incorrect ID specification!' %tablename 519 self.log.error('%s: Incorrect ID specification',tablename) 520 sys.exit(1) 521 if not isinstance(ID[0],(int,long)):#id input ID is integer get ID, if long insert record 522 print 'Error, %s: Incorrect ID specification!' %tablename 523 self.log.error('%s: Incorrect ID specification',tablename) 524 sys.exit(1) 525 tabcol.append('ChemCompIdFk') 526 else: 527 print 'Error, %s: Specify needed input values!' %tablename 528 sys.exit(1) 529 530 ### check if smile exists in table 531 select='Select([table.'+ tablename +'.IsoSmi,table.'+tablename+'.Id], where=OR(table.'+tablename+'.'+tabcol[0]+'==tabcolvaldict[\''+tabcol[0]+'\']))' 532 sql=queryBuilder(eval(select))#build query 533 result=self.executeQuery(sql)#execute query 534 if result:#if record exists in table 535 IDict=dict([(resultval['IsoSmi'],[resultval['Id']]) for resultval in result]) 536 return IDict 537 else: 538 return 0
539
540 - def ProtStat(self,dictobj=None,logdebug=False,lowercasetablenames=True,**kwarg):
541 """ 542 Protomer/Tautomer State table import 543 INPUT: 544 dictobj - shelve dictionary object 545 logdebug - log debug in log file 546 lowercasetablenames - boolean, lower case table names MySQL engine settings, default True 547 **kwarg - ID, IDlist, IDict - foreign key value 548 OUTPUT: 549 ID - id table value 550 """ 551 ########## settings ######################################################## 552 tablename='ProtStat'#table name 553 if lowercasetablenames: 554 tablename=tablename.lower() 555 tabcol=[ 'Id', 'IsoSmi','ChemCompIdFk']#table columname list 556 mapdict=['autoincrement','isosmi','ID']# mapping dictionary object attributes 557 ############################################################################ 558 if not dictobj: 559 print 'Error, %s: Missing dictionary object!' %tablename 560 self.log.error('%s: Missing dictionary object',tablename) 561 sys.exit(1) 562 if not self.isTabDesCorr(tablename,tabcol):#is table description correct 563 print 'Error, %s: Incorrect table description!' %tablename 564 self.log.error('%s: Incorrect table description',tablename) 565 sys.exit(1) 566 567 ### check if dictionary object has specified attributes 568 if not dictobj.hasAttr(mapdict[1]): 569 if logdebug: 570 self.log.debug('%s: Missing isosmi attribute: %s',tablename,dictobj.kwargs['filename']) 571 return 0 572 573 if not kwarg.has_key('anyflag'): 574 kwarg.setdefault('anyflag',False) 575 576 ID=kwarg.get('ID',None) 577 if ID:## ID must be list of length=1 578 if not isinstance(ID,list) or len(ID)!=1: 579 print 'Error, %s: Incorrect ID specification!' %tablename 580 if logdebug: 581 self.log.debug('%s: Incorrect ID specification',tablename) 582 return 0 583 if isinstance(ID[0],int):#id input ID is integer get ID, if long insert record 584 ## check if record exists in ProtStat 585 select='Select([table.'+ tablename +'.'+tabcol[0]+'], where=AND(table.'+tablename+'.'+tabcol[1]+'==dictobj.kwargs[\''+mapdict[1]+'\']))' 586 sql=queryBuilder(eval(select))#build query 587 result=self.executeQuery(sql)#execute query 588 if result:#if record exists in table 589 ID=[]#get ID 590 for resitem in result: 591 ID.append(resitem[tabcol[0]]) 592 return ID 593 594 IDlist=kwarg.get('IDlist',None) 595 if IDlist:## must be list 596 if not isinstance(IDlist,list): 597 print 'Error, %s: Incorrect IDlist specification!' %tablename 598 if logdebug: 599 self.log.debug('%s: Incorrect IDlist specification',tablename) 600 return 0 601 602 IDict=kwarg.get('IDict',None) 603 if IDict:## must be dictionary 604 if not isinstance(IDict,dict): 605 print 'Error, %s: Incorrect IDict specification!' %tablename 606 if logdebug: 607 self.log.debug('%s: Incorrect IDict specification',tablename) 608 return 0 609 610 ### DATA VALIDATION ### 611 inrow=self.ligvalidrecord(dictobj,tablename,tabcol,mapdict,ID,IDlist,IDict,logdebug) 612 if not inrow: 613 return 0 614 ### DATA INSERTION ### 615 inskey,insval=insPrep(**inrow)#preparation for insertion to table 616 if kwarg['anyflag']:#remove items with at least False value 617 insval=filter(checkitem,insval) 618 if not all([checkitem(val) for val in insval]):#check all values are True 619 return 0 620 if not insval:##input list is empty 621 return 0 622 IDlist=self.insertrecord(tablename,inskey,insval,logdebug) 623 if logdebug: 624 self.log.debug('%s: Insertion %s done',tablename,dictobj.kwargs['filename']) 625 return IDlist
626
627 - def UserConnTab(self,smi,logdebug=False,lowercasetablenames=True,**kwarg):
628 """ 629 Connection Table import 630 INPUT: 631 smi - str, smile string 632 logdebug - log debug in log file 633 **kwarg - 634 ID, IDlist, IDict - foreign key value 635 iso - boolean, isomeric SMILE code, default True 636 addH - boolean, add hydrogens, default True 637 OUTPUT: 638 ID - id table value 639 """ 640 if not kwarg.has_key('iso'): 641 kwarg.setdefault('iso',True) 642 if not kwarg.has_key('addH'): 643 kwarg.setdefault('addH',True) 644 smiobj=Smile(smile=smi) 645 connlist={} 646 conntab={} 647 if kwarg['addH']: 648 smiobj.addH(mol=smiobj.getMol()) 649 if kwarg['iso']: 650 conntab['conntab'],conntab['symq']=smiobj.Smi2ConnTab(mol=smiobj.getMol()) 651 connlist['bonds']=conntab['conntab'] 652 else: 653 connlist=smiobj.getBonds(mol=smiobj.getMol()) 654 dictobj=InputDB(**connlist) 655 ID=kwarg.get('ID',None) 656 ConnTabID=self.ConnTab(dictobj,logdebug,lowercasetablenames,ID=ID) 657 return ConnTabID
658
659 - def ConnTab(self,dictobj=None,logdebug=False,lowercasetablenames=True,**kwarg):
660 """ 661 Connection Table import 662 INPUT: 663 dictobj - shelve dictionary object 664 logdebug - log debug in log file 665 lowercasetablenames - boolean, lower case table names MySQL engine settings, default True 666 **kwarg - ID, IDlist, IDict - foreign key value 667 OUTPUT: 668 ID - id table value 669 """ 670 ########## settings ######################################################## 671 tablename='ConnTab'#table name 672 if lowercasetablenames: 673 tablename=tablename.lower() 674 tabcol=[ 'Id', 'BegAtom', 'EndAtom','BondType','ProtStatIdFk']#table columname list 675 mapdict=['autoincrement','bonds[0]','bonds[1]','bonds[2]','ID']# mapping dictionary object attributes 676 mapdict1=['autoincrement','conntab[0]','conntab[1]','conntab[2]','ID']# 677 ############################################################################ 678 if not dictobj: 679 print 'Error, %s: Missing dictionary object!' %tablename 680 self.log.error('%s: Missing dictionary object',tablename) 681 sys.exit(1) 682 if not self.isTabDesCorr(tablename,tabcol):#is table description correct 683 print 'Error, %s: Incorrect table description!'%tablename 684 self.log.error('%s: Incorrect table description',tablename) 685 sys.exit(1) 686 687 if not dictobj.hasAttr('bonds'):#check if has attribute 688 mapdict[1]=mapdict1[1] 689 mapdict[2]=mapdict1[2] 690 mapdict[3]=mapdict1[3] 691 if logdebug: 692 self.log.debug('%s: Missing bonds attribute: %s',tablename,dictobj.kwargs['filename']) 693 if not dictobj.hasAttr('conntab'): 694 if logdebug: 695 self.log.debug('%s: Missing bonds attribute: %s',tablename,dictobj.kwargs['filename']) 696 return 0 697 698 if not kwarg.has_key('anyflag'): 699 kwarg.setdefault('anyflag',False) 700 701 ID=kwarg.get('ID',None) 702 if ID:##ID must be list of length=1 703 if not isinstance(ID,list) or len(ID)!=1: 704 print 'Error, %s: Incorrect ID specification!' %tablename 705 if logdebug: 706 self.log.debug('%s: Incorrect ID specification',tablename) 707 return 0 708 if isinstance(ID[0],int):##if ID is integer check if exists if long insert 709 ### check if record exists in ConnTab 710 select='Select([table.'+ tablename +'.'+tabcol[0]+'], where=AND(table.'+tablename+'.'+tabcol[4]+'==ID))' 711 sql=queryBuilder(eval(select))#build query 712 result=self.executeQuery(sql)#execute query 713 if result:#if record exists in table 714 ID=[]#get ID 715 for resitem in result: 716 ID.append(resitem[tabcol[0]]) 717 return ID 718 719 IDlist=kwarg.get('IDlist',None) 720 if IDlist:#must be list 721 if not isinstance(IDlist,list): 722 print 'Error, %s: Incorrect IDlist specification!'%tablename 723 if logdebug: 724 self.log.debug('%s: Incorrect IDlist specification',tablename) 725 return 0 726 727 IDict=kwarg.get('IDict',None) 728 if IDict:#must be dictionary 729 if not isinstance(IDict,dict): 730 print 'Error, %s: Incorrect IDict specification!'%tablename 731 if logdebug: 732 self.log.debug('%s: Incorrect ID specification',tablename) 733 return 0 734 735 ### DATA VALIDATION ### 736 inrow=self.ligvalidrecord(dictobj,tablename,tabcol,mapdict,ID,IDlist,IDict,logdebug) 737 if not inrow: 738 return 0 739 ### DATA INSERTION ### 740 inskey,insval=insPrep(**inrow)#preparation for insertion to table 741 if kwarg['anyflag']:#remove items with at least one False value 742 insval=filter(checkitem,insval) 743 if not all([checkitem(val) for val in insval]):#check all values are True 744 return 0 745 if not insval:##input list is empty 746 return 0 747 IDlist=self.insertrecord(tablename,inskey,insval,logdebug) 748 if logdebug: 749 self.log.debug('%s: Insertion %s done',tablename,dictobj.kwargs['filename']) 750 return IDlist
751
752 - def getConnTabonIdFk(self,logdebug=False,tabcolvaldict={},lowercasetablenames=True,**kwarg):
753 """ 754 get connection table list for specified foreign key 755 INPUT: 756 logdebug - log debug in log file 757 tabcolvaldict - dict, table column/value dictionary 758 lowercasetablenames - boolean, lower case table names MySQL engine settings, default True 759 **kwarg - ID, IDlist, IDict - foreign key value 760 OUTPUT: 761 IDict - dictionary, id: atom list 762 """ 763 ### Settings #################### 764 tablename='ConnTab' 765 if lowercasetablenames: 766 tablename=tablename.lower() 767 ################################# 768 if not len(tabcolvaldict): 769 print 'Error, %s: Specify column name/value dictionary!' %tablename 770 sys.exit(1) 771 tabcol=tabcolvaldict.keys()# tablename list 772 773 if not self.isTabDesCorr(tablename,tabcol):#is table description correct 774 print 'Error, %s: Incorrect table description!' %tablename 775 self.log.error('%s: Incorrect table description',tablename) 776 sys.exit(1) 777 778 ###check if record exists in ProtStat on basis of ChemCompIdFk 779 tabcol=[] 780 if tabcolvaldict.has_key('ProtStatIdFk'): 781 ID=tabcolvaldict['ProtStatIdFk'] 782 if not isinstance(ID,list) or len(ID)!=1: 783 print 'Error, %s: Incorrect ID specification!' %tablename 784 self.log.error('%s: Incorrect ID specification',tablename) 785 sys.exit(1) 786 if not isinstance(ID[0],(int,long)):#id input ID is integer get ID, if long insert record 787 print 'Error, %s: Incorrect ID specification!' %tablename 788 self.log.error('%s: Incorrect ID specification',tablename) 789 sys.exit(1) 790 tabcol.append('ProtStatIdFk') 791 if not tabcolvaldict.has_key('ProtStatIdFk'): 792 print 'Error, %s: Specify needed input values!' %tablename 793 sys.exit(1) 794 795 ### check if record exists in Atoms 796 select='Select([table.'+ tablename +'.Id,table.'+tablename+'.BegAtom,table.'+tablename+'.EndAtom,table.'+tablename+'.BondType], where=AND(table.'+tablename+'.'+tabcol[0]+'==ID))' 797 sql=queryBuilder(eval(select))#build query 798 result=self.executeQuery(sql)#execute query 799 if result:#if record exists in table 800 IDict=dict([(item['Id'],[item['BegAtom'],item['EndAtom'],item['BondType']]) for item in result]) 801 return IDict 802 else: 803 return 0
804
805 - def UserAtoms(self,smi,logdebug=False,lowercasetablenames=True,**kwarg):
806 """ 807 Atoms table import 808 INPUT: 809 logdebug - log debug in log file 810 **kwarg - 811 ID, IDlist, IDict - foreign key value 812 iso - boolean, isomeric SMILE code, default True 813 addH - boolean, add hydrogens, default True 814 OUTPUT: 815 ID - id table value 816 """ 817 if not kwarg.has_key('iso'): 818 kwarg.setdefault('iso',True) 819 smiobj=Smile(smile=smi) 820 conntab={} 821 connlist={} 822 if kwarg['addH']: 823 smiobj.addH(mol=smiobj.getMol()) 824 if kwarg['iso']: 825 conntab['conntab'],conntab['symq']=smiobj.Smi2ConnTab(mol=smiobj.getMol()) 826 connlist['symq']=conntab['symq'] 827 else: 828 connlist=smiobj.getAtoms(mol=smiobj.getMol()) 829 dictobj=InputDB(**connlist) 830 ID=kwarg.get('ID',None) 831 AtomsID=self.Atoms(dictobj,logdebug,lowercasetablenames,ID=ID) 832 return AtomsID
833
834 - def getAtomsonIdFk(self,logdebug=False,tabcolvaldict={},lowercasetablenames=True,**kwarg):
835 """ 836 get Id/atoms dictionary for specified foreign key 837 INPUT: 838 logdebug - log debug in log file 839 tabcolvaldict - dict, table column/value dictionary 840 lowercasetablenames - boolean, lower case table names MySQL engine settings, default True 841 **kwarg - ID, IDlist, IDict - foreign key value 842 OUTPUT: 843 IDict - dictionary, id: atom list 844 """ 845 ### Settings ####################### 846 tablename='Atoms' 847 if lowercasetablenames: 848 tablename=tablename.lower() 849 #################################### 850 if not len(tabcolvaldict): 851 print 'Error, %s: Specify column name/value dictionary!' %tablename 852 sys.exit(1) 853 tabcol=tabcolvaldict.keys()# tablename list 854 855 if not self.isTabDesCorr(tablename,tabcol):#is table description correct 856 print 'Error, %s: Incorrect table description!' %tablename 857 self.log.error('%s: Incorrect table description',tablename) 858 sys.exit(1) 859 860 ###check if record exists in AtomStat on basis of ChemCompIdFk 861 tabcol=[] 862 if tabcolvaldict.has_key('ProtStatIdFk'): 863 ID=tabcolvaldict['ProtStatIdFk'] 864 if not isinstance(ID,list) or len(ID)!=1: 865 print 'Error, %s: Incorrect ID specification!' %tablename 866 self.log.error('%s: Incorrect ID specification',tablename) 867 sys.exit(1) 868 if not isinstance(ID[0],(int,long)):#id input ID is integer get ID, if long insert record 869 print 'Error, %s: Incorrect ID specification!' %tablename 870 self.log.error('%s: Incorrect ID specification',tablename) 871 sys.exit(1) 872 tabcol.append('ProtStatIdFk') 873 if not tabcolvaldict.has_key('ProtStatIdFk'): 874 print 'Error, %s: Specify needed input values!' %tablename 875 sys.exit(1) 876 877 ### check if record exists in Atoms 878 select='Select([table.'+ tablename +'.Id,table.'+tablename+'.AtomSeqNum,table.'+tablename+'.ElemTabIdFk], where=AND(table.'+tablename+'.'+tabcol[0]+'==ID))' 879 sql=queryBuilder(eval(select))#build query 880 result=self.executeQuery(sql)#execute query 881 if result:#if record exists in table 882 IDlist=numpy.array(numpy.zeros(len(result)),dtype=object) 883 for item in result: 884 IDlist[item['AtomSeqNum']-1]=[item['Id'],item['ElemTabIdFk']] 885 return list(IDlist) 886 else: 887 return 0
888
889 - def getConfStatonIdFk(self,logdebug=False,tabcolvaldict={},lowercasetablenames=True,**kwarg):
890 """ 891 get conformation list for specified foreign key 892 INPUT: 893 logdebug - log debug in log file 894 tabcolvaldict - dict, table column/value dictionary 895 lowercasetablenames - boolean, lower case table names MySQL engine settings, default True 896 **kwarg - ID, IDlist, IDict - foreign key value 897 OUTPUT: 898 IDict - dictionary, id: atom list 899 """ 900 ### Settings #################### 901 tablename='ConfStat' 902 if lowercasetablenames: 903 tablename=tablename.lower() 904 ################################# 905 if not len(tabcolvaldict): 906 print 'Error, %s: Specify column name/value dictionary!' %tablename 907 sys.exit(1) 908 tabcol=tabcolvaldict.keys()# tablename list 909 910 if not self.isTabDesCorr(tablename,tabcol):#is table description correct 911 print 'Error, %s: Incorrect table description!' %tablename 912 self.log.error('%s: Incorrect table description',tablename) 913 sys.exit(1) 914 915 ###check if record exists in ConfStat on basis of ChemCompIdFk 916 tabcol=[] 917 if tabcolvaldict.has_key('ProtStatIdFk'): 918 ID=tabcolvaldict['ProtStatIdFk'] 919 if not isinstance(ID,list) or len(ID)!=1: 920 print 'Error, %s: Incorrect ID specification!' %tablename 921 self.log.error('%s: Incorrect ID specification',tablename) 922 sys.exit(1) 923 if not isinstance(ID[0],(int,long)):#id input ID is integer get ID, if long insert record 924 print 'Error, %s: Incorrect ID specification!' %tablename 925 self.log.error('%s: Incorrect ID specification',tablename) 926 sys.exit(1) 927 tabcol.append('ProtStatIdFk') 928 if not tabcolvaldict.has_key('ProtStatIdFk'): 929 print 'Error, %s: Specify needed input values!' %tablename 930 sys.exit(1) 931 932 ### check if record exists in ConfStat 933 select='Select([table.'+ tablename +'.Id], where=AND(table.'+tablename+'.'+tabcol[0]+'==ID))' 934 sql=queryBuilder(eval(select))#build query 935 result=self.executeQuery(sql)#execute query 936 if result:#if record exists in table 937 IDlist=[item['Id'] for item in result] 938 return sorted(IDlist) 939 else: 940 return 0
941
942 - def Atoms(self,dictobj=None,logdebug=False,lowercasetablenames=True,**kwarg):
943 """ 944 Atoms table import 945 INPUT: 946 dictobj - shelve dictionary object 947 logdebug - log debug in log file 948 lowercasetablenames - boolean, lower case table names MySQL engine settings, default True 949 **kwarg - ID, IDlist, IDict - foreign key value 950 OUTPUT: 951 ID - id table value 952 """ 953 ########## sett1ings ####################################################### 954 tablename='Atoms'#table name 955 if lowercasetablenames: 956 tablename=tablename.lower() 957 tabcol=[ 'Id', 'AtomSeqNum','ProtStatIdFk','ElemTabIdFk']#table columname list 958 mapdict=['autoincrement','coords[0]', 'ID', 'coords[4]']# mapping dictionary object attributes 959 mapdict1=['autoincrement','symq[0]','ID','symq[1]']# 960 ############################################################################ 961 if not dictobj: 962 print 'Error, %s: Missing dictionary object!'%tablename 963 self.log.error('%s: Missing dictionary object',tablename) 964 sys.exit(1) 965 if not self.isTabDesCorr(tablename,tabcol):#is table description correct 966 print 'Error, %s: Incorrect table description!'%tablename 967 self.log.error('%s: Incorrect table description',tablename) 968 sys.exit(1) 969 970 if not dictobj.hasAttr('coords'):#check if has attribute 971 mapdict[1]=mapdict1[1] 972 mapdict[3]=mapdict1[3] 973 if logdebug: 974 self.log.debug('%s: Missing coords attribute: %s',tablename,dictobj.kwargs['filename']) 975 if not dictobj.hasAttr('symq'): 976 if logdebug: 977 self.log.debug('%s: Missing symq attribute: %s',tablename,dictobj.kwargs['filename']) 978 return 0 979 980 if not kwarg.has_key('anyflag'): 981 kwarg.setdefault('anyflag',False) 982 983 ID=kwarg.get('ID',None) 984 if ID:# must be list of length=1 985 if not isinstance(ID,list) or len(ID)!=1: 986 print 'Error, %s: Incorrect ID specification!'%tablename 987 if logdebug: 988 self.log.debug('%s: Incorrect ID specification',tablename) 989 return 0 990 if isinstance(ID[0],int):#if integer check if exists in table, if long insert 991 ## check if record exists in Atoms 992 select='Select([table.'+ tablename +'.'+tabcol[0]+',table.'+tablename+'.'+tabcol[1]+'], where=AND(table.'+tablename+'.'+tabcol[2]+'==ID))' 993 sql=queryBuilder(eval(select))#build query 994 result=self.executeQuery(sql)#execute query 995 if result:#if record exists in table 996 IDlist=numpy.array(numpy.zeros(len(result)),'int32') 997 for item in result: 998 IDlist[item[tabcol[1]]-1]=item[tabcol[0]] 999 return list(IDlist) 1000 IDlist=kwarg.get('IDlist',None) 1001 if IDlist:#must be list 1002 if not isinstance(IDlist,list): 1003 print 'Error, %s: Incorrect IDlist specification!'%tablename 1004 if logdebug: 1005 self.log.debug('%s: Incorrect IDlist specification',tablename) 1006 return 0 1007 1008 IDict=kwarg.get('IDict',None) 1009 if IDict:#must be dictionary 1010 if not isinstance(IDict,dict): 1011 print 'Error, %s: Incorrect IDict specification!'%tablename 1012 if logdebug: 1013 self.log.debug('%s: Incorrect IDict specification',tablename) 1014 return 0 1015 1016 ### DATA VALIDATION ### 1017 inrow=self.ligvalidrecord(dictobj,tablename,tabcol,mapdict,ID,IDlist,IDict,logdebug) 1018 if not inrow: 1019 return 0 1020 ## DATA INSERTION 1021 inskey,insval=insPrep(**inrow)#preparation for insertion to table 1022 if kwarg['anyflag']:#remove items with at least one False value 1023 insval=filter(checkitem,insval) 1024 if not all([checkitem(val) for val in insval]):#check all values are True 1025 return 0 1026 if not insval:##input list is empty 1027 return 0 1028 IDlist=self.insertrecord(tablename,inskey,insval,logdebug) 1029 if logdebug: 1030 self.log.debug('%s: Insertion %s done',tablename,dictobj.kwargs['filename']) 1031 return IDlist
1032
1033 - def ConfStat(self,dictobj=None,logdebug=False,lowercasetablenames=True,**kwarg):
1034 """ 1035 Conformation State table import 1036 INPUT: 1037 dictobj - shelve dictionary object 1038 logdebug - log debug in log file 1039 lowercasetablenames - boolean, lower case table names MySQL engine settings, default True 1040 **kwarg - ID, IDlist, IDict - foreign key value 1041 OUTPUT: 1042 ID - id table value 1043 """ 1044 ########## settings ######################################################## 1045 tablename='ConfStat'#table name 1046 if lowercasetablenames: 1047 tablename=tablename.lower() 1048 tabcol=['Id','ProtStatIdFk']#table columname list 1049 mapdict=['autoincrement','ID']# mapping dictionary object attributes 1050 ############################################################################ 1051 if not dictobj: 1052 print 'Error, %s: Missing dictionary object!'%tablename 1053 self.log.error('%s: Missing dictionary object',tablename) 1054 sys.exit(1) 1055 if not self.isTabDesCorr(tablename,tabcol):#is table description correct 1056 print 'Error, %s: Incorrect table description!'%tablename 1057 self.log.error('%s: Incorrect table descirption',tablename) 1058 sys.exit(1) 1059 1060 if not dictobj.hasAttr('coords'):#check if has attribute 1061 if logdebug: 1062 self.log.debug('Missing coords attribute: %s',dictobj.kwargs['filename']) 1063 return 0 1064 1065 if not kwarg.has_key('anyflag'): 1066 kwarg.setdefault('anyflag',False) 1067 1068 ID=kwarg.get('ID',None) 1069 if ID:# must be list of length=1 1070 if not isinstance(ID,list) or len(ID)!=1: 1071 print 'Error, %s: Incorrect ID specification!'%tablename 1072 if logdebug: 1073 self.log.debug('%s: Incorrect ID specification',tablename) 1074 return 0 1075 if isinstance(ID[0],int):#if integer skip, if long insert 1076 return 0 1077 1078 IDlist=kwarg.get('IDlist',None) 1079 if IDlist:#must be list 1080 if not isinstance(IDlist,list): 1081 print 'Error, %s: Incorrect IDlist specification!'%tablename 1082 if logdebug: 1083 self.log.debug('%s: Incorrect IDlist specification',tablename) 1084 return 0 1085 1086 IDict=kwarg.get('IDict',None) 1087 if IDict:#must be dicionary 1088 if not isinstance(IDict,dict): 1089 print 'Error, %s: Incorrect IDict specification!'%tablename 1090 if logdebug: 1091 self.log.debug('%s: Incorrect ID specification',tablename) 1092 return 0 1093 1094 ### DATA VALIDATION ### 1095 inrow=self.ligvalidrecord(dictobj,tablename,tabcol,mapdict,ID,IDlist,IDict,logdebug) 1096 if not inrow: 1097 return 0 1098 ### DATA INSERTION 1099 inskey,insval=insPrep(**inrow)#preparation for insertion to table 1100 if kwarg['anyflag']:#remove items with at least False value 1101 insval=filter(checkitem,insval) 1102 if not all([checkitem(val) for val in insval]):#check all values are True 1103 return 0 1104 if not insval:##input list is empty 1105 return 0 1106 IDlist=self.insertrecord(tablename,inskey,insval,logdebug) 1107 if logdebug: 1108 self.log.debug('%s: Insertion %s done',tablename,dictobj.kwargs['filename']) 1109 return IDlist
1110
1111 - def getAtomStatonIdFk(self,logdebug=False,tabcolvaldict={},lowercasetablenames=True,**kwarg):
1112 """ 1113 get atoms coordinate list for specified foreign key 1114 INPUT: 1115 logdebug - log debug in log file 1116 tabcolvaldict - dict, table column/value dictionary 1117 lowercasetablenames - boolean, lower case table names MySQL engine settings, default True 1118 **kwarg - ID, IDlist, IDict - foreign key value 1119 OUTPUT: 1120 IDict - dictionary, id: atom list 1121 """ 1122 ### Settings #################### 1123 tablename='AtomStat' 1124 if lowercasetablenames: 1125 tablename=tablename.lower() 1126 ################################# 1127 if not len(tabcolvaldict): 1128 print 'Error, %s: Specify column name/value dictionary!' %tablename 1129 sys.exit(1) 1130 tabcol=tabcolvaldict.keys()# tablename list 1131 if not self.isTabDesCorr(tablename,tabcol):#is table description correct 1132 print 'Error, %s: Incorrect table description!' %tablename 1133 self.log.error('%s: Incorrect table description',tablename) 1134 sys.exit(1) 1135 1136 tabcol=[] 1137 if tabcolvaldict.has_key('ConfStatIdFk'): 1138 IDlist=tabcolvaldict['ConfStatIdFk'] 1139 if not isinstance(IDlist,list): 1140 print 'Error, %s: Incorrect ID specification!' %tablename 1141 self.log.error('%s: Incorrect ID specification',tablename) 1142 sys.exit(1) 1143 1144 if not all([isinstance(item,(int,long)) for item in IDlist]): 1145 print 'Error, %s: Incorrect ID specification!' %tablename 1146 self.log.error('%s: Incorrect ID specification',tablename) 1147 sys.exit(1) 1148 tabcol.append('ConfStatIdFk') 1149 1150 if not tabcolvaldict.has_key('ConfStatIdFk'): 1151 print 'Error, %s: Specify needed input values!' %tablename 1152 sys.exit(1) 1153 1154 ### check if record exists in AtomStat ### 1155 IDict={} 1156 for iditem in IDlist: 1157 select='Select([table.'+ tablename +'.AtomsIdFk,table.'+tablename+'.posX,table.'+tablename+'.posY,table.'+tablename+'.posZ,table.'+tablename+'.Id], where=AND(table.'+tablename+'.'+tabcol[0]+'==iditem))' 1158 sql=queryBuilder(eval(select))#build query 1159 result=self.executeQuery(sql)#execute query 1160 if result: 1161 coords=[[item['AtomsIdFk'],item['posX'],item['posY'],item['posZ'],item['Id']] for item in result] 1162 IDict[iditem]=coords 1163 return IDict
1164
1165 - def AtomStat(self,dictobj=None,logdebug=False,lowercasetablenames=True,**kwarg):
1166 """ 1167 Atom State table import 1168 INPUT: 1169 dictobj - shelve dictionary object 1170 logdebug - log debug in log file 1171 lowercasetablenames - boolean, lower case table names MySQL engine settings, default True 1172 **kwarg - ID, IDlist, IDict - foreign key value 1173 OUTPUT: 1174 ID - id table value 1175 """ 1176 ########## settings ################################################################### 1177 tablename='AtomStat'#table name 1178 if lowercasetablenames: 1179 tablename=tablename.lower() 1180 tabcol= ['Id', 'posX', 'posY', 'posZ', 'ConfStatIdFk','AtomsIdFk']#table columname list 1181 mapdict=['autoincrement','coords[1]','coords[2]','coords[3]','ID', 'coords[0]']# mapping dictionary object attributes 1182 ####################################################################################### 1183 if not dictobj: 1184 print 'Error, %s: Missing dictionary object!'%tablename 1185 self.log.error('%s: Missing dictionary object',tablename) 1186 sys.exit(1) 1187 if not self.isTabDesCorr(tablename,tabcol):#is table description correct 1188 print 'Error, %s: Incorrect table description!' %tablename 1189 self.log.error('%s: Incorrect table description',tablename) 1190 sys.exit(1) 1191 1192 if not dictobj.hasAttr('coords'):#check if has attribute 1193 if logdebug: 1194 self.log.debug('%s: Missing coords attribute: %s',tablename,dictobj.kwargs['filename']) 1195 return 0 1196 1197 if not kwarg.has_key('anyflag'): 1198 kwarg.setdefault('anyflag',False) 1199 1200 ID=kwarg.get('ID',None) 1201 if ID:#must be list of length=1 1202 if not isinstance(ID,list) or len(ID)!=1: 1203 print 'Error, %s: Incorrect ID specification!'%tablename 1204 if logdebug: 1205 self.log.debug('%s: Incorrect ID specification',tablename) 1206 return 0 1207 if isinstance(ID[0],int):#if integer skip, if long insert 1208 return 0 1209 1210 IDlist=kwarg.get('IDlist',None) 1211 if IDlist:#must be list 1212 if not isinstance(IDlist,list): 1213 print 'Error, %s: Incorrect IDlist specification!'%tablename 1214 if logdebug: 1215 self.log.debug('%s: Incorrect IDlist specification',tablename) 1216 return 0 1217 1218 IDict=kwarg.get('IDict',None) 1219 if IDict:#must be dictionary 1220 if not isinstance(IDict,dict): 1221 print 'Error, %s: Incorrect IDict specification!'%tablename 1222 if logdebug: 1223 self.log.debug('%s: Incorrect IDict specification',tablename) 1224 return 0 1225 1226 ### DATA VALIDATION ### 1227 inrow=self.ligvalidrecord(dictobj,tablename,tabcol,mapdict,ID,IDlist,IDict,logdebug) 1228 if not inrow: 1229 return 0 1230 ### DATA INSERTION 1231 inskey,insval=insPrep(**inrow)#preparation for insertion to table 1232 if kwarg['anyflag']:#remove items with at least False value 1233 insval=filter(checkitem,insval) 1234 if not all([checkitem(val) for val in insval]):#check all values are True 1235 return 0 1236 if not insval:##input list is empty 1237 return 0 1238 IDlist=self.insertrecord(tablename,inskey,insval,logdebug) 1239 if logdebug: 1240 self.log.debug('%s: Insertion %s done',tablename,dictobj.kwargs['filename']) 1241 return IDlist
1242
1243 - def getElemTab(self,lowercasetablenames=True):
1244 """ 1245 get table of elements 1246 INPUT: 1247 OUTPUT: 1248 ID - id dictionary value 1249 """ 1250 ################################################ 1251 tablename='ElemTab'#table name 1252 if lowercasetablenames: 1253 tablename=tablename.lower() 1254 tabcol=['Id','AtomName','AtomSymbol','AtoMass'] 1255 ################################################ 1256 sql="SELECT * FROM %s;"%tablename 1257 ID={}#output dictionary 1258 result=self.executeQuery(sql)#execute query 1259 if result:#if record exists in table 1260 for resitem in result: 1261 ID[resitem[tabcol[0]]]=resitem[tabcol[2]] 1262 return ID
1263
1264 - def ElemTab(self,logdebug=False,lowercasetablenames=True,**dictobj):
1265 """ 1266 Elements Table import 1267 INPUT: 1268 dictobj - shelve dictionary object 1269 logdebug - log debug in log file 1270 lowercasetablenames - boolean, lower case table names MySQL engine settings, default True 1271 **dictobj - elements dictionary 1272 OUTPUT: 1273 ID - id table value 1274 """ 1275 ############# Settings ########################################################### 1276 tablename='ElemTab' 1277 if lowercasetablenames: 1278 tablename=tablename.lower() 1279 tabcol=['Id','AtomName','AtomSymbol','AtoMass'] 1280 mapdict=['ElemTab[0]','ElemTab[1]','ElemTab[2]','ElemTab[3]'] 1281 ################################################################################## 1282 if not self.isTabDesCorr(tablename,tabcol):#is table description correct 1283 print 'Error, %s: Incorrect table description!'%tablename 1284 self.log.error('%s: Incorrect table description',tablename) 1285 sys.exit(1) 1286 tabledesc=self.tabdesc[tablename]#get table description 1287 ### check if dictionary object has specified attributes 1288 if not dictobj.has_key('ElemTab'): 1289 if logdebug: 1290 self.log.debug('%s: Missing ElemTab attribute in dictionary',tablename) 1291 return 0 1292 1293 propval=dictobj.get('ElemTab',None)#get property values 1294 if not propval: 1295 if logdebug: 1296 self.log.debug('%s: Missing values for ElemTab attribute',tablename) 1297 return 0 1298 ID={}#output dictionary 1299 for propitem in propval:#iterate property list 1300 select='Select([table.'+ tablename +'.Id,table.'+tablename+'.AtomSymbol], where=AND(table.'+tablename+'.'+tabcol[1]+'==propitem[1],table.'+tablename+'.'+tabcol[2]+'==propitem[2]))' 1301 sql=queryBuilder(eval(select))#build query 1302 result=self.executeQuery(sql)#execute query 1303 if result:#if record exists in table 1304 for resitem in result: 1305 ID[resitem['Id']]=resitem['AtomSymbol'] 1306 continue 1307 else:#record NOT exists in table 1308 inrow={} 1309 for col in range(len(tabcol)):##iterate column name 1310 coldesc=tabledesc.getColumn(tabcol[col])#column description object 1311 1312 if not coldesc:#if no column description 1313 print 'Error, %s: Missing description for %s' %(tablename,tabcol[col]) 1314 self.log.error('%s: Missing descripton for %s',tablename,tabcol[col]) 1315 sys.exit(1) 1316 if coldesc.getAutoIncrem():#if column is auto_incremented 1317 continue 1318 if (coldesc.getFKey() and mapdict[col]=='ID'):##if column is foreign key 1319 inrow[tabcol[col]]=ID 1320 continue 1321 ### if column NOT auto_incremented and NOT foreign key 1322 attrname,attridx,attrtype,newattrname=geType(mapdict[col])##check mapping dictionary 1323 try: 1324 attrval=propitem[attridx]#get attrib value if exists 1325 except IndexError: 1326 attrval=None 1327 1328 if attrval:#attribute exists 1329 # DATA VALIDATION 1330 if isinstance(attrval,str):#if dicitonary attribute is str 1331 ### attribute validation 1332 exec('isvalid=TypeValidator(attrval,coldesc).'+coldesc.geType()+'()') 1333 if not isvalid: ##if attribute NOT valid 1334 if logdebug: 1335 self.log.error('%s: Value \'%s\' not valid for %s/%s in %s',tablename,attrval,coldesc.getField(),coldesc.geType(),dictobj.kwargs['filename']) 1336 inrow[tabcol[col]]=False 1337 continue 1338 inrow[tabcol[col]]=attrval#input row dictionary 1339 else: 1340 print 'Error, %s: Atomic value needed! %s'%(tablename,attrval) 1341 if logdebug: 1342 self.log.debug('%s: Atomic value needed! %s',tablename,attrval) 1343 inrow[tabcol[col]]=False 1344 continue 1345 else:#attribute NOT exist 1346 if coldesc.getNull():#if column accept NULL 1347 inrow[tabcol[col]]='NULL' 1348 else: 1349 print 'Error, %s: Column %s is not nullable!' %(tablename,tabcol[col]) 1350 if logdebug: 1351 self.log.debug('%s: Column %s is not nullable',tablename,tabcol[col]) 1352 inrow[tabcol[col]]=False 1353 continue 1354 ### DATA INSERTION 1355 inskey,insval=insPrep(**inrow)#preparation for insertion to table 1356 if not all([checkitem(val) for val in insval]):#check all values are True 1357 continue 1358 for insitem in insval:#iterate items in insertion values list 1359 insrecord=dict(zip(inskey,insitem))#insert record 1360 query='Insert(table.'+tablename+', [insrecord])' 1361 sql=queryBuilder(eval(query))#build query 1362 self.executeQuery(sql) 1363 select='Select([table.'+ tablename +'.Id,table.'+tablename+'.AtomSymbol], where=AND(table.'+tablename+'.'+tabcol[1]+'==propitem[1],table.'+tablename+'.'+tabcol[2]+'==propitem[2]))' 1364 sql=queryBuilder(eval(select))#build query 1365 result=self.executeQuery(sql)#execute query 1366 for resitem in result: 1367 ID[resitem['Id']]=resitem['AtomSymbol'] 1368 if logdebug: 1369 self.log.debug('%s: Insertion %s done',tablename,propitem[1]) 1370 return ID
1371
1372 - def getPropDef(self,lowercasetablenames=True):
1373 """ 1374 get property definition 1375 INPUT: 1376 class object 1377 OUTPUT: 1378 ID - id table dictionary 1379 """ 1380 ################################### 1381 tablename='PropDef' 1382 if lowercasetablenames: 1383 tablename=tablename.lower() 1384 tabcol=['Id','PropName','PropDesc'] 1385 #################################### 1386 sql='SELECT * FROM %s;'%tablename 1387 ID={}#output dictionary 1388 result=self.executeQuery(sql)#execute query 1389 if result:#if record exists in table 1390 for resitem in result: 1391 ID[resitem[tabcol[0]]]=resitem[tabcol[1]] 1392 return ID
1393
1394 - def UserPropDef(self,logdebug=False,lowercasetablenames=True,tabcolvaldict={}):
1395 """ 1396 Property Definition table import 1397 INPUT: 1398 dictobj - shelve dictionary object 1399 logdebug - log debug in log file 1400 lowercasetablenames - boolean, lower case table names MySQL engine settings, default True 1401 **dictobj - property definition dictionary 1402 OUTPUT: 1403 ID - id table value 1404 """ 1405 ############# Settings ######################### 1406 tablename='PropDef' 1407 if lowercasetablenames: 1408 tablename=tablename.lower() 1409 ############################################### 1410 if not len(tabcolvaldict): 1411 print 'Error, %s: Specify column name/value dictionary!'%tablename 1412 sys.exit(1) 1413 tabcol=tabcolvaldict.keys()# tablename list 1414 if not self.isTabDesCorr(tablename,tabcol):#is table description correct 1415 print 'Error, %s: Incorrect table description!'%tablename 1416 self.log.error('%s: Incorrect table description',tablename) 1417 sys.exit(1) 1418 propdefdict={} 1419 propval=[] 1420 tabcol=[] 1421 tabcol.append('Id') 1422 if tabcolvaldict.has_key('PropName'): 1423 tabcol.append('PropName') 1424 propval.append(tabcolvaldict['PropName']) 1425 if tabcolvaldict.has_key('PropDesc'): 1426 tabcol.append('PropDesc') 1427 propval.append(tabcolvaldict['PropDesc']) 1428 if not (tabcolvaldict.has_key('PropName')): 1429 print 'Error, %s: Specify needed input values!'%tablename 1430 sys.exit(1) 1431 propdefdict[tablename]=[propval] 1432 return self.PropDef(logdebug,lowercasetablenames,**propdefdict)
1433
1434 - def PropDef(self,logdebug=False,lowercasetablenames=True,**dictobj):
1435 """ 1436 Property Definition table import 1437 INPUT: 1438 logdebug - log debug in log file 1439 lowercasetablenames - boolean, lower case table names MySQL engine settings, default True 1440 **dictobj - property definition dictionary 1441 OUTPUT: 1442 ID - id table value 1443 """ 1444 ############# Settings ################################# 1445 tablename='PropDef' 1446 if lowercasetablenames: 1447 tablename=tablename.lower() 1448 tabcol=['Id','PropName','PropDesc'] 1449 mapdict=['autoincrement','PropDef[0]','PropDef[1]'] 1450 ####################################################### 1451 if not self.isTabDesCorr(tablename,tabcol):#is table description correct 1452 print 'Error, %s: Incorrect table description!' %tablename 1453 self.log.error('%s: Incorrect table description',tablename) 1454 sys.exit(1) 1455 tabledesc=self.tabdesc[tablename]#get table description 1456 ### check if dictionary object has specified attributes 1457 if not dictobj.has_key(tablename): 1458 if logdebug: 1459 self.log.debug('%s: Missing PropDef attribute in dictionary',tablename) 1460 return 0 1461 propval=dictobj.get(tablename,None)#get property values 1462 if not propval: 1463 if logdebug: 1464 self.log.debug('%s: Missing values for PropDef attribute',tablename) 1465 return 0 1466 1467 ID={}#output dictionary 1468 for propitem in propval:#iterate property list 1469 select='Select([table.'+ tablename +'.'+tabcol[0]+',table.'+tablename+'.'+tabcol[1]+'], where=AND(table.'+tablename+'.'+tabcol[1]+'==propitem[0]))' 1470 sql=queryBuilder(eval(select))#build query 1471 result=self.executeQuery(sql)#execute query 1472 if result:#if record exists in table 1473 for resitem in result: 1474 ID[resitem[tabcol[0]]]=resitem[tabcol[1]] 1475 continue 1476 else:#record NOT exists in table 1477 inrow={}#input row dictionary 1478 for col in range(len(tabcol)):##iterate column name 1479 coldesc=tabledesc.getColumn(tabcol[col])#column description object 1480 1481 if not coldesc:#if no column description 1482 print 'Error, %s: Missing description for %s' %(tablename,tabcol[col]) 1483 self.log.error('%s: Missing description for %s',tablename,tabcol[col]) 1484 sys.exit(1) 1485 if coldesc.getAutoIncrem():#if column is auto_incremented 1486 continue 1487 if (coldesc.getFKey() and mapdict[col]=='ID'):##if column is foreign key 1488 inrow[tabcol[col]]=ID 1489 continue 1490 ### if column NOT auto_incremented and NOT foreign key 1491 attrname,attridx,attrtype,newattrname=geType(mapdict[col])##check mapping dictionary 1492 try: 1493 attrval=propitem[attridx]#get attrib value if exists 1494 except IndexError: 1495 attrval=None 1496 if attrval:#attribute exists 1497 ### DATA VALIDATION 1498 if isinstance(attrval,str):#if dicitonary attribute is str 1499 ### attribute validation 1500 exec('isvalid=TypeValidator(attrval,coldesc).'+coldesc.geType()+'()') 1501 if not isvalid: ##if attribute NOT valid 1502 if logdebug: 1503 self.log.error('%s: Value \'%s\' not valid for %s/%s in %s',tablename,attrval,coldesc.Field(),coldesc.geType(),dictobj.kwargs['filename']) 1504 inrow[tabcol[col]]=False 1505 continue 1506 inrow[tabcol[col]]=attrval#input row dictionary 1507 else: 1508 print 'Error, %s: Atomic value needed! %s'%(tablename,attrval) 1509 if logdebug: 1510 self.log.debug('%s: Atomic value needed! %s',tablename,attrval) 1511 inrow[tabcol[col]]=False 1512 continue 1513 else:#attribute NOT exist 1514 if coldesc.getNull():#if column accept NULL 1515 inrow[tabcol[col]]='NULL' 1516 else: 1517 if logdebug: 1518 self.log.debug('%s: Column %s is not nullable',tablename,tabcol[col]) 1519 inrow[tabcol[col]]=False 1520 continue 1521 1522 ### DATA INSERTION 1523 inskey,insval=insPrep(**inrow)#preparation for insertion to table 1524 if not all([checkitem(val) for val in insval]):#check all values are True 1525 continue 1526 1527 for insitem in insval:#iterate items in insertion values list 1528 insrecord=dict(zip(inskey,insitem))#insert record 1529 query='Insert(table.'+tablename+', [insrecord])' 1530 sql=queryBuilder(eval(query))#build query 1531 self.executeQuery(sql) 1532 lastID=self.lastInsID() 1533 ID[lastID]=insrecord['PropName'] 1534 if logdebug: 1535 self.log.debug('%s: Insertion %s done',tablename,propitem[0]) 1536 return ID 1537
1538 - def UserProp2Tab(self,tablename=None,tabcol=None,smi=None,propval={},logdebug=False,lowercasetablenames=True,**kwarg):
1539 """ 1540 Property Table Import 1541 INPUT: 1542 tablename - tablename, str 1543 tabcol - table column, list of strings 1544 smi - str, smile string 1545 propval - dict, property value dictionary 1546 logdebug - logging flag to log debug info, default=True 1547 lowercasetablenames - boolean, lower case table names MySQL engine settings, default True 1548 **kwarg - IDcheck, ID, IDlist, IDict - foreign key value 1549 - anyflag - boolean, some specified in mapdict attributes are True 1550 OUTPUT: 1551 ID - id table value 1552 """ 1553 if not len(propval): 1554 print 'Error, %s: Specify column name/value dictionary!'%tablename 1555 sys.exit(1) 1556 if not smi: 1557 print 'Error, %s: Specify needed input values!' %tablename 1558 sys.exit(1) 1559 if lowercasetablenames: 1560 tablename=tablename.lower() 1561 1562 if not self.isTabDesCorr(tablename,tabcol):#is table description correct 1563 print 'Error, %s: Incorrect table description!'%tablename 1564 self.log.error('%s: Incorrect table description',tablename) 1565 sys.exit(1) 1566 1567 if not kwarg.has_key('anyflag'): 1568 kwarg.setdefault('anyflag',False) 1569 if not kwarg.has_key('IDcheck'): 1570 kwarg.setdefault('IDcheck',False) 1571 mapdict=['autoincrement', 0,'IDict', 'ID']# mapping dictionary object attributes 1572 if smi: 1573 smiobj=Smile(smile=smi) 1574 smi=smiobj.CanSmi(mol=smiobj.getMol(),iso=True,kek=False,verbose=True)#create isomeric smile 1575 property={} 1576 property['property']=propval 1577 dictobj=InputDB(**property) 1578 1579 propstr='property{' 1580 for key in propval.keys(): 1581 propstr+='\''+key+ '\',' 1582 propstr=propstr[:-1]+'}' 1583 mapdict[1]=propstr 1584 ### get property dictionary ### 1585 IDict=self.getPropDef(lowercasetablenames=lowercasetablenames) 1586 1587 if 'ChemCompIdFk' in tabcol: 1588 query='Select([table.ChemComp.Id], where=AND(table.ChemComp.IsoSmi==smi))' 1589 1590 elif 'ProtStatIdFk' in tabcol: 1591 query='Select([table.ProtStat.Id], where=AND(table.ProtStat.IsoSmi==smi))' 1592 else: 1593 print 'Error: You are not allowed to insert to %s table!' %tablename 1594 sys.exit(1) 1595 1596 sql=queryBuilder(eval(query))#build query 1597 result=self.executeQuery(sql) 1598 if not result: 1599 print 'Error: Id not available!' 1600 sys.exit(1) 1601 ID=result[0]['Id'] 1602 ID=[ID] 1603 return self.Prop2Tab(tablename=tablename,tabcol=tabcol,mapdict=mapdict,dictobj=dictobj,logdebug=logdebug,lowercasetablenames=lowercasetablenames,anyflag=kwarg['anyflag'],IDCheck=kwarg['IDcheck'],IDict=IDict,ID=ID)
1604
1605 - def getProponIdFk(self,tablename=None,tabcol=None,tabcolvaldict=None,logdebug=False,lowercasetablenames=True,idflag=False,**kwarg):
1606 """ 1607 get specified property from property table 1608 INPUT: 1609 tablename - tablename, str 1610 tabcol - table column, list of strings 1611 tabcolvaldict - dict, table column/value dictionary 1612 logdebug - logging flag to log debug info, default=True 1613 lowercasetablenames - boolean, lower case table names MySQL engine settings, default True 1614 idflag - boolean, if true output dictionary format {id:[property_value,property_name]} 1615 false {propertyname_tablename_id: property_value}, default false 1616 **kwarg - IDcheck, ID, IDlist, IDict - foreign key value 1617 - anyflag - boolean, some specified in mapdict attributes are True 1618 OUTPUT: 1619 IDict - dict, tag dictionary {tagnema_tablename_id:value} 1620 """ 1621 if not tablename: 1622 print 'Error, %s: Missing tablename!'%tablename 1623 self.log.error('%s: Missing tablename',tablename) 1624 sys.exit(1) 1625 1626 if not len(tabcolvaldict): 1627 print 'Error, %s: Specify column name/value dictionary!' %tablename 1628 sys.exit(1) 1629 1630 if lowercasetablenames: 1631 tablename=tablename.lower() 1632 1633 if not self.isTabDesCorr(tablename,tabcol):#is table description correct 1634 print 'Error, %s: Incorrect table description!'%tablename 1635 self.log.error('%s: Incorrect table description',tablename) 1636 sys.exit(1) 1637 1638 ID=kwarg.get('ID',None) 1639 if ID:#must be list of length=1 1640 if not isinstance(ID,list) or len(ID)!=1: 1641 print 'Error, %s: Incorrect ID specification!'%tablename 1642 if logdebug: 1643 self.log.debug('%s: Incorrect ID specification',tablename) 1644 return 0 1645 1646 propidfk=tabcolvaldict.keys()#get property foreign keys 1647 IDict={} 1648 for propid in propidfk:#iterate property list 1649 select='Select([table.'+ tablename+'.'+tabcol[0]+',table.'+tablename+'.'+tabcol[1]+',table.'+tablename+'.'+tabcol[2]+'], where=AND(table.'+tablename+'.'+tabcol[2]+'==propid,table.'+tablename+'.'+tabcol[3]+'==ID[0]))' 1650 sql=queryBuilder(eval(select))#build query 1651 result=self.executeQuery(sql)#execute query 1652 if result:#if record exists in table 1653 for resitem in result: 1654 if idflag: 1655 ### id: [propval, propdef] 1656 IDict[resitem['Id']]=[resitem['PropVal'],tabcolvaldict[resitem['PropDefIdFk']]] 1657 else: 1658 ### key: propname_tablename_id ### 1659 dictkey='%s_%s_%s' %(tabcolvaldict[resitem['PropDefIdFk']],tablename,resitem['Id']) 1660 IDict[dictkey]=resitem['PropVal'] 1661 return IDict
1662
1663 - def Prop2Tab(self,tablename=None,tabcol=None,mapdict=None,dictobj=None,logdebug=False,lowercasetablenames=True,**kwarg):
1664 """ 1665 Property Table Import 1666 INPUT: 1667 tablename - tablename, str 1668 tabcol - table column, list of strings 1669 mapdict - mapping dictionary, list of strings 1670 logdebug - logging flag to log debug info, default=True 1671 dictobj - sheleve dictionary object 1672 lowercasetablenames - boolean, lower case table names MySQL engine settings, default True 1673 **kwarg - ID, IDlist, IDict - foreign key value 1674 IDCheck - boolean, check if specified ID is long or integer, default False 1675 - anyflag - boolean, some specified in mapdict attributes are True 1676 OUTPUT: 1677 ID - id table value 1678 """ 1679 if not dictobj: 1680 print 'Error, %s: Missing dictionary object!'%tablename 1681 self.log.error('%s: Missing dictionary object',tablename) 1682 sys.exit(1) 1683 if not tablename: 1684 print 'Error, %s: Missing tablename!'%tablename 1685 self.log.error('%s: Missing tablename',tablename) 1686 sys.exit(1) 1687 if not mapdict: 1688 print 'Error, %s: Missing mapping dictionary!'%tablename 1689 self.log.error('%s: Missing mapping dictionary',tablename) 1690 sys.exit(1) 1691 1692 if lowercasetablenames: 1693 tablename=tablename.lower() 1694 1695 if not self.isTabDesCorr(tablename,tabcol):#is table description correct 1696 print 'Error, %s: Incorrect table description!'%tablename 1697 self.log.error('%s: Incorrect table description',tablename) 1698 sys.exit(1) 1699 1700 if not dictobj.hasAttr('property'):#check if has attribute 1701 if logdebug: 1702 self.log.debug('%s: Missing property attribute in %s',tablename,dictobj.kwargs['filename']) 1703 return 0 1704 1705 if not kwarg.has_key('anyflag'): 1706 kwarg.setdefault('anyflag',False) 1707 if not kwarg.has_key('IDcheck'): 1708 kwarg.setdefault('IDcheck',False) 1709 1710 ID=kwarg.get('ID',None) 1711 if ID:#must be list of length=1 1712 if not isinstance(ID,list) or len(ID)!=1: 1713 print 'Error, %s: Incorrect ID specification!'%tablename 1714 if logdebug: 1715 self.log.debug('%s: Incorrect ID specification',tablename) 1716 return 0 1717 if kwarg['IDcheck']:#check ID type 1718 if isinstance(ID[0],int): 1719 return 0 1720 IDlist=kwarg.get('IDlist',None) 1721 if IDlist:#must be list 1722 if not isinstance(IDlist,list): 1723 print 'Error, %s: Incorrect IDlist specification!'%tablename 1724 if logdebug: 1725 self.log.debug('%s: Incorrect IDlist specification',tablename) 1726 return 0 1727 1728 IDict=kwarg.get('IDict',None) 1729 if IDict:#must be dictionary 1730 if not isinstance(IDict,dict): 1731 print 'Error, %s: Incorrect IDict specification!'%tablename 1732 if logdebug: 1733 self.log.debug('%s: Incorrect IDict specification',tablename) 1734 return 0 1735 ## DATA VALIDATION ### 1736 inrow=self.ligvalidrecord(dictobj,tablename,tabcol,mapdict,ID,IDlist,IDict,logdebug) 1737 if not inrow: 1738 return 0 1739 ### DATA INSERTION 1740 inskey,insval=insPrep(**inrow)#preparation for insertion to table 1741 if kwarg['anyflag']:#remove items with at least False value 1742 insval=filter(checkitem,insval) 1743 if not all([checkitem(val) for val in insval]):#check all values are True 1744 return 0 1745 if not insval:##input list is empty 1746 return 0 1747 IDlist=self.insertrecord(tablename,inskey,insval,logdebug) 1748 if logdebug: 1749 self.log.debug('%s: Insertion %s done',tablename,dictobj.kwargs['filename']) 1750 1751 return IDlist
1752
1753 - def insertrecord(self,tablename,inskey,insval,logdebug):
1754 """ 1755 insert record into specified table 1756 INPUT: 1757 tablename - str, table name 1758 inskey - list, record name list 1759 insval - list, record value list 1760 OUTPUT: 1761 IDlist - last inserted Id list 1762 """ 1763 IDlist=[]#output list 1764 for insitem in insval:#iterate items in insertion values list 1765 insrecord=dict(zip(inskey,insitem))#insert record 1766 query='Insert(table.'+tablename+', [insrecord])' 1767 sql=queryBuilder(eval(query))#build query 1768 self.executeQuery(sql) 1769 lastID=UserQuery().getMaxId(tabname=tablename)#get Id 1770 result=self.executeQuery(lastID)#execute query 1771 if result: 1772 maxID=result[0].get('maxId',None) 1773 if maxID: 1774 IDlist.append(long(maxID)) 1775 else: 1776 print 'Error: Id not available!' 1777 sys.exit(1) 1778 if logdebug: 1779 self.log.debug('table: %s, key: %s value, %s: Insertion done.',tablename,inskey, insitem) 1780 return IDlist
1781
1782 - def validrecord(self,tablename,tabcol,indict,logdebug=False):
1783 """ 1784 validate record 1785 INPUT: 1786 tablename - str, table name 1787 tabcol - list, column list 1788 indict - dict, input dictionary 1789 OUTPUT: 1790 inrow - dict, validated input dictionary 1791 """ 1792 inrow={}#insert row dictionary 1793 tabledesc=self.macmoltabdesc[tablename]#get table description 1794 for col in range(len(tabcol)):##iterate column name 1795 coldesc=tabledesc.getColumn(tabcol[col])#column description object 1796 if not coldesc:#if no column description 1797 print 'Error, %s: Missing description for %s' %(tablename,tabcol[col]) 1798 self.log.error('%s: Missing description for %s',tablename,tabcol[col]) 1799 sys.exit(1) 1800 if coldesc.getAutoIncrem():#if column is auto_incremented 1801 continue 1802 1803 attrval=indict.get(tabcol[col],None)#get attribute values 1804 if attrval: 1805 if isinstance(attrval,(str,int,float,long)):#if dictionary attribute is string 1806 ## attribute validation 1807 exec('isvalid=TypeValidator(attrval,coldesc).'+coldesc.geType()+'()') 1808 if not isvalid: ##if attribute NOT valid 1809 if logdebug: 1810 self.log.error('%s: Value \'%s\' not valid for %s/%s',tablename,attrval,coldesc.getField(),coldesc.geType()) 1811 inrow[tabcol[col]]=False 1812 continue 1813 inrow[tabcol[col]]=attrval#input row dictionary 1814 1815 elif isinstance(attrval,(list,tuple)):#if dictionary attribute is list 1816 templist=[] 1817 for itemval in attrval: 1818 ##data validation 1819 exec('isvalid=TypeValidator(str(itemval),coldesc).'+coldesc.geType()+'()') 1820 if not isvalid: ##if attribute NOT valid 1821 if logdebug: 1822 self.log.error('%s: Value \'%s\' not valid for %s/%s',tablename,itemval,coldesc.getField(),coldesc.geType(),dictobj.kwargs['filename']) 1823 templist.append(False) 1824 continue 1825 templist.append(itemval) 1826 inrow[tabcol[col]]=templist 1827 1828 else:#attribute NOT exists 1829 if not coldesc.getNull():#if column accept NULL 1830 if logdebug: 1831 self.log.debug('%s: Column %s is not nullable',tablename,tabcol[col]) 1832 inrow[tabcol[col]]=False 1833 continue 1834 return inrow
1835
1836 - def ligvalidrecord(self,dictobj,tablename,tabcol,mapdict,ID=None,IDlist=None,IDict=None,logdebug=False):
1837 """ 1838 validate record 1839 INPUT: 1840 dictobj - dictionary object 1841 tablename - str, table name 1842 tabcol - list, column list 1843 mapdict - dict, input dictionary 1844 ID - int, ID number 1845 IDlist - list, ID list 1846 IDict - dict, ID dictionary 1847 logdebug - boolean, debug logging, default False 1848 OUTPUT: 1849 inrow - dict, validated input dictionary 1850 """ 1851 inrow={} #input row dictionary 1852 tabledesc=self.tabdesc[tablename]#get table description 1853 for col in range(len(tabcol)):##iterate column name 1854 coldesc=tabledesc.getColumn(tabcol[col])#column description object 1855 if not coldesc:#if no column description 1856 print 'Error, %s: Missing description for %s' %(tablename,tabcol[col]) 1857 self.log.error('%s: Missing description for %s',tablename,tabcol[col]) 1858 sys.exit(1) 1859 if coldesc.getAutoIncrem():#if column is auto_incremented 1860 continue 1861 if (coldesc.getFKey() and mapdict[col]=='ID'):##if column is foreign key 1862 inrow[tabcol[col]]=ID 1863 continue 1864 1865 if (coldesc.getFKey() and mapdict[col]=='IDict'): 1866 try: 1867 propname=attrlist 1868 except NameError: 1869 if logdebug: 1870 self.log.error('%s: Missing attribute in property for %s or incorrect column order',tablename,dictobj.kwargs['filename']) 1871 return 0 1872 except UnboundLocalError: 1873 if logdebug: 1874 self.log.error('%s: Missing attribute in property for %s or incorrect column order',tablename,dictobj.kwargs['filename']) 1875 return 0 1876 propIdFklist=[findDictKey(propitem,IDict) for propitem in propname] 1877 inrow[tabcol[col]]=propIdFklist 1878 continue 1879 1880 if (coldesc.getFKey() and mapdict[col]=='IDlist'): 1881 inrow[tabcol[col]]=IDlist 1882 continue 1883 1884 ### if column NOT auto_incremented and NOT foreign key 1885 attrname,attridx,attrtype,newattrname=geType(mapdict[col])##check mapping dictionary 1886 attrval=dictobj.kwargs.get(attrname,None)#get attrib value if exists 1887 if attrval:#attribute exists 1888 ### DATA VALIDATION 1889 if isinstance(attrval,eval(attrtype)): 1890 ###check type consistence between shelve dictionary and mapping dictionary 1891 if isinstance(attrval,(str,int,float,long)):#if dicitonary attribute is str 1892 ### attribute validation 1893 if newattrname:#if newname is specified 1894 attrlist=[newattrname] 1895 else:#newname NOT defined 1896 attrlist=[attrname] 1897 exec('isvalid=TypeValidator(str(attrval),coldesc).'+coldesc.geType()+'()') 1898 if not isvalid: ##if attribute NOT valid 1899 if logdebug: 1900 self.log.error('%s: Value \'%s\' not valid for %s/%s in %s',tablename,attrval,coldesc.getField(),coldesc.geType(),dictobj.kwargs['filename']) 1901 inrow[tabcol[col]]=False 1902 continue 1903 inrow[tabcol[col]]=attrval#input row dictionary 1904 1905 if isinstance(attrval,list):#if dicitonary attribute is list 1906 templist=[] 1907 if newattrname:#if newname is specified 1908 attrlist=[newattrname] 1909 else:#newname NOT defined 1910 attrlist=[attrname] 1911 for item in attrval:#iterate list 1912 ###data validation 1913 try:#if item value NOT exists 1914 itemval=item[attridx] 1915 except IndexError: 1916 if logdebug: 1917 self.log.debug('%s: Missing value in list for %s',tablename,dictobj.kwargs['filename']) 1918 return 0 1919 exec('isvalid=TypeValidator(str(itemval),coldesc).'+coldesc.geType()+'()') 1920 if not isvalid: ##if attribute NOT valid 1921 if logdebug: 1922 self.log.error('%s: Value \'%s\' not valid for %s/%s in %s',tablename,itemval,coldesc.getField(),coldesc.geType(),dictobj.kwargs['filename']) 1923 templist.append(False) 1924 continue 1925 if coldesc.getFKey() and IDlist: 1926 templist.append(IDlist[itemval-1]) 1927 else: 1928 templist.append(itemval) 1929 inrow[tabcol[col]]=templist 1930 1931 if isinstance(attrval,dict):#if dictionary attribute is dictionary 1932 attrlist=[attritem for attritem in attridx.split(',')]#get attribute list 1933 templist=[] 1934 for attritem in attrlist:##iterate attribute list 1935 attritem=attritem.strip('\'')#remove quota 1936 attritemval=attrval.get(attritem,None)#get attrib value if exists 1937 1938 if attritemval:#if exists 1939 exec('isvalid=TypeValidator(attritemval,coldesc).'+coldesc.geType()+'()') 1940 if not isvalid: ##if attribute NOT valid 1941 if logdebug: 1942 self.log.error('%s: Value \'%s\' not valid for %s/%s in %s',tablename,attritemval,coldesc.getField(),coldesc.geType(),dictobj.kwargs['filename']) 1943 templist.append(False) 1944 continue 1945 else: 1946 templist.append(attritemval) 1947 else:##value NOT exists 1948 if logdebug: 1949 self.log.debug('%s: Missing %s attribute for %s',tablename,attritem,dictobj.kwargs['filename']) 1950 templist.append(False) 1951 if newattrname:#new attribute name 1952 newattrlist=[attritem for attritem in newattrname.split(',')]#get attribute list 1953 if len(newattrlist)==len(attrlist): 1954 attrlist=newattrlist 1955 else: 1956 print 'Error, %s: Number of attributes must agree!'%tablename 1957 self.log.error('%s: Number of attributes must agree',tablename) 1958 sys.exit(1) 1959 inrow[tabcol[col]]=templist 1960 1961 else: 1962 print 'Error, %s: Incorrect type specification for %s!'%(tablename,mapdict[col]) 1963 self.log.error('%s: Incorrect mapping type specification for %s',tablename,mapdict[col]) 1964 sys.exit(1) 1965 else:#attribute NOT exists 1966 if coldesc.getNull():#if column accept NULL 1967 inrow[tabcol[col]]='NULL' 1968 else: 1969 if logdebug: 1970 self.log.debug('%s: Column %s is not nullable',tablename,tabcol[col]) 1971 inrow[tabcol[col]]=False 1972 continue 1973 return inrow
1974
1975 - def uservalidrecord(self,tablename,tabcol,tabcolvaldict,ID=None,logdebug=False):
1976 """ 1977 validate record 1978 INPUT: 1979 tablename - str, table name 1980 tabcol - list, column list 1981 tabcolvaldict - dict, input dictionary 1982 ID - int, ID number 1983 IDlist - list, ID list 1984 IDict - dict, ID dictionary 1985 logdebug - boolean, debug logging, default False 1986 OUTPUT: 1987 inrow - dict, validated input dictionary 1988 """ 1989 inrow={}#input row dictionary 1990 tabledesc=self.tabdesc[tablename]#get table description 1991 for col in range(len(tabcol)):##iterate column name 1992 coldesc=tabledesc.getColumn(tabcol[col])#column description object 1993 if not coldesc:#if no column description 1994 print 'Error, %s: Missing description for %s' %(tablename,tabcol[col]) 1995 self.log.error('%s: Missing description for %s',tablename,tabcol[col]) 1996 sys.exit(1) 1997 if coldesc.getAutoIncrem():#if column is auto_incremented 1998 continue 1999 if (coldesc.getFKey() and ID):##if column is foreign key 2000 inrow[tabcol[col]]=ID 2001 continue 2002 ## if column NOT auto_incremented and NOT foreign key 2003 attrname,attridx,attrtype,newattrname=geType(tabcol[col])##check mapping dictionary 2004 attrval=tabcolvaldict.get(attrname,None)#get attrib value if exists 2005 if attrval:#attribute exists 2006 ## DATA VALIDATION 2007 if isinstance(attrval,eval(attrtype)): 2008 ##check type consistency between shelve dictionary and mapping dictionary 2009 2010 if isinstance(attrval,str):#if dictionary attribute is str 2011 ## attribute validation 2012 exec('isvalid=TypeValidator(attrval,coldesc).'+coldesc.geType()+'()') 2013 if not isvalid: ##if attribute NOT valid 2014 inrow[tabcol[col]]=False 2015 continue 2016 inrow[tabcol[col]]=attrval#input row dictionary 2017 2018 elif isinstance(attrval,list):#if dictionary attribute is list 2019 templist=[] 2020 for item in attrval: 2021 ##data validation 2022 try:#if item value NOT exists 2023 itemval=item[attridx] 2024 except IndexError: 2025 if logdebug: 2026 self.log.debug('%s: Missing value in list for %s',tablename,tabcolvaldict['IsoSmi']) 2027 return 0 2028 exec('isvalid=TypeValidator(str(itemval),coldesc).'+coldesc.geType()+'()') 2029 if not isvalid: ##if attribute NOT valid 2030 templist.append(False) 2031 continue 2032 templist.append(itemval) 2033 inrow[tabcol[col]]=templist 2034 else: 2035 print 'Error: Incorrect mapping type specification!' 2036 self.log.error('%s: Incorrect mapping type specification for %s',tablename,tabcol[col]) 2037 sys.exit(1) 2038 else: #attribute NOT exist 2039 if coldesc.getNull():#if column accept NULL 2040 inrow[tabcol[col]]='NULL' 2041 else: 2042 if logdebug: 2043 self.log.debug('%s: Column %s in is not nullable',tablename,tabcol[col]) 2044 inrow[tabcol[col]]=False 2045 continue 2046 return inrow
2047 ############# End of class ######################################### 2048 ############# Supportive funcions ################################## 2049
2050 -def findDictKey(item,dictionary):
2051 """- 2052 find dictionary key 2053 INPUT: 2054 item- list item 2055 dictionary 2056 OUTPUT: 2057 list of keys 2058 """ 2059 item=item.strip('\'') 2060 for key,value in dictionary.iteritems(): 2061 if item==value: 2062 return key 2063 return False
2064
2065 -def checkitem(iterable):
2066 """ 2067 Return True if any element of the iterable is true. Build-in Python 2.5 2068 INPUT: 2069 iterable 2070 OUTPUT: 2071 boolean 2072 """ 2073 for element in iterable: 2074 if (isinstance(element,bool) or element is None): 2075 return False 2076 return True
2077
2078 -def all(iterable):
2079 """ 2080 Return True if any element of the iterable is true. Build-in Python 2.5 2081 INPUT: 2082 iterable 2083 OUTPUT: 2084 boolean 2085 """ 2086 for element in iterable: 2087 if not element: 2088 return False 2089 return True
2090 -def geType(varname):
2091 """ 2092 get type of variable 2093 INPUT: 2094 varname - variable name 2095 OUTPUT: 2096 name - variable name 2097 idx - index 2098 type - variable type 2099 """ 2100 try: 2101 idxo=varname.index('[') 2102 except ValueError: 2103 try: 2104 idxo=varname.index('{') 2105 except ValueError: 2106 try: 2107 idxo=varname.index(':') 2108 except ValueError: 2109 newname=None 2110 name=varname 2111 idx=None 2112 vartype='(str,int,long,float)' 2113 else: 2114 name=varname[:idxo] 2115 newname=varname[idxo+1:] 2116 vartype='(str,int,long,float)' 2117 idx=None 2118 2119 else:#opening { exists 2120 try: 2121 idxc=varname.index('}') 2122 except ValueError: 2123 print 'Error: No closing bracket!' 2124 sys.exit(1) 2125 else:#closing } exists 2126 name=varname[:idxo] 2127 2128 if idxo+1==idxc:#value between brackets exists 2129 print 'Error: Specify value between brackets!' 2130 sys.exit(1) 2131 idx=varname[idxo+1:idxc] 2132 vartype='dict' 2133 try: 2134 idxo=varname.index(':') 2135 except ValueError: 2136 newname=None 2137 else: 2138 newname=varname[idxo+1:] 2139 2140 else:# opening [ exists 2141 try: 2142 idxc=varname.index(']') 2143 except ValueError: 2144 print 'Error: No closing bracket!' 2145 sys.exit(1) 2146 else:#closing ] exists 2147 name=varname[:idxo] 2148 if idxo+1==idxc:#value between brackets exists 2149 print 'Error: Specify value between brackets!' 2150 sys.exit(1) 2151 idx=varname[idxo+1:idxc] 2152 vartype='list' 2153 try:##value is integer 2154 idx=int(idx) 2155 except ValueError,e: 2156 print 'Error: %s!' %e 2157 sys.exit(1) 2158 try: 2159 idxo=varname.index(':') 2160 except ValueError: 2161 newname=None 2162 else: 2163 newname=varname[idxo+1:] 2164 2165 return name,idx,vartype,newname
2166
2167 -def changeType(item):
2168 """ 2169 change item type to list 2170 INPUT: 2171 item 2172 OUTPUT: 2173 item 2174 """ 2175 if isinstance(item,(int,float,str,long,bool,complex)): 2176 item=[item] 2177 return item
2178
2179 -def checkLength(item):
2180 """ 2181 check length of item 2182 INPUT: 2183 item 2184 OUTPUT: 2185 item length 2186 """ 2187 return len(item)
2188
2189 -def extLength(item,length):
2190 """ 2191 extend lenght of item to lengthest item 2192 INPUT: 2193 item 2194 length 2195 OUTPUT" 2196 item 2197 """ 2198 if len(item)==1: 2199 return length*item 2200 else: 2201 return item
2202
2203 -def insPrep(**kwargs):
2204 """ 2205 insert preparation function 2206 INPUT: 2207 **kwargs - dictionary 2208 OUTPUT: 2209 keys - list of keys 2210 value - list of values 2211 """ 2212 keys=kwargs.keys() 2213 values=kwargs.values() 2214 values=map(lambda value: changeType(value),values)#change type of values to list 2215 maxlen=max(map(lambda value: checkLength(value),values))#check length of values 2216 values=map(lambda value: extLength(value,maxlen),values)#increase length to biggest one 2217 if len(values)==1: 2218 return keys,values 2219 values=map(None,*values)#join values 2220 return keys, values
2221 ################## End of class ######################################################## 2222 ############## MAIN ################################################################ 2223 ############ Example of usage ########################################################## 2224 if __name__=='__main__': 2225 pass 2226