def read_oligolist(filename, start_OLI_ID): """Import the data from a .tsv file into the database Keyword Arguments: filename -- string, filepath to importfile. only .tsv extension! start_OLI_ID -- string, the whole OLI ID of the row to start importing from""" data = open(filename, 'r') start = False for line in data: line = line.split('\t') # Start from the OLI ID from input if line[0] == start_OLI_ID: start = True if start == True: if "OLI" in line[0]: print line[0] print line print len(line) # OLIGO oligodict = {} # Retrieve employee_ID's for creator and modifier creator = TLQ.search_in_single_attribute('employee', 'emp_name', line[8])[0][0] modifier = TLQ.search_in_single_attribute('employee', 'emp_name', line[9])[0][0] # Create new dates entry_date = convertdate(line[6]) mod_date = convertdate(line[7]) oligodict[cfg.db_tables_views['oligo'][0]] = line[0] # OLI ID oligodict[cfg.db_tables_views['oligo'][1]] = line[11] # Oli name # newdict[cfg.db_tables_views['oligo'][2]] = line[0] # Oli Type oligodict[cfg.db_tables_views['oligo'][3]] = line[12] # Seq oligodict[cfg.db_tables_views['oligo'][4]] = line[15] # description oligodict[cfg.db_tables_views['oligo'][5]] = entry_date # Entry Date oligodict[cfg.db_tables_views['oligo'][6]] = creator # Creator oligodict[cfg.db_tables_views['oligo'][7]] = mod_date # Update Date oligodict[cfg.db_tables_views['oligo'][8]] = modifier # Modifier oligodict[cfg.db_tables_views['oligo'][9]] = line[23] # label5 oligodict[cfg.db_tables_views['oligo'][10]] = line[24] # label3 oligodict[cfg.db_tables_views['oligo'][11]] = line[25] # labelM1 oligodict[cfg.db_tables_views['oligo'][12]] = line[28] # LabelM1Pos # newdict[cfg.db_tables_views['oligo'][13]] = line[0] # pathogen # newdict[cfg.db_tables_views['oligo'][14]] = line[0] # target # newdict[cfg.db_tables_views['oligo'][15]] = line[0] # notes # Insert into Oligo try: TUQ.insert_row('oligo', oligodict) except MySQLdb.Error,e: raise ValueError(e[0], e[1])
def check_labels_duplicated(seq, fiveprime='', threeprime='', M1='', M1pos=''): """ Checks whether the imported labels of a sequence are equal to the already existing labels Keyword arguments: seq -- the sequence that we want to import into database fiveprime -- the label at 5' of sequence that we want to import into db threeprime -- the label at 3' of sequence that we want to import into db M1 -- the internal label of sequence that we want to import into db M1pos -- the position of internal label of sequence that we want to import into db Returns: duplicated -- boolean, True when the labels are duplicated, false when unique """ sql = """SELECT sequence, oligo_ID, label5prime, label3prime, labelM1, labelM1position FROM %s.oligo WHERE sequence = "%s" """ % (cfg.mysql['database'], seq) all_tuples = TLQ.execute_select_queries(sql) for one_tuple in all_tuples: sequence, oli_ID, labelfive, labelthree, labelM1, labelM1pos = one_tuple if (fiveprime == labelfive and threeprime == labelthree and M1pos == labelM1pos): duplicated = True return duplicated, oli_ID else: duplicated = False return duplicated, oli_ID
def refresh(self, sql, table_str, attributes, sortattribute, sortmethod): """Destroy the window in order to build a new one Keyword Arguments: sql -- string, SQL query statement including an ORDER BY attributes -- list, list of all the attributes of the current table sortattribute -- string, an attribute of the current table sortmethod -- string, either Ascending or Descending""" if self.frame is not None: self.frame.destroy() # build new one self.frame = TLQ.build_table_window(sql, table_str, attributes, sortattribute, sortmethod)
def get_max_ID(table): """ Retrieves the maximum ID from a table as a string from the SQL database Keyword Arguments: table -- string, the name of the table that information need to be taken from """ # when a typo occurs in table naming table = table.lower() # makes a choice between which table is selected sql = """SELECT MAX(%s) FROM %s.`%s` """ % (cfg.db_tables_views[table][0], cfg.mysql['database'], table) # the sql query retuns a tuple, we only want to take the number max_ID = TLQ.execute_select_queries(sql)[0][0] return max_ID
def check_sequence_duplicated(seq, fiveprime='', threeprime='', M1='', M1pos=''): # fix to get information from import_oli_dict """checks whether an oligo sequence plus its labels is unique" Keyword arguments: seq -- the sequence that we want to import into database fiveprime -- the label at 5' of sequence that we want to import into db threeprime -- the label at 3' of sequence that we want to import into db M1 -- the internal label of sequence that we want to import into db M1pos -- the position of internal label of sequence that we want to import into db Returns: duplicated -- A boolean, False when oligo sequence is unique and True when duplicated oligoID -- a string, the oligoID of duplicated seq, empty when seq is unique """ # from import_oli_dict the info is obtained # and read into seq, fiveprime, threeprime and M1, M1pos # if sequence == sequence in db # raise some frame # choose whether commit and it will get a new batchnumber but not a new olinumber # or choose to abort sql = """SELECT sequence, oligo_ID FROM %s.oligo WHERE sequence = "%s" """ % (cfg.mysql['database'], seq) seq_tuples = TLQ.execute_select_queries(sql) if seq_tuples: # we loop trough all tuples, because sometimes more oli_ID's are # associated to same sequence # not unpacking tuple yet, checking whether something is inside # need to retrieve oligoID for when user wants to re-order oligo # the oligo gets a new batch, but keeps the oligoID # when tuple is filled, means that sequence is inside database # we also need to check whether the labels are the same dupl_label, oligoID = check_labels_duplicated(seq, fiveprime, threeprime, M1, M1pos) if dupl_label == True: duplicated = True else: duplicated = False else: # when tuple is found empty, we can proceed importing the oligo duplicated = False oligoID = "" return duplicated, oligoID
def move_row(pk_ID, source, target): # works """Moves an oligo from the oligo table to the Oligo bin. Keyword Arguments: pk_ID -- string, the primary key of the row to move source -- string, tablename of the table the row is in target -- string, tablename of the table the row has to move to """ db = MySQLdb.connect(cfg.mysql['host'], cfg.mysql['user'], cfg.mysql['password'], cfg.mysql['database']) # open connection cursor = db.cursor() # prepare a cursor object #locate the oligo in the table, retrieve values row = TLQ.search_in_single_attribute(source, cfg.db_tables_views[source][0], pk_ID) if len(row) == 0 or str(row[0][0]) != pk_ID: raise ValueError("Could not find ID") #convert to list, for indexing purposes row = list(row[0]) #initialize attributes and dictionary attributes = cfg.db_tables_views[source] insertdict = {} #create dictionary for function inputs for i in range(len(attributes)): if row[i] == None: row[i] = '' if type(row[i]) == long: row[i] = float(row[i]) insertdict[attributes[i]] = row[i] # Replace source key-attribute with target key-attribute and value del insertdict[cfg.db_tables_views[source][0]] insertdict[cfg.db_tables_views[target][0]] = iop.make_new_ID(target) # get the queries insert_sql = make_insert_row(target, insertdict) delete_sql = make_delete_row(source, {cfg.db_tables_views[source][0]: pk_ID}) try: cursor.execute(insert_sql) cursor.execute(delete_sql) db.commit() except MySQLdb.Error, e: # Rollback in case there is any error print e[0], e[1] db.rollback()
def get_project_ID(project_name): """Returns the project ID of a project_name Keyword arguments: project_name -- string, the name of project as in sql database Returns: project_ID -- string, the ID of project associated to project_name """ sql = """SELECT project_ID FROM `%s`.project WHERE project_name = "%s";""" % (cfg.mysql['database'], project_name) project_tuple = TLQ.execute_select_queries(sql) if project_tuple: project_ID = project_tuple[0][0] return project_ID else: raise ValueError('project is not in db. Please ask admin to import \ new project name and ID first')
def get_supplier_ID(supplier_name): # not sure whether need to use """Returns the supplier ID of a supplier_name Keyword arguments: supplier_name -- string, the name of supplier as in sql database Returns: supplier_ID -- string, the ID of supplier associated to supplier_name """ sql = """SELECT supplier_ID FROM `%s`.supplier WHERE supplier_name = "%s";""" % (cfg.mysql['database'], supplier_name) supplier_tuple = TLQ.execute_select_queries(sql) if supplier_tuple: supplier_ID = supplier_tuple[0][0] return supplier_ID else: raise ValueError('supplier is not in db. Please ask admin to import \ supplier name and ID first')
def empty_bin(): # works """ Empties the Order Bin. """ db = MySQLdb.connect(cfg.mysql['host'], cfg.mysql['user'], cfg.mysql['password'], cfg.mysql['database']) # open connection cursor = db.cursor() # prepare a cursor object pkeys = TLQ.all_pks_table('order_bin') if len(pkeys) == 0: raise ValueError("Table is already empty") # per id make sql and execute deletion for i in range(len(pkeys)): delete_sql = make_delete_row('order_bin', {'bin_ID': pkeys[i][0]}) try: cursor.execute(delete_sql) db.commit() except MySQLdb.Error, e: # Rollback in case there is any error print e[0], e[1] db.rollback()
def get_from_orderqueue(queue_ID_list): """ Yields the rows from order_queue table in a tuple Keyword Arguments: queue_ID_list -- numeric list, a list that contains the queue_ID of the information that we want to import in the db Yields: yields a tuple for every separate queue_ID row in the db """ # open connection db = MySQLdb.connect(cfg.mysql['host'], cfg.mysql['user'], cfg.mysql['password'], cfg.mysql['database']) # prepare a cursor object cursor = db.cursor() # lookup and retrieve values # get a boolean here, that checks which oligos were selected for processing # if process = selected: for queue_ID in queue_ID_list: sql = """SELECT * FROM Pathofinder.order_queue WHERE queue_ID = "%s";""" % ( queue_ID) orderqueue_tuple = TLQ.execute_select_queries(sql)[0] yield orderqueue_tuple
def get_from_db(table): """ Yields the rows that have order status processed from specified table Keyword Arguments: table: string, the table where oligos need to be taken from """ # open connection db = MySQLdb.connect(cfg.mysql['host'], cfg.mysql['user'], cfg.mysql['password'], cfg.mysql['database']) # prepare a cursor object cursor = db.cursor() # lookup and retrieve values # get a boolean here, that checks which oligos were selected for processing # if process = selected: sql = """SELECT * FROM %s.%s WHERE order_status = "processed";""" % (cfg.mysql['database'], table) db_rows_tuple = TLQ.execute_select_queries(sql) if db_rows_tuple: for db_row in db_rows_tuple: yield db_row else: raise ValueError("No oligo's found with order_status 'processed'")
def search_button_go(self, table_str, search_input, sortattribute, sortmethod, window): sql, attributes = TLQ.search(table_str, search_input, sortattribute, sortmethod) window.destroy() self.refresh(sql, table_str, attributes, sortattribute, sortmethod)
def process_to_db(self, queue_ID_list): """ Processes the information from order_queue table to the db Keyword Arguments: queue_ID_list -- numeric list, a list that contains the queue_ID of the information that we want to import in the db """ import_oli_dict = {} import_batch_dict = {} #import_supplier_dict = {} #import_project_dict = {} import_projoli_dict = {} import_order_dict = {} # check whether supplier names that are imported are all the same # this is for correctness of ORDNO if supplierlist_check(queue_ID_list) == True: self.message.set('suppliers are the same, starting process') for orderqueue_tuple in get_from_orderqueue(queue_ID_list): supplier_ID = orderqueue_tuple[19] # order needs to be imported outside the for-loop, # only for every process once # for Order table # deze moet echter pas gecreerd worden na het processen van de oligos # make new order number order_number = make_new_ID("order") import_order_dict["order_number"] = order_number #supplier_ID is taken from Supplier table import_order_dict["supplier_ID"] = supplier_ID #order_date is entered when processed ord_date = get_date_stamp() import_order_dict["order_date"] = ord_date # creator needs to be imported from the log-in # import_order_dict["employee_ID"] = emp_loggedin TUQ.insert_row("Order", import_order_dict) for orderqueue_tuple in get_from_orderqueue(queue_ID_list): queue_ID, oli_name, oli_type, oli_seq, descr, entry_date, \ crea, label5, label3, labelm, labelpos, path_name, target, \ notes, syn_lev, pur_met, orderst, \ proj_ID, proj_name, supp_ID, supp_name = orderqueue_tuple # if sequence == sequence in db, raise error/frame sequence_duplicated = check_sequence_duplicated( oli_seq, label5, label3, labelm, labelpos) # make all dictionaries first # oligo table dictionary import_oli_dict["oligo_name"] = oli_name import_oli_dict["oligo_type"] = oli_type import_oli_dict["sequence"] = oli_seq import_oli_dict["description"] = descr import_oli_dict["entry_date"] = entry_date import_oli_dict["label5prime"] = label5 import_oli_dict["label3prime"] = label3 import_oli_dict["labelM1"] = labelm import_oli_dict["labelM1position"] = labelpos import_oli_dict["pathogen_name"] = path_name import_oli_dict["target"] = target import_oli_dict["notes"] = notes # creator needs to be imported from the log-in username = TLQ.search_in_single_attribute( 'employee', 'emp_name', self.controller.shared_data["username"].get()) username = username[0][0] import_oli_dict["creator"] = username # when an update is done also needs to be imported still, not here # batch table dictionary syn_lev = int(syn_lev) import_batch_dict["synthesis_level_ordered"] = syn_lev import_batch_dict["purification_method"] = pur_met import_batch_dict["order_number"] = order_number import_batch_dict["order_status"] = "processed" #import2order = False if sequence_duplicated[0] == True: # when sequence is duplicated ask user whether sure to import # into database, give new batchno but same olino as sequence GUI.ProcessPopup(tk.Tk(), sequence_duplicated, import_batch_dict, order_number, import_projoli_dict, queue_ID) #import_anyway = raw_input("The sequence (with labels) is duplicated, \ # import anyway? The seq will get a new \ # batchnumber. y/n: ") #print self.confirm().get() ## MELANIE, ik heb dit hele blok uitgezet, omdat het nu in GUI-windows staat. Werkt zoals het volgens mij zou moeten. ## maar de batch komt dan in status "processed" te staan, klopt dat? ## Als je hier nu wat aan wilt veranderen, is het denk ik het handigst om in de GUI-windows te werken ## if self.confirm() == "y": ## # do not make new oligono ## # get oligo_ID from check_sequence_duplicated function ## # at 2nd position in returned list the oliID is contained ## print "only importing new batch..." ## oli_ID = sequence_duplicated[1] ## import_batch_dict["oligo_ID"] = oli_ID ## batch_no = make_new_ID('Batch') ## import_batch_dict["batch_number"] = batch_no ## import_batch_dict["order_number"] = order_number ## # also import project belonging to oligo ## # may be a new project belonging to the oli ## import_projoli_dict["oligo_ID"] = oli_ID ## #proj_ID = get_project_ID(proj_name) ## #import_projoli_dict["project_ID"] = proj_ID ## ## TUQ.insert_row("Batch", import_batch_dict) ## #TUQ.insert_row("Project_Oligo", import_projoli_dict) ## ## TUQ.delete_row("Order_queue", {"queue_ID": queue_ID}) ## ## else: ## print "not importing..." ## TUQ.delete_row("Order_queue", {"queue_ID": queue_ID}) if sequence_duplicated[0] == False: # Make new oli_ID and add to dictionary oli_ID = make_new_ID('Oligo') import_oli_dict["oligo_ID"] = oli_ID # Make new batchno and add to dict # also add oli_ID to dict import_batch_dict["oligo_ID"] = oli_ID batch_no = make_new_ID('Batch') import_batch_dict["batch_number"] = batch_no import_batch_dict["order_number"] = order_number # import proj_ID and oli_ID import_projoli_dict["oligo_ID"] = oli_ID proj_ID = get_project_ID(proj_name) import_projoli_dict["project_ID"] = proj_ID # order needs to be imported outside the for-loop, # only for every process once import2order = True # when an update is done also needs to be imported still, not here # for every row insert the information into the specified tables TUQ.insert_row("Oligo", import_oli_dict) TUQ.insert_row("Batch", import_batch_dict) TUQ.insert_row("Project_Oligo", import_projoli_dict) TUQ.delete_row("Order_queue", {"queue_ID": queue_ID}) else: self.message.set('two or more suppliers provided, not able to process')