diff options
| -rw-r--r-- | wqflask/wqflask/correlation/pre_computes.py | 57 |
1 files changed, 56 insertions, 1 deletions
diff --git a/wqflask/wqflask/correlation/pre_computes.py b/wqflask/wqflask/correlation/pre_computes.py index 1c52a0f5..6d5e5f04 100644 --- a/wqflask/wqflask/correlation/pre_computes.py +++ b/wqflask/wqflask/correlation/pre_computes.py @@ -175,7 +175,8 @@ def fetch_text_file(dataset_name, conn, text_dir=TEXTDIR): """fetch textfiles with strain vals if exists""" with conn.cursor() as cursor: - cursor.execute('SELECT Id, FullName FROM ProbeSetFreeze WHERE Name = %s', (dataset_name,)) + cursor.execute( + 'SELECT Id, FullName FROM ProbeSetFreeze WHERE Name = %s', (dataset_name,)) results = cursor.fetchone() if results: try: @@ -204,3 +205,57 @@ def read_text_file(sample_dict, file_path): _posit, sample_vals = __fetch_id_positions__( next(csv_reader)[1:], sample_dict) return (sample_vals, [",".join([line[i] for i in _posit]) for line in csv_reader]) + + +def write_db_to_textfile(db_name, conn, text_dir=TEXTDIR): + + def __generate_file_name__(db_name): + # todo add expiry time and checker + with conn.cursor() as cursor: + cursor.execute( + 'SELECT Id, FullName FROM ProbeSetFreeze WHERE Name = %s', (db_name,)) + results = cursor.fetchone() + if (results): + return f"ProbeSetFreezeId_{results[0]}_{results[1]}" + + def __parse_to_dict__(results): + ids = ["ID"] + data = {} + for (trait, strain, val) in results: + if strain not in ids: + ids.append(strain) + if trait in data: + data[trait].append(val) + else: + data[trait] = [trait, val] + return (data, ids) + + def __write_to_file__(file_path, data, col_names): + with open(file_path, 'w+', encoding='UTF8') as file_handler: + + writer = csv.writer(file_handler) + writer.writerow(col_names) + writer.writerows(data.values()) + with conn.cursor() as cursor: + cursor.execute( + "SELECT ProbeSet.Name,Strain.Name, ProbeSetData.value " + "FROM (ProbeSetData, ProbeSetFreeze, Strain, ProbeSet, " + "ProbeSetXRef) LEFT JOIN ProbeSetSE ON " + "(ProbeSetSE.DataId = ProbeSetData.Id AND " + "ProbeSetSE.StrainId = ProbeSetData.StrainId) " + "LEFT JOIN NStrain ON " + "(NStrain.DataId = ProbeSetData.Id AND " + "NStrain.StrainId = ProbeSetData.StrainId) " + "WHERE ProbeSetXRef.ProbeSetId = ProbeSet.Id " + "AND ProbeSetXRef.ProbeSetFreezeId = ProbeSetFreeze.Id " + "AND ProbeSetFreeze.Name = %s AND " + "ProbeSetXRef.DataId = ProbeSetData.Id " + "AND ProbeSetData.StrainId = Strain.Id " + "ORDER BY Strain.Name", + (db_name,)) + results = cursor.fetchall() + file_name = __generate_file_name__( + db_name) + if (results and file_name): + __write_to_file__(os.path.join(text_dir, file_name), + *__parse_to_dict__(results)) |