合併多個小sqlite資料庫方法應該有很多種,這邊使用python3來處理,好處是可以減少所使用的sql語法。
這邊主要使用的SQL語法為:
ATTACH DATABASE "Data_sub.db" AS DB; INSERT INTO mergedintable select * from db.bemergedtable DETACH DATABASE DB;
藉由迴圈來把所有資料庫合併在一起。
在python3中的代碼則使用到基本的sqlite3.connect, cursor, execute, commit, close來完成。(都是..最基本的pyrhon3 sqlite3 db api)
def createdatabase(choose_HLA_type_super): """ create the database and table with the HLA type """ database_name = "{database}_netMHC.db".format(database=re.sub('-','_',choose_HLA_type_super)) # create the table in the database conn = sqlite3.connect(database_name) c = conn.cursor() # Create different table according to peptides length # Create table c.execute('''CREATE TABLE Neoantigen_14 (pos integer, HLA text, peptide text, Core text, Offset integer, I_pos integer, I_len integer, D_pose integer, D_len integer, iCore text, Identity text, llog50k real, Affinity real, Rank REAL, BindLevel integer) ''') c.execute('''CREATE TABLE Neoantigen_13 (pos integer, HLA text, peptide text, Core text, Offset integer, I_pos integer, I_len integer, D_pose integer, D_len integer, iCore text, Identity text, llog50k real, Affinity real, Rank REAL, BindLevel integer) ''') c.execute('''CREATE TABLE Neoantigen_12 (pos integer, HLA text, peptide text, Core text, Offset integer, I_pos integer, I_len integer, D_pose integer, D_len integer, iCore text, Identity text, llog50k real, Affinity real, Rank REAL, BindLevel integer) ''') c.execute('''CREATE TABLE Neoantigen_11 (pos integer, HLA text, peptide text, Core text, Offset integer, I_pos integer, I_len integer, D_pose integer, D_len integer, iCore text, Identity text, llog50k real, Affinity real, Rank REAL, BindLevel integer) ''') c.execute('''CREATE TABLE Neoantigen_10 (pos integer, HLA text, peptide text, Core text, Offset integer, I_pos integer, I_len integer, D_pose integer, D_len integer, iCore text, Identity text, llog50k real, Affinity real, Rank REAL, BindLevel integer) ''') c.execute('''CREATE TABLE Neoantigen_9 (pos integer, HLA text, peptide text, Core text, Offset integer, I_pos integer, I_len integer, D_pose integer, D_len integer, iCore text, Identity text, llog50k real, Affinity real, Rank REAL, BindLevel integer) ''') c.execute('''CREATE TABLE Neoantigen_8 (pos integer, HLA text, peptide text, Core text, Offset integer, I_pos integer, I_len integer, D_pose integer, D_len integer, iCore text, Identity text, llog50k real, Affinity real, Rank REAL, BindLevel integer) ''') # Save (commit) the changes conn.commit() # We can also close the connection if we are done with it. conn.close() #print(''.join(['Database ', database_name,' created!'])) HLA_type_super=["HLA-A0101","HLA-A0201","HLA-A0301","HLA-A2402","HLA-A2601","HLA-B0702","HLA-B0801","HLA-B2705","HLA-B3901","HLA-B4001","HLA-B5801","HLA-B1501"] HLA_type_all_ABC=["HLA-A0101","HLA-A0201","HLA-A0202","HLA-A0203","HLA-A0205","HLA-A0206","HLA-A0207","HLA-A0211","HLA-A0212","HLA-A0216","HLA-A0217","HLA-A0219","HLA-A0250","HLA-A0301","HLA-A1101","HLA-A2301","HLA-A2402","HLA-A2403","HLA-A2501","HLA-A2601","HLA-A2602","HLA-A2603","HLA-A2902","HLA-A3001","HLA-A3002","HLA-A3101","HLA-A3201","HLA-A3207","HLA-A3215","HLA-A3301","HLA-A6601","HLA-A6801","HLA-A6802","HLA-A6823","HLA-A6901","HLA-A8001","HLA-B4403","HLA-B4501","HLA-B4601","HLA-B4801","HLA-B5101","HLA-B5301","HLA-B5401","HLA-B5701","HLA-B5801","HLA-B8301","HLA-B0702","HLA-B0801","HLA-B0802","HLA-B0803","HLA-B1402","HLA-B1501","HLA-B1502","HLA-B1503","HLA-B1509","HLA-B1517","HLA-B1801","HLA-B2705","HLA-B2720","HLA-B3501","HLA-B3503","HLA-B3801","HLA-B3901","HLA-B4001","HLA-B4002","HLA-B4013","HLA-B4201","HLA-B4402","HLA-B5802","HLA-B7301","HLA-C0303","HLA-C0401","HLA-C0501","HLA-C0602","HLA-C0701","HLA-C0702","HLA-C0802","HLA-C1203","HLA-C1402","HLA-C1502","HLA-E0101"] result_title = ['pos', 'HLA', 'peptide', 'core', 'offset','I_pos','I_len', 'D_pos','D_len', 'iCore', 'Identity', 'log50k', 'Affinity','Rank','BindLevel'] createdatabase(''.join([HLA_type_super[0], "_merged"])) conn = sqlite3.connect("HLA_A0101_merged_netMHC.db") c = conn.cursor() for i in range(50): string = ''.join(["attach ", "database","'HLA_A0101_",str(i),"_netMHC.db'", "AS 'toMerge'"]) c.execute(string) c.execute("insert into Neoantigen_14 select * from toMerge.Neoantigen_14") c.execute("insert into Neoantigen_13 select * from toMerge.Neoantigen_13") c.execute("insert into Neoantigen_12 select * from toMerge.Neoantigen_12") c.execute("insert into Neoantigen_11 select * from toMerge.Neoantigen_11") c.execute("insert into Neoantigen_10 select * from toMerge.Neoantigen_10") c.execute("insert into Neoantigen_9 select * from toMerge.Neoantigen_9") c.execute("insert into Neoantigen_8 select * from toMerge.Neoantigen_8") c.execute("detach database toMerge") conn.commit() conn.close()