I have a tsv file containing the mapping for two kinds of identifiers.
accession accession.version taxid gi
V00184 V00184.1 44689 7184
V00185 V00185.1 44689 7186
V00187 V00187.1 44689 7190
X07806 X07806.1 7227 8179
Basically, I want to be able to get the taxid
from an accession
number. I though I could put this in a database with a PRIMARY KEY on the accession
field.
This is what I did and It works but I have around 1.5billion lines and it takes a lot of time to do.
import sqlite3
import time
start = time.time()
connection = sqlite3.connect("test.sqlite")
cursor = connection.cursor()
cursor.execute("""
CREATE TABLE IF NOT EXISTS map (
accession TEXT PRIMARY KEY,
accession_version TEXT,
taxid TEXT, gi TEXT
)""")
def read_large_file(f):
"""Generator for the file"""
for l in f:
yield l.strip().split()
with open("test_file.map", "r") as f:
next(f) # ignore header
cursor.executemany("INSERT INTO map VALUES (?, ?, ?, ?)", read_large_file(f))
cursor.close()
connection.commit()
connection.close()
print(time.time() - start)
Do you have any tricks or ideas I could use to speed up the insert ? (I will do very basic SELECT on the data using the accession
primary key)