Package install :: Package MoSTBioDat :: Package DataBase :: Package Connect :: Module MoSTBioDatDB
[hide private]
[frames] | no frames]

Source Code for Module install.MoSTBioDat.DataBase.Connect.MoSTBioDatDB

  1  #!/usr/bin/env python 
  2  ###################################################### 
  3  # MoSTBioDatDB.py: Using the MySQLdb DB-API with the # 
  4  # MoStBioDat 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, 07.01.2010                             # 
 14  ###################################################### 
 15   
 16  try: 
 17      import sys 
 18      import os 
 19      import socket 
 20      import time 
 21      import MySQLdb 
 22      import numpy 
 23      from getpass import getuser,getpass 
 24      from timeit import Timer 
 25      from getpass import getuser,getpass 
 26      from MoSTBioDatDesc import DescMoSTBioDat_host,DescMoSTBioDat_db,DescMoSTBioDat_port 
 27      from MoSTBioDatErrors import ErrorMessage,UserAuthenticationError,Error,Warning,InterfaceError,DatabaseError,InternalError 
 28      from MoSTBioDatErrors import OperationalError,ProgrammingError,IntegrityError,DuplicateEntryError, DataError,NotSupportedError,MissingInputError 
 29      from MoSTBioDatErrors import DataQueryError,ArrayError,DataImportTypeError,MissingDataError 
 30      from MoSTBioDatDBSupport import dict2array,descolumn,filterKeys,PickleArray,rmfile,findQueryCacheFile,getQueryFileObject 
 31      from MoSTBioDat.Log.MoSTBioDatLog import MoSTBioDatLog,datetime,geTime 
 32      from MoSTBioDat.DataBase.Query.UserQuery.UserQuery import UserQuery 
 33      from MoSTBioDat.DataBase.Query.SQLBuilder.QueryBuilder import * 
 34   
 35  except ImportError,e: 
 36      print 'Error: %s' %e 
 37      sys.exit(1) 
 38   
 39  ############ MoSTBioDatDB class ##################### 
