使用SQLite作为键值存储

Question

正如在Key: value store in Python for possibly 100 GB of data, without client/server和其他问题的评论中建议的那样，SQLite完全可以用作持久的键值存储。

你该如何定义一个类（或仅使用包装函数），以便使用带有SQLite的键值存储就像这样简单：

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

?

- Basj

---

我使用Python创建了一个库，它在SQLite之上公开了一个okvs接口，网址为https://github.com/amirouche/python-okdb。 - amirouche

Answer 1

即使存在可以做到这一点的模块（请参见其他答案），我还是尝试编写了一个简单、自包含版本。以下是一个类KeyValueStore（键和值都是字符串），它的工作方式如下：

演示

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

---

代码：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()

- Basj

---

可以使用SQLite的JSON功能实现这个吗？请参阅：https://www.sqlite.org/json1.html - Halil Ibrahim Özcan

Answer 2

已经有 sqlitedict 这个库可以满足您的所有需求。

从文档中可以看到：

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

- bgse

Answer 3

我喜欢Basj的回答，但我还想在KeyValueStore类中添加以下函数，以便在使用数据库时，我们可以提交数据而不必关闭数据库。

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

- Arnav Kamath

Answer 4

sqlite3dbm提供了这个功能，同时保持了传统的dbm接口。它是一个比sqlitedict更简单的接口，适用于当您只需要一个非常基本的键值存储时。它还包括一个方便的类，可以使用Python的shelve功能将任意对象作为值存储。

- Brian Minton