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
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)