def to_db(self): """Send the organism's parameters to be stored in the database's table for this species. If this organism is already in the database, reset its OrgID to match. """ db = sqlite3.connect(self.dbpath) cursor = db.cursor() try: # attempt to add in an entry, with OrgID 'Tester' Testerdict = {'OrgID': ['TEXT', 'Tester']} Testerdict.update(self.dbdict) command, vals = sqlsc.INSERTlst(self.host.name, Testerdict) cursor.execute(command, vals) db.commit() # if this goes through, generate an OrgID cursor.execute('SELECT rowid FROM ' + self.host.name + ' WHERE' + \ ' OrgID = ?', ('Tester',)) entryno = cursor.fetchone()[0] self.host.OrgID = (self.host.name + \ str(entryno) + date.today().strftime("_%d%m%y")) # Now replace the 'Tester OrgID in the database' cursor.execute('UPDATE '+self.host.name + ' SET OrgID = ? ' + \ 'WHERE OrgID = ?', (self.host.OrgID, 'Tester')) db.commit() except sqlite3.IntegrityError as e: if 'column OrgID' in str(e): logger.warning( "\n OrgID already in use, Tester hsan't been replaced somewhere \n" ) raise e elif 'columns' or 'UNIQUE' in str(e): logger.info('\n It looks like an organism with these parameters ' + \ 'already exists in the database \n') reset, vals = sqlsc.ANDlst(self.dbdict) cursor.execute( sqlsc.SELECTpreamble(self.host.name, 'OrgID') + reset, vals) ID = cursor.fetchone() logger.info("\n Setting this Organism's ID to " + ID[0] + "\n") self.host.OrgID = ID[0] else: raise e finally: db.close() # Now the organism database needs to be updated so we can find these # details in ecosystem self.update_org_db()
def add_to_Composition(self): """Add the composition of the reactor to the Composition table if it is not already saved. Return the CompID.""" compdictstr = self.get_comp_activities() db = sqlite3.connect(self.dbpath) cursor = db.cursor() try: # attempt to add in an entry, with CompID 'Tester' Testerdict = {'CompID' : ['TEXT', 'Tester']} compdict = self.Composition_db_sqlparams() Testerdict.update(compdict) command, vals = sqlsc.INSERTlst('Composition', Testerdict) cursor.execute(command, vals) db.commit() # if this goes through, generate a CompID cursor.execute('SELECT rowid FROM Composition WHERE' + \ ' CompID = ?', ('Tester',)) entryno = cursor.fetchone()[0] CompID = str(entryno) + date.today().strftime("_%d%m%y") # Now replace the 'Tester OrgID in the database' cursor.execute('UPDATE Composition SET CompID = ? ' + \ 'WHERE CompID = ?', (CompID, 'Tester')) db.commit() except sqlite3.IntegrityError as e: if 'column CompID' in str(e): logger.warning("CompID already in use, Tester hasn't ' + \ ' been replaced somewhere") raise e elif 'columns' or 'UNIQUE' in str(e): logger.info('It looks like a composition with these ' + \ 'parameters already exists in the database \n') reset, vals = sqlsc.ANDlst(compdict) cursor.execute(sqlsc.SELECTpreamble('Composition', 'CompID') + reset, vals) ID = cursor.fetchone() logger.info("Setting this Composition's ID to " + ID[0]) CompID = ID[0] else: raise e finally: db.close() return CompID
def add_to_Reactions(self, rxxn): """Add the reaction passed rxxn to the Reactions table if it is not already saved. Return the ReactID of the reaction in the table.""" db = sqlite3.connect(self.dbpath) cursor = db.cursor() try: # attempt to add in an entry, with ReactID 'Tester' Testerdict = {'ReactID' : ['TEXT', 'Tester']} rxxndict = rdb_helper.reaction_db_sqlparams(rxxn) Testerdict.update(rxxndict) command, vals = sqlsc.INSERTlst('Reactions', Testerdict) cursor.execute(command, vals) db.commit() # if this goes through, generate a ReactID cursor.execute('SELECT rowid FROM Reactions WHERE' + \ ' ReactID = ?', ('Tester',)) entryno = cursor.fetchone()[0] ReactID = str(entryno) + date.today().strftime("_%d%m%y") # Now replace the 'Tester OrgID in the database' cursor.execute('UPDATE Reactions SET ReactID = ? ' + \ 'WHERE ReactID = ?', (ReactID, 'Tester')) db.commit() except sqlite3.IntegrityError as e: if 'column ReactID' in str(e): logger.warning("ReactID already in use, Tester hsan't been replaced somewhere") raise e elif 'columns' or 'UNIQUE' in str(e): logger.info('It looks like a reaction with these ' + \ 'parameters already exists in the database \n') reset, vals = sqlsc.ANDlst(rxxndict) cursor.execute(sqlsc.SELECTpreamble('Reactions', 'ReactID') + reset, vals) ID = cursor.fetchone() logger.info("Setting this Reaction's ID to " + ID[0]) ReactID = ID[0] else: raise e finally: db.close() return ReactID
def workoutID(self, dbdict=None): """Find this reactor's ID from the database, and if there's no entry create one. """ self.update_ReactIDs() self.update_CompID() self.get_db_sqlparams(dbdict=dbdict) db = sqlite3.connect(self.dbpath) cursor = db.cursor() # search in the database for the organism's identifiers try: logger.info('Trying to find the reactor with name: ' + \ self.host.name + ' in the database') command, vals = sqlsc.ANDlst(self.dbdict) cursor.execute(sqlsc.SELECTpreamble(self.host.name, 'LocID')+command, vals) op = cursor.fetchone() if op is not None: logger.info("Success! Its LocID is: "+op[0]) self.host.LocID = op[0] else: raise Exception('No reactor found') except Exception as e: if 'no such table' in str(e): logger.info('Undefined table for this reactor, creating one.') self.createtable() logger.info("Table created. Adding reactor type: "+self.host.name+".") self.to_db() elif str(e) == 'No reactor found': logger.info('This is a unique reactor! Adding it to the ' + \ self.host.name + ' table.') self.to_db() else: logger.info("Other error encountered: "+str(e)) logger.info('Assuming this is a new reactor of type: ' + \ self.host.name + '. Adding to database.') logger.info('First make sure the table exists') self.createtable() logger.info('Now, adding an entry.') self.to_db() finally: db.close()
def workoutID(self, dbdict=None): """Find this organisms' ID from the database, and if there's no entry create one. """ db = sqlite3.connect(self.dbpath) cursor = db.cursor() self.get_db_sqlparams(dbdict=None) # search in the database for the organism's identifiers try: logger.debug('Trying to find your organism with name: ' + \ self.host.name + ' in the database') command, vals = sqlsc.ANDlst(self.dbdict) cursor.execute( sqlsc.SELECTpreamble(self.host.name, 'OrgID') + command, vals) op = cursor.fetchone() if op is not None: logger.debug("Success! Its OrgID is: " + op[0]) self.host.OrgID = op[0] else: raise Exception('No organism found') except Exception as e: if 'no such table' in str(e): logger.info('Undefined table for this organism, creating one.') self.createtable() logger.info("Table created. Adding species: " + self.host.name + ".") self.to_db() elif str(e) == 'No organism found': logger.info('This is a unique organism! Adding it to the ' + \ self.host.name + ' table.') self.to_db() else: logger.info("Other error encountered: " + str(e)) logger.info('Assuming this is a new organism of species: ' + \ self.host.name + '. Adding to database.') self.to_db() finally: db.close() self.host.output.refreshparams()