Package install :: Package MoSTBioDat :: Package DataBase :: Package Query :: Package UserQuery :: Module UserQuery
[hide private]
[frames] | no frames]

Source Code for Module install.MoSTBioDat.DataBase.Query.UserQuery.UserQuery

   1  #!/usr/bin/env python 
   2  ########################################### 
   3  # UserQuery.py: Predefined query syntax   # 
   4  # to create MySQL query                   # 
   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  except ImportError,e: 
  19      print 'Error: %s' %e 
  20      sys.exit(1) 
  21  ###### UserQuery class ############################### 
22 -class UserQuery(object):
23 """ 24 predefined user MySQL query 25 """ 26 queryDict={} 27
28 - def __init__(self):
29 self.insertDictQuery(1,query=self.getPropValId('NRB')) 30 self.insertDictQuery(2,query=self.getPropValIdin(propname=('tPSA','MWT'))) 31 self.insertDictQuery(3,query=self.getIsoSmiId(isosmi='CC[C@@]12CCC[N@H+]3[C@@H]1c4c(c5ccccc5n4C(=C2)C(=O)OCC)CC3')) 32 self.insertDictQuery(4,query=self.getPropValonIsoSmi1(isosmi='CC[C@@]12CCC[N@H+]3[C@@H]1c4c(c5ccccc5n4C(=C2)C(=O)OCC)CC3' 33 ,proptabname='PropChemCompFloat',propname='MWT')) 34 self.insertDictQuery(5,self.getPropValonIsoSmi2(isosmi='CC[C@@]12CCC[N@H+]3[C@@H]1c4c(c5ccccc5n4C(=C2)C(=O)OCC)CC3', 35 proptabname1='PropChemCompInt', propname1='NRB', 36 proptabname2='PropChemCompFloat',propname2='MWT')) 37 self.insertDictQuery(6,query=self.getIsoSmionPropValrange(proptabname='PropChemCompInt',propname='tPSA',range=[40,42])) 38 self.insertDictQuery(7,query=self.getIdPropNamePropVal(isosmi='CC[C@@]12CCC[N@H+]3[C@@H]1c4c(c5ccccc5n4C(=C2)C(=O)OCC)CC3', 39 proptabname='PropChemCompInt',propname=('tPSA','NRB','PUBCHEM_ISOTOPIC_ATOM_COUNT'))) 40 self.insertDictQuery(8,query=self.getIsoSmionPropValrangeand(proptabname1='PropChemCompInt',propname1='tPSA',range=[40,50], 41 proptabname2='PropChemCompFloat',propname2='MWT',condition='<',value=200)) 42 self.insertDictQuery(9,query=self.getIsoSmiPropValNumConflike(proptabname1='PropChemCompText',propname1='PUBCHEM_NIST_INCHI',proptabname2='PropChemCompText',propname2='PUBCHEM_IUPAC_NAME',like='%/C3H9NO/%')) 43 self.insertDictQuery(10,query=self.getConfIdonIsoSmi(isosmi='CC[C@@]12CCC[N@H+]3[C@@H]1c4c(c5ccccc5n4C(=C2)C(=O)OCC)CC3')) 44 self.insertDictQuery(11,query=self.getXYZonIsoSmi(isosmi='CC[C@@]12CCC[N@H+]3[C@@H]1c4c(c5ccccc5n4C(=C2)C(=O)OCC)CC3')) 45 self.insertDictQuery(12,query=self.getXYZQonIsoSmi(isosmi='CC[C@@]12CCC[N@H+]3[C@@H]1c4c(c5ccccc5n4C(=C2)C(=O)OCC)CC3',chargedef='Charge')) 46 self.insertDictQuery(13,query=self.getXYZQAtSymAtSeqNumonIsoSmi(isosmi='CC[C@@]12CCC[N@H+]3[C@@H]1c4c(c5ccccc5n4C(=C2)C(=O)OCC)CC3',chargedef='Charge')) 47 self.insertDictQuery(14,query=self.getXYZQonConfId(confId=5,chargedef='Charge')) 48 self.insertDictQuery(15,query=self.getIdPropValcondition(proptabname='PropChemCompInt',propname='tPSA',condition=">",value=40)) 49 self.insertDictQuery(16,query=self.getIdPropVal4condition(proptabname1='PropChemCompInt',propname1='tPSA',condition1=">",value1=40, 50 proptabname2='PropChemCompInt',propname2='NRB',condition2="=",value2=3, 51 proptabname3='PropChemCompFloat',propname3='MWT',condition3="<",value3=200, 52 proptabname4='PropChemCompText',propname4='PUBCHEM_IUPAC_TRADITIONAL_NAME',condition4="LIKE",value4='%1-aminopropan-2-ol%')) 53 self.insertDictQuery(17,query=self.getPropValonId(proptabname='PropChemCompInt',propname='tPSA',subId=4)) 54 self.insertDictQuery(18,query=self.getPropVal2onId(subId=4,proptabname1='PropChemCompInt',propname1='tPSA' 55 ,proptabname2='PropChemCompFloat',propname2='MWT')) 56 self.insertDictQuery(19,query=self.getConfNumonId(subId=2)) 57 self.insertDictQuery(20,query=self.getIsoSmiconTautNum(proptabname='PropChemCompText',propname='PUBCHEM_NIST_INCHI',condition='>',value=1)) 58 self.insertDictQuery(21,query=self.getIsoSmionConfNum(proptabname='PropChemCompText',propname='PUBCHEM_NIST_INCHI',condition='>',value=1)) 59 self.insertDictQuery(22,query=self.getIsoSmionConfNumPropVal(proptabname='PropConfStatFloat',propname='logP',propcondition=">", 60 propfunction="MIN",cutoff='+1',confcondition="<",confvalue=2)) 61 self.insertDictQuery(23,query=self.getIsoSmionAtomPropValcondition(proptabname='PropAtomStatFloat',propname='chargePM3')) 62 self.insertDictQuery(24,query=self.getIsoSmiQonPropValcondition(proptabname='PropAtomStatFloat',propname='Charge',atomsymbol='O',range=[-2,0.1])) 63 self.insertDictQuery(25,query=self.getIsoSmionAtomNum(atomsymbol='O',condition='>',value=1)) 64 self.insertDictQuery(26,query=self.getIsoSmionPropValisempty(proptabname='PropChemCompText',propname='PUBCHEM_IUPAC_CAS_NAME')) 65 self.insertDictQuery(27,query=self.getIsoSmionPropValexist(proptabname='PropChemCompText',propname='SMILES')) 66 self.insertDictQuery(28,query=self.updateTable('PropDef','PropDesc','NULL','PropName','PUBCHEM_COMPONENT_COUNT')) 67 self.insertDictQuery(29,query=self.insertRecord('ChemComp', ['IsoSmi','ZINCode'], ['CC','Null'])) 68 self.insertDictQuery(30,query=self.select('ChemComp',['Id','IsoSmi'],where="Id=3",order='Id')) 69 self.insertDictQuery(31,query=self.join('ChemComp',['ProtStat'],['ChemComp.Id'],['Id','ChemCompIdFk'],where='ProtStat.Id=10',limit=10)) 70 self.insertDictQuery(32,query=self.getMaxId(tabname='ChemComp')) 71 self.insertDictQuery(33,query=self.setLastId(tabname='ChemComp',value=18)) 72 self.insertDictQuery(34,query=self.getMaxId2(tabname='ChemComp',colname='Id')) 73 self.insertDictQuery(35,query=self.getXYZQAtNumAtSeqNumonConfId(confId='1',chargedef='Charge'))
74
75 - def getDictQuery(self,number=None):
76 """ 77 get dictionary query item 78 INPUT: 79 class object 80 number - int - number of query 81 OUTPUT: 82 sql query 83 """ 84 query=None 85 if number: 86 query=UserQuery.queryDict.get(number,None) 87 return query
88
89 - def insertDictQuery(self,number,query=''):
90 """ 91 insert query into dictionary 92 INPUT: 93 class object 94 number - int - query number 95 query - str - SQL query, default '' 96 """ 97 UserQuery.queryDict[number]=query
98
99 - def showDictQuery(self,number='all'):
100 """ 101 show dictionary query 102 INPUT: 103 class object 104 number - int - number of query, default = 'all' 105 """ 106 if number=='all': 107 for number,query in UserQuery.queryDict.iteritems(): 108 print "%i" %number, " %s" %(query) 109 else: 110 query=UserQuery.queryDict.get(number,'') 111 print "%i" %number, " %s" %(query)
112
113 - def addLimit(self,query,limit=None):
114 """ 115 add limit 116 INPUT: 117 class object 118 query - SQL query 119 limit - int - query limit, default=None 120 OUTPUT: 121 sql query 122 """ 123 if limit: 124 return "%s LIMIT %i;" %(query,limit) 125 else: 126 return query
127 ############## TABLE QUERY ########################
128 - def createTable(self,table,column,columndata):
129 """ 130 create table 131 INPUT: 132 class object 133 table - str - table name 134 column - str - column name 135 columndata - str - column data 136 OUTPUT: 137 sql query 138 """ 139 query='CREATE TABLE IF NOT EXISTS %s (%s %s);' %(table,column,columndata) 140 return query
141
142 - def delTable(self,table):
143 """ 144 delete table 145 INPUT: 146 class object 147 table - str - table name to delete 148 OUTPUT: 149 sql query 150 """ 151 query='DROP TABLE %s;'%table 152 return query
153
154 - def showTable(self,database=None):
155 """ 156 show table 157 INPUT: 158 class object 159 database - str - database name, default=None 160 OUTPUT: 161 sql query 162 """ 163 query = "SHOW TABLES" 164 if database: 165 query+=" FROM %s" %database 166 return query+';'
167
168 - def updateTable(self,table,field1,data1,field2,data2):
169 """ 170 update table 171 INPUT: 172 class object 173 table - str - table name 174 field1 - str - table field 175 data1 - str - table data 176 field2 - str - table field 177 data2 0 str - table data 178 OUTPUT: 179 sql query 180 """ 181 if isinstance(data1,str): 182 data1="\'" + data1+"\'" 183 if isinstance(data2,str): 184 data2="\'" + data2+"\'" 185 query='UPDATE %s SET %s=%s WHERE %s=%s;' %(table,field1,data1,field2,data2) 186 return query
187
188 - def descTable(self,table):
189 """ 190 describe table 191 INPUT: 192 class object 193 table - str - table name 194 """ 195 query='DESCRIBE %s' %(table) 196 return query+';'
197 ############ COLUMN QUERY ############################
198 - def addColumn(self,table,column):
199 """ 200 add column 201 INPUT: 202 class object 203 table - str - table name 204 column - str - column name 205 OUTPUT: 206 sql query 207 """ 208 query='ALTER TABLE %s ADD COLUMN %s;' %(table,column) 209 return query
210
211 - def delColumn(self,table,column):
212 """ 213 delete column 214 INPUT: 215 class object 216 table - str - table name 217 column - str - table column 218 OUTPUT: 219 sql query 220 """ 221 query='ALTER TABLE %s DROP COLUMN %s;' %(table,column) 222 return query
223
224 - def showColumn(self,table):
225 """ 226 show column 227 INPUT: 228 class object 229 table - str- table name 230 OUTPUT: 231 sql query 232 """ 233 query='SHOW COLUMNS FROM %s;' %table 234 return query
235 ############# RECORD QUERY #############################
236 - def insertRecord(self,table,columnlist,valuelist):
237 """ 238 insert record into table 239 INPUT: 240 class object 241 table - str - table name 242 columnlist - list - column list 243 valuelist - list - value list 244 OUTPUT: 245 sql query 246 """ 247 if len(columnlist)!=len(valuelist): 248 print 'Number of columns must be equal number of values!' 249 else: 250 query='INSERT INTO %s' %table +'(' 251 for column in columnlist: 252 query+=str(column)+',' 253 query=query[:-1] 254 query+=') VALUES (' 255 for value in valuelist: 256 if value !='': 257 query+='"'+str(value)+'"'+',' 258 else: 259 query+='"NULL"' 260 query=query[:-1] 261 query+=');' 262 return query
263
264 - def delRecord(self,table,field,data):
265 """ 266 delete record 267 INPUT: 268 class object 269 table - str -table name 270 field - str - field name 271 data - str - data 272 OUTPUT: 273 sql query 274 """ 275 if isinstance(data,str): 276 data='\'%s\'' %data 277 query='DELETE FROM %s WHERE %s=%s;' %(table,field,data) 278 return query
279 ############### DATA RETRIVING QUERY ###############################
280 - def select(self,table,columnlist,where=None,order=None,having=None):
281 """ 282 select 283 INPUT: 284 class object 285 table - str - table name 286 columnlist - list - columnlist 287 where - str - add where, default=None 288 order - str - add order, default=None 289 having - str - add having, default=None 290 OUTPUT: 291 sql query 292 """ 293 query='SELECT ' 294 for column in columnlist: 295 query+=str(column)+',' 296 query=query[:-1] 297 query+=' FROM %s' %table 298 if where: 299 query += " WHERE %s" %where 300 if order: 301 query += " ORDER BY %s" %order 302 if having: 303 query += " HAVING %s" %having 304 return query+';'
305
306 - def join(self,table1,tablelist,columnlist,columnjoinlist,where=None,order=None,having=None,limit=None):
307 """ 308 join two tables 309 table1 - str - table name 310 tablelist - list - table list 311 columnlist - list - column list 312 columnjoinlist - list - column join list (order is important) 313 where - str - sql where 314 order - str - sql order 315 having - str - sql having 316 """ 317 query='SELECT ' 318 for column in columnlist: 319 query+=str(column)+',' 320 query=query[:-1] 321 query+=' FROM %s' %table1 322 i=0; 323 for table in tablelist: 324 i+=1 325 query+=' JOIN %s' %table 326 query+=' ON %s.%s=%s.%s'%(table1,columnjoinlist[0],table,columnjoinlist[i]) 327 if where: 328 query += " WHERE %s" %where 329 if order: 330 query += " ORDER BY %s" %order 331 if having: 332 query += " HAVING %s" %having 333 if limit: 334 return self.addLimit(query, limit) 335 return query+';'
336 337 ########### User Predefined Queries ################################################
338 - def removEnd(self,query):
339 """ 340 remove ending ';' from query 341 INPUT: 342 query - str, mysql query syntax 343 OUTPUT" 344 query without ending character 345 """ 346 query=str(query).rstrip()[:-1] 347 return query
348
349 - def getPropValId(self,propname):
350 """ 351 get property definition Id 352 INPUT: 353 propname - str, property name 354 OUTPUT: 355 query, str, example 1 in dictionary 356 """ 357 query=""" 358 SELECT Id 359 FROM PropDef 360 WHERE PropName='%s'; 361 """%(propname) 362 return query
363
364 - def getPropValIdin(self,propname=()):
365 """ 366 get property definition Id 367 INPUT: 368 propname - str, property name 369 OUTPUT: 370 query, str, example 2 in dictionary 371 """ 372 query=""" 373 SELECT Id,PropName 374 FROM PropDef 375 WHERE PropName IN """+str(propname)+';' 376 return query
377
378 - def getIsoSmiId(self,isosmi):
379 """ 380 get Id for specified isomeric SMILE 381 INPUT: 382 isosmi- str, isomeric SMILE 383 OUTPUT: 384 query, str, example 3 in dictionary 385 """ 386 query=""" 387 SELECT Id 388 FROM ChemComp 389 WHERE IsoSmi='%s'; 390 """%(isosmi) 391 return query
392
393 - def getPropValonIsoSmi1(self,isosmi,proptabname,propname):
394 """ 395 get property value for specified isomeric SMILE 396 VALID for ONE table 397 INPUT 398 isosmi - str, isomeric SMILE 399 proptabname- str, property tablename 400 propname - str, property name 401 OUTPUT" 402 query, example 4 in dictionary query 403 """ 404 query=""" 405 SELECT Id,PropVal 406 FROM ("""+self.removEnd(self.getIsoSmiId(isosmi))+""" 407 )AS CC 408 JOIN ( 409 SELECT ChemCompIdFk, PropVal 410 FROM %s AS PCCI 411 JOIN ("""%(proptabname)+self.removEnd(self.getPropValId(propname))+""") AS PD 412 ON PCCI.PropDefIdFk=PD.Id ) AS PD1 413 ON PD1.ChemCompIdFk=CC.Id; 414 """ 415 return query
416
417 - def getPropValonIsoSmi2(self,isosmi,proptabname1,propname1,proptabname2,propname2):
418 """ 419 get property value for specified isomeric SMILE 420 VALID for TWO tables 421 INPUT 422 isosmi - str, isomeric SMILE 423 proptabname1- str, property tablename 424 propname1 - str, property name 425 proptabname2- str, property tablename 426 propname2 - str, property name 427 OUTPUT: 428 query, example 5 in dictionary query 429 """ 430 query=""" 431 SELECT Q1.Id,Q1.PropVal AS %s,Q2.PropVal AS %s 432 FROM ("""%(propname1,propname2)+self.removEnd(self.getPropValonIsoSmi1(isosmi,proptabname1,propname1))+""" 433 ) AS Q1 434 JOIN 435 ("""+self.removEnd(self.getPropValonIsoSmi1(isosmi,proptabname2,propname2))+""" 436 )AS Q2 437 ON Q1.Id=Q2.Id;""" 438 return query
439
440 - def getIsoSmionPropValrange(self,proptabname,propname,range=[]):
441 """ 442 get isomeric SMILE and property value if property is in specified range 443 INPUT: 444 proptabname- str, property tablename 445 propname - str, property name 446 range - list, value range 447 OUTPUT: 448 query, example 6 in dictionary query 449 """ 450 query=""" 451 SELECT Id,IsoSmi, PropVal 452 FROM ChemComp AS CC 453 JOIN ( 454 SELECT ChemCompIdFk, PropVal 455 FROM %s AS PCCI 456 JOIN (""" %(proptabname)+self.removEnd(self.getPropValId(propname))+""" 457 ) AS PD 458 ON PCCI.propDefIdFk=PD.Id 459 WHERE PropVal BETWEEN %s AND %s ) AS PD1 460 ON PD1.ChemCompIdFk=CC.Id; 461 """%(range[0],range[1]) 462 return query
463
464 - def getIdPropNamePropVal(self,isosmi,proptabname,propname=()):
465 """ 466 get substance Id, property name, property value 467 INPUT: 468 isosmi - str, isomeric SMILE 469 proptabname -str, property tablename 470 propname- tuple, property name tuple 471 OUTPUT: 472 query, example 7 in dictionary query 473 """ 474 query=""" 475 SELECT Id,PropName,PropVal 476 FROM ("""+self.removEnd(self.getIsoSmiId(isosmi))+""") AS CC 477 JOIN ( 478 SELECT ChemCompIdFk,PropVal,PropName 479 FROM %s AS PCCI 480 JOIN ("""%(proptabname)+self.removEnd(self.getPropValIdin(propname))+""") AS PD 481 ON PCCI.PropDefIdFk=PD.Id) AS PD1 482 ON PD1.ChemCompIdFk=CC.Id; 483 """ 484 return query
485
486 - def getIsoSmionPropValrangeand(self,proptabname1,propname1,proptabname2,propname2,condition,value,range=[]):
487 """ 488 get isomeric SMILE and property value if property in specified range 489 and specified condition (<,>,=) 490 INPUT: 491 proptabname1 - str, property table name 492 proptabname2 - str, property table name 493 propname1 -str, property name 494 propname1 - str, property name 495 condition - str, myql condition >,<,= itd. 496 value - condition value 497 range - value range 498 OUTPUT 499 query, example 8 in dictionary query 500 """ 501 query=""" 502 SELECT Q1.Id,Q1.PropVal AS %s,Q2.PropVal AS %s 503 FROM ("""%(propname1,propname2)+self.removEnd(self.getIsoSmionPropValrange(proptabname1,propname1,range))+""" 504 ) AS Q1 505 JOIN ( 506 SELECT IsoSmi, PropVal,Id 507 FROM ChemComp AS CC 508 JOIN ( 509 SELECT ChemCompIdFk, PropVal 510 FROM %s AS PCCI 511 JOIN ("""%(proptabname2)+self.removEnd(self.getPropValId(propname2))+""") AS PD 512 ON PCCI.PropDefIdFk=PD.Id 513 WHERE PropVal %s %s) AS PD1 514 ON PD1.ChemCompIdFk=CC.Id)AS Q2 515 ON Q1.Id=Q2.Id; 516 """%(condition,value) 517 return query
518
519 - def getIsoSmiPropValNumConflike(self,proptabname1,propname1,proptabname2,propname2,like):
520 """ 521 get isomeric SMILE, property value, number of conformers if property like '%' 522 INPUT: 523 proptabname1 - str, property table name 524 proptabname2 - str, property table name 525 propname1 -str, property name 526 propname1 - str, property name 527 like - str, mysql like expression 528 OUTPUT: 529 query, example 9 in dictionary query 530 """ 531 query=""" 532 SELECT a AS %s,b AS %s,IsoSmi,num_conf 533 FROM ChemComp AS CC 534 JOIN ( 535 SELECT a,b,ChemCompIdFk,num_conf 536 FROM ( 537 SELECT ProtStatIdFk,COUNT(ProtStatIdFk) AS num_conf 538 FROM ConfStat AS CC 539 GROUP BY (ProtStatIdFk) 540 ORDER BY (ProtStatIdFk) ASC ) AS Q4 541 JOIN ( 542 SELECT Id, Q3.ChemCompIdFk,a,b 543 FROM ProtStat AS PS 544 JOIN ( 545 SELECT Q1.ChemCompIdFk,Q1.PropVal AS a,Q2.PropVal AS b 546 FROM ( 547 SELECT ChemCompIdFk,PropVal 548 FROM %s AS PCCT 549 JOIN ("""%(propname1,propname2,proptabname1)+self.removEnd(self.getPropValId(propname1))+""" 550 ) AS PD 551 ON PCCT.PropDefIdFk=PD.Id 552 WHERE PropVal LIKE '%s') AS Q1 553 LEFT JOIN ( 554 SELECT ChemCompIdFk,PropVal 555 FROM %s AS PCCT 556 JOIN("""%(like,proptabname2)+self.removEnd(self.getPropValId(propname2))+""" 557 ) AS PD 558 ON PCCT.PropDefIdFk=PD.Id ) AS Q2 559 ON Q1.ChemCompIdFk=Q2.ChemCompIdFk ) AS Q3 560 ON Q3.ChemCompIdFk=PS.ChemCompIdFk) AS Q5 561 ON Q4.ProtStatIdFk=Q5.Id) AS Q6 562 ON CC.Id=Q6.ChemCompIdFk; 563 """ 564 return query
565
566 - def getConfIdonIsoSmi(self,isosmi):
567 """ 568 get conformer Id for specified isomeric SMILE 569 INPUT: 570 isosmi- str, isomeric SMILE 571 OUTPUT: 572 query, example 10 in dictionary query 573 """ 574 query=""" 575 SELECT CS.Id 576 FROM ConfStat AS CS 577 JOIN ( 578 SELECT PS.Id 579 FROM ProtStat AS PS 580 JOIN ("""+self.removEnd(self.getIsoSmiId(isosmi))+""" 581 ) AS CC 582 ON CC.Id=PS.ChemCompIdFk 583 ) AS Q1 584 ON Q1.Id=CS.ProtStatIdFk; 585 """ 586 return query
587
588 - def getXYZonIsoSmi(self,isosmi):
589 """ 590 get X,Y,Z coordinates for specified isomeric SMILE 591 INPUT: 592 isosmi - str, isomeric SMILE 593 OUTPUT: 594 query, example 11 in dictionary query 595 """ 596 query=""" 597 SELECT At.Id, At.posX,At.posY,At.posZ 598 FROM AtomStat AS At 599 JOIN("""+self.removEnd(self.getConfIdonIsoSmi(isosmi))+""") AS Q1 600 ON Q1.Id=At.ConfStatIdFk; 601 """ 602 return query
603
604 - def getXYZQonIsoSmi(self,isosmi,chargedef):
605 """ 606 get X,Y,Z,charge for specified isomeric SMILE 607 INPUT: 608 isosmi - str, isomeric SMILE 609 chargedef - str, charge definition 610 OUTPUT: 611 query, example 12 ind dictionary query 612 """ 613 query=""" 614 SELECT Q3.posX,Q3.posY,Q3.posZ,Q3.PropVal 615 FROM ( 616 SELECT Q2.posX,Q2.posY,Q2.posZ,PASI.PropVal,PASI.PropDefIdFk 617 FROM PropAtomStatInt AS PASI 618 JOIN ("""+self.removEnd(self.getXYZonIsoSmi(isosmi))+""") AS Q2 619 ON Q2.Id=PASI.AtomStatIdFk) AS Q3 620 JOIN ("""+self.removEnd(self.getPropValId(chargedef))+""")AS PD 621 ON PD.Id=Q3.PropDefIdFk; 622 """ 623 return query
624
625 - def getXYZQAtSymAtSeqNumonIsoSmi(self,isosmi,chargedef):
626 """ 627 get X,Y,Z,charge, atom symbol, sequence number for specified isomeric SMILE 628 INPUT: 629 isosmi - str, isomeric SMILE 630 chargedef - str, charge definition 631 OUTPUT: 632 query, example 13 ind dictionary query 633 """ 634 query=""" 635 SELECT Q5.posX,Q5.posY,Q5.posZ,Q5.PropVal,Q5.AtomSeqNum,ET.AtomSymbol 636 FROM ( 637 SELECT Q4.posX,Q4.posY,Q4.posZ,Q4.PropVal,A.AtomSeqNum,A.ElemTabIdFk 638 FROM ( 639 SELECT Q3.posX,Q3.posY,Q3.posZ,Q3.PropVal,Q3.AtomsIdFk 640 FROM ( 641 SELECT Q2.posX,Q2.posY,Q2.posZ,Q2.AtomsIdFk,PASI.PropVal,PASI.PropDefIdFk 642 FROM PropAtomStatInt AS PASI 643 JOIN ( 644 SELECT At.posX,At.posY,At.posZ,At.Id,At.AtomsIdFk 645 FROM AtomStat AS At 646 JOIN("""+self.removEnd(self.getConfIdonIsoSmi(isosmi))+ """) AS Q1 647 ON Q1.Id=At.ConfStatIdFk 648 ) AS Q2 649 ON Q2.Id=PASI.AtomStatIdFk) AS Q3 650 JOIN ("""+self.removEnd(self.getPropValId(chargedef))+""")AS PD 651 ON PD.Id=Q3.PropDefIdFk ) AS Q4 652 JOIN ( 653 SELECT Id,AtomSeqNum,ElemTabIdFk 654 FROM Atoms) AS A 655 ON A.Id=Q4.AtomsIdFk ) AS Q5 656 JOIN ( 657 SELECT Id,AtomSymbol 658 FROM ElemTab) AS ET 659 ON ET.Id=Q5.ElemTabIdFk; 660 """ 661 return query
662
663 - def getXYZQonConfId(self,confId,chargedef):
664 """ 665 get X,Y,Z, and charge for specified conformation Id 666 INPUT: 667 confId - integer, conformation Id number 668 chargedf - str, charge definition 669 OUTPUT: 670 query, example 14 ind dictionary query 671 """ 672 query=""" 673 SELECT Q2.posX,Q2.posY,Q2.posZ,Q2.PropVal 674 FROM ( 675 SELECT At.posX,At.posY,At.posZ,Q1.PropVal,Q1.PropDefIdFk 676 FROM ( 677 SELECT posX,posY,posZ,Id 678 FROM AtomStat 679 WHERE ConfStatIdFk=%s)AS At 680 JOIN ( 681 SELECT AtomStatIdFk,PropVal,PropDefIdFk 682 FROM PropAtomStatInt) AS Q1 683 ON Q1.AtomStatIdFk=At.Id) AS Q2 684 JOIN ("""%(confId)+self.removEnd(self.getPropValId(chargedef))+""") AS PD 685 ON PD.Id=Q2.PropDefIdFk; 686 """ 687 return query
688
689 - def getIdPropValcondition(self,proptabname,propname,condition,value):
690 """ 691 get Id and property value for specified condition 692 INPUT: 693 proptabname- str, property table name 694 propname- str, property name 695 condition - str, mysql condition ">""<" 696 value - condition value 697 OUTPUT: 698 query, example 14 ind dictionary query 699 """ 700 if isinstance(value,str): 701 value='\'%s\'' %value 702 query=""" 703 SELECT ChemCompIdFk,PropVal 704 FROM %s AS PCCI 705 JOIN ("""%(proptabname)+self.removEnd(self.getPropValId(propname))+""") AS PD 706 ON PCCI.PropDefIdFk=PD.Id 707 WHERE PropVal %s %s; 708 """%(condition,value) 709 return query
710
711 - def getIdPropVal4condition(self,proptabname1,propname1,condition1,value1, 712 proptabname2,propname2,condition2,value2, 713 proptabname3,propname3,condition3,value3, 714 proptabname4,propname4,condition4,value4):
715 """ 716 get Id and propery value for specified condition, 4 tables 717 INPUT: 718 proptabname1- str, property table name 719 propname1- str, property name 720 condition1 - str, mysql condition ">""<" 721 value1 - condition value 722 proptabname2- str, property table name 723 propname2- str, property name 724 condition2 - str, mysql condition ">""<" 725 value2 - condition value 726 proptabname3- str, property table name 727 propname3- str, property name 728 condition3 - str, mysql condition ">""<" 729 value3 - condition value 730 proptabname4- str, property table name 731 propname4- str, property name 732 condition4 - str, mysql condition ">""<" 733 value4 - condition value 734 OUTPUT: 735 query, example 15 ind dictionary query 736 """ 737 query=""" 738 SELECT Q1.ChemCompIdFk, Q1.PropVal,Q2.PropVal,Q3.PropVal,Q4.PropVal 739 FROM ("""+self.removEnd(self.getIdPropValcondition(proptabname1,propname1,condition1,value1))+""" 740 ) AS Q1 741 JOIN 742 ("""+self.removEnd(self.getIdPropValcondition(proptabname2,propname2,condition2,value2))+""" 743 ) AS Q2 744 ON Q1.ChemCompIdFk=Q2.ChemCompIdFk 745 JOIN ("""+self.removEnd(self.getIdPropValcondition(proptabname3,propname3,condition3,value3))+""" 746 ) AS Q3 747 ON Q3.ChemCompIdFk=Q1.ChemCompIdFk 748 JOIN ("""+self.removEnd(self.getIdPropValcondition(proptabname4,propname4,condition4,value4))+""" 749 ) AS Q4 750 ON Q4.ChemCompIdFk=Q1.ChemCompIdFk; 751 """ 752 return query
753
754 - def getPropValonId(self,subId,proptabname,propname):
755 """ 756 get property value for specified compound Id 757 INPUT: 758 subId - int, substance Id 759 proptabname - str, property table name 760 propname - str, property name 761 OUTPUT: 762 query, example 17 ind dictionary query 763 """ 764 query=""" 765 SELECT ChemCompIdFk,PropVal 766 FROM %s AS PCCI 767 JOIN ("""%(proptabname)+self.removEnd(self.getPropValId(propname))+""") AS PD 768 ON PCCI.PropDefIdFk=PD.Id 769 WHERE ChemCompIdFk=%s;"""%(subId) 770 return query
771
772 - def getPropVal2onId(self,subId,proptabname1,propname1,proptabname2,propname2):
773 """ 774 get property value for specified compound Id 775 TWO table example 776 INPUT: 777 subId - int, substance Id 778 proptabname1 - str, property table name 779 propname1 - str, property name 780 proptabname2 - str, property table name 781 propname2 - str, property name 782 OUTPUT: 783 query, example 18 ind dictionary query 784 """ 785 query=""" 786 SELECT Q1.ChemCompIdFk AS %s,Q1.PropVal,Q2.PropVal AS %s 787 FROM ("""%(propname1,propname2)+self.removEnd(self.getPropValonId(subId=subId,proptabname=proptabname1,propname=propname1))+""") AS Q1 788 LEFT JOIN ("""+self.removEnd(self.getPropValonId(subId=subId,proptabname=proptabname2,propname=propname2))+""") AS Q2 789 ON Q1.ChemCompIdFk=Q2.ChemCompIdFk; 790 """ 791 return query
792
793 - def getConfNumonId(self,subId):
794 """ 795 get number of conformers for specified substance Id 796 INPUT: 797 subId- int, substance Id 798 OUTPUT: 799 query, example 19 ind dictionary query 800 """ 801 query=""" 802 SELECT SUM(CountId) 803 FROM ( 804 SELECT COUNT(CS.Id) AS CountId 805 FROM ConfStat AS CS 806 JOIN ( 807 SELECT Id 808 FROM ProtStat 809 WHERE ChemCompIdFk=%s ) AS PS 810 ON PS.Id=CS.ProtStatIdFk 811 GROUP BY CS.Id) AS Q1; 812 """%(subId) 813 return query
814
815 - def getIsoSmiconTautNum(self,proptabname,propname,condition,value):
816 """ 817 get isomeric SMILE for specified tautomer condition 818 INPUT: 819 proptabname - str, property table name 820 propname - str, property name 821 condition - str, number of tautome, mysql condition '>,<,=' 822 value - int, condition value 823 OUTPUT: 824 query, example 20 ind dictionary query 825 """ 826 query=""" 827 SELECT CC.IsoSmi,Q3.countaut,Q3.PropVal 828 FROM ( 829 SELECT Q1.ChemCompIdFk, Q1.countaut,Q2.PropVal 830 FROM ( 831 SELECT ChemCompIdFk, COUNT(ChemCompIdFk) AS countaut 832 FROM ProtStat 833 GROUP BY (ChemCompIdFk) 834 HAVING COUNT(ChemCompIdFk) %s %s) AS Q1 835 JOIN ( 836 SELECT PCCF.Id,ChemCompIdFk,PropVal 837 FROM %s AS PCCF 838 JOIN ("""%(condition,value,proptabname)+self.removEnd(self.getPropValId(propname))+""") AS PD 839 ON PCCF.PropDefIdFk=PD.Id 840 ) AS Q2 841 ON Q1.ChemCompIdFk=Q2.ChemCompIdFk) AS Q3 842 JOIN ( 843 SELECT Id,IsoSmi 844 FROM ChemComp )AS CC 845 ON CC.Id=Q3.ChemCompIdFk; 846 """ 847 return query
848
849 - def getIsoSmionConfNum(self,proptabname,propname,condition,value):
850 """ 851 get isomeric SMILE for specified conformer condition 852 INPUT: 853 proptabname - str, property table name 854 propname - str, property name 855 condition - str, number of tautomer, mysql condition '>,<,=' 856 value - int, condition value 857 OUTPUT: 858 query, example 21 ind dictionary query 859 """ 860 query=""" 861 SELECT Q3.ChemCompIdFk,Q3.PropVal,Q2.sumId 862 FROM ( 863 SELECT PS.ChemCompIdFk, SUM(Q1.countId) AS sumId 864 FROM ( 865 SELECT ProtStatIdFk,COUNT(ProtStatIdFk) AS countId 866 FROM ConfStat 867 GROUP BY ProtStatIdFk 868 HAVING COUNT(ProtStatIdFk) %s %s) AS Q1 869 JOIN ( 870 SELECT Id,ChemCompIdFk 871 FROM ProtStat) AS PS 872 ON PS.Id=Q1.ProtStatIdFk 873 GROUP BY PS.ChemCompIdFk) AS Q2 874 JOIN ( 875 SELECT PCCF.Id,ChemCompIdFk,PropVal 876 FROM %s AS PCCF 877 JOIN ("""%(condition,value,proptabname)+self.removEnd(self.getPropValId(propname))+""") AS PD 878 ON PCCF.PropDefIdFk=PD.Id) AS Q3 879 ON Q3.ChemCompIdFk=Q2.ChemCompIdFk; 880 """ 881 return query
882
883 - def getIsoSmionConfNumPropVal(self,proptabname,propname,propcondition,propfunction,cutoff,confcondition,confvalue):
884 """ 885 get isomeric SMILE for specified number of conformers and property value condition 886 INPUT: 887 proptabname - str, property table name 888 propname -str, property name 889 propcondition -str, property condition, ">,<" 890 propfunction - str, property function "MAX, MIN" 891 cutoff - str, property cutoff condition "+1, -2" 892 confcondition - str, conformation number condition, ">,<" 893 confvalue -int, conformation condition value 894 OUTPUT: 895 query, example 22 ind dictionary query 896 """ 897 query=""" 898 SELECT CC.IsoSmi,Q3.countId 899 FROM ChemComp AS CC 900 JOIN ( 901 SELECT IsoSmi,Q2.countId,ChemCompIdFk 902 FROM ProtStat AS PS 903 JOIN ( 904 SELECT ProtStatIdFk, COUNT(ProtStatIdFk) AS countId 905 FROM ConfStat AS CS 906 JOIN ( 907 SELECT ConfStatIdFk,PropVal 908 FROM %s AS PCSF 909 JOIN ("""%(proptabname)+self.removEnd(self.getPropValId(propname))+""") AS PD 910 ON PD.Id=PCSF.PropDefIdFk 911 WHERE PropVal %s ( 912 SELECT %s(PropVal) 913 FROM PropConfStatFloat AS PCSF 914 JOIN ("""%(propcondition,propfunction)+self.removEnd(self.getPropValId(propname))+""") AS PD 915 ON PD.Id=PCSF.PropDefIdFk)%s 916 ) AS Q1 917 ON Q1.ConfStatIdFk=CS.Id 918 GROUP BY ProtStatIdFk 919 HAVING COUNT(ProtStatIdFk) %s %s )AS Q2 920 ON Q2.ProtStatIdFk=PS.Id) AS Q3 921 ON Q3.ChemCompIdFk=CC.Id; 922 """%(cutoff,confcondition,confvalue) 923 return query
924
925 - def getIsoSmionAtomPropValcondition(self,proptabname,propname):
926 """ 927 get isomeric SMILE for atoms which do NOT have specified property condition 928 INPUT: 929 proptabname - str, property table name 930 propname - str, property name 931 OUTPUT: 932 query, example 23 ind dictionary query 933 """ 934 query=""" 935 SELECT IsoSmi 936 FROM ChemComp AS CC 937 JOIN ( 938 SELECT ChemCompIdFk 939 FROM ProtStat AS PS 940 JOIN ( 941 SELECT ProtStatIdFk 942 FROM ConfStat AS CS 943 JOIN ( 944 SELECT DISTINCT (ConfStatIdFk) AS distconfstatId 945 FROM AtomStat AS At 946 JOIN ( 947 SELECT AtomStatIdFk 948 FROM %s AS PASF 949 WHERE PASF.PropDefIdFk NOT IN ("""%(proptabname)+self.removEnd(self.getPropValId(propname))+""")) AS Q1 950 ON Q1.AtomStatIdFk=At.Id) AS Q2 951 ON Q2.distconfstatId=CS.Id) AS Q3 952 ON Q3.ProtStatIdFk=PS.Id) AS Q4 953 ON Q4.ChemCompIdFk=CC.Id; 954 """ 955 return query
956
957 - def getIsoSmiQonPropValcondition(self,proptabname,propname,atomsymbol,range=[]):
958 """ 959 get isomeric SMILE for atom with specified range property value 960 INPUT: 961 proptabname - str, property table name 962 propname- str, property name 963 atomsymbol - str, atom symbol 964 range - list, property range 965 OUTPUT: 966 query, example 24 ind dictionary query 967 """ 968 query=""" 969 SELECT IsoSmi,Q6.AtomSeqNum,Q6.PropVal 970 FROM ProtStat AS PS 971 JOIN ( 972 SELECT ProtStatIdFk,Q5.PropVal,Q5.AtomSeqNum 973 FROM ConfStat AS CS 974 JOIN ( 975 SELECT Q3.Id,Q3.ConfStatIdFk,Q3.AtomSeqNum,Q4.PropVal 976 FROM ( 977 SELECT ASt.Id,ASt.ConfStatIdFk,Q1.AtomSeqNum 978 FROM AtomStat AS ASt 979 JOIN ( 980 SELECT At.Id,At.AtomSeqNum 981 FROM Atoms AS At 982 JOIN ( 983 SELECT Id 984 FROM ElemTab 985 WHERE AtomSymbol='%s') AS ET 986 ON ET.Id=At.ElemTabIdFk 987 ) AS Q1 988 ON Q1.Id=ASt.AtomsIdFk 989 ) AS Q3 990 JOIN ( 991 SELECT AtomStatIdFk,PropVal 992 FROM %s AS PASF 993 JOIN ("""%(atomsymbol,proptabname)+self.removEnd(self.getPropValId(propname))+""") AS PD 994 ON PD.Id=PASF.PropDefIdFk) AS Q4 995 ON Q4.AtomStatIdFk=Q3.Id 996 WHERE Q4.PropVal BETWEEN %s AND %s 997 ) AS Q5 998 ON Q5.ConfStatIdFk=CS.Id 999 ) AS Q6 1000 ON Q6.ProtStatIdFk=PS.Id; 1001 """%(range[0],range[1]) 1002 return query
1003
1004 - def getIsoSmionAtomNum(self,atomsymbol,condition,value):
1005 """ 1006 get isomeric SMILE for specified number of atom 1007 INPUT: 1008 atomsymbol - str, atom symbol 1009 condition - str, condition "> <" 1010 value - value 1011 OUTPUT 1012 query, example 25 ind dictionary query 1013 """ 1014 query=""" 1015 SELECT IsoSmi,Q2.countId 1016 FROM ChemComp AS CC 1017 JOIN ( 1018 SELECT ChemCompIdFk,Q1.countId 1019 FROM ProtStat AS PS 1020 JOIN ( 1021 SELECT ProtStatIdFk,COUNT(ProtStatIdFk) countId 1022 FROM Atoms AS At 1023 JOIN ( 1024 SELECT Id 1025 FROM ElemTab 1026 WHERE AtomSymbol='%s') AS ET 1027 ON ET.Id=At.ElemTabIdFk 1028 GROUP BY ProtStatIdFk 1029 HAVING COUNT(ProtStatIdFk) %s %s) AS Q1 1030 ON Q1.ProtStatIdFk=PS.Id) AS Q2 1031 ON Q2.ChemCompIdFk=CC.Id; 1032 """%(atomsymbol,condition,value) 1033 return query
1034
1035 - def getIsoSmionPropValisempty(self,proptabname,propname):
1036 """ 1037 get isomeric SMILE if property value is empty 1038 INPUT: 1039 proptabname - str, property table name 1040 propname - str, property name 1041 OUTPUT: 1042 query, example 26 ind dictionary query 1043 """ 1044 query=""" 1045 SELECT IsoSmi 1046 FROM ChemComp AS CC 1047 JOIN ( 1048 SELECT ChemCompIdFk 1049 FROM %s AS PCCT 1050 WHERE PropDefIdFk NOT IN 1051 ("""%(proptabname)+self.removEnd(self.getPropValId(propname))+""")) AS Q1 1052 ON Q1.ChemCompIdFk=CC.Id; 1053 """ 1054 return query
1055
1056 - def getIsoSmionPropValexist(self,proptabname,propname):
1057 """ 1058 get isomeric SMILE if propery value exists 1059 INPUT: 1060 proptabname - str, property table name 1061 propname - str, property name 1062 OUTPUT: 1063 query, example 27 ind dictionary query 1064 """ 1065 query=""" 1066 SELECT IsoSmi 1067 FROM ChemComp AS CC 1068 JOIN ( 1069 SELECT ChemCompIdFk 1070 FROM %s AS PCCT 1071 JOIN ("""%(proptabname)+self.removEnd(self.getPropValId(propname))+""") AS PD 1072 ON PD.Id=PCCT.PropDefIdFk) AS Q1 1073 ON Q1.ChemCompIdFk=CC.Id; 1074 """ 1075 return query
1076
1077 - def getMaxId(self,tabname):
1078 """ 1079 get maximal Id value 1080 INPUT: 1081 tabname - str, table name 1082 OUTPUT" 1083 query 1084 """ 1085 query=""" 1086 SELECT max(Id) AS maxId 1087 FROM %s; 1088 """%(tabname) 1089 return query
1090
1091 - def setLastId(self,tabname,value):
1092 """ 1093 set last inserted Id 1094 INPUT: 1095 tabname -str, table name 1096 value - int, last Id value 1097 """ 1098 query=""" 1099 ALTER TABLE %s AUTO_INCREMENT=%s; 1100 """%(tabname,value) 1101 return query
1102
1103 - def getMaxId2(self,tabname,colname):
1104 """ 1105 get maximal Id value 1106 INPUT: 1107 tabname - str, table name 1108 OUTPUT" 1109 query 1110 """ 1111 query=""" 1112 SELECT max(%s) AS maxId 1113 FROM %s; 1114 """ %(colname,tabname) 1115 return query
1116
1117 - def getXYZQAtNumAtSeqNumonConfId(self,confId,chargedef):
1118 """ 1119 get X,Y,Z,charge, atom number, sequence number for specified conformer Id 1120 INPUT: 1121 confid - str, conformation id 1122 chargedef - str, charge definition 1123 OUTPUT: 1124 query, example 35 ind dictionary query 1125 """ 1126 query=""" 1127 SELECT Q3.posX,Q3.posY,Q3.posZ,Q3.PropVal,A.AtomSeqNum,A.ElemTabIdFk 1128 FROM ( 1129 SELECT Q2.posX,Q2.posY,Q2.posZ,Q2.PropVal,Q2.AtomsIdFk 1130 FROM ( 1131 SELECT At.posX,At.posY,At.posZ,At.AtomsIdFk,Q1.PropVal,Q1.PropDefIdFk 1132 FROM ( 1133 SELECT posX,posY,posZ,Id,AtomsIdFk 1134 FROM AtomStat 1135 WHERE ConfStatIdFk=%s)AS At"""%confId+""" 1136 JOIN ( 1137 SELECT AtomStatIdFk,PropVal,PropDefIdFk 1138 FROM PropAtomStatFloat) AS Q1 1139 ON Q1.AtomStatIdFk=At.Id) AS Q2 1140 JOIN ("""+self.removEnd(self.getPropValId(chargedef))+""") AS PD 1141 ON PD.Id=Q2.PropDefIdFk) AS Q3 1142 JOIN ( 1143 SELECT Id,AtomSeqNum,ElemTabIdFk 1144 FROM Atoms) AS A 1145 ON A.Id=Q3.AtomsIdFk; 1146 """ 1147 return query
1148
1149 - def getIdXYZConfId(self,confId):
1150 """ 1151 get Id,X,Y,Z, AtomsIdFk for specified conformer Id 1152 INPUT: 1153 confid - str, conformation id 1154 OUTPUT: 1155 query, example 36 ind dictionary query 1156 """ 1157 query=""" 1158 SELECT Id,posX,posY,posZ,AtomsIdFk 1159 FROM AtomStat 1160 WHERE ConfStatIdFk=%s; 1161 """%confId 1162 return query
1163 1164 ####### End of class ##################################################################### 1165 ####### MAIN ############################################################################# 1166 ######## Example of usage ################################################################ 1167 if __name__=='__main__': 1168 pass 1169 A=UserQuery() 1170 # print A.getPropValId('NRB') 1171 # print A.getDictQuery(1) 1172 # print A.getPropValIdin(propname=('tPSA','MWT')) 1173 # print A.getDictQuery(2) 1174 # print A.getIsoSmiId(isosmi='CC[C@@]12CCC[N@H+]3[C@@H]1c4c(c5ccccc5n4C(=C2)C(=O)OCC)CC3') 1175 # print A.getDictQuery(3) 1176 # print A.getPropValonIsoSmi1(isosmi='CC[C@@]12CCC[N@H+]3[C@@H]1c4c(c5ccccc5n4C(=C2)C(=O)OCC)CC3' 1177 # ,proptabname='PropChemCompFloat',propname='MWT') 1178 # print A.getDictQuery(4) 1179 # print A.getPropValonIsoSmi2(isosmi='CC[C@@]12CCC[N@H+]3[C@@H]1c4c(c5ccccc5n4C(=C2)C(=O)OCC)CC3', 1180 # proptabname1='PropChemCompInt', propname1='NRB', 1181 # proptabname2='PropChemCompFloat',propname2='MWT') 1182 # print A.getDictQuery(5) 1183 # print A.getIsoSmionPropValrange1(proptabname='PropChemCompInt',propname='tPSA',range=[40,42]) 1184 # print A.getDictQuery(6) 1185 # print A.getIdPropNamePropVal(isosmi='CC[C@@]12CCC[N@H+]3[C@@H]1c4c(c5ccccc5n4C(=C2)C(=O)OCC)CC3', 1186 # proptabname='PropChemCompInt',propname=('tPSA','NRB','PUBCHEM_ISOTOPIC_ATOM_COUNT')) 1187 # print A.getDictQuery(7) 1188 # print A.getIsoSmionPropValrangeand(proptabname1='PropChemCompInt',propname1='tPSA',range=[40,50], 1189 # proptabname2='PropChemCompFloat',propname2='MWT',condition='<',value=200) 1190 # print A.getDictQuery(8) 1191 # print A.getIsoSmiPropValNumConflike(proptabname1='PropChemCompText',propname1='PUBCHEM_NIST_INCHI',proptabname2='PropChemCompText',propname2='PUBCHEM_IUPAC_NAME',like='%/C3H9NO/%') 1192 # print A.getDictQuery(9) 1193 # print A.getConfIdonIsoSmi(isosmi='CC[C@@]12CCC[N@H+]3[C@@H]1c4c(c5ccccc5n4C(=C2)C(=O)OCC)CC3') 1194 # print A.getDictQuery(10) 1195 # print A.getXYZonIsoSmi(isosmi='CC[C@@]12CCC[N@H+]3[C@@H]1c4c(c5ccccc5n4C(=C2)C(=O)OCC)CC3') 1196 # print A.getDictQuery(11) 1197 # print A.getXYZQonIsoSmi(isosmi='CC[C@@]12CCC[N@H+]3[C@@H]1c4c(c5ccccc5n4C(=C2)C(=O)OCC)CC3',chargedef='Charge') 1198 # print A.getDictQuery(12) 1199 # print A.getXYZQAtSymAtSeqNumonIsoSmi(isosmi='CC[C@@]12CCC[N@H+]3[C@@H]1c4c(c5ccccc5n4C(=C2)C(=O)OCC)CC3',chargedef='Charge') 1200 # print A.getDictQuery(13) 1201 # A.getXYZQonConfId(confId=5,chargedef='Charge') 1202 # print A.getDictQuery(14) 1203 # print A.getIdPropValcondition(proptabname='PropChemCompInt',propname='tPSA',condition=">",value=40) 1204 # print A.getDictQuery(15) 1205 # print A.getIdPropVal4condition(proptabname1='PropChemCompInt',propname1='tPSA',condition1=">",value1=40, 1206 # proptabname2='PropChemCompInt',propname2='NRB',condition2="=",value2=3, 1207 # proptabname3='PropChemCompFloat',propname3='MWT',condition3="<",value3=200, 1208 # proptabname4='PropChemCompText',propname4='PUBCHEM_IUPAC_TRADITIONAL_NAME',condition4="LIKE",value4='%1-aminopropan-2-ol%') 1209 # print A.getDictQuery(16) 1210 # print A.getPropValonId(proptabname='PropChemCompInt',propname='tPSA',subId=4) 1211 # print A.getDictQuery(17) 1212 # print A.getPropVal2onId(subId=4,proptabname1='PropChemCompInt',propname1='tPSA' 1213 # ,proptabname2='PropChemCompFloat',propname2='MWT') 1214 # print A.getDictQuery(18) 1215 # print A.getConfNumonId(subId=2) 1216 # print A.getDictQuery(19) 1217 # print A.getIsoSmiconTautNum(proptabname='PropChemCompText',propname='PUBCHEM_NIST_INCHI',condition='>',value=1) 1218 # print A.getDictQuery(20) 1219 # print A.getIsoSmionConfNum(proptabname='PropChemCompText',propname='PUBCHEM_NIST_INCHI',condition='>',value=1) 1220 # print A.getDictQuery(21) 1221 # print A.getIsoSmionConfNumPropVal(proptabname='PropConfStatFloat',propname='logP',propcondition=">", 1222 # propfunction="MIN",cutoff='+1',confcondition="<",confvalue=2) 1223 # print A.getDictQuery(22) 1224 # print A.getIsoSmionAtomPropValcondition(proptabname='PropAtomStatFloat',propname='chargePM3') 1225 # print A.getDictQuery(23) 1226 # print A.getIsoSmiQonPropValcondition(proptabname='PropAtomStatFloat',propname='Charge',atomsymbol='O',range=[-2,0.1]) 1227 # print A.getDictQuery(24) 1228 # print A.getIsoSmionAtomNum(atomsymbol='O',condition='>',value=1) 1229 # print A.getDictQuery(25) 1230 # print A.getIsoSmionPropValisempty(proptabname='PropChemCompText',propname='PUBCHEM_IUPAC_CAS_NAME') 1231 # print A.getDictQuery(26) 1232 # print A.getIsoSmionPropValexist(proptabname='PropChemCompText',propname='SMILES') 1233 # print A.getDictQuery(27) 1234 # print A.updateTable('PropDef','PropDesc','NULL','PropName','PUBCHEM_COMPONENT_COUNT') 1235 # print A.getDictQuery(28) 1236 # print A.insertRecord('ChemComp', ['IsoSmi','ZINCode'], ['CC','Null']) 1237 # print A.getDictQuery(29) 1238 # print A.select('ChemComp',['Id','IsoSmi'],where="Id=3",order='Id') 1239 # print A.getDictQuery(30) 1240 # print A.join('ChemComp',['ProtStat'],['ChemComp.Id'],['Id','ChemCompIdFk'],where='ProtStat.Id=10',limit=10) 1241 # print A.getDictQuery(31) 1242 # print A.getMaxId(tabname='ChemComp') 1243 # print A.getDictQuery(32) 1244 # print A.setLastId(tabname='ChemComp',value=18) 1245 # print A.getDictQuery(33) 1246 # print A.getMaxId2(tabname='ChemComp',colname='Id') 1247 # print A.getDictQuery(34) 1248 # print A.getXYZQAtNumAtSeqNumonConfId(confId='1',chargedef='Charge') 1249 # print A.getDictQuery(35) 1250 # print A.getIdXYZConfId(confId=943535) 1251