def initialize(): global logTestFile, htmpFile global fpLogTest, fpHTMP logTestFile = os.getenv('LOG_TEST') htmpFile = os.getenv('HTMPUNIQ_INPUT_FILE') rc = 0 # # Make sure the environment variables are set. # if not logTestFile: print 'Environment variable not set: LOG_TEST' rc = 1 # # Make sure the environment variables are set. # if not htmpFile: print 'Environment variable not set: HTMPUNIQ_INPUT_FILE' rc = 1 # # Initialize file pointers. # fpLogTest = None fpHTMP = None db.useOneConnection(1) return rc
def updateDatabase(cmds): dbServer = os.environ['MGD_DBSERVER'] dbName = os.environ['MGD_DBNAME'] dbUser = os.environ['MGD_DBUSER'] dbPasswordFile = os.environ['MGD_DBPASSWORDFILE'] dbPassword = string.strip(open(dbPasswordFile, 'r').readline()) db.set_sqlLogin(dbUser, dbPassword, dbServer, dbName) # process in batches of 100 total = len(cmds) try: db.useOneConnection(1) while cmds: print('Current running time (secs): %s' % (time.time() - STARTTIME)) db.sql(cmds[:100], 'auto') cmds = cmds[100:] db.useOneConnection(0) except: bailout('Failed during database updates') print('Processed %d updates to SEQ_Sequence._SequenceStatus_key' % total) print('Total running time (secs): %s' % (time.time() - STARTTIME)) return
def init(): # Purpose: initialize file descriptors, create connection to # the database and load lookups # Returns: nothing # Assumes: nothing # Effects: gives SQL commands to the database # Throws: nothing global inFile, outFile, nextKey print '%s' % mgi_utils.date() print 'Initializing' db.useOneConnection(1) db.set_sqlLogin(user, password, mgdServer, mgdDB) results = db.sql('''select max(_Assoc_key) + 1 as nextKey from %s''' % table, 'auto') nextKey = results[0]['nextKey'] if nextKey == None: nextKey = 1001 inFilePath = os.environ['INFILE_NAME'] try: inFile = open(inFilePath, 'r') except: exit('Could not open file for reading %s\n' % inFilePath) outFilePath = '%s/%s.bcp' % (os.environ['OUTPUTDIR'], table) try: outFile = open(outFilePath, 'w') except: exit('Could not open file for writing %s\n' % outFilePath) loadLookups()
def exit( status, # numeric exit status (integer) message = None # exit message (string) ): # Purpose: # Returns: nothing # Assumes: nothing # Effects: nothing # Throws: nothing if message is not None: sys.stderr.write('\n' + str(message) + '\n') try: diagFile.write('\n\nEnd Date/Time: %s\n' % (mgi_utils.date())) diagFile.close() except: pass try: reportlib.finish_nonps(reportFile) except: pass db.useOneConnection(0) sys.exit(status)
def exit(status, message=None): # # requires: status, the numeric exit status (integer) # message (string) # # effects: # Print message to stderr and exits # # returns: # if message is not None: sys.stderr.write('\n' + str(message) + '\n') try: diagFile.write('\n\nEnd Date/Time: %s\n' % (mgi_utils.date())) diagFile.close() inputerrorFile.close() ens1errorFile.close() ens2errorFile.close() mgierrorFile.close() except: pass try: db.useOneConnection() except: pass sys.exit(status)
def closeFiles(): if fpLogDiag: fpLogDiag.close() if fpLogCur: fpLogCur.close() if fpHTMPInput: fpHTMPInput.close() if fpHTMPDup: fpHTMPDup.close() if fpHTMPError: fpHTMPError.close() if fpHTMP: fpHTMP.close() if fpGenotype: fpGenotype.close() db.useOneConnection(0) return 0
def init(): print('DB Server:' + db.get_sqlServer()) print('DB Name: ' + db.get_sqlDatabase()) sys.stdout.flush() db.useOneConnection(1) openFiles() loadTempTable() results = db.sql( '''select distinct a1.accid as secondary, a2.accid as primary from ACC_Accession a1, ACC_Accession a2 where a1._MGIType_key = 19 and a1._LogicalDB_key in (9, 27) and a1.preferred = 0 and a1._object_key = a2._object_key and a2._MGIType_key = 19 and a2._LogicalDB_key in (9, 27) and a2.preferred = 1''', 'auto') for r in results: s = r['secondary'] p = r['primary'] if s not in secondaryToPrimaryDict: secondaryToPrimaryDict[s] = [] secondaryToPrimaryDict[s].append(p)
def doDeletes(refsKeys): # Purpose: deletes all MGI_Relationships created by this load # Returns: 1 if error, else 0 # Assumes: database connection exists # Effects: queries a database, writes number deleted to curation log # Throws: Nothing db.sql( '''select _StrainMarker_key into temporary table toDelete from MRK_StrainMarker where _Refs_key in (%s)''' % refsKeys, None) db.sql('''create index idx1 on toDelete(_StrainMarker_key)''', 'auto') results = db.sql('''select count(*) as deleteCt from toDelete''', 'auto') deleteCt = 0 for r in results: deleteCt = r['deleteCt'] fpLogCur.write('\nDeleting %s Strain Markers\n\n' % deleteCt) db.sql( '''delete from MRK_StrainMarker sm using toDelete d where d._StrainMarker_key = sm._StrainMarker_key''', None) db.commit() db.useOneConnection(0) return 0
def exit( # Purpose: prints error 'message' if it is not None # writes to log files and exits with 'status' # Returns: nothing # Assumes: Nothing # Effects: Exits with 'status' status, # numeric exit status (integer) message=None # exit message (str. ): if message is not None: sys.stderr.write('\n' + str(message) + '\n') try: fpDiagFile.write('\n\nEnd Date/Time: %s\n' % (mgi_utils.date())) fpErrorFile.write('\n\nEnd Date/Time: %s\n' % (mgi_utils.date())) fpDiagFile.close() fpErrorFile.close() fpInputFile.close() except: pass db.useOneConnection(0) sys.exit(status)
def exit(status, message = None): ''' # requires: status, the numeric exit status (integer) # message (string) # # effects: # Print message to stderr and exits # # returns: # ''' if message is not None: sys.stderr.write('\n' + str(message) + '\n') try: inputFile.close() outputFile.close() diagFile.write('\n\nEnd Date/Time: %s\n' % (mgi_utils.date())) errorFile.write('\n\nEnd Date/Time: %s\n' % (mgi_utils.date())) diagFile.close() errorFile.close() except: pass db.useOneConnection() sys.exit(status)
def initialize(): global mitMapFile, fpMITMap global mitDiffFile, fpMITDiff mitMapFile = os.getenv('MIT_MAP_FILE') mitDiffFile = os.getenv('MIT_DIFF_FILE') rc = 0 # # Make sure the environment variables are set. # if not mitMapFile: print 'Environment variable not set: MIT_MAP_FILE' rc = 1 if not mitDiffFile: print 'Environment variable not set: MIT_DIFF_FILE' rc = 1 # # Initialize file pointers. # fpMITMap = None fpMITDiff = None db.useOneConnection(1) return rc
def closefiles(): fpCur.close() fpDiag.close() #fpStudentRpt.close() db.useOneConnection(0) return 0
def init(): # Purpose: Initialization of database connection and file descriptors, # create database lookup dictionaries; create dictionary from # input file # Returns: 1 if file descriptors cannot be initialized # Assumes: Nothing # Effects: opens a database connection # Throws: Nothing global egToMarkerDict, mgiToMarkerDict global fpInFile, fpClustererFile, fpLoadFile, fpQcRpt user = os.environ['MGD_DBUSER'] passwordFileName = os.environ['MGD_DBPASSWORDFILE'] db.useOneConnection(1) db.set_sqlUser(user) db.set_sqlPasswordFromFile(passwordFileName) try: fpInFile = open(inFilePath, 'r') except: exit('Could not open file for reading %s\n' % inFilePath) try: fpLoadFile = open(loadFilePath, 'w') except: exit('Could not open file for writing %s\n' % loadFilePath) try: fpQcRpt = open(qcRptPath, 'w') except: exit('Could not open file for writing %s\n' % qcRptPath) # Create lookup of homology IDs to their marker keys results = db.sql( '''select a.accid, a._object_key as markerKey, m._organism_key from acc_accession a, mrk_marker m where a._mgitype_key = 2 and a._logicalDB_key in (47, 64, 172) and a._object_key = m._marker_key and m._marker_status_key = 1''', 'auto') for r in results: #print('hMrkID: %s orgKey: %s hMrkKey: %s' % (r['accid'], int(r['_organism_key']), int(r['markerKey']) )) homologyLookup[r['accid']] = [ int(r['_organism_key']), int(r['markerKey']) ] # Create lookup of mouse MGI IDs to their marker keys results = db.sql( '''select a.accid, a._object_key as markerKey from acc_accession a, mrk_marker m where a._mgitype_key = 2 and a._logicalDB_key = 1 and a.prefixPart = 'MGI:' and a._object_key = m._marker_key and m._marker_status_key = 1''', 'auto') for r in results: mouseLookup[r['accid']] = r['markerKey'] return
def bcpFiles(): ''' # requires: # # effects: # BCPs the data into the database # # returns: # nothing # ''' db.commit() db.useOneConnection() noteFile.close() noteChunkFile.close() sqlFile.close() if DEBUG: return bcpCommand = os.environ['PG_DBUTILS'] + '/bin/bcpin.csh' currentDir = os.getcwd() bcpNote = '%s %s %s %s %s %s "\\t" "\\n" mgd' \ % (bcpCommand, db.get_sqlServer(), db.get_sqlDatabase(), noteTable, currentDir, noteFileName) diagFile.write('%s\n' % bcpNote) os.system(bcpNote) bcpNote = '%s %s %s %s %s %s "\\t" "\\n" mgd' \ % (bcpCommand, db.get_sqlServer(), db.get_sqlDatabase(), noteChunkTable, currentDir, noteChunkFileName) diagFile.write('%s\n' % bcpNote) os.system(bcpNote)
def init(): # # requires: # # effects: # 1. Processes command line options # 2. Initializes global file descriptors/file names # # returns: # global diagFile, errorFile db.useOneConnection(1) try: diagFile = open(diagFileName, 'w') except: exit(1, 'Could not open file %s\n' % diagFileName) try: errorFile = open(errorFileName, 'w') except: exit(1, 'Could not open file %s\n' % errorFileName) # Log all SQL db.set_sqlLogFunction(db.sqlLogAll) # Set Log File Descriptor db.set_sqlLogFD(diagFile) diagFile.write('Start Date/Time: %s\n' % (mgi_utils.date())) errorFile.write('Start Date/Time: %s\n\n' % (mgi_utils.date()))
def initialize(): global mgiMapFile global fpMap global user global passwordFile mgiMapFile = os.getenv('MGI_MAP_FILE') user = os.getenv('MGD_DBUSER') passwordFile = os.getenv('MGD_DBPASSWORDFILE') rc = 0 # # Make sure the environment variables are set. # if not mgiMapFile: print 'Environment variable not set: MGI_MAP_FILE' rc = 1 # # Initialize file pointers. # fpMap = None # # Use one connection to the database # db.set_sqlUser(user) db.set_sqlPasswordFromFile(passwordFile) db.useOneConnection(1) return rc
def init(): # Purpose: initialize file descriptors, create connection to # the database and load lookups # Returns: nothing # Assumes: nothing # Effects: gives SQL commands to the database # Throws: nothing global inFile, outFile, nextKey print('%s' % mgi_utils.date()) print('Initializing') db.useOneConnection(1) db.set_sqlLogin(user, password, mgdServer, mgdDB) results = db.sql( '''select max(_Assoc_key) + 1 as nextKey from %s''' % table, 'auto') nextKey = results[0]['nextKey'] if nextKey == None: nextKey = 1001 inFilePath = os.environ['INFILE_NAME'] try: inFile = open(inFilePath, 'r') except: exit('Could not open file for reading %s\n' % inFilePath) outFilePath = '%s/%s.bcp' % (os.environ['OUTPUTDIR'], table) try: outFile = open(outFilePath, 'w') except: exit('Could not open file for writing %s\n' % outFilePath) loadLookups()
def exit( status, # numeric exit status (integer) message=None # exit message (string) ): # Purpose: writes message to error log and exits # Returns: nothing # Assumes: nothing # Effects: nothing # Throws: nothing if message is not None: sys.stderr.write('\n' + str(message) + '\n') try: inputFile.close() diagFile.write('\n\nEnd Date/Time: %s\n' % (mgi_utils.date())) errorFile.write('\n\nEnd Date/Time: %s\n' % (mgi_utils.date())) diagFile.close() errorFile.close() except: pass db.useOneConnection(0) sys.exit(status)
def init(): global fpB6BcpFile, fpMgpBcpFile, sourceLookup, nextAssocKey try: fpB6BcpFile = open(b6BcpFile, 'w') except: 'Could not open file for writing %s\n' % b6BcpFile sys.exit(1) if B6_ONLY == 'false': try: fpMgpBcpFile = open(mgpBcpFile, 'w') except: 'Could not open file for writing %s\n' % mgpBcpFile sys.exit(1) loadSourceLookup() # Log all SQL db.set_sqlLogFunction(db.sqlLogAll) db.useOneConnection(1) # # get next _Assoc_key # results = db.sql(''' select nextval('seq_source_assoc_seq') as nextAssocKey ''', 'auto') nextAssocKey = results[0]['nextAssocKey'] return 0
def init(): # Purpose: Initialization of database connection and file descriptors # Returns: 1 if file descriptors cannot be initialized # Assumes: Nothing # Effects: opens a database connection # Throws: Nothing global fpCC, fpH, fpRptFile, fpLoadFile user = os.environ['MGD_DBUSER'] passwordFileName = os.environ['MGD_DBPASSWORDFILE'] db.useOneConnection(1) db.set_sqlUser(user) db.set_sqlPasswordFromFile(passwordFileName) # temp report fpCC = open('connComp.rpt', 'w') # temp report fpH = open('hybridCluster.rpt', 'w') # Sue's temp report fpRptFile = open(os.environ['HYBRID_RPT'], 'w') # Load file in homologyload format try: fpLoadFile = open(loadFilePath, 'w') except: exit('Could not open file for writing %s\n' % loadFilePath) return
def initialize(): global mgi_to_uniprotFile global uniprotFile global markerIPFile global markerIPRef global annotEvidence, annotEditor, annotDate # # initialize caches # db.useOneConnection(1) db.set_sqlLogFunction(db.sqlLogAll) mgi_to_uniprotFile = os.getenv('MGI_UNIPROT_LOAD_FILE') uniprotFile = os.getenv('UNIPROT_ACC_ASSOC_FILE') markerIPFile = os.getenv('MARKER_IP_ASSOC_FILE') markerIPRef = os.environ['MARKER_IP_ANNOT_REF'] annotEvidence = os.environ['ANNOT_EVIDENCECODE'] annotEditor = os.environ['ANNOT_EDITOR'] annotDate = os.environ['ANNOT_DATE'] rc = 0 # # Make sure the required environment variables are set. # if not mgi_to_uniprotFile: print 'Environment variable not set: MGI_UNIPROT_LOAD_FILE' rc = 1 if not uniprotFile: print 'Environment variable not set: UNIPROT_ACC_ASSOC' rc = 1 if not markerIPFile: print 'Environment variable not set: MARKER_IP_ASSOC_FILE' rc = 1 if not markerIPRef: print 'Environment variable not set: MARKER_IP_ANNOT_REF' rc = 1 if not annotEvidence: print 'Environment variable not set: ANNOT_EVIDENCECODE' rc = 1 if not annotEditor: print 'Environment variable not set: ANNOT_EDITOR' rc = 1 if not annotDate: print 'Environment variable not set: ANNOT_DATE' rc = 1 return rc
def closeFiles(): if fpAssoc: fpAssoc.close() db.useOneConnection(0) return 0
def closeFiles(): inputFile.close(); outputFile.close(); db.useOneConnection(0) return 0
def init(): ''' # requires: # # effects: # 1. Processes command line options # 2. Initializes local DBMS parameters # 3. Initializes global file descriptors # # returns: # ''' global annotFile1, diagFile global omimToDOLookup try: diagFile = open(diagFileName, 'w') except: exit(1, 'Could not open file %s\n' % diagFileName) try: annotFile1 = open(annotFileName1, 'w') except: exit(1, 'Could not open file %s\n' % annotFileName1) db.useOneConnection(1) # # omimToDOLookup # omim id -> do id # only translate OMIM->DO where OMIM translates to at most one DO id # results = db.sql(''' WITH includeOMIM AS ( select a2.accID from ACC_Accession a1, ACC_Accession a2 where a1._MGIType_key = 13 and a1._LogicalDB_key = 191 and a1._Object_key = a2._Object_key and a1.preferred = 1 and a2._LogicalDB_key = 15 group by a2.accID having count(*) = 1 ) select distinct a1.accID as doID, a2.accID as omimID from includeOMIM d, ACC_Accession a1, VOC_Term t, ACC_Accession a2 where d.accID = a2.accID and t._Vocab_key = 125 and t._Term_key = a1._Object_key and a1._LogicalDB_key = 191 and a1._Object_key = a2._Object_key and a2._LogicalDB_key = 15 ''', 'auto') for r in results: key = r['omimID'] value = r['doID'] omimToDOLookup[key] = [] omimToDOLookup[key].append(value)
def init(): ''' # requires: # # effects: # 1. Processes command line options # 2. Initializes local DBMS parameters # 3. Initializes global file descriptors # # returns: # ''' global annotFile1, diagFile global omimToDOLookup try: diagFile = open(diagFileName, 'w') except: exit(1, 'Could not open file %s\n' % diagFileName) try: annotFile1 = open(annotFileName1, 'w') except: exit(1, 'Could not open file %s\n' % annotFileName1) db.useOneConnection(1) # # omimToDOLookup # omim id -> do id # only translate OMIM->DO where OMIM translates to at most one DO id # results = db.sql(''' WITH includeOMIM AS ( select a2.accID from ACC_Accession a1, ACC_Accession a2 where a1._MGIType_key = 13 and a1._LogicalDB_key = 191 and a1._Object_key = a2._Object_key and a1.preferred = 1 and a2._LogicalDB_key = 15 group by a2.accID having count(*) = 1 ) select distinct a1.accID as doID, a2.accID as omimID from includeOMIM d, ACC_Accession a1, VOC_Term t, ACC_Accession a2 where d.accID = a2.accID and t._Vocab_key = 125 and t._Term_key = a1._Object_key and a1._LogicalDB_key = 191 and a1._Object_key = a2._Object_key and a2._LogicalDB_key = 15 ''', 'auto') for r in results: key = r['omimID'] value = r['doID'] omimToDOLookup[key] = [] omimToDOLookup[key].append(value)
def init(): # Purpose: Initialization of database connection and file descriptors, # and next available database keys # Returns: 1 if file descriptors cannot be initialized # Assumes: Nothing # Effects: opens a database connection # Throws: Nothing global fpInFile, fpClusterBCP, fpMemberBCP, fpAccessionBCP global nextClusterKey, nextMemberKey, nextAccessionKey # create file descriptors for input/output files try: fpInFile = open(inFile, 'r') except: exit(1, 'Could not open file %s\n' % inFile) try: fpClusterBCP = open(clusterBCP, 'w') except: exit(1, 'Could not open file %s\n' % clusterBCP) try: fpMemberBCP = open(memberBCP, 'w') except: exit(1, 'Could not open file %s\n' % memberBCP) try: fpAccessionBCP = open(accessionBCP, 'w') except: exit(1, 'Could not open file %s\n' % accessionBCP) # get next ACC_Accession, MRK_Cluster and MRK_ClusterMember key user = os.environ['MGD_DBUSER'] passwordFileName = os.environ['MGD_DBPASSWORDFILE'] db.useOneConnection(1) db.set_sqlUser(user) db.set_sqlPasswordFromFile(passwordFileName) results = db.sql('''select max(_Cluster_key) + 1 as nextKey from MRK_Cluster''', 'auto') if results[0]['nextKey'] is None: nextClusterKey = 1000 else: nextClusterKey = results[0]['nextKey'] results = db.sql('''select max(_ClusterMember_key) + 1 as nextKey from MRK_ClusterMember''', 'auto') if results[0]['nextKey'] is None: nextMemberKey = 1000 else: nextMemberKey = results[0]['nextKey'] results = db.sql('''select max(_Accession_key) + 1 as nextKey from ACC_Accession''', 'auto') nextAccessionKey = results[0]['nextKey'] return
def init (): global accKey, gensatLogicalDBKey, egLogicalDBKey global markerMGITypeKey, createdByKey db.set_sqlUser(user) db.set_sqlPasswordFromFile(passwordFile) db.useOneConnection(1) # # Get the keys from the database. # cmds = [] cmds.append('select max(_Accession_key) + 1 as _Accession_key from ACC_Accession') cmds.append('select _LogicalDB_key from ACC_LogicalDB where name = \'%s\'' % (gensatLogicalDB)) cmds.append('select _LogicalDB_key from ACC_LogicalDB where name = \'%s\'' % (egLogicalDB)) cmds.append('select _MGIType_key from ACC_MGIType where name = \'%s\'' % (markerMGIType)) cmds.append('select _User_key from MGI_User where name = \'%s\'' % (createdBy)) results = db.sql(cmds,'auto') # # If any of the keys cannot be found, stop the load. # if len(results[0]) == 1: accKey = results[0][0]['_Accession_key'] else: print 'Cannot determine the next Accession key' sys.exit(1) if len(results[1]) == 1: gensatLogicalDBKey = results[1][0]['_LogicalDB_key'] else: print 'Cannot determine the Logical DB key for "' + gensatLogicalDB + '"' sys.exit(1) if len(results[2]) == 1: egLogicalDBKey = results[2][0]['_LogicalDB_key'] else: print 'Cannot determine the Logical DB key for "' + egLogicalDB + '"' sys.exit(1) if len(results[3]) == 1: markerMGITypeKey = results[3][0]['_MGIType_key'] else: print 'Cannot determine the MGI Type key for "' + markerMGIType + '"' sys.exit(1) if len(results[4]) == 1: createdByKey = results[4][0]['_User_key'] else: print 'Cannot determine the User key for "' + createdBy + '"' sys.exit(1) return
def closeFiles(): fpInFile.close() fpClusterBCP.close() fpMemberBCP.close() fpAccessionBCP.close() db.useOneConnection(0) return
def init(): ''' # requires: # # effects: # 1. Processes command line options # 2. Initializes local DBMS parameters # 3. Initializes global file descriptors/file names # 4. Initializes global keys # # returns: # ''' global inputFile, outputFile, diagFile, errorFile, errorFileName, diagFileName db.useOneConnection(1) db.set_sqlUser(user) db.set_sqlPasswordFromFile(passwordFileName) diagFileName = inputFileName + '.diagnostics' errorFileName = inputFileName + '.error' outputFileName = inputFileName + '.trans' try: inputFile = open(inputFileName, 'r') except: exit(1, 'Could not open file %s\n' % inputFileName) try: diagFile = open(diagFileName, 'w') except: exit(1, 'Could not open file %s\n' % diagFileName) try: errorFile = open(errorFileName, 'w') except: exit(1, 'Could not open file %s\n' % errorFileName) try: outputFile = open(outputFileName, 'w') except: exit(1, 'Could not open file %s\n' % outputFileName) # Log all SQL db.set_sqlLogFunction(db.sqlLogAll) # Set Log File Descriptor db.set_sqlLogFD(diagFile) diagFile.write('Start Date/Time: %s\n' % (mgi_utils.date())) diagFile.write('Server: %s\n' % (db.get_sqlServer())) diagFile.write('Database: %s\n' % (db.get_sqlDatabase())) diagFile.write('Input File: %s\n' % (inputFileName)) diagFile.write('Output File: %s\n' % (outputFileName)) errorFile.write('Start Date/Time: %s\n\n' % (mgi_utils.date()))
def unsetupSql (): # Purpose: un-initialize the 'db' module # Returns: nothing # Assumes: nothing # Effects: resets the db.useOneConnection() value # Throws: nothing db.useOneConnection(0) return
def closeFiles(): global fpMap if fpMap: fpMap.close() db.useOneConnection(0) return 0
def init(): # requires: # # effects: # 1. Processes command line options # 2. Initializes local DBMS parameters # 3. Initializes global file descriptors/file names # 4. Initializes global keys # # returns: # global diagFile, errorFile, inputFile, errorFileName, diagFileName global strainFile db.useOneConnection(1) db.set_sqlUser(user) db.set_sqlPasswordFromFile(passwordFileName) fdate = mgi_utils.date('%m%d%Y') # current date head, tail = os.path.split(inputFileName) diagFileName = tail + '.' + fdate + '.diagnostics' errorFileName = tail + '.' + fdate + '.error' try: diagFile = open(diagFileName, 'w') except: exit(1, 'Could not open file %s\n' % diagFileName) try: errorFile = open(errorFileName, 'w') except: exit(1, 'Could not open file %s\n' % errorFileName) try: inputFile = open(inputFileName, 'r') except: exit(1, 'Could not open file %s\n' % inputFileName) try: strainFile = open(strainFileName, 'w') except: exit(1, 'Could not open file %s\n' % strainFileName) # Log all SQL db.set_sqlLogFunction(db.sqlLogAll) # Set Log File Descriptor db.set_sqlLogFD(diagFile) diagFile.write('Start Date/Time: %s\n' % (mgi_utils.date())) diagFile.write('Server: %s\n' % (db.get_sqlServer())) diagFile.write('Database: %s\n' % (db.get_sqlDatabase())) errorFile.write('Start Date/Time: %s\n\n' % (mgi_utils.date())) return
def init(): ''' # requires: # # effects: # 1. Processes command line options # 2. Initializes local DBMS parameters # 3. Initializes global file descriptors/file names # 4. Initializes global keys # # returns: # ''' global inputFile, outputFile, diagFile, errorFile, errorFileName, diagFileName db.useOneConnection(1) db.set_sqlUser(user) db.set_sqlPasswordFromFile(passwordFileName) diagFileName = inputFileName + '.diagnostics' errorFileName = inputFileName + '.error' outputFileName = inputFileName + '.trans' try: inputFile = open(inputFileName, 'r') except: exit(1, 'Could not open file %s\n' % inputFileName) try: diagFile = open(diagFileName, 'w') except: exit(1, 'Could not open file %s\n' % diagFileName) try: errorFile = open(errorFileName, 'w') except: exit(1, 'Could not open file %s\n' % errorFileName) try: outputFile = open(outputFileName, 'w') except: exit(1, 'Could not open file %s\n' % outputFileName) # Log all SQL db.set_sqlLogFunction(db.sqlLogAll) # Set Log File Descriptor db.set_sqlLogFD(diagFile) diagFile.write('Start Date/Time: %s\n' % (mgi_utils.date())) diagFile.write('Server: %s\n' % (db.get_sqlServer())) diagFile.write('Database: %s\n' % (db.get_sqlDatabase())) diagFile.write('Input File: %s\n' % (inputFileName)) diagFile.write('Output File: %s\n' % (outputFileName)) errorFile.write('Start Date/Time: %s\n\n' % (mgi_utils.date()))
def init(): # requires: # # effects: # 1. Processes command line options # 2. Initializes local DBMS parameters # 3. Initializes global file descriptors/file names # 4. Initializes global keys # # returns: # global diagFile, errorFile, inputFile, errorFileName, diagFileName global strainFile db.useOneConnection(1) db.set_sqlUser(user) db.set_sqlPasswordFromFile(passwordFileName) fdate = mgi_utils.date('%m%d%Y') # current date head, tail = os.path.split(inputFileName) diagFileName = tail + '.' + fdate + '.diagnostics' errorFileName = tail + '.' + fdate + '.error' try: diagFile = open(diagFileName, 'w') except: exit(1, 'Could not open file %s\n' % diagFileName) try: errorFile = open(errorFileName, 'w') except: exit(1, 'Could not open file %s\n' % errorFileName) try: inputFile = open(inputFileName, 'r') except: exit(1, 'Could not open file %s\n' % inputFileName) try: strainFile = open(strainFileName, 'w') except: exit(1, 'Could not open file %s\n' % strainFileName) # Log all SQL db.set_sqlLogFunction(db.sqlLogAll) # Set Log File Descriptor db.set_sqlLogFD(diagFile) diagFile.write('Start Date/Time: %s\n' % (mgi_utils.date())) diagFile.write('Server: %s\n' % (db.get_sqlServer())) diagFile.write('Database: %s\n' % (db.get_sqlDatabase())) errorFile.write('Start Date/Time: %s\n\n' % (mgi_utils.date())) return
def init(): global bcpCommand global diagFile, errorFile, inputFile, errorFileName, diagFileName global markerFile, refFile, aliasFile db.useOneConnection(1) db.set_sqlUser(user) db.set_sqlPasswordFromFile(passwordFileName) bcpCommand = bcpCommand + db.get_sqlServer() + ' ' + db.get_sqlDatabase( ) + ' %s ' + currentDir + ' %s "\\t" "\\n" mgd' head, tail = os.path.split(inputFileName) diagFileName = outputDir + '/' + tail + '.diagnostics' errorFileName = outputDir + '/' + tail + '.error' try: diagFile = open(diagFileName, 'w') except: exit(1, 'Could not open file %s\n' % diagFileName) try: errorFile = open(errorFileName, 'w') except: exit(1, 'Could not open file %s\n' % errorFileName) try: inputFile = open(inputFileName, 'r') except: exit(1, 'Could not open file %s\n' % inputFileName) try: markerFile = open(markerFileName, 'w') except: exit(1, 'Could not open file %s\n' % markerFileName) try: refFile = open(refFileName, 'w') except: exit(1, 'Could not open file %s\n' % refFileName) try: aliasFile = open(aliasFileName, 'w') except: exit(1, 'Could not open file %s\n' % aliasFileName) # Log all SQL db.set_sqlLogFunction(db.sqlLogAll) diagFile.write('Start Date/Time: %s\n' % (mgi_utils.date())) diagFile.write('Server: %s\n' % (db.get_sqlServer())) diagFile.write('Database: %s\n' % (db.get_sqlDatabase())) errorFile.write('Start Date/Time: %s\n\n' % (mgi_utils.date())) return
def init(): global bcpCommand global diagFile, errorFile, inputFile, errorFileName, diagFileName global markerFile, refFile, aliasFile db.useOneConnection(1) db.set_sqlUser(user) db.set_sqlPasswordFromFile(passwordFileName) bcpCommand = bcpCommand + db.get_sqlServer() + ' ' + db.get_sqlDatabase() + ' %s ' + currentDir + ' %s "\\t" "\\n" mgd' head, tail = os.path.split(inputFileName) diagFileName = outputDir + '/' + tail + '.diagnostics' errorFileName = outputDir + '/' + tail + '.error' try: diagFile = open(diagFileName, 'w') except: exit(1, 'Could not open file %s\n' % diagFileName) try: errorFile = open(errorFileName, 'w') except: exit(1, 'Could not open file %s\n' % errorFileName) try: inputFile = open(inputFileName, 'r') except: exit(1, 'Could not open file %s\n' % inputFileName) try: markerFile = open(markerFileName, 'w') except: exit(1, 'Could not open file %s\n' % markerFileName) try: refFile = open(refFileName, 'w') except: exit(1, 'Could not open file %s\n' % refFileName) try: aliasFile = open(aliasFileName, 'w') except: exit(1, 'Could not open file %s\n' % aliasFileName) # Log all SQL db.set_sqlLogFunction(db.sqlLogAll) diagFile.write('Start Date/Time: %s\n' % (mgi_utils.date())) diagFile.write('Server: %s\n' % (db.get_sqlServer())) diagFile.write('Database: %s\n' % (db.get_sqlDatabase())) errorFile.write('Start Date/Time: %s\n\n' % (mgi_utils.date())) return
def finalize(): # Purpose: closes file descriptors and connection to the db # Returns: nothing # Assumes: nothing # Effects: nothing # Throws: nothing global inFile, outFile db.useOneConnection(0) inFile.close() outFile.close()
def closeFiles(): if fpLogTest: fpLogTest.close() if fpHTMP: fpHTMP.close() db.useOneConnection(0) return 0
def finalize(): # Purpose: closes file descriptors and connection to the db # Returns: nothing # Assumes: nothing # Effects: nothing # Throws: nothing global inFile, outFile db.useOneConnection(0) inFile.close() outFile.close()
def finalize(): global fpSnpMrk db.useOneConnection(0) # # Close the bcp file. # fpSnpMrk.close() return
def closeFiles(): # Purpose: closes file descriptors and database connection # Returns: 0 # Assumes: file descriptors have been initialized # Effects: None # Throws: Nothing db.useOneConnection(0) fpClusterBCP.close() fpMemberBCP.close() return
def closeFiles(): if fpLogDiag: fpLogDiag.close() if fpLogCur: fpLogCur.close() db.useOneConnection(0) return 0
def closeFiles(): global fpMITMap, fpMITDiff if fpMITMap: fpMITMap.close() if fpMITDiff: fpMITDiff.close() db.useOneConnection(0) return 0
def processCommandLine(): if len(sys.argv) != 3: bailout('Incorrect command-line; need two parameters.') db.set_sqlServer(sys.argv[1]) db.set_sqlDatabase(sys.argv[2]) db.useOneConnection(1) try: db.sql('select count(1) from MGI_dbInfo', 'auto') except: bailout('Cannot query database %s..%s' % (sys.argv[1], sys.argv[2])) return
def closeFiles(): # Purpose: closes file descriptors and database connection # Returns: 0 # Assumes: file descriptors have been initialized # Effects: None # Throws: Nothing fpLoadFile.close() fpRptFile.close() # close the database connection db.useOneConnection(0) return
def initialize(): global fpQcFile, fpInFile, pubMedByExptDict, geoIdToKeyDict # create file descriptors try: fpInFile = open(inFileName, 'r') except: print('Cannot create %s' % inFileName) # create file descriptors try: fpQcFile = open(qcFileName, 'w') except: print('Cannot create %s' % qcFileName) db.useOneConnection(1) # create the pubmed ID property lookup by GEO experiment # get all experiments with 2ndary GEO IDs, and their pubMed IDs if they # have them db.sql('''select p._Object_key, p.value into temporary table pubMedIds from MGI_Property p where p._PropertyTerm_key = %s and p._PropertyType_key = %s''' % (pubmedPropKey, propTypeKey), None) db.sql('''create index idx1 on pubMedIds(_Object_key)''', None) db.sql('''select e._Experiment_key, a.accid into temporary table exp from GXD_HTExperiment e, ACC_Accession a where e._Experiment_key = a._Object_key and a._MGIType_key = %s and a._LogicalDB_key = %s''' % (mgiTypeKey, geoLdbKey), None) db.sql('''create index idx2 on exp(_Experiment_key)''', None) results = db.sql('''select e._Experiment_key, e.accid, p.value from exp e left outer join pubMedIds p on (e._Experiment_key = p._Object_key)''', 'auto') for r in results: key = r['_Experiment_key'] accid = r['accid'] value = r['value'] geoIdToKeyDict[accid] = key if accid not in pubMedByExptDict: pubMedByExptDict[accid] = [] if value != None: pubMedByExptDict[accid].append(value) db.useOneConnection(0) return
def closeFiles(): # Purpose: closes file descriptors and database connection # Returns: 0 # Assumes: file descriptors have been initialized # Effects: None # Throws: Nothing fpInFile.close() fpLoadFile.close() fpQcRpt.close() # close the database connection db.useOneConnection(0) return
def process(assayKey): """ processes cache based on assayKey argument if assayKey == 0 we create full BCP File else we live update the cache for one assay """ db.useOneConnection(1) # determine type of load if assayKey == 0: createFullBCPFile() else: updateSingleAssay(assayKey) db.useOneConnection(0)
def init(): # Purpose: open file descriptors # Returns: Nothing # Assumes: Nothing # Effects: creates files in the file system, opens db connection # # Open input and output files # openFiles() # # create database connection # db.useOneConnection(1)
def init(): # Purpose: process command line options # Returns: nothing # Assumes: nothing # Effects: initializes global variables # exits if files cannot be opened # Throws: nothing global inputFile, diagFile, errorFile, errorFileName, diagFileName db.useOneConnection(1) db.set_sqlUser(user) db.set_sqlPasswordFromFile(passwordFileName) fdate = mgi_utils.date('%m%d%Y') # current date head, tail = os.path.split(inputFileName) diagFileName = tail + '.' + fdate + '.diagnostics' errorFileName = tail + '.' + fdate + '.error' try: inputFile = open(inputFileName, 'r') except: exit(1, 'Could not open file %s\n' % inputFileName) try: diagFile = open(diagFileName, 'w') except: exit(1, 'Could not open file %s\n' % diagFileName) try: errorFile = open(errorFileName, 'w') except: exit(1, 'Could not open file %s\n' % errorFileName) # Log all SQL db.set_sqlLogFunction(db.sqlLogAll) # Set Log File Descriptor db.set_sqlLogFD(diagFile) diagFile.write('Start Date/Time: %s\n' % (mgi_utils.date())) diagFile.write('Server: %s\n' % (db.get_sqlServer())) diagFile.write('Database: %s\n' % (db.get_sqlDatabase())) diagFile.write('Input File: %s\n' % (inputFileName)) errorFile.write('Start Date/Time: %s\n\n' % (mgi_utils.date())) return
def closeFiles(): # Purpose: closes file descriptors and database connection # Returns: 0 # Assumes: file descriptors have been initialized # Effects: None # Throws: Nothing db.useOneConnection(0) fpClusterBCP.close() fpMemberBCP.close() if accessionBCP != '': fpAccessionBCP.close() if propertyBCP != '': fpPropertyBCP.close() return
def init(): """ Initialize database connection Open output files """ global seqFile, rawFile, sourceFile, accFile db.useOneConnection(1) seqFile = open(seqFileName, 'w') rawFile = open(rawFileName, 'w') sourceFile = open(sourceFileName, 'w') accFile = open(accFileName, 'w')
def runBCP (): sys.stdout.flush() db.commit() db.useOneConnection(0) bcpCommand = os.environ['PG_DBUTILS'] + '/bin/bcpin.csh' currentDir = os.getcwd() bcpCmd = '%s %s %s %s %s %s "\\t" "\\n" mgd' \ % (bcpCommand, db.get_sqlServer(), db.get_sqlDatabase(), assocTable, currentDir, bcpFile) diagFile.write('%s\n' % bcpCmd) os.system(bcpCmd) return
def bcpFiles( recordsProcessed # number of records processed (integer) ): outPrepFile.close() outAssayFile.close() outAssayNoteFile.close() outGelLaneFile.close() outGelLaneStFile.close() outGelRowFile.close() outGelBandFile.close() outAccFile.close() # update the max Accession ID value db.sql('select * from ACC_setMax (%d)' % (recordsProcessed), None) db.commit() db.useOneConnection(0) if DEBUG or not bcpon: return bcpCommand = os.environ['PG_DBUTILS'] + '/bin/bcpin.csh' currentDir = os.getcwd() bcp1 = '%s %s %s %s %s %s "\\t" "\\n" mgd' \ % (bcpCommand, db.get_sqlServer(), db.get_sqlDatabase(), probeprepTable, currentDir, outPrepFileName) bcp2 = '%s %s %s %s %s %s "\\t" "\\n" mgd' \ % (bcpCommand, db.get_sqlServer(), db.get_sqlDatabase(), assayTable, currentDir, outAssayFileName) bcp3 = '%s %s %s %s %s %s "\\t" "\\n" mgd' \ % (bcpCommand, db.get_sqlServer(), db.get_sqlDatabase(), assaynoteTable, currentDir, outAssayNoteFileName) bcp4 = '%s %s %s %s %s %s "\\t" "\\n" mgd' \ % (bcpCommand, db.get_sqlServer(), db.get_sqlDatabase(), gelLaneTable, currentDir, outGelLaneFileName) bcp5 = '%s %s %s %s %s %s "\\t" "\\n" mgd' \ % (bcpCommand, db.get_sqlServer(), db.get_sqlDatabase(), gelLaneStTable, currentDir, outGelLaneStFileName) bcp6 = '%s %s %s %s %s %s "\\t" "\\n" mgd' \ % (bcpCommand, db.get_sqlServer(), db.get_sqlDatabase(), gelRowTable, currentDir, outGelRowFileName) bcp7 = '%s %s %s %s %s %s "\\t" "\\n" mgd' \ % (bcpCommand, db.get_sqlServer(), db.get_sqlDatabase(), gelBandTable, currentDir, outGelBandFileName) bcp8 = '%s %s %s %s %s %s "\\t" "\\n" mgd' \ % (bcpCommand, db.get_sqlServer(), db.get_sqlDatabase(), accTable, currentDir, outAccFileName) for bcpCmd in [bcp1, bcp2, bcp3, bcp4, bcp5, bcp6, bcp7, bcp8]: diagFile.write('%s\n' % bcpCmd) os.system(bcpCmd) return
def setupSql (server, # str. name of database server database, # str. name of database username, # str. user with full permissions on database password # str. password for 'username' ): # Purpose: initialize the 'db' module to use the desired database # login information # Returns: nothing # Assumes: the parameters are all valid # Effects: initializes the 'db' module with the given login info, and # tells it to use one connection (rather than a separate # connection for each db.sql() call) # Throws: nothing db.set_sqlLogin (username, password, server, database) db.useOneConnection(1) return
def init(): # Purpose: Initialization of database connection and file descriptors, # create database lookup dictionaries; create dictionary from # input file # Returns: 1 if file descriptors cannot be initialized # Assumes: Nothing # Effects: opens a database connection # Throws: Nothing global fpInFile, fpClusterBCP, fpMemberBCP global createdByKey, nextClusterKey, nextMemberKey # create file descriptors for input/output files try: fpInFile = open(inFile, 'r') except: exit(1, 'Could not open file %s\n' % inFile) try: fpClusterBCP = open(clusterBCP, 'w') except: exit(1, 'Could not open file %s\n' % clusterBCP) try: fpMemberBCP = open(memberBCP, 'w') except: exit(1, 'Could not open file %s\n' % memberBCP) # get next MRK_Cluster and MRK_ClusterMember key user = os.environ['MGD_DBUSER'] passwordFileName = os.environ['MGD_DBPASSWORDFILE'] db.useOneConnection(1) db.set_sqlUser(user) db.set_sqlPasswordFromFile(passwordFileName) results = db.sql('''select _User_key from MGI_User where login = '******' ''' % createdBy, 'auto') createdByKey = results[0]['_User_key'] #print 'createdByKey: %s' % createdByKey results = db.sql(''' select nextval('mrk_cluster_seq') as nextKey ''', 'auto') nextClusterKey = results[0]['nextKey'] results = db.sql(''' select nextval('mrk_clustermember_seq') as nextKey ''', 'auto') nextMemberKey = results[0]['nextKey'] return
def exit( status, # numeric exit status (integer) message=None # exit message (str. ): if message is not None: sys.stderr.write('\n' + str(message) + '\n') try: diagFile.write('\n\nEnd Date/Time: %s\n' % (mgi_utils.date())) errorFile.write('\n\nEnd Date/Time: %s\n' % (mgi_utils.date())) diagFile.close() errorFile.close() except: pass db.useOneConnection(0) sys.exit(status)
def exit( status, # numeric exit status (integer) message = None # exit message (string) ): if message is not None: sys.stderr.write('\n' + str(message) + '\n') try: diagFile.write('\n\nEnd Date/Time: %s\n' % (mgi_utils.date())) errorFile.write('\n\nEnd Date/Time: %s\n' % (mgi_utils.date())) diagFile.close() errorFile.close() except: pass db.useOneConnection(0) sys.exit(status)
def finish_nonps(fp, isHTML = 0): ''' # requires: fp, the output file descriptor # # effects: # 1. Closes the output file # # returns: # ''' if isHTML: fp.write("</PRE>") fp.write("</BODY>") fp.write("</HTML>") fp.close() db.useOneConnection(0)
def exit(status, message=None): # # requires: status, the numeric exit status (integer) # message (string) # # effects: # Print message to stderr and exits # # returns: # if message is not None: sys.stderr.write('\n' + str(message) + '\n') try: db.useOneConnection() except: pass sys.exit(status)