def bcpFiles(): ''' # requires: # # effects: # BCPs the data into the database # # returns: # nothing # ''' transTypeFile.close() transFile.close() cmd1 = 'cat %s | bcp %s..%s in %s -c -t\"%s" -S%s -U%s' \ % (passwordFileName, db.get_sqlDatabase(), \ 'MGI_TranslationType', transTypeFileName, bcpdelim, db.get_sqlServer(), db.get_sqlUser()) cmd2 = 'cat %s | bcp %s..%s in %s -c -t\"%s" -S%s -U%s' \ % (passwordFileName, db.get_sqlDatabase(), \ 'MGI_Translation', transFileName, bcpdelim, db.get_sqlServer(), db.get_sqlUser()) diagFile.write('%s\n' % cmd1) diagFile.write('%s\n' % cmd2) if DEBUG: return os.system(cmd1) os.system(cmd2)
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 bcpFiles(): ''' # requires: # # effects: # BCPs the data into the database # # returns: # nothing # ''' transTypeFile.close() transFile.close() cmd1 = 'cat %s | bcp %s..%s in %s -c -t\"%s" -S%s -U%s' \ % (passwordFileName, db.get_sqlDatabase(), \ 'MGI_TranslationType', transTypeFileName, bcpdelim, db.get_sqlServer(), db.get_sqlUser()) cmd2 = 'cat %s | bcp %s..%s in %s -c -t\"%s" -S%s -U%s' \ % (passwordFileName, db.get_sqlDatabase(), \ 'MGI_Translation', transFileName, bcpdelim, db.get_sqlServer(), db.get_sqlUser()) diagFile.write('%s\n' % cmd1) diagFile.write('%s\n' % cmd2) if DEBUG: return os.system(cmd1) os.system(cmd2)
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 bcpFiles(): ''' # requires: # # effects: # BCPs the data into the database # # returns: # nothing # ''' exptFile.close() exptMarkerFile.close() accFile.close() noteFile.close() db.commit() bcpCommand = os.environ['PG_DBUTILS'] + '/bin/bcpin.csh' currentDir = os.getcwd() cmd1 = '%s %s %s %s %s %s "%s" "\\n" mgd' % \ (bcpCommand, db.get_sqlServer(), db.get_sqlDatabase(), 'MLD_Expts', currentDir, exptFileName, bcpdelim) cmd2 = '%s %s %s %s %s %s "%s" "\\n" mgd' % \ (bcpCommand, db.get_sqlServer(), db.get_sqlDatabase(), 'MLD_Expt_Marker', currentDir, exptMarkerFileName, bcpdelim) cmd3 = '%s %s %s %s %s %s "%s" "\\n" mgd' % \ (bcpCommand, db.get_sqlServer(), db.get_sqlDatabase(), 'ACC_Accession', currentDir, accFileName, bcpdelim) cmd4 = '%s %s %s %s %s %s "%s" "\\n" mgd' % \ (bcpCommand, db.get_sqlServer(), db.get_sqlDatabase(), 'MLD_Notes', currentDir, noteFileName, bcpdelim) diagFile.write('%s\n' % cmd1) diagFile.write('%s\n' % cmd2) diagFile.write('%s\n' % cmd3) diagFile.write('%s\n' % cmd4) os.system(cmd1) os.system(cmd2) os.system(cmd3) os.system(cmd4) # update mld_expts_seq auto-sequence db.sql( ''' select setval('mld_expts_seq', (select max(_Expt_key) from MLD_Expts)) ''', None) db.commit() # update mld_expt_marker_seq auto-sequence db.sql( ''' select setval('mld_expt_marker_seq', (select max(_Assoc_key) from MLD_Expt_Marker)) ''', None) db.commit()
def header(fp, headerType = "JAX"): ''' # requires: fp, output file descriptor pointing to an open file # headerType, string that denotes which header to use # effects: writes the specified header to the file ''' jaxheaderfile = '# The Jackson Laboratory - Mouse Genome Informatics (MGI)\n# Copyright 1996, 1999, 2002, 2005, 2008 The Jackson Laboratory\n# All Rights Reserved\n' # # always write the JAX header # fp.write(jaxheaderfile) fp.write('# Date Generated: %s\n' % (mgi_utils.date())) # # special case # if headerType == 'JAX': fp.write('#\n') else: fp.write('# (server = %s, database = %s)\n#\n\n' % (db.get_sqlServer(), db.get_sqlDatabase()))
def executeBCP(): ''' # requires: # # effects: # BCPs the data into the database # # returns: # nothing # ''' synFile.close() db.commit() bcpCommand = os.environ['PG_DBUTILS'] + '/bin/bcpin.csh' bcp1 = '%s %s %s %s %s %s "|" "\\n" mgd' % \ (bcpCommand, db.get_sqlServer(), db.get_sqlDatabase(), 'MGI_Synonym', datadir, 'MGI_Synonym.bcp') diagFile.write('%s\n' % bcp1) os.system(bcp1) # update mgi_synonym_seq auto-sequence db.sql(''' select setval('mgi_synonym_seq', (select max(_synonym_key) from MGI_Synonym)) ''', None) db.commit()
def bcpFiles(): # requires: # # effects: # BCPs the data into the database # # returns: # nothing # bcpdelim = "|" if DEBUG or not bcpon: return refFile.close() bcp1 = 'cat %s | bcp %s..%s in %s -c -t\"%s" -S%s -U%s' \ % (passwordFileName, db.get_sqlDatabase(), \ 'MGI_Reference_Assoc', refFileName, bcpdelim, db.get_sqlServer(), db.get_sqlUser()) diagFile.write('%s\n' % bcp1) os.system(bcp1) # update mgi_reference_assoc_seq auto-sequence db.sql( ''' select setval('mgi_reference_assoc_seq', (select max(_Assoc_key) + 1 from MGI_Reference_Assoc)) ''', None)
def executeBCP(): ''' # requires: # # effects: # BCPs the data into the database # # returns: # nothing # ''' synFile.close() db.commit() bcpCommand = os.environ['PG_DBUTILS'] + '/bin/bcpin.csh' bcp1 = '%s %s %s %s %s %s "|" "\\n" mgd' % \ (bcpCommand, db.get_sqlServer(), db.get_sqlDatabase(), 'MGI_Synonym', datadir, 'MGI_Synonym.bcp') diagFile.write('%s\n' % bcp1) os.system(bcp1) # update mgi_synonym_seq auto-sequence db.sql(''' select setval('mgi_synonym_seq', (select max(_synonym_key) from MGI_Synonym)) ''', None) db.commit()
def bcpFiles(): # requires: # # effects: # BCPs the data into the database # # returns: # nothing # bcpdelim = "|" if DEBUG or not bcpon: return refFile.close() bcp1 = 'cat %s | bcp %s..%s in %s -c -t\"%s" -S%s -U%s' \ % (passwordFileName, db.get_sqlDatabase(), \ 'MGI_Reference_Assoc', refFileName, bcpdelim, db.get_sqlServer(), db.get_sqlUser()) diagFile.write('%s\n' % bcp1) os.system(bcp1) # update mgi_reference_assoc_seq auto-sequence db.sql(''' select setval('mgi_reference_assoc_seq', (select max(_Assoc_key) + 1 from MGI_Reference_Assoc)) ''', None)
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 bcpFiles(): bcpdelim = "|" if DEBUG or not bcpon: return closeFiles() bcpI = '%s %s %s' % (BCP_COMMAND, db.get_sqlServer(), db.get_sqlDatabase()) bcpII = '"|" "\\n" mgd' bcp1 = '%s %s "/" %s %s' % (bcpI, alleleTable, alleleFileName, bcpII) bcp2 = '%s %s "/" %s %s' % (bcpI, mutationTable, mutationFileName, bcpII) bcp3 = '%s %s "/" %s %s' % (bcpI, mutantTable, mutantFileName, bcpII) bcp4 = '%s %s "/" %s %s' % (bcpI, refTable, refFileName, bcpII) bcp5 = '%s %s "/" %s %s' % (bcpI, accTable, accFileName, bcpII) bcp6 = '%s %s "/" %s %s' % (bcpI, accRefTable, accRefFileName, bcpII) bcp7 = '%s %s "/" %s %s' % (bcpI, noteTable, noteFileName, bcpII) bcp8 = '%s %s "/" %s %s' % (bcpI, annotTable, annotFileName, bcpII) db.commit() for bcpCmd in [bcp1, bcp2, bcp3, bcp4, bcp5, bcp6, bcp7, bcp8]: diagFile.write('%s\n' % bcpCmd) os.system(bcpCmd) if len(ikmcSQLs) > 0: print(ikmcSQLs) db.sql(ikmcSQLs, None) db.commit() # update all_allele_seq auto-sequence db.sql( ''' select setval('all_allele_seq', (select max(_Allele_key) from ALL_Allele)) ''', None) # update mgi_reference_assoc_seq auto-sequence db.sql( ''' select setval('mgi_reference_assoc_seq', (select max(_Assoc_key) from MGI_Reference_Assoc)) ''', None) # update mgi_note_seq auto-sequence db.sql( ''' select setval('mgi_note_seq', (select max(_Note_key) from MGI_Note)) ''', None) # update all_allele_mutation_seq auto-sequence db.sql( ''' select setval('all_allele_mutation_seq', (select max(_Assoc_key) from ALL_Allele_Mutation)) ''', None) # update all_allele_cellline_seq auto-sequence db.sql( ''' select setval('all_allele_cellline_seq', (select max(_Assoc_key) from ALL_Allele_CellLine)) ''', None) # update voc_annot_seq auto-sequence db.sql( ''' select setval('voc_annot_seq', (select max(_Annot_key) from VOC_Annot)) ''', None) db.commit()
def init (): print 'DB Server:' + db.get_sqlServer() print 'DB Name: ' + db.get_sqlDatabase() sys.stdout.flush() db.set_sqlUser(user) db.set_sqlPasswordFromFile(passwordFileName) 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 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 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(): print('DB Server:' + db.get_sqlServer()) print('DB Name: ' + db.get_sqlDatabase()) sys.stdout.flush() db.set_sqlUser(user) db.set_sqlPasswordFromFile(passwordFileName) return
def bcpFiles(): # Purpose: BCPs the data into the database # Returns: 1 if error, else 0 # Assumes: connection to the database # Effects: copies data into the db # Throws: Nothing if DEBUG == 'true': return 0 closeFiles() bcpI = '%s %s %s' % (BCP_COMMAND, db.get_sqlServer(), db.get_sqlDatabase()) bcpII = '"|" "\\n" mgd' bcp1 = '%s %s "/" %s %s' % (bcpI, alleleTable, alleleFileName, bcpII) bcp2 = '%s %s "/" %s %s' % (bcpI, mutationTable, mutationFileName, bcpII) bcp3 = '%s %s "/" %s %s' % (bcpI, refTable, refFileName, bcpII) bcp4 = '%s %s "/" %s %s' % (bcpI, accTable, accFileName, bcpII) bcp5 = '%s %s "/" %s %s' % (bcpI, noteTable, noteFileName, bcpII) bcp6 = '%s %s "/" %s %s' % (bcpI, annotTable, annotFileName, bcpII) db.commit() for bcpCmd in [bcp1, bcp2, bcp3, bcp4, bcp5, bcp6]: fpDiagFile.write('%s\n' % bcpCmd) os.system(bcpCmd) # update all_allele_mutation_seq auto-sequence db.sql( ''' select setval('all_allele_mutation_seq', (select max(_Assoc_key) from ALL_Allele_Mutation)) ''', None) # update all_allele_seq auto-sequence db.sql( ''' select setval('all_allele_seq', (select max(_Allele_key) from ALL_Allele)) ''', None) # update mgi_reference_assoc auto-sequence db.sql( ''' select setval('mgi_reference_assoc_seq', (select max(_Assoc_key) + 1 from MGI_Reference_Assoc)) ''', None) # update mgi_note_seq auto-sequence db.sql( ''' select setval('mgi_note_seq', (select max(_Note_key) from MGI_Note)) ''', None) # update voc_annot_seq auto-sequence db.sql( ''' select setval('voc_annot_seq', (select max(_Annot_key) from VOC_Annot)) ''', None) db.commit() return 0
def bcpFiles(): ''' # requires: # # effects: # BCPs the data into the database # # returns: # nothing # ''' exptFile.close() exptMarkerFile.close() accFile.close() noteFile.close() db.commit() bcpCommand = os.environ['PG_DBUTILS'] + '/bin/bcpin.csh' currentDir = os.getcwd() cmd1 = '%s %s %s %s %s %s "%s" "\\n" mgd' % \ (bcpCommand, db.get_sqlServer(), db.get_sqlDatabase(), 'MLD_Expts', currentDir, exptFileName, bcpdelim) cmd2 = '%s %s %s %s %s %s "%s" "\\n" mgd' % \ (bcpCommand, db.get_sqlServer(), db.get_sqlDatabase(), 'MLD_Expt_Marker', currentDir, exptMarkerFileName, bcpdelim) cmd3 = '%s %s %s %s %s %s "%s" "\\n" mgd' % \ (bcpCommand, db.get_sqlServer(), db.get_sqlDatabase(), 'ACC_Accession', currentDir, accFileName, bcpdelim) cmd4 = '%s %s %s %s %s %s "%s" "\\n" mgd' % \ (bcpCommand, db.get_sqlServer(), db.get_sqlDatabase(), 'MLD_Notes', currentDir, noteFileName, bcpdelim) diagFile.write('%s\n' % cmd1) diagFile.write('%s\n' % cmd2) diagFile.write('%s\n' % cmd3) diagFile.write('%s\n' % cmd4) os.system(cmd1) os.system(cmd2) os.system(cmd3) os.system(cmd4)
def executeBCP(): ''' # requires: # # effects: # BCPs the data into the database # # returns: # nothing # ''' accFile.close() accrefFile.close() markerFile.close() db.commit() bcpCommand = os.environ['PG_DBUTILS'] + '/bin/bcpin.csh' bcp1 = '%s %s %s %s %s %s "|" "\\n" mgd' % \ (bcpCommand, db.get_sqlServer(), db.get_sqlDatabase(), 'MRK_Marker', datadir, 'MRK_Marker.bcp') bcp2 = '%s %s %s %s %s %s "|" "\\n" mgd' % \ (bcpCommand, db.get_sqlServer(), db.get_sqlDatabase(), 'ACC_Accession', datadir, 'ACC_Accession.bcp') bcp3 = '%s %s %s %s %s %s "|" "\\n" mgd' % \ (bcpCommand, db.get_sqlServer(), db.get_sqlDatabase(), 'ACC_AccessionReference', datadir, 'ACC_AccessionReference.bcp') diagFile.write('%s\n' % bcp1) diagFile.write('%s\n' % bcp2) diagFile.write('%s\n' % bcp3) os.system(bcp1) os.system(bcp2) os.system(bcp3) # update mrk_marker_seq auto-sequence db.sql( ''' select setval('mrk_marker_seq', (select max(_Marker_key) from MRK_Marker)) ''', None) db.commit()
def executeBCP(): ''' # requires: # # effects: # BCPs the data into the database # # returns: # nothing # ''' accFile.close() accrefFile.close() markerFile.close() db.commit() bcpCommand = os.environ['PG_DBUTILS'] + '/bin/bcpin.csh' bcp1 = '%s %s %s %s %s %s "|" "\\n" mgd' % \ (bcpCommand, db.get_sqlServer(), db.get_sqlDatabase(), 'MRK_Marker', datadir, 'MRK_Marker.bcp') bcp2 = '%s %s %s %s %s %s "|" "\\n" mgd' % \ (bcpCommand, db.get_sqlServer(), db.get_sqlDatabase(), 'ACC_Accession', datadir, 'ACC_Accession.bcp') bcp3 = '%s %s %s %s %s %s "|" "\\n" mgd' % \ (bcpCommand, db.get_sqlServer(), db.get_sqlDatabase(), 'ACC_AccessionReference', datadir, 'ACC_AccessionReference.bcp') diagFile.write('%s\n' % bcp1) diagFile.write('%s\n' % bcp2) diagFile.write('%s\n' % bcp3) os.system(bcp1) os.system(bcp2) os.system(bcp3) # update mrk_marker_seq auto-sequence db.sql(''' select setval('mrk_marker_seq', (select max(_Marker_key) from MRK_Marker)) ''', None) db.commit()
def execBCP(): bcpCmd = '%s %s %s %s %s %s "\\t" "\\n" mgd' % \ (bcpCommand, db.get_sqlServer(), db.get_sqlDatabase(), setTable, outputDir, setBcp) fpDiag.write('%s\n' % bcpCmd) os.system(bcpCmd) bcpCmd = '%s %s %s %s %s %s "\\t" "\\n" mgd' % \ (bcpCommand, db.get_sqlServer(), db.get_sqlDatabase(), memberTable, outputDir, memberBcp) fpDiag.write('%s\n' % bcpCmd) os.system(bcpCmd) # reset the rnaseq primary key sequence db.sql( ''' select setval('gxd_htsample_rnaseq_seq', (select max(_rnaseq_key) from gxd_htsample_rnaseq)) ''', None) db.commit() db.useOneConnection(0) return 0
def bcpFiles(): outSetFile.close() outMemberFile.close() db.commit() if DEBUG or not bcpon: return bcpCommand = os.environ['PG_DBUTILS'] + '/bin/bcpin.csh' bcp1 = '%s %s %s %s %s %s "\\t" "\\n" mgd' % \ (bcpCommand, db.get_sqlServer(), db.get_sqlDatabase(), setTable, outputDir, outSetFileName) bcp2 = '%s %s %s %s %s %s "\\t" "\\n" mgd' % \ (bcpCommand, db.get_sqlServer(), db.get_sqlDatabase(), memberTable, outputDir, outMemberFileName) for bcpCmd in [bcp1, bcp2]: diagFile.write('%s\n' % bcpCmd) os.system(bcpCmd) return
def bcpFiles(): outSetFile.close() outMemberFile.close() db.commit() if DEBUG or not bcpon: return bcpCommand = os.environ['PG_DBUTILS'] + '/bin/bcpin.csh' bcp1 = '%s %s %s %s %s %s "\\t" "\\n" mgd' % \ (bcpCommand, db.get_sqlServer(), db.get_sqlDatabase(), setTable, outputDir, outSetFileName) bcp2 = '%s %s %s %s %s %s "\\t" "\\n" mgd' % \ (bcpCommand, db.get_sqlServer(), db.get_sqlDatabase(), memberTable, outputDir, outMemberFileName) for bcpCmd in [bcp1, bcp2]: 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 init(): ''' # requires: # # effects: # 1. Processes command line options # 2. Initializes local DBMS parameters # 3. Initializes global file descriptors/file names # # returns: # ''' global inputFile, outputFile, diagFile, errorFile global inputFileName, errorFileName, diagFileName, outputFileName 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_commandLogFile(diagFileName) # Set Log File Descriptor 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('\nStart file: %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 # # returns: # ''' global inputFile, outputFile, diagFile, errorFile global inputFileName, errorFileName, diagFileName, outputFileName 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_commandLogFile(diagFileName) # Set Log File Descriptor 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('\nStart file: %s\n\n' % (mgi_utils.date()))
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 bcpFiles(): bcpCommand = os.environ['PG_DBUTILS'] + '/bin/bcpin.csh' bcpCmd = '%s %s %s %s %s %s "|" "\\n" mgd' % \ (bcpCommand, db.get_sqlServer(), db.get_sqlDatabase(), 'MGI_Relationship', outputDir, bcpFile) rc = os.system(bcpCmd) if rc != 0: closeFiles() sys.exit(2) # update mgi_relationship auto-sequence db.sql(''' select setval('mgi_relationship_seq', (select max(_Relationship_key) from MGI_Relationship)) ''', None) return
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 (): bcpScript = os.environ['PG_DBUTILS'] + '/bin/bcpin.csh' db.commit() fpImageAccFile.close() bcpI = '%s %s %s' % (bcpScript, db.get_sqlServer(), db.get_sqlDatabase()) bcpII = '"\\t" "\\n" mgd' accTable = 'ACC_Accession' bcpCmd = '%s %s "/" %s %s' % (bcpI, accTable, imageAccFile, bcpII) os.system(bcpCmd) return
def bcpFiles(): bcpdelim = "|" if DEBUG or not bcpon: return closeFiles() bcpI = '%s %s %s' % (BCP_COMMAND, db.get_sqlServer(), db.get_sqlDatabase()) bcpII = '"|" "\\n" mgd' bcp1 = '%s %s "/" %s %s' % (bcpI, alleleTable, alleleFileName, bcpII) bcp2 = '%s %s "/" %s %s' % (bcpI, mutationTable, mutationFileName, bcpII) bcp3 = '%s %s "/" %s %s' % (bcpI, mutantTable, mutantFileName, bcpII) bcp4 = '%s %s "/" %s %s' % (bcpI, refTable, refFileName, bcpII) bcp5 = '%s %s "/" %s %s' % (bcpI, accTable, accFileName, bcpII) bcp6 = '%s %s "/" %s %s' % (bcpI, accRefTable, accRefFileName, bcpII) bcp7 = '%s %s "/" %s %s' % (bcpI, noteTable, noteFileName, bcpII) bcp8 = '%s %s "/" %s %s' % (bcpI, noteChunkTable, noteChunkFileName, bcpII) bcp9 = '%s %s "/" %s %s' % (bcpI, annotTable, annotFileName, bcpII) db.commit() for bcpCmd in [bcp1, bcp2, bcp3, bcp4, bcp5, bcp6, bcp7, bcp8, bcp9]: diagFile.write('%s\n' % bcpCmd) os.system(bcpCmd) if len(ikmcSQLs) > 0: print ikmcSQLs db.sql(ikmcSQLs, None) db.commit() # update mgi_reference_assoc_seq auto-sequence db.sql(''' select setval('mgi_reference_assoc_seq', (select max(_Assoc_key) from MGI_Reference_Assoc)) ''', None) # update all_allele_cellline_seq auto-sequence db.sql(''' select setval('all_allele_cellline_seq', (select max(_Assoc_key) from ALL_Allele_CellLine)) ''', None) # update voc_annot_seq auto-sequence db.sql(''' select setval('voc_annot_seq', (select max(_Annot_key) from VOC_Annot)) ''', None) db.commit()
def bcpFiles(): outResultFile.close() outResultStFile.close() outResultImageFile.close() db.commit() db.useOneConnection(0) bcpScript = os.environ['PG_DBUTILS'] + '/bin/bcpin.csh' bcpI = '%s %s %s' % (bcpScript, db.get_sqlServer(), db.get_sqlDatabase()) bcpII = '"|" "\\n" mgd' bcp1 = '%s %s "/" %s %s' % (bcpI, resultTable, outResultFileName, bcpII) bcp2 = '%s %s "/" %s %s' % (bcpI, resultStTable, outResultStFileName, bcpII) bcp3 = '%s %s "/" %s %s' % (bcpI, resultImageTable, outResultImageFileName, bcpII) for bcpCmd in [bcp1, bcp2, bcp3]: diagFile.write('%s\n' % bcpCmd) os.system(bcpCmd) return
def init(): global diagFile, errorFile, inputFile, errorFileName, diagFileName db.useOneConnection(1) db.set_sqlUser(user) db.set_sqlPasswordFromFile(passwordFileName) 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) # 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 diagFile, errorFile, inputFile, errorFileName, diagFileName db.useOneConnection(1) db.set_sqlUser(user) db.set_sqlPasswordFromFile(passwordFileName) 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) # 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 loadTempTable(): print('Create a bcp file from the input file') sys.stdout.flush() # # Read each record from the input file, perform validation checks and # write them to a bcp file. # line = fpInput.readline() while line: #for line in fpInput.readlines(): seqID = str.strip(line) # write out to the bcp file: fpBCP.write('%s%s' % (seqID, CRT)) line = fpInput.readline() # # Close the bcp file. # fpBCP.close() # # Load the input data into the temp table. # print('Load the input data into the temp table: ' + tempTable) sys.stdout.flush() bcpCmd = '%s %s %s %s "/" %s "\\t" "\\n" mgd' % \ (BCP_COMMAND, db.get_sqlServer(), db.get_sqlDatabase(),tempTable, bcpFile) rc = os.system(bcpCmd) if rc != 0: closeFiles() sys.exit(1)
def bcpFiles(): strainFile.close() markerFile.close() accFile.close() annotFile.close() noteFile.close() noteChunkFile.close() if DEBUG or not bcpon: return db.commit() bcpCommand = os.environ['PG_DBUTILS'] + '/bin/bcpin.csh' bcp1 = '%s %s %s %s %s %s "\|" "\\n" mgd' % \ (bcpCommand, db.get_sqlServer(), db.get_sqlDatabase(), strainTable, outputDir, strainTableBCP) bcp2 = '%s %s %s %s %s %s "\|" "\\n" mgd' % \ (bcpCommand, db.get_sqlServer(), db.get_sqlDatabase(), markerTable, outputDir, markerTableBCP) bcp3 = '%s %s %s %s %s %s "\|" "\\n" mgd' % \ (bcpCommand, db.get_sqlServer(), db.get_sqlDatabase(), accTable, outputDir, accTableBCP) bcp4 = '%s %s %s %s %s %s "\|" "\\n" mgd' % \ (bcpCommand, db.get_sqlServer(), db.get_sqlDatabase(), annotTable, outputDir, annotTableBCP) bcp5 = '%s %s %s %s %s %s "\|" "\\n" mgd' % \ (bcpCommand, db.get_sqlServer(), db.get_sqlDatabase(), noteTable, outputDir, noteTableBCP) bcp6 = '%s %s %s %s %s %s "\|" "\\n" mgd' % \ (bcpCommand, db.get_sqlServer(), db.get_sqlDatabase(), noteChunkTable, outputDir, noteChunkTableBCP) for bcpCmd in [bcp1, bcp2, bcp3, bcp4, bcp5, bcp6]: diagFile.write('%s\n' % bcpCmd) os.system(bcpCmd) # update prb_strain_marker_seq auto-sequence db.sql(''' select setval('prb_strain_marker_seq', (select max(_StrainMarker_key) from PRB_Strain_Marker)) ''', None) db.commit() # update voc_annot_seq auto-sequence db.sql(''' select setval('voc_annot_seq', (select max(_Annot_key) from VOC_Annot)) ''', None) db.commit() return
def loadTempTables(): global assoc print('Create a bcp file from the gene model input file') sys.stdout.flush() # # Read each record from the gene model input file, perform validation # checks and write them to a bcp file. # line = fpGM.readline() count = 1 while line: tokens = re.split(TAB, line[:-1]) gmID = tokens[0] chromosome = tokens[1] startCoordinate = tokens[2] endCoordinate = tokens[3] strand = tokens[4] description = tokens[5] if len(re.findall('[^0-9]', startCoordinate)) > 0: print('Invalid start coordinate (line ' + str(count) + ')') fpGMBCP.close() closeFiles() sys.exit(1) if len(re.findall('[^0-9]', endCoordinate)) > 0: print('Invalid end coordinate (line ' + str(count) + ')') fpGMBCP.close() closeFiles() sys.exit(1) if strand != '-' and strand != '+' and strand != '.': print('Invalid strand (line ' + str(count) + ')') fpGMBCP.close() closeFiles() sys.exit(1) fpGMBCP.write(gmID + TAB + chromosome + TAB + startCoordinate + TAB + endCoordinate + TAB + strand + TAB + description + NL) line = fpGM.readline() count += 1 # # Close the bcp file. # fpGMBCP.close() print('Create a bcp file from the association input file') sys.stdout.flush() # # Read each record from the association input file, perform validation # checks and write them to a bcp file. # line = fpAssoc.readline() count = 1 while line: tokens = re.split(TAB, line[:-1]) mgiID = tokens[0] gmID = tokens[1] if re.match('MGI:[0-9]+', mgiID) == None: print('Invalid MGI ID (line ' + str(count) + ')') fpAssocBCP.close() closeFiles() sys.exit(1) fpAssocBCP.write(mgiID + TAB + gmID + NL) # # Maintain a dictionary of the MGI IDs that are in the association # input file. The value for each dictionary entry is a list of the # gene model IDs that are associated with the MGI ID key. # if mgiID in assoc: list = assoc[mgiID] list.append(gmID) assoc[mgiID] = list else: list = [gmID] assoc[mgiID] = list line = fpAssoc.readline() count += 1 # # Close the bcp file. # fpAssocBCP.close() # # Load the temp tables with the input data. # print('Load the gene model data into the temp table: ' + gmTempTable) sys.stdout.flush() bcpCmd = '%s %s %s %s "/" %s "\\t" "\\n" mgd' % \ (bcpCommand, db.get_sqlServer(), db.get_sqlDatabase(),gmTempTable, gmBCPFile) rc = os.system(bcpCmd) if rc != 0: closeFiles() sys.exit(1) print('Load the association data into the temp table: ' + assocTempTable) sys.stdout.flush() bcpCmd = '%s %s %s %s "/" %s "\\t" "\\n" mgd' % \ (bcpCommand, db.get_sqlServer(), db.get_sqlDatabase(),assocTempTable, assocBCPFile) rc = os.system(bcpCmd) if rc != 0: closeFiles() sys.exit(1) return
def initialize(): global diagFile, errorFile, inputFile, errorFileName, diagFileName global alleleFile, mutationFile, mutantFile, refFile global accFile, accRefFile, noteFile, noteChunkFile, annotFile global newAlleleFile db.useOneConnection(1) db.set_sqlUser(user) db.set_sqlPasswordFromFile(passwordFileName) head, tail = os.path.split(inputFileName) diagFileName = outputDir + '/' + tail + '.diagnostics' errorFileName = outputDir + '/' + tail + '.error' newAlleleFileName = outputDir + '/' + tail + '.new' 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: newAlleleFile = open(newAlleleFileName, 'w') except: exit(1, 'Could not open file %s\n' % newAlleleFileName) try: inputFile = open(inputFileName, 'r') except: exit(1, 'Could not open file %s\n' % inputFileName) try: alleleFile = open(alleleFileName, 'w') except: exit(1, 'Could not open file %s\n' % alleleFileName) try: mutationFile = open(mutationFileName, 'w') except: exit(1, 'Could not open file %s\n' % mutationFileName) try: mutantFile = open(mutantFileName, 'w') except: exit(1, 'Could not open file %s\n' % mutantFileName) try: refFile = open(refFileName, 'w') except: exit(1, 'Could not open file %s\n' % refFileName) try: accFile = open(accFileName, 'w') except: exit(1, 'Could not open file %s\n' % accFileName) try: accRefFile = open(accRefFileName, 'w') except: exit(1, 'Could not open file %s\n' % accRefFileName) try: noteFile = open(noteFileName, 'w') except: exit(1, 'Could not open file %s\n' % noteFileName) try: noteChunkFile = open(noteChunkFileName, 'w') except: exit(1, 'Could not open file %s\n' % noteChunkFileName) try: annotFile = open(annotFileName, 'w') except: exit(1, 'Could not open file %s\n' % annotFileName) # 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()))
def init(): global bcpCommand global diagFile, errorFile, inputFile, errorFileName, diagFileName global outImageFile, outPaneFile, outAccFile global outCopyrightFile, outCaptionFile global inImageFile, inPaneFile global createdByKey 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' diagFileName = currentDir + '/gxdimageload.diagnostics' errorFileName = currentDir + '/gxdimageload.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) # Input Files try: inImageFile = open(inImageFileName, 'r') except: exit(1, 'Could not open file %s\n' % inImageFileName) try: inPaneFile = open(inPaneFileName, 'r') except: exit(1, 'Could not open file %s\n' % inPaneFileName) # Output Files try: outImageFile = open(outImageFileName, 'w') except: exit(1, 'Could not open file %s\n' % outImageFileName) try: outPaneFile = open(outPaneFileName, 'w') except: exit(1, 'Could not open file %s\n' % outPaneFileName) try: outAccFile = open(outAccFileName, 'w') except: exit(1, 'Could not open file %s\n' % outAccFileName) try: outCaptionFile = open(outCaptionFileName, 'w') except: exit(1, 'Could not open file %s\n' % outCaptionFileName) try: outCopyrightFile = open(outCopyrightFileName, 'w') except: exit(1, 'Could not open file %s\n' % outCopyrightFileName) db.setTrace(True) 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())) createdByKey = loadlib.verifyUser(createdBy, 0, errorFile) return
def writeBCP(expID, matrixList): global rnaSeqKey, combinedKey, bcpCommandList #print 'creating bcp files for expID: %s' % expID start_time = time.time() combinedFile = '%s.%s' % (combinedBcpFile, expID) combinedFilePath = '%s/%s' % (outputDir, combinedFile) initCombinedBcp(combinedFilePath) rnaSeqFile = '%s.%s' % (rnaSeqBcpFile, expID) rnaSeqFilePath = '%s/%s' % (outputDir, rnaSeqFile) initRnaSeqBcp(rnaSeqFilePath) # counts in the bcp file combinedLineCt = 0 rnaSeqLineCt = 0 for matrix in matrixList: for row in matrix: #print row rowLen = len(row) # position 0 is always markerKey # position rowLen -1 is # Biological Replicates # position rowLen - 2 is ave QN TPM (all samples) markerKey = row[0] numBioRepl = row[-1] aveQnTpm = row[-2] #print 'avQnTpm: %s' % aveQnTpm levelKey = calcLevel(aveQnTpm) #print 'levelKey: %s' % levelKey line = '%s%s%s%s%s%s%s%s%s%s%s%s%s%s%s%s%s%s' % ( combinedKey, TAB, markerKey, TAB, levelKey, TAB, numBioRepl, TAB, aveQnTpm, TAB, createdByKey, TAB, createdByKey, TAB, loaddate, TAB, loaddate, CRT) #print 'combined: %s' %line fpCombinedBcp.write(line) combinedLineCt += 1 # positions 1 to rowLen-2 are samples: sampleKey|aveTPM|qnTpm # to get samples we iterate over rowLen-3 starting with i+1 for i in range(rowLen - 3): #print 'next sample: %s' % row[i+1] sampleKey, aveTpm, qnTpm = string.split(row[i + 1], PIPE) line = '%s%s%s%s%s%s%s%s%s%s%s%s%s%s%s%s%s%s%s%s' % ( rnaSeqKey, TAB, sampleKey, TAB, combinedKey, TAB, markerKey, TAB, aveTpm, TAB, qnTpm, TAB, createdByKey, TAB, createdByKey, TAB, loaddate, TAB, loaddate, CRT) #print 'rnaseq: %s' % line fpRnaSeqBcp.write(line) rnaSeqLineCt += 1 rnaSeqKey += 1 combinedKey += 1 closeCombinedBcp() closeRnaSeqBcp() # gather bcp commands in a List so we can execute them together at the # end of processing # cmd = '%s %s %s %s %s %s "\\t" "\\n" mgd' % \ (bcpCommand, db.get_sqlServer(), db.get_sqlDatabase(), combinedTable, outputDir, combinedFile) bcpCommandList.append(cmd) #print 'Num combined bcp lines for expID %s: %s' % (expID, combinedLineCt) cmd = '%s %s %s %s %s %s "\\t" "\\n" mgd' % \ (bcpCommand, db.get_sqlServer(), db.get_sqlDatabase(), rnaSeqTable, outputDir, rnaSeqFile) bcpCommandList.append(cmd) #print 'Num rnaSeq bcp lines for expID %s: %s' % (expID, rnaSeqLineCt) elapsed_time = time.time() - start_time elapsed_time = time.time() - start_time print '%sTIME: Creating bcp files %s %s%s' % ( CRT, expID, time.strftime("%H:%M:%S", time.gmtime(elapsed_time)), CRT) return 0
def init(): global diagFile, errorFile, errorFileName, diagFileName global outResultStFile, outResultFile, outResultImageFile global inResultsFile, inStructureFile db.useOneConnection(1) db.set_sqlUser(user) db.set_sqlPasswordFromFile(passwordFileName) diagFileName = 'resultsload.diagnostics' errorFileName = 'resultsload.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) # Input Files try: inResultsFile = open(inResultsFileName, 'r') except: exit(1, 'Could not open file %s\n' % inResultsFileName) try: inStructureFile = open(inStructureFileName, 'r') except: exit(1, 'Could not open file %s\n' % inStructureFileName) # Output Files try: outResultStFile = open(outResultStFileName, 'w') except: exit(1, 'Could not open file %s\n' % outResultStFileName) try: outResultFile = open(outResultFileName, 'w') except: exit(1, 'Could not open file %s\n' % outResultFileName) try: outResultImageFile = open(outResultImageFileName, 'w') except: exit(1, 'Could not open file %s\n' % outResultImageFileName) # 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 mgiIDToSymbolDict, termIDToTermDict global mgdMgiIdToTermIdDict, inputTermIdLookupByMgiId global mcvTermToParentMkrTypeTermDict global mcvKeyToTermDict, mkrTypeToAssocMCVTermDict global mcvTermToMkrTypeDict, mkrKeyToMkrTypeKeyDict, mkrTypeToKeyDict global updatedBy, updatedByKey print 'DB Server:' + db.get_sqlServer() print 'DB Name: ' + db.get_sqlDatabase() sys.stdout.flush() db.useOneConnection(1) #db.set_sqlLogFunction(db.sqlLogAll) openFiles() loadTempTable() # get user key for updates results = db.sql('''select _User_key from MGI_User where login = '******' ''' % updatedBy) updatedByKey = results[0]['_User_key'] # # Load global lookup dictionaries # # create lookup of all official markers in the database # mapped to their symbols results = db.sql('''select a.accid, m.symbol 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''', 'auto') for r in results: mgiIDToSymbolDict[r['accid']] = r['symbol'] # create lookup of all mcv and so ids mapped to their terms results = db.sql('''select a.accID, t.term from ACC_Accession a, VOC_Term t where a._LogicalDB_key in (145,146) and a._MGIType_key = 13 and a._Object_key = t._Term_key''', 'auto') for r in results: termIDToTermDict[r['accID']] = r['term'] # create lookup of markers mapped to their SO/MCV IDs results = db.sql('''select a1.accID as termID, a2.accID as mgiID from VOC_Annot v, ACC_Accession a1, ACC_Accession a2 where v._AnnotType_key = 1011 and v._Term_key = a1._Object_key and a1._MGIType_key = 13 and a1._LogicalDB_key in (145, 146) and a1.preferred = 1 and a1.prefixPart = 'MCV:' and v._Object_key = a2._Object_key and a2._MGIType_key = 2 and a2._LogicalDB_key = 1 and a2.prefixPart = 'MGI:' ''', 'auto') for r in results: mgiID = r['mgiID'] termID = r['termID'] if not mgdMgiIdToTermIdDict.has_key(mgiID): mgdMgiIdToTermIdDict[mgiID] = [] mgdMgiIdToTermIdDict[mgiID].append(termID) # # get all SO/MCV annotations to markers from temp table # loaded from the input file # results = db.sql('select tmp.termID, tmp.mgiID from %s tmp ' % (tempTable), 'auto') #'where tmp.mgiID is not null ' + \ #'and tmp.termID is not null', 'auto') # load lookup for r in results: mgiID = r['mgiID'] termID = r['termID'] if not inputTermIdLookupByMgiId.has_key(mgiID): inputTermIdLookupByMgiId[mgiID] = [] # default if termID != None: # this case when only mgiID in file for delete inputTermIdLookupByMgiId[mgiID].append(termID) # # get marker types from the database # results = db.sql('''select a.accId as mgiID, t.name from MRK_Marker m, ACC_Accession a, MRK_Types t where m._Marker_Status_key = 1 and m._Organism_key = 1 and m._Marker_key = a._Object_key and a._MGIType_key = 2 and a._LogicalDB_key = 1 and a.preferred = 1 and a.prefixPart = 'MGI:' and m._Marker_Type_key = t._Marker_Type_key''', 'auto') for r in results: mgiIdToMkrTypeDict[r['mgiID']] = r['name'] results = db.sql('''select name, _Marker_Type_key from MRK_Types''', 'auto') for r in results: mkrTypeKeyToMkrTypeDict[ r['_Marker_Type_key'] ] = r['name'] mkrTypeToKeyDict[r['name']] = r['_Marker_Type_key'] # parse the MCV Note and load lookups # we store the association of a marker type to a MCV # term in the term Note. Only MCV terms which correspond to # marker types have these notes # note looks like: # Marker_Type=N # # _Vocab_key = 79 = Marker Category Vocab # _NoteType_key = 1001 = Private Vocab Term Comment' # Get the MCV vocab terms and their notes from the database # Notes tell us the term's MGI marker type if term maps directly to a # marker type cmds = [] cmds.append(''' select n._Object_key, rtrim(nc.note) as chunk, nc.sequenceNum into temp notes from MGI_Note n, MGI_NoteChunk nc where n._MGIType_key = 13 and n._NoteType_key = 1001 and n._Note_key = nc._Note_key ''') cmds.append('create index notes_idx1 on notes(_Object_key)') cmds.append(''' select t._Term_key, t.term, n.chunk from VOC_Term t left outer join notes n on n._object_key = t._term_key where t._Vocab_key = 79 and t._Term_key = n._Object_key order by t._Term_key, n.sequenceNum ''') results = db.sql(cmds, 'auto') notes = {} # map the terms to their note chunks for r in results[2]: term = r['term'] chunk = r['chunk'] # if there is a note chunk add it to the notes dictionary # we'll pull all the chunks together later if chunk != None: if not notes.has_key(term): notes[term] = [] notes[term].append(chunk) # parse the marker type from the note, if there is one for term in notes.keys(): note = string.join(notes[term], '') if not note[0:11] == 'Marker_Type': continue # parse the note tokens = string.split(note, ';') mType = tokens[0] tokens = string.split(mType, '=') # 2nd token is the marker type key mkrTypeKey = int(string.strip(tokens[1])) mkrType = mkrTypeKeyToMkrTypeDict[mkrTypeKey] # There is only 1 MCV term per MGI Mkr type mkrTypeToAssocMCVTermDict[mkrType]= term mcvTermToMkrTypeDict[term] = mkrType # # now map all mcv terms to their parent term representing a marker type # for all children in the closure table - find the parent # which is a marker type parent # cmds = [] cmds.append(''' select _AncestorObject_key, _DescendentObject_key into temp clos from DAG_Closure where _DAG_key = 9 and _MGIType_key = 13 ''') cmds.append('create index clos_idx1 on clos(_AncestorObject_key)') cmds.append('create index clos_idx2 on clos(_DescendentObject_key)') cmds.append(''' select t1.term as ancestorTerm, t2.term as descendentTerm from clos c, VOC_Term t1, VOC_Term t2 where c._AncestorObject_key = t1._Term_key and c._DescendentObject_key = t2._Term_key order by t2.term ''') results = db.sql(cmds, 'auto') # the mcv terms that represent marker types mcvMarkerTypeValues = mkrTypeToAssocMCVTermDict.values() for r in results[3]: aTerm = r['ancestorTerm'] dTerm = r['descendentTerm'] if mcvTermToParentMkrTypeTermDict.has_key(dTerm): # we've already mapped this descendent to its marker type parent continue # dTerm may be a marker type term elif dTerm in mcvMarkerTypeValues: mcvTermToParentMkrTypeTermDict[dTerm] = dTerm # if the ancestor of this descendent term is a # marker type term load it into the dict elif aTerm in mcvMarkerTypeValues: mcvTermToParentMkrTypeTermDict[dTerm] = aTerm # map marker keys to their marker type cmd = ''' select _Marker_Type_key, _Marker_key from MRK_Marker where _Marker_Status_key = 1 ''' results = db.sql(cmd, 'auto') for r in results: mkrTypeKey = r['_Marker_Type_key'] mkrKey = r['_Marker_key'] mkrKeyToMkrTypeKeyDict[mkrKey] = mkrTypeKey
def init(): global diagFile, errorFile, inputFile, errorFileName, diagFileName global strainFile, markerFile, accFile, annotFile global noteFile, noteChunkFile 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) try: markerFile = open(markerFileName, 'w') except: exit(1, 'Could not open file %s\n' % markerFileName) try: accFile = open(accFileName, 'w') except: exit(1, 'Could not open file %s\n' % accFileName) try: noteFile = open(noteFileName, 'w') except: exit(1, 'Could not open file %s\n' % noteFileName) try: noteChunkFile = open(noteChunkFileName, 'w') except: exit(1, 'Could not open file %s\n' % noteChunkFileName) try: annotFile = open(annotFileName, 'w') except: exit(1, 'Could not open file %s\n' % annotFileName) # 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 diagFile, errorFile, inputFile, errorFileName, diagFileName global outSetFile, outMemberFile global setKey, setMemberKey, createdByKey, mgiTypeKey, useSetKey global DEBUG db.useOneConnection(1) db.set_sqlUser(user) db.set_sqlPasswordFromFile(passwordFileName) diagFileName = '%s/setload.diagnostics' % (outputDir) errorFileName = '%s/setload.error' % (outputDir) 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) # Output Files try: fullPathSetFile = '%s/%s' % (outputDir, outSetFileName) outSetFile = open(fullPathSetFile, 'w') except: exit(1, 'Could not open file %s\n' % fullPathSetFile) try: fullPathMemberFile = '%s/%s' % (outputDir, outMemberFileName) outMemberFile = open(fullPathMemberFile, 'w') except: exit(1, 'Could not open file %s\n' % fullPathMemberFile) # 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())) if mode == 'preview': DEBUG = 1 bcpon = 0 elif mode != 'load': exit(1, 'Invalid Processing Mode: %s\n' % (mode)) results = db.sql('select max(_Set_key) + 1 as maxKey from MGI_Set', 'auto') setKey = results[0]['maxKey'] createdByKey = loadlib.verifyUser(createdBy, 0, errorFile) mgiTypeKey = loadlib.verifyMGIType(setType, 0, errorFile) # # use existing MGI_Set, or create a new one # results = db.sql('select _Set_key from MGI_Set where _MGIType_key = %s and name = \'%s\'' % (mgiTypeKey, setName), 'auto') if len(results) > 0: for r in results: setKey = r['_Set_key'] # delete/reload db.sql('delete from MGI_SetMember where _Set_key = %s' % (setKey), None) else: outSetFile.write(str(setKey) + TAB + \ str(mgiTypeKey) + TAB + \ str(setName) + TAB + \ '1' + TAB + \ str(createdByKey) + TAB + str(createdByKey) + TAB + \ loaddate + TAB + loaddate + CRT) results = db.sql('select max(_SetMember_key) + 1 as maxKey from MGI_SetMember', 'auto') setMemberKey = results[0]['maxKey'] return
def init(): global mgiIDToSymbolDict, termIDToTermDict global mgdMgiIdToTermIdDict, inputTermIdLookupByMgiId global mcvTermToParentMkrTypeTermDict global mcvKeyToTermDict, mkrTypeToAssocMCVTermDict global mcvTermToMkrTypeDict, mkrKeyToMkrTypeKeyDict, mkrTypeToKeyDict global updatedBy, updatedByKey print('DB Server:' + db.get_sqlServer()) print('DB Name: ' + db.get_sqlDatabase()) sys.stdout.flush() db.useOneConnection(1) #db.set_sqlLogFunction(db.sqlLogAll) openFiles() loadTempTable() # get user key for updates results = db.sql('''select _User_key from MGI_User where login = '******' ''' % updatedBy) updatedByKey = results[0]['_User_key'] # # Load global lookup dictionaries # # create lookup of all official markers in the database # mapped to their symbols results = db.sql( '''select a.accid, m.symbol 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''', 'auto') for r in results: mgiIDToSymbolDict[r['accid']] = r['symbol'] # create lookup of all mcv and so ids mapped to their terms results = db.sql( '''select a.accID, t.term from ACC_Accession a, VOC_Term t where a._LogicalDB_key in (145,146) and a._MGIType_key = 13 and a._Object_key = t._Term_key''', 'auto') for r in results: termIDToTermDict[r['accID']] = r['term'] # create lookup of markers mapped to their SO/MCV IDs results = db.sql( '''select a1.accID as termID, a2.accID as mgiID from VOC_Annot v, ACC_Accession a1, ACC_Accession a2 where v._AnnotType_key = 1011 and v._Term_key = a1._Object_key and a1._MGIType_key = 13 and a1._LogicalDB_key in (145, 146) and a1.preferred = 1 and a1.prefixPart = 'MCV:' and v._Object_key = a2._Object_key and a2._MGIType_key = 2 and a2._LogicalDB_key = 1 and a2.prefixPart = 'MGI:' ''', 'auto') for r in results: mgiID = r['mgiID'] termID = r['termID'] if mgiID not in mgdMgiIdToTermIdDict: mgdMgiIdToTermIdDict[mgiID] = [] mgdMgiIdToTermIdDict[mgiID].append(termID) # # get all SO/MCV annotations to markers from temp table # loaded from the input file # results = db.sql('select tmp.termID, tmp.mgiID from %s tmp ' % (tempTable), 'auto') #'where tmp.mgiID is not null ' + \ #'and tmp.termID is not null', 'auto') # load lookup for r in results: mgiID = r['mgiID'] termID = r['termID'] if mgiID not in inputTermIdLookupByMgiId: inputTermIdLookupByMgiId[mgiID] = [] # default if termID != None: # this case when only mgiID in file for delete inputTermIdLookupByMgiId[mgiID].append(termID) # # get marker types from the database # results = db.sql( '''select a.accId as mgiID, t.name from MRK_Marker m, ACC_Accession a, MRK_Types t where m._Marker_Status_key = 1 and m._Organism_key = 1 and m._Marker_key = a._Object_key and a._MGIType_key = 2 and a._LogicalDB_key = 1 and a.preferred = 1 and a.prefixPart = 'MGI:' and m._Marker_Type_key = t._Marker_Type_key''', 'auto') for r in results: mgiIdToMkrTypeDict[r['mgiID']] = r['name'] results = db.sql( '''select name, _Marker_Type_key from MRK_Types''', 'auto') for r in results: mkrTypeKeyToMkrTypeDict[r['_Marker_Type_key']] = r['name'] mkrTypeToKeyDict[r['name']] = r['_Marker_Type_key'] # parse the MCV Note and load lookups # we store the association of a marker type to a MCV # term in the term Note. Only MCV terms which correspond to # marker types have these notes # note looks like: # Marker_Type=N # # _Vocab_key = 79 = Marker Category Vocab # _NoteType_key = 1001 = Private Vocab Term Comment' # Get the MCV vocab terms and their notes from the database # Notes tell us the term's MGI marker type if term maps directly to a # marker type cmds = [] cmds.append(''' select n._Object_key, rtrim(n.note) as chunk into temp notes from MGI_Note n where n._MGIType_key = 13 and n._NoteType_key = 1001 ''') cmds.append('create index notes_idx1 on notes(_Object_key)') cmds.append(''' select t._Term_key, t.term, n.chunk from VOC_Term t left outer join notes n on n._object_key = t._term_key where t._Vocab_key = 79 and t._Term_key = n._Object_key order by t._Term_key ''') results = db.sql(cmds, 'auto') notes = {} # map the terms to their note chunks for r in results[2]: term = r['term'] chunk = r['chunk'] # if there is a note chunk add it to the notes dictionary # we'll pull all the chunks together later if chunk != None: if term not in notes: notes[term] = [] notes[term].append(chunk) # parse the marker type from the note, if there is one for term in list(notes.keys()): note = ''.join(notes[term]) if not note[0:11] == 'Marker_Type': continue # parse the note tokens = str.split(note, ';') mType = tokens[0] tokens = str.split(mType, '=') # 2nd token is the marker type key mkrTypeKey = int(str.strip(tokens[1])) mkrType = mkrTypeKeyToMkrTypeDict[mkrTypeKey] # There is only 1 MCV term per MGI Mkr type mkrTypeToAssocMCVTermDict[mkrType] = term mcvTermToMkrTypeDict[term] = mkrType # # now map all mcv terms to their parent term representing a marker type # for all children in the closure table - find the parent # which is a marker type parent # cmds = [] cmds.append(''' select _AncestorObject_key, _DescendentObject_key into temp clos from DAG_Closure where _DAG_key = 9 and _MGIType_key = 13 ''') cmds.append('create index clos_idx1 on clos(_AncestorObject_key)') cmds.append('create index clos_idx2 on clos(_DescendentObject_key)') cmds.append(''' select t1.term as ancestorTerm, t2.term as descendentTerm from clos c, VOC_Term t1, VOC_Term t2 where c._AncestorObject_key = t1._Term_key and c._DescendentObject_key = t2._Term_key order by t2.term ''') results = db.sql(cmds, 'auto') # the mcv terms that represent marker types mcvMarkerTypeValues = list(mkrTypeToAssocMCVTermDict.values()) for r in results[3]: aTerm = r['ancestorTerm'] dTerm = r['descendentTerm'] if dTerm in mcvTermToParentMkrTypeTermDict: # we've already mapped this descendent to its marker type parent continue # dTerm may be a marker type term elif dTerm in mcvMarkerTypeValues: mcvTermToParentMkrTypeTermDict[dTerm] = dTerm # if the ancestor of this descendent term is a # marker type term load it into the dict elif aTerm in mcvMarkerTypeValues: mcvTermToParentMkrTypeTermDict[dTerm] = aTerm # map marker keys to their marker type cmd = ''' select _Marker_Type_key, _Marker_key from MRK_Marker where _Marker_Status_key = 1 ''' results = db.sql(cmd, 'auto') for r in results: mkrTypeKey = r['_Marker_Type_key'] mkrKey = r['_Marker_key'] mkrKeyToMkrTypeKeyDict[mkrKey] = mkrTypeKey
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, diagFile, errorFile, errorFileName, diagFileName global transTypeFile, transFile global transTypeFileName, transFileName db.useOneConnection(1) db.set_sqlUser(user) db.set_sqlPasswordFromFile(passwordFileName) # the default output file names are bases on 'inputFileName' head, fileName = os.path.split(inputFileName) # rename 'head' head = outputFileDir fdate = mgi_utils.date('%m%d%Y') # current date diagFileName = head + '/' + fileName + '.' + fdate + '.diagnostics' print diagFileName errorFileName = head + '/' + fileName + '.' + fdate + '.error' print errorFileName transTypeFileName = head + '/' + fileName + '.' + fdate + '.MGI_TranslationType.bcp' print transTypeFileName transFileName = head + '/' + fileName + '.' + fdate + '.MGI_Translation.bcp' print transFileName 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: transTypeFile = open(transTypeFileName, 'w') except: exit(1, 'Could not open file %s\n' % transTypeFileName) try: transFile = open(transFileName, 'w') except: exit(1, 'Could not open file %s\n' % transFileName) # 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()))
def init(): global diagFile, errorFile, inputFile, errorFileName, diagFileName global outSetFile, outMemberFile global setKey, setMemberKey, createdByKey, mgiTypeKey, useSetKey global DEBUG db.useOneConnection(1) db.set_sqlUser(user) db.set_sqlPasswordFromFile(passwordFileName) diagFileName = '%s/setload.diagnostics' % (outputDir) errorFileName = '%s/setload.error' % (outputDir) 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) # Output Files try: fullPathSetFile = '%s/%s' % (outputDir, outSetFileName) outSetFile = open(fullPathSetFile, 'w') except: exit(1, 'Could not open file %s\n' % fullPathSetFile) try: fullPathMemberFile = '%s/%s' % (outputDir, outMemberFileName) outMemberFile = open(fullPathMemberFile, 'w') except: exit(1, 'Could not open file %s\n' % fullPathMemberFile) # 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())) if mode == 'preview': DEBUG = 1 bcpon = 0 elif mode != 'load': exit(1, 'Invalid Processing Mode: %s\n' % (mode)) results = db.sql('select max(_Set_key) + 1 as maxKey from MGI_Set', 'auto') setKey = results[0]['maxKey'] createdByKey = loadlib.verifyUser(createdBy, 0, errorFile) mgiTypeKey = loadlib.verifyMGIType(setType, 0, errorFile) # # use existing MGI_Set, or create a new one # results = db.sql( 'select _Set_key from MGI_Set where _MGIType_key = %s and name = \'%s\'' % (mgiTypeKey, setName), 'auto') if len(results) > 0: for r in results: setKey = r['_Set_key'] # delete/reload db.sql('delete from MGI_SetMember where _Set_key = %s' % (setKey), None) else: outSetFile.write(str(setKey) + TAB + \ str(mgiTypeKey) + TAB + \ str(setName) + TAB + \ '1' + TAB + \ str(createdByKey) + TAB + str(createdByKey) + TAB + \ loaddate + TAB + loaddate + CRT) results = db.sql( 'select max(_SetMember_key) + 1 as maxKey from MGI_SetMember', 'auto') setMemberKey = results[0]['maxKey'] return
def loadTempTables(): global build, mb2mgiInInputDict, header, fpMirbaseInvalidIdRpt, fatalErrorCount print('Create a bcp file from the coordinate input file') sys.stdout.flush() # # Read each record from the coordinate input file, perform validation # checks and write them to a bcp file. # # set the global header value; remove any tabs, preserve newline header = '%s\n' % fpCoord.readline().strip() tokens = header.split(';') for t in tokens: a = t.split('=') if a[0].strip().lower() == 'build': build = a[1].strip() count = 1 writeInvcoordStrandHeader() for line in fpCoord.readlines(): tokens = re.split(TAB, line[:-1]) mgiID = tokens[0].strip() chromosome = tokens[1].strip() startCoordinate = tokens[2].strip() endCoordinate = tokens[3].strip() strand = tokens[4].strip() source = tokens[5].strip() display = tokens[6].strip() miRBaseID = tokens[7].strip() badIdList = [] if miRBaseID != '': for id in str.split(miRBaseID, ','): id = str.strip(id) if str.find(str.strip(id), 'MI') != 0: print('bad mirbase id: %s' % id) badIdList.append(id) if badIdList: # report, go to next line print('bad MiRBase Ids: %s' % ', '.join(badIdList)) fpMirbaseInvalidIdRpt.write( '%s%s%s%s' % (mgiID, TAB, ', '.join(badIdList, ), NL)) fatalErrorCount += 1 continue sourceDisplay = '%s/%s' % (source, display) #print 'sourceDisplay: %s' % sourceDisplay if not sourceDisplay in sourceDisplayList: sourceDisplayList.append(sourceDisplay) errors = createInvCoordStrandReport(mgiID, startCoordinate, endCoordinate, strand, source) if errors != 0: continue fpCoordBCP.write(mgiID + TAB + chromosome + TAB + startCoordinate + TAB + endCoordinate + TAB + strand + TAB + source + TAB + display + TAB + miRBaseID + TAB + build + NL) count += 1 writeInvcoordStrandFooter() # # Close the bcp file. # fpCoordBCP.close() # # Load the temp tables with the input data. # print('Load the coordinate data into the temp table: ' + coordTempTable) bcpCmd = '%s %s %s %s "/" %s "\\t" "\\n" mgd' % \ (bcpCommand, db.get_sqlServer(), db.get_sqlDatabase(),coordTempTable, coordBCPFile) print('bcp cmd = %s' % bcpCmd) sys.stdout.flush() rc = os.system(bcpCmd) if rc != 0: closeFiles() sys.exit(1) return
def init(): global diagFile, errorFile, inputFile, errorFileName, diagFileName global strainFile, markerFile, accFile, annotFile global noteFile 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', encoding="latin-1") 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) try: markerFile = open(markerFileName, 'w') except: exit(1, 'Could not open file %s\n' % markerFileName) try: accFile = open(accFileName, 'w') except: exit(1, 'Could not open file %s\n' % accFileName) try: noteFile = open(noteFileName, 'w') except: exit(1, 'Could not open file %s\n' % noteFileName) try: annotFile = open(annotFileName, 'w') except: exit(1, 'Could not open file %s\n' % annotFileName) # 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 loadTempTable(): global annot, updatedBy print('Create a bcp file from the input file') sys.stdout.flush() # # Read each record from the input file, perform validation checks and # write them to a bcp file. # line = fpInput.readline() updatedBy = re.split(TAB, line[:-1])[6] count = 1 while line: tokens = re.split(TAB, line[:-1]) termID = tokens[0] mgiID = tokens[1] jNum = tokens[2] evidCode = tokens[3] inferFrom = tokens[4] qual = tokens[5] if qual == None: qual = '' editor = tokens[6] date = tokens[7] if date == None: date = '' notes = tokens[8] if notes == None: notes = '' ldb = '' # not used for mcvload termIDExists = len(re.findall('[a-zA-Z0-9]', termID)) mgiIDExists = len(re.findall('[a-zA-Z0-9]', mgiID)) jNumExists = len(re.findall('[a-zA-Z0-9]', jNum)) evidCodeExists = len(re.findall('[a-zA-Z0-9]', evidCode)) inferFromExists = len(re.findall('[a-zA-Z0-9]', inferFrom)) qualExists = len(re.findall('[a-zA-Z0-9]', qual)) editorExists = len(re.findall('[a-zA-Z0-9]', editor)) dateExists = len(re.findall('[a-zA-Z0-9]', date)) # # Special Case # If there is only an mgiID this is ok, it means # curator intends to delete all annotations # if termIDExists == 0 and jNumExists == 0 and \ evidCodeExists == 0 and inferFromExists == 0 \ and editorExists == 0 and mgiIDExists > 0: # check the MGI ID for format if re.match('MGI:[0-9]+', mgiID) == None: print('Invalid MGI ID (line ' + str(count) + ')') fpBCP.close() closeFiles() sys.exit(1) # write out to the bcp file: fpBCP.write(termID + TAB + mgiID + TAB + jNum + TAB + evidCode + \ TAB + editor + NL) # add to the annotation dictionary so it gets written to the # annotation file annotList = [termID, mgiID, jNum, evidCode, inferFrom, qual, \ editor, date, notes, ldb] if mgiID not in annot: annot[mgiID] = [] annot[mgiID].append(annotList) # get next input line and continue line = fpInput.readline() continue # There must be a term ID in proper format # SO:nnnnnnn or MCV:nnnnnnn if termIDExists == 0: print('Missing Term ID (line ' + str(count) + ')') fpBCP.close() closeFiles() sys.exit(1) else: if re.match('MCV:[0-9]+',termID) == None and \ re.match('SO:[0-9]+',termID) == None: print('Invalid Term ID (line ' + str(count) + ')' + ' ' + \ termID) fpBCP.close() closeFiles() sys.exit(1) # # There must be an MGI ID in proper format (MGI:nnnnnnn). # if mgiIDExists == 0: print('Missing MGI ID (line ' + str(count) + ')') fpBCP.close() closeFiles() sys.exit(1) else: if re.match('MGI:[0-9]+', mgiID) == None: print('Invalid MGI ID (line ' + str(count) + ')') fpBCP.close() closeFiles() sys.exit(1) # # There must be an J Number in proper format (J:nnnnnnn). # if jNumExists == 0: print('Missing J Number (line ' + str(count) + ')') fpBCP.close() closeFiles() sys.exit(1) else: if re.match('J:[0-9]+', jNum) == None: print('Invalid J Number (line ' + str(count) + ')') fpBCP.close() closeFiles() sys.exit(1) # # There must be an evidence code # if evidCodeExists == 0: print('Missing Evidence Code(line ' + str(count) + ')') fpBCP.close() closeFiles() sys.exit(1) # # There must be an editor login # if editorExists == 0: print('Missing Editor login (line ' + str(count) + ')') fpBCP.close() closeFiles() sys.exit(1) fpBCP.write(termID + TAB + mgiID + TAB + jNum + TAB + evidCode + \ TAB + editor + NL) # # Maintain a dictionary of the MGI IDs that are in the input file. # The key for each entry is the MGI ID and the value is a list of # the annotation attributes for that the MGI ID. # if mgiID != '': annotList = [termID, mgiID, jNum, evidCode, inferFrom, \ qual, editor, date, notes, ldb] if mgiID not in annot: annot[mgiID] = [] annot[mgiID].append(annotList) line = fpInput.readline() count += 1 # # Close the bcp file. # fpBCP.close() # # Load the input data into the temp table. # print('Load the input data into the temp table: ' + tempTable) sys.stdout.flush() bcpCmd = '%s %s %s %s "/" %s "\\t" "\\n" mgd' % \ (BCP_COMMAND, db.get_sqlServer(), db.get_sqlDatabase(),tempTable, bcpFile) rc = os.system(bcpCmd) if rc != 0: closeFiles() sys.exit(1)
def initialize(): # Purpose: open file descriptors; write timestamps to log files # Returns: 1 if error, else 0 # Assumes: Nothing # Effects: Sets global variables, exits if a file can't be opened, # creates files in the file system global fpDiagFile, fpErrorFile, fpInputFile, errorFileName, diagFileName global fpAlleleFile, fpMutationFile, fpRefFile global fpAccFile, fpNoteFile, fpAnnotFile global fpNewAlleleRptFile db.useOneConnection(1) #db.set_sqlUser(user) #db.set_sqlPasswordFromFile(passwordFileName) head, tail = os.path.split(inputFileName) diagFileName = outputDir + '/' + tail + '.diagnostics' errorFileName = outputDir + '/' + tail + '.error' try: fpDiagFile = open(diagFileName, 'w') except: exit(1, 'Could not open file %s\n' % diagFileName) try: fpErrorFile = open(errorFileName, 'w') except: exit(1, 'Could not open file %s\n' % errorFileName) try: fpNewAlleleRptFile = open(newAlleleRptFileName, 'w') except: exit(1, 'Could not open file %s\n' % newAlleleRptFileName) try: fpInputFile = open(inputFileName, 'r') except: exit(1, 'Could not open file %s\n' % inputFileName) try: fpAlleleFile = open(alleleFileName, 'w') except: exit(1, 'Could not open file %s\n' % alleleFileName) try: fpMutationFile = open(mutationFileName, 'w') except: exit(1, 'Could not open file %s\n' % mutationFileName) try: fpRefFile = open(refFileName, 'w') except: exit(1, 'Could not open file %s\n' % refFileName) try: fpAccFile = open(accFileName, 'w') except: exit(1, 'Could not open file %s\n' % accFileName) try: fpNoteFile = open(noteFileName, 'w') except: exit(1, 'Could not open file %s\n' % noteFileName) try: fpAnnotFile = open(annotFileName, 'w') except: exit(1, 'Could not open file %s\n' % annotFileName) # Log all SQL db.set_sqlLogFunction(db.sqlLogAll) fpDiagFile.write('Start Date/Time: %s\n' % (mgi_utils.date())) fpDiagFile.write('Server: %s\n' % (db.get_sqlServer())) fpDiagFile.write('Database: %s\n' % (db.get_sqlDatabase())) fpErrorFile.write('Start Date/Time: %s\n\n' % (mgi_utils.date())) 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 inputFile, diagFile, errorFile, errorFileName, diagFileName global refFileName, refFile global mgiTypeKey global refAssocKey, createdByKey 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' refFileName = tail + '.MGI_Reference_Assoc.bcp' 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: refFile = open(refFileName, 'w') except: exit(1, 'Could not open file %s\n' % refFileName) # 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('Object Type: %s\n' % (mgiType)) diagFile.write('Input File: %s\n' % (inputFileName)) errorFile.write('Start Date/Time: %s\n\n' % (mgi_utils.date())) mgiTypeKey = loadlib.verifyMGIType(mgiType, 0, errorFile) createdByKey = loadlib.verifyUser(createdBy, 0, errorFile)
def bcpFiles(): ''' # requires: # # effects: # BCPs the data into the database # # returns: # nothing # ''' db.commit() strainFile.flush() markerFile.flush() accFile.flush() annotFile.flush() noteFile.flush() bcpCommand = os.environ['PG_DBUTILS'] + '/bin/bcpin.csh' currentDir = os.getcwd() bcp1 = '%s %s %s %s %s %s "|" "\\n" mgd' % \ (bcpCommand, db.get_sqlServer(), db.get_sqlDatabase(), 'PRB_Strain', currentDir, strainFileName) bcp2 = '%s %s %s %s %s %s "|" "\\n" mgd' % \ (bcpCommand, db.get_sqlServer(), db.get_sqlDatabase(), 'PRB_Strain_Marker', currentDir, markerFileName) bcp3 = '%s %s %s %s %s %s "|" "\\n" mgd' % \ (bcpCommand, db.get_sqlServer(), db.get_sqlDatabase(), 'ACC_Accession', currentDir, accFileName) bcp4 = '%s %s %s %s %s %s "|" "\\n" mgd' % \ (bcpCommand, db.get_sqlServer(), db.get_sqlDatabase(), 'VOC_Annot', currentDir, annotFileName) bcp5 = '%s %s %s %s %s %s "|" "\\n" mgd' % \ (bcpCommand, db.get_sqlServer(), db.get_sqlDatabase(), 'MGI_Note', currentDir, noteFileName) diagFile.write('%s\n' % bcp1) diagFile.write('%s\n' % bcp2) diagFile.write('%s\n' % bcp3) diagFile.write('%s\n' % bcp4) diagFile.write('%s\n' % bcp5) os.system(bcp1) os.system(bcp2) os.system(bcp3) os.system(bcp4) os.system(bcp5) # update the AccessionMax value db.sql('select * from ACC_setMax (%d)' % (lineNum), None) db.commit() # update prb_strain_seq auto-sequence db.sql(''' select setval('prb_strain_seq', (select max(_Strain_key) from PRB_Strain)) ''', None) db.commit() # update prb_strain_marker_seq auto-sequence db.sql(''' select setval('prb_strain_marker_seq', (select max(_StrainMarker_key) from PRB_Strain_Marker)) ''', None) db.commit() # update voc_annot_seq auto-sequence db.sql(''' select setval('voc_annot_seq', (select max(_Annot_key) from VOC_Annot)) ''', None) db.commit() # update mgi_note_seq auto-sequence db.sql(''' select setval('mgi_note_seq', (select max(_Note_key) from MGI_Note)) ''', None) db.commit()
def loadTempTable (): global annot, updatedBy print 'Create a bcp file from the input file' sys.stdout.flush() # # Read each record from the input file, perform validation checks and # write them to a bcp file. # line = fpInput.readline() updatedBy = re.split(TAB, line[:-1])[6] count = 1 while line: tokens = re.split(TAB, line[:-1]) termID = tokens[0] mgiID = tokens[1] jNum = tokens[2] evidCode = tokens[3] inferFrom = tokens[4] qual = tokens[5] if qual == None: qual = '' editor = tokens[6] date = tokens[7] if date == None: date = '' notes = tokens[8] if notes == None: notes = '' ldb = '' # not used for mcvload termIDExists = len(re.findall('[a-zA-Z0-9]',termID)) mgiIDExists = len(re.findall('[a-zA-Z0-9]',mgiID)) jNumExists = len(re.findall('[a-zA-Z0-9]',jNum)) evidCodeExists = len(re.findall('[a-zA-Z0-9]',evidCode)) inferFromExists = len(re.findall('[a-zA-Z0-9]',inferFrom)) qualExists = len(re.findall('[a-zA-Z0-9]',qual)) editorExists = len(re.findall('[a-zA-Z0-9]',editor)) dateExists = len(re.findall('[a-zA-Z0-9]',date)) # # Special Case # If there is only an mgiID this is ok, it means # curator intends to delete all annotations # if termIDExists == 0 and jNumExists == 0 and \ evidCodeExists == 0 and inferFromExists == 0 \ and editorExists == 0 and mgiIDExists > 0: # check the MGI ID for format if re.match('MGI:[0-9]+',mgiID) == None: print 'Invalid MGI ID (line ' + str(count) + ')' fpBCP.close() closeFiles() sys.exit(1) # write out to the bcp file: fpBCP.write(termID + TAB + mgiID + TAB + jNum + TAB + evidCode + \ TAB + editor + NL) # add to the annotation dictionary so it gets written to the # annotation file annotList = [termID, mgiID, jNum, evidCode, inferFrom, qual, \ editor, date, notes, ldb] if not annot.has_key(mgiID): annot[mgiID] = [] annot[mgiID].append(annotList) # get next input line and continue line = fpInput.readline() continue # There must be a term ID in proper format # SO:nnnnnnn or MCV:nnnnnnn if termIDExists == 0: print 'Missing Term ID (line ' + str(count) + ')' fpBCP.close() closeFiles() sys.exit(1) else: if re.match('MCV:[0-9]+',termID) == None and \ re.match('SO:[0-9]+',termID) == None: print 'Invalid Term ID (line ' + str(count) + ')' + ' ' + \ termID fpBCP.close() closeFiles() sys.exit(1) # # There must be an MGI ID in proper format (MGI:nnnnnnn). # if mgiIDExists == 0: print 'Missing MGI ID (line ' + str(count) + ')' fpBCP.close() closeFiles() sys.exit(1) else: if re.match('MGI:[0-9]+',mgiID) == None: print 'Invalid MGI ID (line ' + str(count) + ')' fpBCP.close() closeFiles() sys.exit(1) # # There must be an J Number in proper format (J:nnnnnnn). # if jNumExists == 0: print 'Missing J Number (line ' + str(count) + ')' fpBCP.close() closeFiles() sys.exit(1) else: if re.match('J:[0-9]+',jNum) == None: print 'Invalid J Number (line ' + str(count) + ')' fpBCP.close() closeFiles() sys.exit(1) # # There must be an evidence code # if evidCodeExists == 0: print 'Missing Evidence Code(line ' + str(count) + ')' fpBCP.close() closeFiles() sys.exit(1) # # There must be an editor login # if editorExists == 0: print 'Missing Editor login (line ' + str(count) + ')' fpBCP.close() closeFiles() sys.exit(1) fpBCP.write(termID + TAB + mgiID + TAB + jNum + TAB + evidCode + \ TAB + editor + NL) # # Maintain a dictionary of the MGI IDs that are in the input file. # The key for each entry is the MGI ID and the value is a list of # the annotation attributes for that the MGI ID. # if mgiID != '': annotList = [termID, mgiID, jNum, evidCode, inferFrom, \ qual, editor, date, notes, ldb] if not annot.has_key(mgiID): annot[mgiID] = [] annot[mgiID].append(annotList) line = fpInput.readline() count += 1 # # Close the bcp file. # fpBCP.close() # # Load the input data into the temp table. # print 'Load the input data into the temp table: ' + tempTable sys.stdout.flush() bcpCmd = '%s %s %s %s "/" %s "\\t" "\\n" mgd' % \ (BCP_COMMAND, db.get_sqlServer(), db.get_sqlDatabase(),tempTable, bcpFile) rc = os.system(bcpCmd) if rc <> 0: closeFiles() sys.exit(1)
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, diagFile, errorFile, errorFileName, diagFileName global transTypeFile, transFile global transTypeFileName, transFileName db.useOneConnection(1) db.set_sqlUser(user) db.set_sqlPasswordFromFile(passwordFileName) # the default output file names are bases on 'inputFileName' head, fileName = os.path.split(inputFileName) # rename 'head' head = outputFileDir fdate = mgi_utils.date('%m%d%Y') # current date diagFileName = head + '/' + fileName + '.' + fdate + '.diagnostics' print diagFileName errorFileName = head + '/' + fileName + '.' + fdate + '.error' print errorFileName transTypeFileName = head + '/' + fileName + '.' + fdate + '.MGI_TranslationType.bcp' print transTypeFileName transFileName = head + '/' + fileName + '.' + fdate + '.MGI_Translation.bcp' print transFileName 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: transTypeFile = open(transTypeFileName, 'w') except: exit(1, 'Could not open file %s\n' % transTypeFileName) try: transFile = open(transFileName, 'w') except: exit(1, 'Could not open file %s\n' % transFileName) # 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()))
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 diagFileName, errorFileName, synFileName global inputFile, diagFile, errorFile, synFile global mgiTypeKey, createdByKey, referenceKey db.useOneConnection(1) db.set_sqlUser(user) db.set_sqlPasswordFromFile(passwordFileName) head, tail = os.path.split(inputFileName) diagFileName = logDir + '/' + tail + '.diagnostics' errorFileName = logDir + '/' + tail + '.error' synFileName = 'MGI_Synonym.bcp' print inputFileName print logDir 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: synFile = open(outputDir + '/' + synFileName, 'w') except: exit(1, 'Could not open file %s\n' % synFileName) # 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())) diagFile.write('Object Type: %s\n' % (mgiType)) diagFile.write('Input File: %s\n' % (inputFileName)) errorFile.write('Start Date/Time: %s\n\n' % (mgi_utils.date())) mgiTypeKey = loadlib.verifyMGIType(mgiType, 0, errorFile) createdByKey = loadlib.verifyUser(createdBy, 0, errorFile) # if reference is J:0, then no reference is given if jnum == 'J:0': referenceKey = '' else: referenceKey = loadlib.verifyReference(jnum, 0, errorFile) # exit if we can't resolve mgiType, createdBy or jnum if mgiTypeKey == 0 or \ createdByKey == 0 or \ referenceKey == 0: exit(1) if mode == 'reload': print 'mode is: %s, deleting synonyms' % mode sys.stdout.flush() db.sql('delete from MGI_Synonym ' + \ 'where _MGIType_key = %d ' % (mgiTypeKey) + \ 'and _CreatedBy_key = %d ' % (createdByKey), None)
def init(): global diagFile, errorFile, errorFileName, diagFileName global outAccFile, outPrepFile, outAssayFile, outAssayNoteFile global outGelLaneFile, outGelLaneStFile, outGelRowFile, outGelBandFile global inPrimerFile, inPrepFile, inAssayFile, inGelLaneFile, inGelBandFile db.useOneConnection(1) db.set_sqlUser(user) db.set_sqlPasswordFromFile(passwordFileName) fdate = mgi_utils.date('%m%d%Y') # current date diagFileName = sys.argv[0] + '.' + fdate + '.diagnostics' errorFileName = sys.argv[0] + '.' + 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) # Input Files try: inPrepFile = open(inPrepFileName, 'r') except: exit(1, 'Could not open file %s\n' % inPrepFileName) try: inAssayFile = open(inAssayFileName, 'r') except: exit(1, 'Could not open file %s\n' % inAssayFileName) try: inGelLaneFile = open(inGelLaneFileName, 'r') except: exit(1, 'Could not open file %s\n' % inGelLaneFileName) try: inGelBandFile = open(inGelBandFileName, 'r') except: exit(1, 'Could not open file %s\n' % inGelBandFileName) # Output Files try: outPrepFile = open(outPrepFileName, 'w') except: exit(1, 'Could not open file %s\n' % outPrepFileName) try: outAssayFile = open(outAssayFileName, 'w') except: exit(1, 'Could not open file %s\n' % outAssayFileName) try: outAssayNoteFile = open(outAssayNoteFileName, 'w') except: exit(1, 'Could not open file %s\n' % outAssayNoteFileName) try: outGelLaneFile = open(outGelLaneFileName, 'w') except: exit(1, 'Could not open file %s\n' % outGelLaneFileName) try: outGelLaneStFile = open(outGelLaneStFileName, 'w') except: exit(1, 'Could not open file %s\n' % outGelLaneStFileName) try: outGelRowFile = open(outGelRowFileName, 'w') except: exit(1, 'Could not open file %s\n' % outGelRowFileName) try: outGelBandFile = open(outGelBandFileName, 'w') except: exit(1, 'Could not open file %s\n' % outGelBandFileName) try: outAccFile = open(outAccFileName, 'w') except: exit(1, 'Could not open file %s\n' % outAccFileName) # 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