def get_report_kplets(id2cdd, limit_to=500, load_locations=None): _db = DbClass() _db.cmd = """SET group_concat_max_len=1500000""" _db.execute() _db.cmd = """select ap.* ,count(*) as cnt, sum(w.weight) as wgt, group_concat(awf.name) as an from bacteria_3plets ap inner join bacteria_3plets_win10 apw on ap.id = apw.kplet_id inner join bacteria_win10_files awf on apw.file_id = awf.id inner join sources s on awf.source_id=s.id inner join weights w on w.genome_id=s.genome_id group by ap.id having count(distinct s.genome_id)>1 order by wgt desc limit 0, %d""" % limit_to out_list = [] for row in _db.retrieve(): id = row[0] kplet_codes = ([id2cdd[int(_id)] for _id in row[1:4]]) if len(set(kplet_codes)) != 3: continue count = row[4] weight = row[5] files = row[6].split(',') tmp_kplet = Kplet(id=id, codes=kplet_codes, count=count, files=files) out_list.append(tmp_kplet) _path = neighborhoods_path() if load_locations: [kplet.load_locations(_path) for kplet in out_list] return out_list
def org2src_src2files_map(files): _db = DbClass() _db.cmd = """select awf.name, s.name, g.name from bacteria_win10_files awf inner join sources s on awf.source_id=s.id inner join genomes g on s.genome_id = g.id where awf.name in ('%s')""" % "','".join(files) _org2src = {} _src2files = {} for row in _db.retrieve(): parts = row [_file, _src, _org] = parts if _org in _org2src: _org2src[_org].update([_src]) else: _org2src[_org] = set([_src]) if _src in _src2files: _src2files[_src].update([_file]) else: _src2files[_src] = set([_file]) return _org2src, _src2files
def get_report_kplets(id2cdd, limit_to=500): _db = DbClass() _db.cmd = """SET group_concat_max_len=1500000""" _db.execute() _db.cmd = """select cp.*, count(distinct cwf.id) as cnt, group_concat(cwf.name) as files from crispr_2plets cp inner join crispr_2plets_wgs cpw on cp.id = cpw.kplet_id inner join crispr_wgs_files cwf on cpw.file_id = cwf.id group by cp.id having count(distinct cwf.id)>1 order by cnt desc limit 0, %d""" % limit_to out_list = [] for row in _db.retrieve(): id = row[0] kplet_codes = ([id2cdd[int(_id)] for _id in row[1:3]]) if len(set(kplet_codes)) != 2: continue count = row[3] files = row[4].split(',') tmp_kplet = Kplet(id=id, codes=kplet_codes, count=count, files=files) out_list.append(tmp_kplet) return out_list
def get_report_kplets(id2cdd, limit_to=500, load_locations=None): _db = DbClass() _db.cmd = """SET group_concat_max_len=1500000""" _db.execute() _db.cmd = """select ap.* ,count(*) as cnt, sum(w.weight) as wgt, group_concat(awf.name) as an from bacteria_3plets ap inner join bacteria_3plets_win10 apw on ap.id = apw.kplet_id inner join bacteria_win10_files awf on apw.file_id = awf.id inner join sources s on awf.source_id=s.id inner join weights w on w.genome_id=s.genome_id group by ap.id having count(distinct s.genome_id)>1 order by wgt desc limit 0, %d""" % limit_to out_list = [] for row in _db.retrieve(): id = row[0] kplet_codes = ([id2cdd[int(_id)] for _id in row[1:4]]) if len(set(kplet_codes)) != 3: continue count = row[4] weight = row[5] files = row[6].split(',') tmp_kplet = Kplet(id=id, codes=kplet_codes, count=count, files=files) out_list.append(tmp_kplet) _path = neighborhoods_path() if load_locations: [kplet.load_locations(_path) for kplet in out_list] return out_list
def get_report_kplets(id2cdd, limit_to=500): _db = DbClass() _db.cmd = """SET group_concat_max_len=15000000""" _db.execute() _db.cmd = """select cp.*, count(distinct cwf.id) as cnt, group_concat(cwf.name) as files from crispr_5plets cp inner join crispr_5plets_wgs cpw on cp.id = cpw.kplet_id inner join crispr_wgs_files cwf on cpw.file_id = cwf.id group by cp.id having count(distinct cwf.id)>1 order by cnt desc limit 0, %d""" % limit_to out_list = [] for row in _db.retrieve(): id = row[0] kplet_codes = ([id2cdd[int(_id)] for _id in row[1:6]]) if len(set(kplet_codes)) != 5: continue count = row[6] files = row[7].split(',') tmp_kplet = Kplet(id=id, codes=kplet_codes, count=count, files=files) out_list.append(tmp_kplet) return out_list
def get_report_kplets(limit_to=300, load_locations=None): sql_cmd = """select apc.*, s1.cnt, s1.wgt, s1.an from ( select ap.id ,count(*) as cnt, sum(w.weight) as wgt, group_concat(awf.name) as an from archea_4plets ap inner join archea_4plets_win10 apw on ap.id = apw.kplet_id inner join archea_win10_files awf on apw.file_id = awf.id inner join sources s on awf.source_id=s.id inner join weights w on w.genome_id=s.genome_id group by ap.id having count(distinct s.genome_id)>1 ) s1 inner join archea_4plets_codes apc on s1.id=apc.id order by s1.wgt desc limit 0,%d""" % limit_to _db = DbClass() _db.cmd = sql_cmd out_list = [] for row in _db.retrieve(): id = row[0] kplet_codes = (row[1:5]) if len(set(kplet_codes)) != 4: continue count = row[5] files = row[7].split(',') tmp_kplet = Kplet(id=id, codes=kplet_codes, count=count, files=files) out_list.append(tmp_kplet) _path = neighborhoods_path() if load_locations: [kplet.load_locations(_path) for kplet in out_list] return out_list
def get_report_kplets(limit_to=300, load_locations=None): _db = DbClass() _db.cmd = """select apc.*, s1.cnt, s1.wgt, s1.an from ( select ap.id ,count(*) as cnt, sum(w.weight) as wgt, group_concat(awf.name) as an from archea_3plets ap inner join archea_3plets_win10 apw on ap.id = apw.kplet_id inner join archea_win10_files awf on apw.file_id = awf.id inner join sources s on awf.source_id=s.id inner join weights w on w.genome_id=s.genome_id group by ap.id having count(distinct s.genome_id)>1 ) s1 inner join archea_3plets_codes apc on s1.id=apc.id order by s1.wgt desc limit 0,%d""" % limit_to out_list = [] for row in _db.retrieve(): id = row[0] kplet_codes = (row[1:4]) if len(set(kplet_codes)) != 3: continue count = row[4] files = row[6].split(',') tmp_kplet = Kplet(id=id, codes=kplet_codes, count=count, files=files) out_list.append(tmp_kplet) _path = neighborhoods_path() if load_locations: [kplet.load_locations(_path) for kplet in out_list] return out_list
def get_code_kplet(kplet_id): _db = DbClass() _db.cmd = """select kplet_1, kplet_2, kplet_3, kplet_4, kplet_5 from bacteria_5plets_codes where id = %s""" % kplet_id return _db.retrieve()[0]
def get_report_kplet(kplet_id, id2cdd, load_locations=None): _db = DbClass() _db.cmd = """SET group_concat_max_len=1500000;""" _db.execute() _db.cmd = """select ap.* ,count(*) as cnt, sum(w.weight) as wgt, group_concat(awf.name) as an from bacteria_5plets ap inner join bacteria_5plets_win10 apw on ap.id = apw.kplet_id inner join bacteria_win10_files awf on apw.file_id = awf.id inner join sources s on awf.source_id=s.id inner join weights w on w.genome_id=s.genome_id where ap.id=%d group by ap.id""" % kplet_id out_list = [] for row in _db.retrieve(): id = row[0] kplet_codes = [id2cdd[int(_id)] for _id in row[1:6]] if len(set(kplet_codes)) != 5: continue count = row[6] files = row[8].split(',') tmp_kplet = Kplet(id=id, codes=kplet_codes, count=count, files=files) out_list.append(tmp_kplet) _path = neighborhoods_path() if load_locations: [kplet.load_locations(_path) for kplet in out_list] return out_list[0]
def get_code_kplet(kplet_id): _sql_cmd = """SELECT cp1.code, cp2.code, cp3.code, cp4.code FROM PatternQuest.archea_4plets ap inner join cdd_profiles cp1 on cp1.id = ap.kplet_1 inner join cdd_profiles cp2 on cp2.id = ap.kplet_2 inner join cdd_profiles cp3 on cp3.id = ap.kplet_3 inner join cdd_profiles cp4 on cp4.id = ap.kplet_4 where ap.id = %d""" % kplet_id _db = DbClass() _db.cmd = _sql_cmd return _db.retrieve()
def get_code_kplet(kplet_id, id2cdd=None): _db = DbClass() if not id2cdd: _db.cmd = """select cp1.code, cp2.code from bacteria_2plets bp inner join cdd_profiles cp1 on cp1.id = bp.kplet_1 inner join cdd_profiles cp2 on cp2.id = bp.kplet_2 where bp.id = %d""" % kplet_id retval = _db.retrieve()[0] else: _db.cmd = """select kplet_1, kplet_2 from bacteria_2plets where id = %d""" % kplet_id retval = _db.retrieve()[0] retval = set([id2cdd[id] for id in retval]) return retval
def archea_kplet_ids2files(id_list): _sql_cmd = """select distinct awf.name from archea_5plets ap inner join archea_5plets_win10 apw on ap.id = apw.kplet_id inner join archea_win10_files awf on apw.file_id = awf.id where ap.id in (%s)""" % " , ".join(id_list) _db = DbClass() _db.cmd = _sql_cmd return [l[0] for l in _db.retrieve()]
def map_profile2id(profile_list): _db = DbClass() if profile_list == 'all': sql_cmd = """select code, id from cdd_profiles""" else: sql_cmd = """select code, id from cdd_profiles where code in ('%s')""" sql_cmd = sql_cmd % "','".join(profile_list) _db.cmd = sql_cmd return {row[0]: int(row[1]) for row in _db.retrieve()}
def get_multiple_kplets(): _sql_cmd = """select ap.id, count(*) cnt, group_concat(convert(apw.file_id, char(15))) as file_ids from archea_5plets ap inner join archea_5plets_win10 apw on ap.id = apw.kplet_id group by ap.id having count(*)>1 order by cnt desc""" _db = DbClass() _db.cmd = _sql_cmd return _db.retrieve()
def get_multiple_kplets(): _db = DbClass() _db.cmd = "SET group_concat_max_len = 100000;" _db.execute() _db.cmd = """ select ap.id, count(*) cnt, group_concat(convert(apw.file_id, char(15))) as file_ids from bacteria_3plets ap inner join bacteria_3plets_win10 apw on ap.id = apw.kplet_id group by ap.id having count(*)>1 order by cnt desc""" return _db.retrieve()
def get_multiple_kplets(): _db = DbClass() _db.cmd = "SET group_concat_max_len = 100000;" _db.execute() _db.cmd = """ select ap.id, count(*) cnt, group_concat(convert(apw.file_id, char(15))) as file_ids from bacteria_3plets ap inner join bacteria_3plets_win10 apw on ap.id = apw.kplet_id group by ap.id having count(*)>1 order by cnt desc""" return _db.retrieve()
def map_baited_file2id(): _db = DbClass() _db.cmd = """ select name, id from prok1402_baited_files""" rows = _db.retrieve() file2id = {} id2file = {} for row in rows: file2id[row[0]] = row[1] id2file[row[1]] = row[0] return file2id, id2file
def get_kplet_id(profiles): profile2id = t.map_profile2id(profiles) _db = DbClass() sql_cmd = """select id from archea_4plets where kplet_1=%d and kplet_2=%d and kplet_3=%d and kplet_4=%d""" sql_cmd = sql_cmd % (profile2id[profiles[0]], profile2id[profiles[1]], profile2id[profiles[2]], profile2id[profiles[3]]) _db.cmd = sql_cmd rows = _db.retrieve() if rows: return rows[0][0] return None
def file2src_src2org_map(files): _db = DbClass() _db.cmd = """select awf.name, s.name, g.name from bacteria_win10_files awf inner join sources s on awf.source_id=s.id inner join genomes g on s.genome_id = g.id where awf.name in ('%s')""" % "','".join(files) _src2org = {} _file2src = {} for row in _db.retrieve(): parts = row [_file, _src, _org] = parts _src2org[_src] = _org _file2src[_file] = _src return _file2src, _src2org
def get_archaea_kplets(): sql_cmd = """select apc.*, s1.cnt, s1.wgt from ( select ap.id ,count(*) as cnt, sum(w.weight) as wgt from archea_5plets ap inner join archea_5plets_win10 apw on ap.id = apw.kplet_id inner join archea_win10_files awf on apw.file_id = awf.id inner join sources s on awf.source_id=s.id inner join weights w on w.genome_id=s.genome_id group by ap.id ) s1 inner join archea_5plets_codes apc on s1.id=apc.id order by s1.wgt desc""" _db = DbClass() _db.cmd = sql_cmd return _db.retrieve()
def extract_baited_duplet_aggregates(duplet_type="adj"): if duplet_type == "adj": sql_cmd = """select d.id, p1.code as profile_1, p2.code as profile_2, count(*) as loci, sum(w.weight) as loci_weight, group_concat(CONVERT(df.file_id, CHAR(20)) separator ',') as files from prok1402_baited_adj_duplet d inner join prok1402_baited_adj_duplet_file df on d.id = df.kplet_id inner join cdd_profiles p1 on d.kplet_1 = p1.id inner join cdd_profiles p2 on d.kplet_2 = p2.id inner join prok1402_baited_files bf on bf.id = df.file_id inner join sources s on bf.source_id = s.id inner join genomes g on g.id = s.genome_id inner join weights w on w.genome_id = g.id group by d.id order by loci desc;""" else: sql_cmd = """select d.id, p1.code as profile_1, p2.code as profile_2, count(*) as loci, sum(w.weight) as loci_weight, group_concat(CONVERT(df.file_id, CHAR(20)) separator ',') as files from prok1402_baited_duplet d inner join prok1402_baited_duplet_file df on d.id = df.kplet_id inner join cdd_profiles p1 on d.kplet_1 = p1.id inner join cdd_profiles p2 on d.kplet_2 = p2.id inner join prok1402_baited_files bf on bf.id = df.file_id inner join sources s on bf.source_id = s.id inner join genomes g on g.id = s.genome_id inner join weights w on w.genome_id = g.id group by d.id order by loci desc;""" _db = DbClass() _db.cmd = sql_cmd rows = _db.retrieve() return rows
def map_file_name2id(): _db = DbClass() _db.cmd = """ select name, id from bacteria_win10_files""" rows = _db.retrieve() return {row[0]: row[1] for row in rows}
def get_code_kplet(kplet_id): _sql_cmd = """select kplet_1, kplet_2, kplet_3, kplet_4, kplet_5 from archea_5plets_codes where id = %s""" % kplet_id _db = DbClass() _db.cmd = _sql_cmd return _db.retrieve()
from lib.db import DbClass _db = DbClass() # for table_name in ['crispr_4plets_wgs', 'crispr_3plets_wgs', 'crispr_2plets_wgs']: for table_name in ['crispr_5plets_wgs']: print "Starting for table:", table_name print print "Loading the duplicates" _db.cmd = """SELECT count(*) as cnt, kplet_id, file_id FROM PatternQuest.%s group by kplet_id, file_id having count(*)>1 order by cnt desc""" % table_name table_rows = [row for row in _db.retrieve()] print("Number of duplicate entries:", len(table_rows)) fname = "duplicates_%s.txt" % table_name with open(fname, 'w') as f: [f.write("%d\t%d\t%d\n" % (row)) for row in table_rows] print "Duplicates saved in file:", fname continue sys.exit() print("Starting to remove duplicates") total_cnt = 0 chunk_size = 10000 chunks = [ table_rows[i:i + chunk_size] for i in range(0, len(table_rows), chunk_size)
def map_source2id(): _db = DbClass() _db.cmd = """ SELECT id, name FROM PatternQuest.sources; """ rows = _db.retrieve() return {row[1]: row[0] for row in rows}
def map_file_id2name(): _db = DbClass() _db.cmd = """select id, name from bacteria_win10_files""" return {str(l[0]): l[1] for l in _db.retrieve()}
from lib.db import DbClass _db = DbClass() # for table_name in ['crispr_4plets_wgs', 'crispr_3plets_wgs', 'crispr_2plets_wgs']: for table_name in ['crispr_5plets_wgs']: print "Starting for table:", table_name print print "Loading the duplicates" _db.cmd = """SELECT count(*) as cnt, kplet_id, file_id FROM PatternQuest.%s group by kplet_id, file_id having count(*)>1 order by cnt desc""" % table_name table_rows = [row for row in _db.retrieve()] print("Number of duplicate entries:", len(table_rows)) fname = "duplicates_%s.txt" % table_name with open(fname, 'w') as f: [f.write("%d\t%d\t%d\n"%(row)) for row in table_rows] print "Duplicates saved in file:", fname continue sys.exit() print("Starting to remove duplicates") total_cnt = 0 chunk_size = 10000 chunks = [table_rows[i: i+chunk_size] for i in range(0, len(table_rows), chunk_size)] for row in table_rows: