def store_kplets_pile(kplets_pile, cdd2id, file2id):
_sql_kplet = """insert ignore into bacteria_3plets (kplet_1, kplet_2, kplet_3) values \n"""
_sql_kplet_file = """insert ignore into bacteria_3plets_win10 (kplet_id, file_id) values \n"""
for (kplets, fname) in kplets_pile:
for kplet in kplets:
kplet = list(kplet)
kplet.sort()
kplet = tuple([int(cdd2id[k]) for k in kplet])
_sql_kplet += """(%d, %d, %d),\n""" % kplet
_sql_kplet_file += (
"""((select id from bacteria_3plets where """ +
"""kplet_1=%d and kplet_2=%d and kplet_3=%d),""" +
"""%d),\n""") % (kplet + (int(file2id[fname]), ))
_sql_kplet = _sql_kplet[:-2]
_sql_kplet += ';'
_sql_kplet_file = _sql_kplet_file[:-2]
_sql_kplet_file += ';'
_db = DbClass()
_db.cmd = _sql_kplet
_db.execute()
_db.commit()
_db.cmd = _sql_kplet_file
_db.execute()
_db.commit()
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(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_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_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 store_kplets_pile(kplets_pile, cdd2id, file2id):
_sql_kplet = """insert ignore into bacteria_3plets (kplet_1, kplet_2, kplet_3) values \n"""
_sql_kplet_file = """insert ignore into bacteria_3plets_win10 (kplet_id, file_id) values \n"""
for (kplets, fname) in kplets_pile:
for kplet in kplets:
kplet = list(kplet)
kplet.sort()
kplet = tuple([int(cdd2id[k]) for k in kplet])
_sql_kplet += """(%d, %d, %d),\n""" % kplet
_sql_kplet_file += ("""((select id from bacteria_3plets where """ +
"""kplet_1=%d and kplet_2=%d and kplet_3=%d),""" +
"""%d),\n""") % (kplet + (int(file2id[fname]),))
_sql_kplet = _sql_kplet[:-2]
_sql_kplet += ';'
_sql_kplet_file = _sql_kplet_file[:-2]
_sql_kplet_file += ';'
_db = DbClass()
_db.cmd = _sql_kplet
_db.execute()
_db.commit()
_db.cmd = _sql_kplet_file
_db.execute()
_db.commit()
def insert_source_duplets(kplets_pile, profile2id, source_id):
sql_insert_kplet = """insert ignore into prok1402_all_adj_duplet (kplet_1, kplet_2, multidomain) values \n"""
sql_insert_kplet_file = """insert into prok1402_all_adj_duplet_source (kplet_id, source_id) values \n"""
for (duplet, fname, multidomain) in kplets_pile:
terms = sorted(list(duplet))
terms = [profile2id[_] for _ in terms]
sql_insert_kplet += """(%s, %s, %d),\n""" % (terms[0], terms[1], multidomain)
sql_insert_kplet_file += """((select id from prok1402_all_adj_duplet where """ + \
"""kplet_1=%s and kplet_2=%s and multidomain=%d), %d),\n""" % \
(terms[0], terms[1], multidomain, source_id)
sql_insert_kplet_file = sql_insert_kplet_file[:-2]
sql_insert_kplet_file += ";"
sql_insert_kplet = sql_insert_kplet[:-2]
sql_insert_kplet += ";"
_db = DbClass()
_db.cmd = sql_insert_kplet
_db.execute()
_db.cmd = sql_insert_kplet_file
_db.execute()
_db.commit()
def insert_duplets(kplets_pile, profile2id, file2id):
sql_insert_kplet = """insert ignore into prok1402_baited_duplet (kplet_1, kplet_2) values \n"""
sql_insert_kplet_file = """insert ignore into prok1402_baited_duplet_file (kplet_id, file_id) values \n"""
for (duplet, fname) in kplets_pile:
terms = sorted(list(duplet))
terms = [profile2id[_] for _ in terms]
fname = os.path.basename(fname)
file_id = file2id[fname]
sql_insert_kplet += """(%s, %s),\n""" % (terms[0], terms[1])
sql_insert_kplet_file += """((select id from prok1402_baited_duplet where """ + \
"""kplet_1=%s and kplet_2=%s), %d),\n""" % (terms[0], terms[1] ,file_id)
sql_insert_kplet_file = sql_insert_kplet_file[:-2]
sql_insert_kplet_file += ";"
sql_insert_kplet = sql_insert_kplet[:-2]
sql_insert_kplet += ";"
_db = DbClass()
_db.cmd = sql_insert_kplet
_db.execute()
_db.cmd = sql_insert_kplet_file
_db.execute()
_db.commit()
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 insert_kplet(profiles):
profile2id = t.map_profile2id(profiles)
_db = DbClass()
sql_cmd = """insert into archea_4plets (kplet_1, kplet_2, kplet_3, kplet_4) values (%d, %d, %d, %d)"""
sql_cmd = sql_cmd % (profile2id[profiles[0]], profile2id[profiles[1]], profile2id[profiles[2]], profile2id[profiles[3]])
_db.cmd = sql_cmd
_db.execute()
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 store_kplets_pile(prefix, kplets_pile, profile2id, file2id):
kplet_table_name = "%s_3plets" % prefix
kplet_table_file_name = "%s_3plets_files" % prefix
_sql_kplet = """insert ignore into %s (kplet_1, kplet_2, kplet_3) values \n""" % kplet_table_name
_sql_kplet_file = """insert ignore into %s (kplet_id, file_id) values \n""" % kplet_table_file_name
for (kplets, fname) in kplets_pile:
for kplet in kplets:
kplet = list(kplet)
kplet.sort()
kplet = tuple([int(profile2id[k]) for k in kplet])
_sql_kplet += """(%d, %d, %d),\n""" % kplet
_sql_kplet_file += (
"""((select id from %s where """ +
"""kplet_1=%d and kplet_2=%d and kplet_3=%d),""" +
"""%d),\n""") % ((kplet_table_name, ) + kplet +
(int(file2id[fname]), ))
_sql_kplet = _sql_kplet[:-2]
_sql_kplet += ';'
_sql_kplet_file = _sql_kplet_file[:-2]
_sql_kplet_file += ';'
_db = DbClass()
_db.cmd = _sql_kplet
_db.execute()
_db.commit()
_db.cmd = _sql_kplet_file
_db.execute()
_db.commit()
def get_report_kplets(limit_to=300, count_filter=1, load_locations=None):
_db = DbClass()
_db.cmd = """SET group_concat_max_len=15000"""
_db.execute()
_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_2plets ap
inner join archea_2plets_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) > %d ) s1
inner join archea_2plets_codes apc on s1.id=apc.id
order by s1.wgt desc
limit 0,%d""" % (count_filter, limit_to)
out_list = []
for row in _db.retrieve():
id = row[0]
kplet_codes = (row[1:3])
if len(set(kplet_codes)) != 2:
continue
count = row[3]
files = row[5].split(',')
tmp_kplet = Kplet(id=id, codes=kplet_codes, count=count, files=files)
out_list.append(tmp_kplet)
if load_locations:
_path = neighborhoods_path()
[kplet.load_locations(_path) for kplet in out_list]
return out_list
def store_kplets(kplets, fname):
_sql_kplet = """insert ignore into bacteria_4plets (kplet_1, kplet_2, kplet_3, kplet_4) values \n"""
_sql_kplet_file = """insert ignore into bacteria_4plets_win10 (kplet_id, file_id) values \n"""
for kplet in kplets:
kplet = list(kplet)
kplet.sort()
kplet = tuple(kplet)
_sql_kplet += (
"""((select id from cdd_profiles where code='%s'),""" +
"""(select id from cdd_profiles where code='%s'),""" +
"""(select id from cdd_profiles where code='%s'),""" +
"""(select id from cdd_profiles where code='%s')),\n""") % kplet
_sql_kplet_file += (
"""((select id from bacteria_4plets where """ +
"""kplet_1=(select id from cdd_profiles where code='%s') """ +
"""and kplet_2=(select id from cdd_profiles where code='%s') """ +
"""and kplet_3=(select id from cdd_profiles where code='%s') """ +
"""and kplet_4=(select id from cdd_profiles where code='%s')),""" +
"""(select id from bacteria_win10_files where name='%s')),\n"""
) % (kplet + (fname, ))
_sql_kplet = _sql_kplet[:-2]
_sql_kplet += ';'
_sql_kplet_file = _sql_kplet_file[:-2]
_sql_kplet_file += ';'
_db = DbClass()
_db.cmd = _sql_kplet
_db.execute()
_db.cmd = _sql_kplet_file
_db.execute()
def get_report_kplets(limit_to=300, count_filter=1, load_locations=None):
_db = DbClass()
_db.cmd = """SET group_concat_max_len=15000"""
_db.execute()
_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_2plets ap
inner join archea_2plets_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) > %d ) s1
inner join archea_2plets_codes apc on s1.id=apc.id
order by s1.wgt desc
limit 0,%d""" % (count_filter, limit_to)
out_list = []
for row in _db.retrieve():
id = row[0]
kplet_codes = (row[1:3])
if len(set(kplet_codes)) != 2:
continue
count = row[3]
files = row[5].split(',')
tmp_kplet = Kplet(id=id, codes=kplet_codes, count=count, files=files)
out_list.append(tmp_kplet)
if load_locations:
_path = neighborhoods_path()
[kplet.load_locations(_path) for kplet in out_list]
return out_list
def store_kplets_pile(prefix, kplets_pile, profile2id, file2id):
kplet_table_name = "%s_4plets" % prefix
kplet_table_file_name = "%s_4plets_files" % prefix
_sql_kplet = """insert ignore into %s (kplet_1, kplet_2, kplet_3, kplet_4) values \n""" % kplet_table_name
_sql_kplet_file = """insert ignore into %s (kplet_id, file_id) values \n""" % kplet_table_file_name
for (kplets, fname) in kplets_pile:
for kplet in kplets:
kplet = list(kplet)
kplet.sort()
kplet = tuple([int(profile2id[k]) for k in kplet])
_sql_kplet += """(%d, %d, %d, %d),\n""" % kplet
_sql_kplet_file += ("""((select id from %s where """ +
"""kplet_1=%d and kplet_2=%d and kplet_3=%d and kplet_4=%d),""" +
"""%d),\n""") % ((kplet_table_name,) + kplet + (int(file2id[fname]),))
_sql_kplet = _sql_kplet[:-2]
_sql_kplet += ';'
_sql_kplet_file = _sql_kplet_file[:-2]
_sql_kplet_file += ';'
_db = DbClass()
_db.cmd = _sql_kplet
_db.execute()
_db.commit()
_db.cmd = _sql_kplet_file
_db.execute()
_db.commit()
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:
(_cnt, _kplet_id, _file_id) = row
assert _cnt > 1
_db.cmd = """delete from %s where kplet_id=%d and file_id=%d""" % (
table_name, _kplet_id, _file_id)
_db.execute()
_db.commit()
_db.cmd = """insert into %s(kplet_id, file_id) values (%d, %d)""" % (
table_name, _kplet_id, _file_id)
_db.execute()
_db.commit()
total_cnt += 1
if total_cnt % 10000 == 0:
print total_cnt
print total_cnt
print "Finished:", table_name
print
print
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:
(_cnt, _kplet_id, _file_id) = row
assert _cnt > 1
_db.cmd = """delete from %s where kplet_id=%d and file_id=%d""" % (table_name, _kplet_id, _file_id)
_db.execute()
_db.commit()
_db.cmd = """insert into %s(kplet_id, file_id) values (%d, %d)""" % (table_name, _kplet_id, _file_id)
_db.execute()
_db.commit()
total_cnt += 1
if total_cnt % 10000 ==0:
print total_cnt
print total_cnt
print "Finished:", table_name
print
print