Code from https://remusao.github.io/posts/python-dbm-module.html modified to add primary keys

README.md

Code from https://remusao.github.io/posts/python-dbm-module.html modified to add primary keys.

The change I made to the original script was to add a primary key, like this:

CREATE TABLE store(key TEXT PRIMARY KEY, value TEXT)

Here are the results before I made that change:

sqlite (this is with executemany)
Took 0.032 seconds, 3.19541 microseconds / record
dict_open
Took 0.002 seconds, 0.20261 microseconds / record
dbm_open
Took 0.043 seconds, 4.26550 microseconds / record
sqlite3_mem_open
Took 2.240 seconds, 224.02620 microseconds / record
sqlite3_file_open
Took 7.119 seconds, 711.87410 microseconds / record

And here's what I got after adding the primary keys:

sqlite
Took 0.040 seconds, 3.97618 microseconds / record
dict_open
Took 0.002 seconds, 0.19641 microseconds / record
dbm_open
Took 0.042 seconds, 4.18961 microseconds / record
sqlite3_mem_open
Took 0.116 seconds, 11.58359 microseconds / record
sqlite3_file_open
Took 5.571 seconds, 557.13968 microseconds / record

bench.py

import dbm
import sqlite3
import os

from random import random
import time


MAX_RECORDS = 10000
WRITES = [str(i) for i in range(MAX_RECORDS)]
RANDOM_READS = [
    str(int(random() * MAX_RECORDS))
    for _ in range(MAX_RECORDS)
]

class SqliteDict:
    def __init__(self, con):
        self.con = con
        self.con.execute('CREATE TABLE store(key TEXT PRIMARY KEY, value TEXT)')

    def __setitem__(self, key, value):
        with self.con:
            self.con.execute('INSERT INTO store VALUES (?,?)', (key, value))
    def __getitem__(self, key):
        with self.con:
            return self.con.execute('SELECT value FROM store WHERE key=?', (key,))

def sqlite3_mem_open():
    with sqlite3.connect(':memory:') as con:
        yield SqliteDict(con)

def sqlite3_file_open():
    with sqlite3.connect('file.sql') as con:
        yield SqliteDict(con)
    try:
        os.remove('file.sql')
    except os.FileNoteFoundError:
        print('Could not find', 'file.sql')

def dbm_open():
    name = 'dbm.db'
    with dbm.open('dbm', 'c') as db:
        yield db
    try:
        os.remove(name)
    except os.FileNoteFoundError:
        print('Could not find', name)

def dict_open():
    yield {}


def bench(db_gen):
    for db in db_gen():
        t = time.time()

        # create some records
        for i in WRITES:
            db[i] = 'x'

        # do a some random reads
        for i in RANDOM_READS:
            x = db[i]

        time_taken = time.time() - t
        print("Took %0.3f seconds, %0.5f microseconds / record" % (time_taken, (time_taken * 1000000) / MAX_RECORDS))

def bench_sqlite():
    with sqlite3.connect('db.sql') as con:
        with con:
            con.execute('CREATE TABLE store(key TEXT PRIMARY KEY, value TEXT)')
        t = time.time()
        with con:
            con.executemany('INSERT INTO store VALUES (?,?)', (
                (k, 'x') for k in WRITES
            ))
        with con:
            con.execute('SELECT * FROM store WHERE key in ({0})'.format(', '.join('?' for _ in RANDOM_READS)), RANDOM_READS).fetchall()
        time_taken = time.time() - t
        print("Took %0.3f seconds, %0.5f microseconds / record" % (time_taken, (time_taken * 1000000) / MAX_RECORDS))


if __name__ == "__main__":
    print("sqlite")
    bench_sqlite()
    print("dict_open")
    bench(dict_open)
    print("dbm_open")
    bench(dbm_open)
    print("sqlite3_mem_open")
    bench(sqlite3_mem_open)
    print("sqlite3_file_open")
    bench(sqlite3_file_open)