CODEWITHSUNDEEP
pythonsqlitedictionarykey-valuekey-value-store
Basj
Basj

Reputation: 46443

Use SQLite as a key:value store

As suggested in comments from Key: value store in Python for possibly 100 GB of data, without client/server and in other questions, SQLite could totally be used as a persistent key:value store.

How would you define a class (or just wrapper functions) such that using a key:value store with SQLite would be as simple as:

kv = Keyvaluestore('/test.db')
kv['hello'] = 'hi'        # set
print(kv['hello'])         # get
print('blah' in kv)        # answer: False because there's no key 'blah' in the store
kv.close()

?

Upvotes: 12

Views: 25757

Answers (4)

Brian Minton
Brian Minton

Reputation: 3777

sqlite3dbm provides this functionality, while keeping the traditional dbm interface. It is a simpler interface than sqlitedict, for when you just need a very basic key-value store. It also includes a convenience class that can store arbitrary objects as the value using python's shelve functionality.

Upvotes: 0

Basj
Basj

Reputation: 46443

Even if there exists modules that do this (see other answer), I tried to write one simple, self-contained version. Here is a class KeyValueStore (key and value are strings) that works like this:

Demo

from sqlitekeyvaluestore import KeyValueStore

kv = KeyValueStore('test.db')  # uses SQLite

print(len(kv))                 # 0 item
kv['hello1'] = 'you1'
kv['hello2'] = 'you2'
kv['hello3'] = 'you3'
print(kv['hello1'])            # you1
print(len(kv))                 # 3 items

del kv['hello1']
print(len(kv))                 # 2 items remaining

print('hello1' in kv)          # False, it has just been deleted!
print('hello3' in kv)          # True

kv['hello3'] = 'newvalue'      # redefine an already present key/value
print(kv['hello3'])            # newvalue

print(kv.keys())               # ['hello2', 'hello3']
print(kv.values())             # ['you2', 'newvalue']
print(kv.items())              # [('hello2', 'you2'), ('hello3', 'newvalue')]

for k in kv:
    print(k, kv[k])

kv.close()                     # important to commit

Code: sqlitekeyvaluestore.py

import sqlite3

class KeyValueStore(dict):
    def __init__(self, filename=None):
        self.conn = sqlite3.connect(filename)
        self.conn.execute("CREATE TABLE IF NOT EXISTS kv (key text unique, value text)")

    def close(self):
        self.conn.commit()
        self.conn.close()

    def __len__(self):
        rows = self.conn.execute('SELECT COUNT(*) FROM kv').fetchone()[0]
        return rows if rows is not None else 0

    def iterkeys(self):
        c = self.conn.cursor()
        for row in c.execute('SELECT key FROM kv'):
            yield row[0]

    def itervalues(self):
        c = self.conn.cursor()
        for row in c.execute('SELECT value FROM kv'):
            yield row[0]

    def iteritems(self):
        c = self.conn.cursor()
        for row in c.execute('SELECT key, value FROM kv'):
            yield row[0], row[1]

    def keys(self):
        return list(self.iterkeys())

    def values(self):
        return list(self.itervalues())

    def items(self):
        return list(self.iteritems())

    def __contains__(self, key):
        return self.conn.execute('SELECT 1 FROM kv WHERE key = ?', (key,)).fetchone() is not None

    def __getitem__(self, key):
        item = self.conn.execute('SELECT value FROM kv WHERE key = ?', (key,)).fetchone()
        if item is None:
            raise KeyError(key)
        return item[0]

    def __setitem__(self, key, value):
        self.conn.execute('REPLACE INTO kv (key, value) VALUES (?,?)', (key, value))

    def __delitem__(self, key):
        if key not in self:
            raise KeyError(key)
        self.conn.execute('DELETE FROM kv WHERE key = ?', (key,))

    def __iter__(self):
        return self.iterkeys()

Upvotes: 25

Arnav Kamath
Arnav Kamath

Reputation: 21

I liked Basj's answer, but I'd also like to add the following function in the KeyValueStore class so that while using the DB, we can commit the data without necessarily closing the db.

class KeyValueStore(dict):
    """Other functions"""
    def commit(self):
        self.conn.commit()

Upvotes: 2

bgse
bgse

Reputation: 8587

There is already sqlitedict which appears to meet all your needs.

From the documentation:

from sqlitedict import SqliteDict
mydict = SqliteDict('./my_db.sqlite', autocommit=True)
mydict['some_key'] = any_picklable_object
print(mydict['some_key'])  # prints the new value
for key, value in mydict.iteritems():
    print(key, value)
print(len(mydict))  # etc... all dict functions work
mydict.close()

Upvotes: 23

Related Questions