40 -class MoSTBioDatDB(object):
41 42 numberConn=0 43 host=DescMoSTBioDat_host(name='host') 44 db=DescMoSTBioDat_db(name='db') 45 port=DescMoSTBioDat_port(name='port') 46 ####### Global settings for MySQL module ###### 47 MySQLdb.threadsafety=1 48 # 0 - threads may not share module 49 # 1 - threads may share module but not connections 50 # 2 - threads may share module and connections 51 # 3 - threads may share module, connections and cursors 52 MySQLdb.apilevel='2.0' 53 # support DB API level 54 MySQLdb.paramstyle='format' 55 # type of parameter marker formatting expected by interface 56 # qmark - question mark style e.g WHERE name=? 57 # numeric - numeric, positional style, e.g WHERE=:1 58 # format - ANSI C printf forma codes, e.g. WHERE=name=%s 59 # pyformat - Python extended format codes e.g WHERE=%(name)s 60 #############################################
61 - def __init__(self,host='localhost',db='Ligand',user=None,passwd=None,port=3306,**kwargs):
62 """ 63 INPUT: 64 host - string, host to connect 65 user - string, user to connect as 66 passwd - string, password to use 67 db - string, database to use 68 port - integer, TCP/IP port to connect to 69 unix_socket - string, location of unix_socket to use 70 conv - conversion dictionary, see MySQLdb.converters 71 connect_timeout - number of seconds to wait before the connection attempt fails. 72 compress - if set, compression is enabled 73 named_pipe - if set, a named pipe is used to connect (Windows only) 74 init_command - command which is run once the connection is created 75 read_default_file - file from which default client values are read 76 read_default_group - configuration group to use from the default file 77 cursorclass - class object, used to create cursors (keyword only) 78 use_unicode - if True, text-like columns are returned as unicode objects 79 using the connection's character set. Otherwise, text-like 80 columns are returned as strings. columns are returned as 81 normal strings. Unicode objects will always be encoded to 82 the connection's character set regardless of this setting. 83 charset - if supplied, the connection character set will be changed 84 to this character set (MySQL-4.1 and newer). This implies 85 use_unicode=True. 86 sql_mode - if supplied, the session SQL mode will be changed to this 87 setting (MySQL-4.1 and newer). For more details and legal 88 values, see the MySQL documentation. 89 client_flag - integer, flags to use or 0 90 (see MySQL docs or constants/CLIENTS.py) 91 ssl - dictionary or mapping, contains SSL connection parameters; 92 see the MySQL documentation for more details 93 (mysql_ssl_set()). If this is set, and the client does not 94 support SSL, NotSupportedError will be raised. 95 local_infile - integer, non-zero enables LOAD LOCAL INFILE; zero disables 96 format - string format for log handler 97 filter - filter object from logger object 98 datefmt - data/time format 99 path - directory path to log file 100 filename - log filename, default log 101 filemode - mode to open log file, default='a' 102 level - set root logger level to specified level 103 logfilelevel- set level to log file 104 OUTPUT: 105 class object 106 """ 107 self.kwargs={} 108 self.kwargs=kwargs 109 self.connection=None 110 self.cursor=None 111 keys=("conv","quote_conv","cursorclass","charset", 112 "connect_timeout","compress", 113 "named_pipe","init_command", 114 "read_default_file", "read_default_group", 115 "unix_socket", "use_unicode","client_flag", 116 "sql_mode","ssl","local_infile", 117 "format","filter","datefmt","path", 118 "filename","filemode","level","logfilelevel","temporary") 119 filter(lambda key: filterKeys(kwargs,keys,key),kwargs.keys())#filter keys 120 MoSTBioDatDB.numberConn+=1 121 self.kwargs['host']=host 122 self.kwargs['db']=db 123 if not user: 124 self.kwargs['user']=getuser() 125 self.kwargs['user']=user 126 if not passwd: 127 print 'Missing password!' 128 sys.exit(1) 129 self.kwargs['passwd']=passwd 130 self.kwargs['port']=port 131 self.status='Disconnected' 132 self.starTrans=0 133 try: 134 self.logobj=MoSTBioDatLog(**kwargs)#create logging object 135 self.log=self.logobj.getLogHandler()#create logging handler 136 except IOError,e: 137 print 'Error: %s, %s' %(e[0],e[1]) 138 sys.exit(1)
139
140 - def __repr__(self):
141 return str(["%s : %s" %(key,value) for (key,value) in self.kwargs.iteritems()])
142
143 - def getClientInfo(self):
144 """ 145 database info 146 INPUT: 147 class object 148 OUTPUT: 149 str 150 """ 151 print 'Client settings:' 152 for (key,value) in self.kwargs.iteritems(): 153 print "%s : %s" %(key,value)
154
155 - def getNumberConnect(self):
156 """ 157 get number of connection 158 INPUT: 159 class object 160 OUTPUT: 161 number of connection 162 """ 163 print "Database connection: %i" %MoSTBioDatDB.numberConn 164 return MoSTBioDatDB.numberConn
165 getNumberConnect=staticmethod(getNumberConnect) 166
167 - def getConnectPort(self):
168 """ 169 Get port 170 INPUT: 171 class object 172 OUTPUT: 173 port 174 """ 175 return self.kwargs['port']
176
177 - def displayStatus(self):
178 """ 179 display the status of the connection 180 INPUT: 181 class object 182 OUTPUT: 183 status of connection 184 """ 185 print "status : %s" %(self.status)
186
187 - def isDB(self):
188 """ 189 check whether database server is available on given port 190 INPUT: 191 class object 192 OUTPUT: 193 boolean 194 """ 195 try: 196 sock=socket.socket(socket.AF_INET,socket.SOCK_STREAM) # create new socket 197 sock.connect((self.kwargs['host'],self.kwargs['port'])) # connect to server on given port 198 sock.settimeout(20) # set timeout on I/O operations 199 sock.close() 200 self.log.info('Socket connection') 201 except socket.error,e: 202 print "Connection failed." 203 self.log.exception('Socket connection failed, error %s',e) 204 print "Error: %s, %s" %(e[0],e[1]) 205 return False 206 else: 207 return True
208
209 - def connectDB(self):
210 """ 211 create database connection 212 INPUT: 213 class object 214 OUTPUT: 215 connection 216 """ 217 if self.kwargs['passwd']=='': 218 self.log.fatal('Error: Missing password!') 219 raise MissingInputError('Error: Missing password!')#def in error module 220 if self.kwargs['user']=='': 221 self.log.fatal('Error: Missing user!') 222 raise MissingInputError('Error: Missing user!')#def in error module 223 keys=("conv","quote_conv","cursorclass", 224 "connect_timeout","compress", 225 "named_pipe","init_command", 226 "read_default_file", "read_default_group", 227 "unix_socket", "use_unicode","client_flag", 228 "sql_mode","ssl","local_infile","passwd","db","host","user","port") 229 ###select keys to MySQLdb.connect 230 filter(lambda key: filterKeys(self.kwargs,keys,key),self.kwargs.keys()) 231 try: 232 self.timeStart=datetime()#get time 233 self.log.info('Start DB connection to %s',self.kwargs['host']) 234 print "Trying to connect to %s on port %d, please wait ..." %(self.kwargs['host'],self.kwargs['port']) 235 self.connection=MySQLdb.connect(**self.kwargs) 236 print "Connection to %s succeeded." %(self.connection.get_host_info()) 237 print "Database server: %s" %(self.connection.get_server_info()) 238 if self.connection: 239 self.status='Connected' 240 self.log.info('Connection successful') 241 except MySQLdb.OperationalError, e: 242 self.log.exception('Operational Error: %s',e) 243 raise UserAuthenticationError(ErrorMessage(e))#def in error module 244 sys.exit(1) 245 except MySQLdb.InternalError,e: 246 self.log.exception('Internal Error: %s',e) 247 raise InternalError(ErrorMessage(e))#def in error module 248 sys.exit(1) 249 else: 250 return self.connection
251
252 - def executeQuery(self,query,**kwarg):
253 """ 254 create cursor and execute SQL query 255 INPUT: 256 query - str - SQL query 257 cache - create cache for query, default=True 258 cachepath - str - path to cache directory, default - current working directory 259 cachefilename - str - cache filenames, default query 260 cachedictfilename - str - dictionary cache filename, default - dictQuery 261 numcachefiles - int - number cache files 262 protocol - int - shelve, cPickle protocol, default -1 (highest) 263 temporary - str - temporary cache directory, default /tmp/Query 264 refresh - boolean - refresh cache memory, default=False 265 OUTPUT: 266 array, dictionary 267 """ 268 self.kwarg={} 269 self.kwarg=kwarg 270 if not self.kwarg.has_key('refresh'): 271 self.kwarg.setdefault('refresh',False) 272 273 if not self.kwarg.has_key('cache'): 274 self.kwarg.setdefault('cache',False) 275 self.cache=self.kwarg['cache'] 276 277 keys=("query","cache","cachepath","cachefilename","cachedictfilename","protocol", 278 "numcachefiles","temporary","refresh") 279 filter(lambda key: filterKeys(kwarg,keys,key),kwarg.keys())#filter keys 280 if self.connection is not None: 281 self.log.info('Query execution: %s',query) 282 283 if self.cache: 284 cache=PickleArray(query,**kwarg)#user cache memory 285 print 'Searching cache memory, please wait ...' 286 self.log.info('Searching cache memory') 287 cacheobj,filepath=cache.cpickle2array()#create array from cPickle with file path 288 if cacheobj: 289 if self.kwarg['refresh']: 290 print 'Refreshing cache file: %s' %filepath 291 self.log.info('Refreshing cache file %s',filepath) 292 rmfile(filepath)#remove cPickle file 293 else: 294 columndescription=cacheobj.get('desc',None) 295 outarray=cacheobj.get('array',None) 296 print "Query in cache file: %s" %filepath 297 self.log.info('Query in %s',filepath) 298 return outarray, columndescription 299 else: 300 print 'No query in cache memory!' 301 self.log.info('No query in cache memory') 302 303 try: 304 self.cursor=self.connection.cursor(cursorclass=MySQLdb.cursors.DictCursor) 305 #create cursor 306 except MySQLdb.InternalError, e: 307 self.connection=None 308 self.log.exception('Cursor error: %s',e) 309 raise InternalError(ErrorMessage(e))#def in error module 310 else: 311 if self.kwargs['db']!='': 312 self.cursor.execute('use %s' %self.kwargs['db'])#SQL use database; 313 try: 314 t0=time.time() 315 print 'Database query execution, please wait ...' 316 self.cursor.execute(query) 317 if self.starTrans: 318 self.autocommit(0) 319 else: 320 self.autocommit(1) 321 except MySQLdb.OperationalError,e: 322 self.log.exception('Operational error: %s',e) 323 raise OperationalError(ErrorMessage(e))#def in error module 324 except MySQLdb.ProgrammingError,e: 325 self.log.exception('Programming error: %s',e) 326 raise ProgrammingError(ErrorMessage(e))#def in error module 327 except MySQLdb.IntegrityError,e: 328 self.log.exception('Integrity error: %s',e) 329 raise IntegrityError(ErrorMessage(e))#def in error module 330 except MySQLdb.InternalError,e: 331 self.log.exception('Internal error: %s',e) 332 raise InternalError(ErrorMessage(e))#def in error module 333 except MySQLdb.DataError,e: 334 self.log.exception('Data error error: %s',e) 335 raise DataError(ErrorMessage(e))#def in error module 336 except MySQLdb.NotSupportedError,e: 337 self.log.exception('Not supported error: %s',e) 338 raise NotSupportedError(ErrorMessage(e))#def in error module 339 except MySQLdb.DatabaseError,e: 340 self.log.exception('Database error: %s',e) 341 raise DatabaseError(ErrorMessage(e))#def in error module 342 except MySQLdb.InterfaceError,e: 343 self.log.exception('Interface error: %s',e) 344 raise InterfaceError(ErrorMessage(e))#def in error module 345 except MySQLdb.Warning,e: 346 self.log.exception('Warning: %s',e) 347 raise Warning(ErrorMessage(e))#def in error module 348 except MySQLdb.Error,e: 349 self.log.exception('MySQLdb error: %s',e) 350 raise Error(ErrorMessage(e)) 351 else: 352 outarray=numpy.array([],dtype=object)#create numpy.array with object type 353 counter=0 354 while True:#retrieve data from database fetching row by row 355 row=self.cursor.fetchone() 356 if not row: 357 break 358 rowarray=dict2array(row,counter)#from cursor row dictionary create object array 359 outarray=numpy.append(outarray,rowarray)#add row array to result array 360 counter+=1 361 lenelem=rowarray.shape[1]#element length 362 363 if counter: 364 outarray.resize(counter+1,lenelem)#resize result array 365 cursorcolumndesc=self.cursor.description#column description 366 if cursorcolumndesc is None: 367 columndescription={} 368 else: 369 columndescription=descolumn(cursorcolumndesc)#get MySQL column descriptions 370 else: 371 columndescription={} 372 t1=time.time() 373 dt=t1-t0 374 if self.cursor.rowcount>1:#improve print row or rows 375 print 'Rows: %d rows in %s' %(self.cursor.rowcount,geTime(dt)) 376 else: 377 print 'Rows: %d row in %s' %(self.cursor.rowcount,geTime(dt)) 378 self.log.info('Database time query execution: %s',geTime(dt)) 379 380 if self.cache: 381 objdict={} 382 objdict['desc']=columndescription 383 objdict['array']=outarray 384 cache.query2dict(self.log) 385 cache.array2cpickle(objdict,self.log)#create cPickle file with result array 386 387 self.cursor.close() 388 return outarray, columndescription 389 else: 390 print 'Connect to database!'
391
392 - def executeFileQuery(self,path,**kwarg):
393 """ 394 execute SQL query from file 395 INPUT: 396 path - str - path to file with query 397 kwarg - like for executeQuery 398 OUTPUT 399 array, dictionary 400 """ 401 if (os.path.exists(path) and os.path.isfile(path)): 402 if os.access(path,os.R_OK): 403 try: 404 file=open(path,'r').read() 405 except IOError,e: 406 print 'Error: %s' 407 self.log.exception('Error: %s',e) 408 else: 409 return self.executeQuery(str(file),**kwarg) 410 else: 411 print 'Access denied: %s' %path 412 self.log.info('Access denied: %s',path) 413 sys.exit(1) 414 else: 415 print 'Unable to find: %s!' %path 416 self.log.info('Unable to find file!') 417 sys.exit(1)
418
419 - def commit(self):
420 """ 421 commit changes to database 422 INPUT: 423 class object 424 OUTPUT: 425 connection committed 426 """ 427 if self.connection: 428 self.connection.commit() 429 else: 430 print 'Connect to database!' 431 sys.exit(1)
432
433 - def rollback(self):
434 """ 435 rollback uncommitted changes 436 INPUT: 437 class object 438 OUTPUT: 439 connection rollbacked 440 """ 441 if self.connection: 442 self.connection.rollback() 443 else: 444 print 'Connect to database!' 445 sys.exit(1)
446
447 - def autocommit(self,value):
448 """ 449 set autocommit 450 INPUT: 451 class object 452 value - int, 0 disable, 1 enable autocommit 453 OUTPUT: 454 autocommit connection 455 """ 456 if self.connection: 457 self.connection.autocommit(value) 458 else: 459 print 'Connect to database!' 460 sys.exit(1)
461
462 - def starTransaction(self):
463 """ 464 start transaction 465 INPUT: 466 class object 467 OUTPUT: 468 array - output array 469 descr - output column description 470 """ 471 self.starTrans=1; 472 self.autocommit(0) 473 array,descr=self.executeQuery("START TRANSACTION;") 474 return array,descr
475
476 - def lockTable(self,tabname):
477 """ 478 lock table 479 INPUT: 480 class object 481 tabname - str, table name 482 OUTPUT: 483 array - output array 484 descr - output column description 485 """ 486 self.autocommit(0) 487 query="LOCK TABLE %s READ, %s AS tab WRITE;" %(tabname,tabname) 488 array,descr=self.executeQuery(query) 489 return array,descr
490
491 - def unlockTables(self):
492 """ 493 unlock tables 494 INPUT: 495 class object 496 OUTPUT: 497 array - output array 498 descr - output column description 499 """ 500 array,descr=self.executeQuery("UNLOCK TABLES;") 501 return array,descr
502
503 - def closeDB(self):
504 """ 505 close the connection to the database 506 INPUT: 507 class object 508 OUTPUT: 509 connection close 510 """ 511 if not self.connection: 512 print 'Connect to database!' 513 sys.exit(1) 514 else: 515 try: 516 self.connection.close() 517 self.kwargs['status']='disconnected' 518 self.status='Disconnected' 519 print 'Connection closed!' 520 self.log.info('Connection closed') 521 self.logobj.rmLogHandler() 522 except MySQLdb.Error, e: 523 print "Error: %s" %e 524 self.log.exception('Unable to close DB connection, error: %s',e) 525 self.logobj.rmLogHandler() 526 sys.exit(1)
527 ################## End of class ######################## 528 ############## MAIN ################################ 529 ############ Example of usage ########################## 530 if __name__=='__main__': 531 pass 532 # A=MoSTBioDatDB(host='',user='',passwd='',path='/tmp/Log',db='',filename='database') 533 # A.getClientInfo() 534 # A.isDB() 535 # A.connectDB() 536 # a,b=A.lockTable('ChemComp') 537 # A.unlockTables() 538 # ############ SQL Query ######################################## 539 # array,descr=A.executeQuery('select Id, IsoSmi from ChemComp limit 10;') 540 # array,descr=A.executeQuery('select * from ProtStat limit 8;') 541 # array,descr=A.executeQuery(""" 542 # SELECT ChemComp.IsoSmi AS 'isomeric smile',ZINCode AS 'zincode' 543 # FROM ChemComp JOIN ProtStat 544 # ON ChemComp.Id=ProtStat.ChemCompIdFk 545 # LIMIT 7; 546 # """,cache=True,refresh=True,cachepath='/tmp/Query',cachefilename='query',numcachefiles=10) 547 # ########## SQL Query from File ################################ 548 # array,descr=A.executeFileQuery('/tmp/query',cache=True,refresh=True,cachepath='/tmp/Query',cachefilename='query',numcachefiles=3) 549 # ########### SQL Query with sqlBuilder ########################### 550 # query=queryBuilder(Select([table.ElemTab.Id, table.ElemTab.AtomName], where=LIKE(table.ElemTab.AtomName, "%M%"))) 551 # array,descr=A.executeQuery(query,cache=True,refresh=True,cachepath='/tmp/Query',cachefilename='query',numcachefiles=3) 552 # ############ SQL User Query ##################################### 553 # query=UserQuery().showTable('Ligand') 554 # array,descr=A.executeQuery('desc chemcomp;') 555 # array,descr=A.executeQuery(query,cache=True,refresh=True,cachepath='/tmp/Query',cachefilename='query',numcachefiles=3) 556 # ######### EMPTY result ######################################### 557 # array,descr=A.executeQuery(""" 558 # SELECT Id FROM ElemTab WHERE AtoMass is NULL; 559 # """,cache=True,refresh=True,cachepath='/tmp/Query',cachefilename='query') 560 # ######## Data Manipulation Language ############################# 561 # array,descr=A.executeQuery(""" 562 # UPDATE ElemTab 563 # SET AtoMass=272.000 564 # WHERE AtomSymbol='Rg'; 565 # """) 566 # ####### TRANSACTIONS ############################################## 567 # A.starTransaction() 568 # array,descr=A.executeQuery(""" 569 # UPDATE ElemTab 570 # SET AtoMass=272.000 571 # WHERE AtomSymbol='Rg'; 572 # """) 573 # A.commit() 574 # A.rollback() 575 # array,descr=A.executeQuery(""" 576 # SELECT * 577 # FROM ElemTab 578 # WHERE AtomSymbol='Rg'; 579 # """) 580 # array,descr=A.executeQuery(""" 581 # SELECT * 582 # FROM ChemComp 583 # WHERE IsoSmi='C=CCSc1c2c([nH]cn2)ncn1'; 584 # """) 585 # print descr 586 # print array 587 # print array.shape 588 # A.closeDB() 589 ###################################################### 590