Storing IDs in a file vs. SQLite

file_vs_sqlite.py

import os
import sys
import sqlite3
import time
import uuid
from functools import wraps


DB_FILE = 'tester.db'
FILE = 'tester.cache'
NUM_RECORDS = 10_000


def timeit(func):
    @wraps(func)
    def wrapper(*args, **kwargs):
        begin = time.time()
        func(*args, **kwargs)
        elapsed = time.time() - begin
        print(f'Time elapsed: {elapsed}')
    return wrapper


def create_sqlite_data():
    sql = '''
    CREATE TABLE IF NOT EXISTS tester (
        id varchar(100) PRIMARY KEY UNIQUE
    )
    '''
    with sqlite3.connect(DB_FILE) as conn:
        conn.execute(sql)
        for _ in range(NUM_RECORDS):
            uuid4_str = str(uuid.uuid4())
            conn.execute('insert into tester (id) values (?)', (uuid4_str, ))


def create_file_data():
    with open(FILE, 'w') as fp:
        for _ in range(NUM_RECORDS):
            fp.write(f'{str(uuid.uuid4())}\n')


@timeit
def sqlite_test():
    with sqlite3.connect(DB_FILE) as conn:
        res = conn.execute('select id from tester where id = ?', (sys.argv[2], ))
        print(res.fetchall())


@timeit
def file_test():
    with open(FILE, 'r') as fp:
        for line in fp.readlines():
            if line.strip() == sys.argv[2]:
                print(line)
                break


DOC = """
file_vs_sqlite.py [COMMAND]

Usage:
    python file_vs_sqlite.py create
    python file_vs_sqlite.py test search-string
    python file_vs_sqlite.py remove

Description:
    This script shows two approaches for caching ids. One is a simple
    text file, the other is with a SQLite database. Follow the usage
    sequence above to run a test. The generated ids are uuid4 ids.
"""


def main():
    if len(sys.argv) > 1:
        if sys.argv[1] == 'create':
            create_sqlite_data()
            create_file_data()
        elif sys.argv[1] == 'remove':
            os.remove(DB_FILE)
            os.remove(FILE)
        elif sys.argv[1] == 'test':
            print('file test')
            file_test()
            print('sqlite test')
            sqlite_test()
    else:
        print(DOC)


if __name__ == '__main__':
    main()

This is a comparison I did between using a SQLite database and a text file for storing an id that would later have to be looked up. This could be used, for example, be used to store a list of ids that have already been seen before in a local cache file.

Here's how to run it:

python file_vs_sqlite.py create
python file_vs_sqlite.py test some_string_value

And this is the performance statistics I received:

file test
Time elapsed: 0.0023338794708251953
sqlite test
Time elapsed: 0.0007169246673583984

Looks like SQLite narrowly beats out the file method, but not by much.