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')