Insert into SQLite3 database
First, some comments on your code:


An sqlite3 connection can be used as a context manager. This ensures that the statement is committed if it succeeds and rolled back in case of an exception. Unfortunately it does not also close the connection afterwards

with sqlite3.connect("test.sqlite") as connection, open("test_file.map") as f:
    connection.execute("""
        CREATE TABLE IF NOT EXISTS map (
            accession TEXT PRIMARY KEY,
            accession_version TEXT,
            taxid TEXT, gi TEXT
        )""")

    next(f)  # ignore header
    connection.executemany("INSERT INTO map VALUES (?, ?, ?, ?)", read_large_file(f))
connection.close()

You should separate your functions from the code calling it. The general layout for Python code is to first define your classes, then your functions and finally have a main block that is protected by a if __name__ == "__main__": guard to allow importing from the script without executing all the code.
open automatically opens a file in read-mode, if not specified otherwise.




That being said, if you have a billion lines, basically any approach is probably going to be slow. Here is an alternate approach using dask. It may or may not be faster, you will have to test it. The usage is very similar to pandas, except that the computations are only performed once committed to with the call to compute().

First, to install dask:

pip install dask[dataframe] --upgrade


Then for the actual usecase you mention, finding a specific gi in the table:

from dask import dataframe

df = dataframe.read_csv("test_file.map", sep="\t")
df[df.gi == 7184].compute()
#   accession accession.version  taxid    gi
# 0    V00184          V00184.1  44689  7184


In the call to dataframe.read_csv you can set it to read the file in blocks if needed, e.g. in 25MB chunks:

df = dataframe.read_csv("test_file.map", sep="\t", blocksize=25e6)