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

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

  1  #!/usr/bin/env python 
  2  ################################################# 
  3  # compare SDict2DB.py                           # 
  4  # compare shelve dictionary objects to DataBase # 
  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.ImportData.Data2DB.SDict2DB import SDict2DB 
 20      from MoSTBioDat.DataBase.ImportData.Data2DB.InserTables import geType, findDictKey,checkitem,all,changeType,checkLength,extLength,insPrep 
 21      from MoSTBioDat.DataBase.Query.SQLBuilder.sqlbuilder import * 
 22      from MoSTBioDat.DataBase.Query.SQLBuilder.QueryBuilder import queryBuilder 
 23      from MoSTBioDat.DataBase.Query.UserQuery.UserQuery import UserQuery 
 24  except ImportError,e: 
 25      print 'Error: %s' %e 
 26      sys.exit(1) 
 27  ###############  class ########################## 
28 -class compareSDict2DB(SDict2DB):
29 """ 30 Compare data from ZINC database and shelve dictionary 31 INPUT: 32 host - string, host to connect 33 user - string, user to connect as 34 passwd - string, password to use 35 db - string, database to use 36 port - integer, TCP/IP port to connect 37 log - boolen, logging flag 38 unix_socket - string, location of unix_socket to use 39 conv - conversion dictionary, see MySQLdb.converters 40 connect_timeout - number of seconds to wait before the connection attempt fails. 41 compress - if set, compression is enabled 42 named_pipe - if set, a named pipe is used to connect (Windows only) 43 init_command - command which is run once the connection is created 44 read_default_file - file from which default client values are read 45 read_default_group - configuration group to use from the default file 46 cursorclass - class object, used to create cursors (keyword only) 47 use_unicode - if True, text-like columns are returned as unicode objects 48 using the connection's character set. Otherwise, text-like 49 columns are returned as strings. columns are returned as 50 normal strings. Unicode objects will always be encoded to 51 the connection's character set regardless of this setting. 52 charset - if supplied, the connection character set will be changed 53 to this character set (MySQL-4.1 and newer). This implies 54 use_unicode=True. 55 sql_mode - if supplied, the session SQL mode will be changed to this 56 setting (MySQL-4.1 and newer). For more details and legal 57 values, see the MySQL documentation. 58 client_flag - integer, flags to use or 0 59 (see MySQL docs or constants/CLIENTS.py) 60 ssl - dictionary or mapping, contains SSL connection parameters; 61 see the MySQL documentation for more details 62 (mysql_ssl_set()). If this is set, and the client does not 63 support SSL, NotSupportedError will be raised. 64 local_infile - integer, non-zero enables LOAD LOCAL INFILE; zero disables 65 format - string format for log handler 66 filter - filter object from logger object 67 datefmt - data/time format 68 path - directory path to log file 69 filename - log filename, default log 70 filemode - mode to open log file, default='a' 71 level - set root logger level to specified level 72 logfilelevel- set level to log file 73 cache - create cache for query, default=True 74 scheme2file - Boolen - save database scheme to shelve file 75 76 dictpath - str - path to dictionary 77 dictfilename - str - dictionary filename 78 dbfile - str - database file path 79 temporary - str - path to temporary dictionary file 80 log - log flag, default=False 81 82 OUTPUT: 83 class object 84 """
85 - def __init__(self,dbfilepath=None,host='localhost',db='Ligand',user=None,passwd=None,port=3306,log=False,**kwargs):
86 SDict2DB.__init__(self,dbfilepath,host,db,user,passwd,port,log,**kwargs)
87
88 - def compSDict2Tab(self,lowercasetablenames=True):
89 """ 90 compare shelve dictionary contents and specified database tables 91 INPUT: 92 lowercasetablenames - boolean, lower case table names MySQL engine settings, default True 93 class object 94 OUTPUT: 95 string 96 """ 97 PropDefID=self.getPropDef() 98 if not PropDefID: 99 print 'Error: Incorrect import to PropDef table' 100 self.log.error('Incorrect import to PropDef table') 101 sys.exit(1) 102 103 self.tabdesc=self.genTables() 104 self.openDict() 105 if self.openDict(): 106 nkeys=0 107 ndict=self.getLength() 108 print 'Number of compounds in shelve dictionary: %s '%(ndict) 109 if len(self.dbinput): 110 for dictobj in self.retDict().iteritems():# tuple (key,value) 111 nkeys+=1# number of keys in shelve dictionary 112 print 'Comparing compound number: %s '%nkeys 113 #compare shelve dictionary and ChemComp table 114 ChemCompID=self.compChemComp(dictobj=dictobj,logdebug=False,lowercasetablenames=lowercasetablenames) 115 if not ChemCompID: 116 continue 117 118 #compare shelve dictionary with PropChemCompInt table 119 tablename='PropChemCompInt'#tablename 120 tabcol= ['Id', 'PropVal', 'PropDefIdFk', 'ChemCompIdFk']#table columname list 121 mapdict=['autoincrement', 'property{\'tPSA\',\'NRB\',\'HBD\',\'HBA\',\'Charge\',\'PUBCHEM_COMPOUND_CANONICALIZED\'}','IDict', 'ID']# mapping dictionary object attributes 122 PropChemCompIDInt=self.compPropTab(tablename,tabcol,mapdict,dictobj,anyflag=True,logdebug=False,IDict=PropDefID,ID=ChemCompID) 123 124 ##compare shelve dictionary with PropChemCompFloat table 125 tablename='PropChemCompFloat'#table name 126 tabcol= ['Id', 'PropVal', 'PropDefIdFk', 'ChemCompIdFk']#table columname list 127 mapdict=['autoincrement', 'property{\'LogP\',\'Desolv_apolar\',\'MWT\',\'Desolv_polar\',\'PUBCHEM_EXACT_MASS\',\'DRUGBANK_MOLECULAR_WEIGHT\'}','IDict', 'ID']# mapping dictionary object attributes 128 PropChemCompIDFloat=self.compPropTab(tablename,tabcol,mapdict,dictobj,anyflag=True,logdebug=False,IDcheck=False,IDict=PropDefID,ID=ChemCompID) 129 130 #compare shelve dictionary with PropChemCompText table 131 tablename='PropChemCompText'#tablename 132 tabcol= ['Id', 'PropVal', 'PropDefIdFk', 'ChemCompIdFk']#table columname list 133 mapdict=['autoincrement', 'property{\'SMILES\',\'PUBCHEM_NIST_INCHI\',\'DRUGBANK_INCHI\',\'Drug\',\'State\'}','IDict', 'ID']# mapping dictionary object attributes 134 PropChemCompIDText=self.compPropTab(tablename,tabcol,mapdict,dictobj,anyflag=True,logdebug=False,IDcheck=True,IDict=PropDefID,ID=ChemCompID) 135 136 #compare shelve dictionary with ProtStat table 137 ProtStatID=self.compProtStat(dictobj=dictobj,logdebug=False,ID=ChemCompID) 138 if not ProtStatID: 139 continue 140 141 #compare shelve dictionary with PropProtStatInt table 142 tablename='PropProtStatInt'#tablename 143 tabcol= ['Id', 'PropVal', 'PropDefIdFk', 'ProtStatIdFk']#table columname list 144 mapdict=['autoincrement', 'property{\'tPSA\',\'NRB\',\'HBD\',\'HBA\',\'Charge\',\'PUBCHEM_HEAVY_ATOM_COUNT\'}','IDict', 'ID']# mapping dictionary object attributes 145 PropProtStatIDInt=self.compPropTab(tablename,tabcol,mapdict,dictobj,anyflag=True,logdebug=False,IDcheck=True,IDict=PropDefID,ID=ProtStatID) 146 147 ##compare shelve dictionary with PropProtStatFloat table 148 tablename='PropProtStatFloat'#tablename 149 tabcol= ['Id', 'PropVal', 'PropDefIdFk', 'ProtStatIdFk']#table columname list 150 mapdict=['autoincrement', 'property{\'LogP\',\'Desolv_apolar\',\'MWT\',\'Desolv_polar\',\'PUBCHEM_MOLECULAR_WEIGHT\',\'DRUGBANK_MOLECULAR_WEIGHT\',\'DRUGBANK_EXACT_MASS\'}','IDict', 'ID']# mapping dictionary object attributes 151 PropProtStatIDFloat=self.compPropTab(tablename,tabcol,mapdict,dictobj,anyflag=True,logdebug=False,IDcheck=True,IDict=PropDefID,ID=ProtStatID) 152 153 ##compare shelve dictionary withPropProtStatText table 154 tablename='PropProtStatText'#tablename 155 tabcol= ['Id', 'PropVal', 'PropDefIdFk', 'ProtStatIdFk']#table columname list 156 mapdict=['autoincrement', 'property{\'SMILES\',\'PUBCHEM_MOLECULAR_FORMULA\',\'DRUGBANK_ID\',\'FORMULA\',\'Synonyms\'}','IDict', 'ID']# mapping dictionary object attributes 157 PropProtStatIDText=self.compPropTab(tablename,tabcol,mapdict,dictobj,anyflag=True,logdebug=False,IDcheck=True,IDict=PropDefID,ID=ProtStatID) 158 159 160 ##compare shelve dictionary with ConnTab table 161 ConnTabID=self.compConnTab(dictobj,logdebug=False,ID=ProtStatID) 162 if not ConnTabID: 163 continue 164 165 ##compare shelve dictionary with Atoms table 166 AtomsID=self.compAtoms(dictobj,logdebug=False,ID=ProtStatID) 167 if not AtomsID: 168 continue 169 170 ##compare shelve dictionary with ConfStat table 171 ConfStatID=self.compConfStat(dictobj,logdebug=False,ID=ProtStatID) 172 if not ConfStatID: 173 continue 174 175 ##compare shelve dictionary with PropConfStatInt table 176 tablename='PropConfStatInt'#tablename 177 tabcol= ['Id', 'PropVal', 'PropDefIdFk', 'ConfStatIdFk']#table columname list 178 mapdict=['autoincrement', 'property{\'Charge\'}','IDict', 'ID']# mapping dictionary object attributes 179 PropConfStatIDInt=self.compPropTab(tablename,tabcol,mapdict,dictobj,anyflag=True,logdebug=False,IDcheck=True,IDict=PropDefID,ID=ConfStatID) 180 181 ##compare shelve dictionary with PropConfStatFloat table 182 tablename='PropConfStatFloat'#tablename 183 tabcol= ['Id', 'PropVal', 'PropDefIdFk', 'ConfStatIdFk']#table columname list 184 mapdict=['autoincrement', 'property{\'LogP\'}','IDict', 'ID']# mapping dictionary object attributes 185 PropConfStatIDFloat=self.compPropTab(tablename,tabcol,mapdict,dictobj,anyflag=True,logdebug=False,IDcheck=True,IDict=PropDefID,ID=ConfStatID) 186 187 ##compare shelve dictionary with PropConfStatText table 188 tablename='PropConfStatText'#tablename 189 tabcol= ['Id', 'PropVal', 'PropDefIdFk', 'ConfStatIdFk']#table columname list 190 mapdict=['autoincrement', 'property{\'SMILES\'}','IDict', 'ID']# mapping dictionary object attributes 191 PropConfStatIDText=self.compPropTab(tablename,tabcol,mapdict,dictobj,anyflag=True,logdebug=False,IDcheck=True,IDict=PropDefID,ID=ConfStatID) 192 193 ##compare shelve dictionary with AtomStat table 194 AtomStatID=self.compAtomStat(dictobj,ID=ConfStatID,IDlist=AtomsID) 195 if not AtomStatID: 196 continue 197 198 #compare shelve dictionary with PropAtomStatInt table 199 tablename='PropAtomStatInt'#tablename 200 tabcol= ['Id', 'PropVal', 'PropDefIdFk', 'AtomStatIdFk']#table columname list 201 mapdict=['autoincrement', 'coords[5]:Charge','IDict', 'IDlist']# mapping dictionary object attributes 202 PropAtomStatIDInt=self.compPropTab(tablename,tabcol,mapdict,dictobj,anyflag=True,logdebug=False,IDcheck=True,IDict=PropDefID,IDlist=AtomStatID) 203 204 ##compare shelve dictionary with PropAtomStatFloat table 205 tablename='PropAtomStatFloat'#tablename 206 tabcol= ['Id', 'PropVal', 'PropDefIdFk', 'AtomStatIdFk']#table columname list 207 mapdict=['autoincrement', 'coords[5]:Charge','IDict', 'IDlist']# mapping dictionary object attributes 208 PropAtomStatIDFloat=self.compPropTab(tablename,tabcol,mapdict,dictobj,anyflag=True,logdebug=False,IDcheck=True,IDict=PropDefID,IDlist=AtomStatID) 209 210 ##compare shelve dictionary with PropAtomStatText table 211 tablename='PropAtomStatText'#tablename 212 tabcol= ['Id', 'PropVal', 'PropDefIdFk', 'AtomStatIdFk']#table columname list 213 mapdict=['autoincrement', 'coords[5]:Charge','IDict', 'IDlist']# mapping dictionary object attributes 214 PropAtomStatIDText=self.compPropTab(tablename,tabcol,mapdict,dictobj,anyflag=True,logdebug=False,IDcheck=True,IDict=PropDefID,IDlist=AtomStatID) 215 216 else: 217 print 'Empty dictionary!' 218 self.closeDict() 219 print 'Number of proceeded compounds in shelve dictionary: %s '%(nkeys) 220 else: 221 print 'Error: Can not open shelve dictionary!' 222 self.closeDB()#close database connection
223
224 - def compChemComp(self,dictobj=None,logdebug=False,lowercasetablenames=True,**kwarg):
225 """ 226 Chemical Compound table comparison 227 INPUT: 228 baseImport - flag to avoid data importing 229 dictobj - shelve dictionary object 230 logdebug - log debug in log file 231 lowercasetablenames - boolean, lower case table names MySQL engine settings, default True 232 anyflag - boolean, all imported values must be validated correctly, default False 233 OUTPUT: 234 ID - id table value 235 """ 236 ########## settings ################################################################## 237 tablename='ChemComp'#table name 238 if lowercasetablenames: 239 tablename=tablename.lower() 240 tabcol=[ 'Id', 'IsoSmi','ZINCode']#table columname list 241 mapdict=['autoincrement','isosmi','filename']# mapping dictionary object attributes 242 ##################################################################################### 243 if not dictobj: 244 print 'Error, %s: Missing dictionary object!' %tablename 245 self.log.error('%s: Missing dictionary object',tablename) 246 sys.exit(1) 247 if not self.isTabDesCorr(tablename,tabcol):#is table description correct 248 print 'Error, %s: Incorrect table description!' %tablename 249 self.log.error('%s: Incorrect table description',tablename) 250 sys.exit(1) 251 252 key=dictobj[0]#shelve dictionary key 253 dictobj=dictobj[1]#shelve dictionary value 254 IDlist=self.compare(dictobj,tablename,tabcol,mapdict,logdebug,lowercasetablenames,**kwarg) 255 return IDlist
256
257 - def compProtStat(self,dictobj=None,logdebug=False,lowercasetablenames=True,**kwarg):
258 """ 259 Protomer/Tautomer State table comparison 260 INPUT: 261 dictobj - shelve dictionary object 262 logdebug - log debug in log file 263 lowercasetablenames - boolean, lower case table names MySQL engine settings, default True 264 **kwarg - ID, IDlist, IDict - foreign key value 265 OUTPUT: 266 ID - id table value 267 """ 268 ########## settings ######################################################## 269 tablename='ProtStat'#table name 270 if lowercasetablenames: 271 tablename=tablename.lower() 272 tabcol=[ 'Id', 'IsoSmi','ChemCompIdFk']#table columname list 273 mapdict=['autoincrement','isosmi','ID']# mapping dictionary object attributes 274 ############################################################################ 275 if not dictobj: 276 print 'Error, %s: Missing dictionary object!' %tablename 277 self.log.error('%s: Missing dictionary object',tablename) 278 sys.exit(1) 279 if not self.isTabDesCorr(tablename,tabcol):#is table description correct 280 print 'Error, %s: Incorrect table description!' %tablename 281 self.log.error('%s: Incorrect table description',tablename) 282 sys.exit(1) 283 284 key=dictobj[0]#shelve dictionary key 285 dictobj=dictobj[1]#shelve dictionary value 286 IDlist=self.compare(dictobj,tablename,tabcol,mapdict,logdebug,lowercasetablenames,**kwarg) 287 return IDlist
288
289 - def compPropTab(self,tablename=None,tabcol=None,mapdict=None,dictobj=None,logdebug=False,lowercasetablenames=True,**kwarg):
290 """ 291 Property Table comparison 292 INPUT: 293 tablename - tablename, str 294 tabcol - table column, list of strings 295 mapdict - mapping dictionary, list of strings 296 logdebug - logging flag to log debug info, default=True 297 lowercasetablenames - boolean, lower case table names MySQL engine settings, default True 298 dictobj - sheleve dictionary object 299 **kwarg - IDcheck, ID, IDlist, IDict - foreign key value 300 - anyflag - boolean, some specified in mapdict attributes are True 301 OUTPUT: 302 ID - id table value 303 """ 304 if not dictobj: 305 print 'Error, %s: Missing dictionary object!'%tablename 306 self.log.error('%s: Missing dictionary object',tablename) 307 sys.exit(1) 308 if not tablename: 309 print 'Error, %s: Missing tablename!'%tablename 310 self.log.error('%s: Missing tablename',tablename) 311 sys.exit(1) 312 if lowercasetablenames: 313 tablename=tablename.lower() 314 if not mapdict: 315 print 'Error, %s: Missing mapping dictionary!'%tablename 316 self.log.error('%s: Missing mapping dictionary',tablename) 317 sys.exit(1) 318 319 if not self.isTabDesCorr(tablename,tabcol):#is table description correct 320 print 'Error, %s: Incorrect table description!'%tablename 321 self.log.error('%s: Incorrect table description',tablename) 322 sys.exit(1) 323 324 key=dictobj[0]#shelve dictionary key 325 dictobj=dictobj[1]#shelve dictionary value 326 327 if not dictobj.hasAttr('property'):#check if has attribute 328 if logdebug: 329 self.log.debug('%s: Missing property attribute in %s',tablename,dictobj.kwargs['filename']) 330 return 0 331 IDlist=self.compare(dictobj,tablename,tabcol,mapdict,logdebug,lowercasetablenames,**kwarg) 332 return IDlist
333
334 - def compConnTab(self,dictobj=None,logdebug=False,lowercasetablenames=True,**kwarg):
335 """ 336 Connection Table comparison 337 INPUT: 338 dictobj - shelve dictionary object 339 logdebug - log debug in log file 340 lowercasetablenames - boolean, lower case table names MySQL engine settings, default True 341 **kwarg - ID, IDlist, IDict - foreign key value 342 OUTPUT: 343 ID - id table value 344 """ 345 ########## settings ######################################################## 346 tablename='ConnTab'#table name 347 if lowercasetablenames: 348 tablename=tablename.lower() 349 tabcol=[ 'Id', 'BegAtom', 'EndAtom','BondType','ProtStatIdFk']#table columname list 350 mapdict=['autoincrement','bonds[0]','bonds[1]','bonds[2]','ID']# mapping dictionary object attributes 351 mapdict1=['autoincrement','conntab[0]','conntab[1]','conntab[2]','ID']# 352 ############################################################################ 353 if not dictobj: 354 print 'Error, %s: Missing dictionary object!' %tablename 355 self.log.error('%s: Missing dictionary object',tablename) 356 sys.exit(1) 357 if not self.isTabDesCorr(tablename,tabcol):#is table description correct 358 print 'Error, %s: Incorrect table description!'%tablename 359 self.log.error('%s: Incorrect table description',tablename) 360 sys.exit(1) 361 362 key=dictobj[0]#shelve dictionary key 363 dictobj=dictobj[1]#shelve dictionary value 364 365 if not dictobj.hasAttr('bonds'):#check if has attribute 366 mapdict[1]=mapdict1[1] 367 mapdict[2]=mapdict1[2] 368 mapdict[3]=mapdict1[3] 369 if logdebug: 370 self.log.debug('%s: Missing bonds attribute: %s',tablename,dictobj.kwargs['filename']) 371 if not dictobj.hasAttr('conntab'): 372 if logdebug: 373 self.log.debug('%s: Missing bonds attribute: %s',tablename,dictobj.kwargs['filename']) 374 return 0 375 IDlist=self.compare(dictobj,tablename,tabcol,mapdict,logdebug,lowercasetablenames,**kwarg) 376 return IDlist
377
378 - def compAtoms(self,dictobj=None,logdebug=False,lowercasetablenames=True,**kwarg):
379 """ 380 Atoms table comparison 381 INPUT: 382 dictobj - shelve dictionary object 383 logdebug - log debug in log file 384 lowercasetablenames - boolean, lower case table names MySQL engine settings, default True 385 **kwarg - ID, IDlist, IDict - foreign key value 386 OUTPUT: 387 ID - id table value 388 """ 389 ########## sett1ings ####################################################### 390 tablename='Atoms'#table name 391 if lowercasetablenames: 392 tablename=tablename.lower() 393 tabcol=[ 'Id', 'AtomSeqNum','ProtStatIdFk','ElemTabIdFk']#table columname list 394 mapdict=['autoincrement','coords[0]', 'ID', 'coords[4]']# mapping dictionary object attributes 395 mapdict1=['autoincrement','symq[0]','ID','symq[1]']# 396 ############################################################################ 397 if not dictobj: 398 print 'Error, %s: Missing dictionary object!'%tablename 399 self.log.error('%s: Missing dictionary object',tablename) 400 sys.exit(1) 401 if not self.isTabDesCorr(tablename,tabcol):#is table description correct 402 print 'Error, %s: Incorrect table description!'%tablename 403 self.log.error('%s: Incorrect table description',tablename) 404 sys.exit(1) 405 406 key=dictobj[0]#shelve dictionary key 407 dictobj=dictobj[1]#shelve dictionary value 408 409 if not dictobj.hasAttr('coords'):#check if has attribute 410 mapdict[1]=mapdict1[1] 411 mapdict[3]=mapdict1[3] 412 if logdebug: 413 self.log.debug('%s: Missing coords attribute: %s',tablename,dictobj.kwargs['filename']) 414 if not dictobj.hasAttr('symq'): 415 if logdebug: 416 self.log.debug('%s: Missing symq attribute: %s',tablename,dictobj.kwargs['filename']) 417 return 0 418 419 IDlist=self.compare(dictobj,tablename,tabcol,mapdict,logdebug,lowercasetablenames,**kwarg) 420 return IDlist
421
422 - def compConfStat(self,dictobj=None,logdebug=False,lowercasetablenames=True,**kwarg):
423 """ 424 Conformation State table comparision 425 INPUT: 426 dictobj - shelve dictionary object 427 logdebug - log debug in log file 428 lowercasetablenames - boolean, lower case table names MySQL engine settings, default True 429 **kwarg - ID, IDlist, IDict - foreign key value 430 OUTPUT: 431 ID - id table value 432 """ 433 ########## settings ######################################################## 434 tablename='ConfStat'#table name 435 if lowercasetablenames: 436 tablename=tablename.lower() 437 tabcol=['Id','ProtStatIdFk']#table columname list 438 mapdict=['autoincrement','ID']# mapping dictionary object attributes 439 ############################################################################ 440 if not dictobj: 441 print 'Error, %s: Missing dictionary object!'%tablename 442 self.log.error('%s: Missing dictionary object',tablename) 443 sys.exit(1) 444 if not self.isTabDesCorr(tablename,tabcol):#is table description correct 445 print 'Error, %s: Incorrect table description!'%tablename 446 self.log.error('%s: Incorrect table descirption',tablename) 447 sys.exit(1) 448 449 key=dictobj[0]#shelve dictionary key 450 dictobj=dictobj[1]#shelve dictionary value 451 452 if not dictobj.hasAttr('coords'):#check if has attribute 453 if logdebug: 454 self.log.debug('Missing coords attribute: %s',dictobj.kwargs['filename']) 455 return 0 456 457 IDlist=self.compare(dictobj,tablename,tabcol,mapdict,logdebug,lowercasetablenames,**kwarg) 458 return IDlist
459
460 - def compAtomStat(self,dictobj=None,logdebug=False,lowercasetablenames=True,**kwarg):
461 """ 462 Atom State table comparsion 463 INPUT: 464 dictobj - shelve dictionary object 465 logdebug - log debug in log file 466 lowercasetablenames - boolean, lower case table names MySQL engine settings, default True 467 **kwarg - ID, IDlist, IDict - foreign key value 468 OUTPUT: 469 ID - id table value 470 """ 471 ########## settings ################################################################### 472 tablename='AtomStat'#table name 473 if lowercasetablenames: 474 tablename=tablename.lower() 475 tabcol= ['Id', 'posX', 'posY', 'posZ', 'ConfStatIdFk','AtomsIdFk']#table columname list 476 mapdict=['autoincrement','coords[1]','coords[2]','coords[3]','ID', 'coords[0]']# mapping dictionary object attributes 477 ####################################################################################### 478 if not dictobj: 479 print 'Error, %s: Missing dictionary object!'%tablename 480 self.log.error('%s: Missing dictionary object',tablename) 481 sys.exit(1) 482 if not self.isTabDesCorr(tablename,tabcol):#is table description correct 483 print 'Error, %s: Incorrect table description!' %tablename 484 self.log.error('%s: Incorrect table description',tablename) 485 sys.exit(1) 486 487 key=dictobj[0]#shelve dictionary key 488 dictobj=dictobj[1]#shelve dictionary value 489 490 if not dictobj.hasAttr('coords'):#check if has attribute 491 if logdebug: 492 self.log.debug('%s: Missing coords attribute: %s',tablename,dictobj.kwargs['filename']) 493 return 0 494 495 IDlist=self.compare(dictobj,tablename,tabcol,mapdict,logdebug,lowercasetablenames,**kwarg) 496 return IDlist
497
498 - def compare(self,dictobj,tablename,tabcol,mapdict,logdebug,lowercasetablenames=True,**kwarg):
499 """ 500 general compare funtion 501 INPUT: 502 dictobj - shelve dictionary object 503 logdebug - log debug in log file 504 tablename - str, table name 505 tabcol - list, list of table columns 506 mapdict - dict, mapping dictionary between shelve dictionary object and database input 507 lowercasetablenames - boolean, lower case table names MySQL engine settings, default True 508 **kwarg - ID, IDlist, IDict - foreign key value 509 OUTPUT: 510 ID - id table value 511 """ 512 if lowercasetablenames: 513 tablename=tablename.lower() 514 tabledesc=self.tabdesc[tablename]#get table description 515 516 if not kwarg.has_key('anyflag'): 517 kwarg.setdefault('anyflag',False) 518 519 ID=kwarg.get('ID',None) 520 521 if ID:#must be list of length=1 522 if not isinstance(ID,list) or len(ID)!=1: 523 print 'Error, %s: Incorrect ID specification!'%tablename 524 if logdebug: 525 self.log.debug('%s: Incorrect ID specification',tablename) 526 return 0 527 528 IDlist=kwarg.get('IDlist',None) 529 if IDlist:#must be list 530 print IDlist 531 if not isinstance(IDlist,list): 532 print 'Error, %s: Incorrect IDlist specification!'%tablename 533 if logdebug: 534 self.log.debug('%s: Incorrect IDlist specification',tablename) 535 return 0 536 537 IDict=kwarg.get('IDict',None) 538 if IDict:#must be dictionary 539 if not isinstance(IDict,dict): 540 print 'Error, %s: Incorrect IDict specification!'%tablename 541 if logdebug: 542 self.log.debug('%s: Incorrect IDict specification',tablename) 543 return 0 544 545 inrow={} #input row dictionary 546 coldescdict={}#column description dictionary 547 for col in range(len(tabcol)):##iterate column name' 548 coldesc=tabledesc.getColumn(tabcol[col])#column description object 549 if not coldesc:#if no column description 550 print 'Error, %s: Missing description for %s' %(tablename,tabcol[col]) 551 self.log.error('%s: Missing description for %s',tablename,tabcol[col]) 552 sys.exit(1) 553 if coldesc.getAutoIncrem():#if column is auto_incremented 554 continue 555 if (coldesc.getFKey() and mapdict[col]=='ID'):##if column is foreign key 556 inrow[tabcol[col]]=ID 557 continue 558 559 if (coldesc.getFKey() and mapdict[col]=='IDict'): 560 try: 561 propname=attrlist 562 except NameError: 563 if logdebug: 564 #print '%s: Missing attribute in property for %s or incorrect column order' %(tablename,dictobj.kwargs['filename']) 565 self.log.error('%s: Missing attribute in property for %s or incorrect column order',tablename,dictobj.kwargs['filename']) 566 return 0 567 except UnboundLocalError: 568 if logdebug: 569 self.log.error('%s: Missing attribute in property for %s or incorrect column order',tablename,dictobj.kwargs['filename']) 570 return 0 571 propIdFklist=[findDictKey(propitem,IDict) for propitem in propname] 572 inrow[tabcol[col]]=propIdFklist 573 continue 574 575 if (coldesc.getFKey() and mapdict[col]=='IDlist'): 576 inrow[tabcol[col]]=IDlist 577 continue 578 579 colen=coldesc.getLength()#get column length 580 colfloat=coldesc.getFpoint()#get column float point 581 coldescdict[tabcol[col]]=[colen,colfloat]#column description dictionary 582 ## if column NOT auto_incremented and NOT foreign key 583 attrname,attridx,attrtype,newattrname=geType(mapdict[col])##check mapping dictionary 584 attrval=dictobj.kwargs.get(attrname,None)#get attrib value if exists 585 586 if attrval:#attribute exists 587 if isinstance(attrval,eval(attrtype)): 588 ###check type consistance between shelve dictionary and mapping dictionary 589 if isinstance(attrval,(str,int,float,long)):#if dictionary attribute is str 590 if newattrname:#if newname is specified 591 attrlist=[newattrname] 592 else:#newname NOT defined 593 attrlist=[attrname] 594 inrow[tabcol[col]]=attrval#input row dictionary 595 if isinstance(attrval,list):#if dictionary attribute is list 596 templist=[] 597 if newattrname:#if newname is specified 598 attrlist=[newattrname] 599 else:#newname NOT defined 600 attrlist=[attrname] 601 for item in attrval:#iterate list 602 try:#if item value NOT exists 603 itemval=item[attridx] 604 except IndexError: 605 if logdebug: 606 self.log.debug('%s: Missing value in list for %s',tablename,dictobj.kwargs['filename']) 607 return 0 608 609 if (coldesc.getFKey()and IDlist): 610 templist.append(IDlist[itemval-1]) 611 else: 612 templist.append(itemval) 613 614 inrow[tabcol[col]]=templist 615 616 if isinstance(attrval,dict):#if dictionary attribute is dictionary 617 attrlist=[attritem for attritem in attridx.split(',')]#get attribute list 618 templist=[] 619 for attritem in attrlist:##iterate attribute list 620 attritem=attritem.strip('\'')#remove quota 621 attritemval=attrval.get(attritem,None)#get attrib value if exists 622 if attritemval:#if exists 623 templist.append(attritemval) 624 else:##value NOT exists 625 if logdebug: 626 self.log.debug('%s: Missing %s attribute for %s',tablename,attritem,dictobj.kwargs['filename']) 627 templist.append(False) 628 if newattrname:#new attribute name 629 newattrlist=[attritem for attritem in newattrname.split(',')]#get attribute list 630 if len(newattrlist)==len(attrlist): 631 attrlist=newattrlist 632 else: 633 print 'Error, %s: Number of attributes must agree!'%tablename 634 self.log.error('%s: Number of attributes must agree',tablename) 635 sys.exit(1) 636 inrow[tabcol[col]]=templist 637 else: 638 print 'Error, %s: Incorrect type specification for %s!'%(tablename,mapdict[col]) 639 self.log.error('%s: Incorrect mapping type specification for %s',tablename,mapdict[col]) 640 sys.exit(1) 641 else:#attribute NOT exists 642 if coldesc.getNull():#if column accept NULL 643 inrow[tabcol[col]]='NULL' 644 else: 645 if logdebug: 646 self.log.debug('%s: Column %s is not nullable',tablename,tabcol[col]) 647 inrow[tabcol[col]]=False 648 continue 649 650 ### DATA COMPARISON 651 inskey,insval=insPrep(**inrow)#preparation for insertion to table 652 if kwarg['anyflag']:#remove items with at least False value 653 insval=filter(checkitem,insval) 654 if not all([checkitem(val) for val in insval]):#check all values are True 655 return 0 656 if not insval:##input list is empty 657 return 0 658 IDlist=[] 659 for insitem in insval:#iterate items in insertion values list 660 insrecord=''#insert record 661 for i,j in zip(inskey,insitem): 662 if isinstance(j,str): 663 try: 664 j=float(j) 665 colfloat=coldescdict.get(i,None) 666 if colfloat: 667 colfloat=colfloat[1] 668 if not colfloat=='default': 669 j=round(j,colfloat) 670 insrecord+='%s.%s=%s AND '%(tablename,i,j) 671 except ValueError,e: 672 j=j.replace('\\','\\\\')#replace '\' with '\\' in strings 673 j=j.replace('\'','\\\'')#replace ''' with '\' 674 insrecord+='%s.%s=\'%s\' AND '%(tablename,i,j) 675 else: 676 colfloat=coldescdict.get(i,None) 677 if colfloat: 678 colfloat=colfloat[1] 679 if not colfloat=='default': 680 j=round(j,colfloat) 681 insrecord+='%s.%s=%s AND '%(tablename,i,j) 682 insrecord=insrecord[:-4]#delete last ',' 683 sql=UserQuery().select(tablename,columnlist=['Id'],where=insrecord,order=None,having=None) 684 result=self.executeQuery(sql)#execute query 685 if result:#if record exists in table 686 ID=[]##get ID 687 for resitem in result:#iterate result dictionary 688 ID.append(resitem['Id'])#output ID list 689 IDlist.extend(ID) 690 else: 691 print 'Error, %s: %s not imported from %s' %(tablename,insrecord,dictobj.kwargs['filename']) 692 if logdebug: 693 self.log.debug('%s: %s not imported from %s',tablename,insrecord,dictobj.kwargs['filename']) 694 self.log.debug('%s: %s not imported from %s',tablename,insrecord,dictobj.kwargs['filename']) 695 self.log.debug('%s',sql) 696 return IDlist
697 698 ################## End of class ##################################### 699 ################### MAIN ############################################ 700 ################# example of usage ################################## 701 if __name__=='__main__': 702 pass 703 # A=compareSDict2DB(dbfilepath='/tmp/InpuTest1/ZINCStdInp0', 704 # path='/tmp/Log',filename='compSDict2DB', 705 # host='',db='',user='',passwd='',log=False) 706 # A.compSDict2Tab(lowercasetablenames=True) 707