Pickle SQL dict for python

picklesqldict.py

import sqlite3
import zlib
import pickle


class PickleSQLDict:

    def __init__(self, filename):
        self._filename = filename
        self.__db = None

    @property
    def _db(self):
        if self.__db is not None:
            return self.__db
        self.__db = sqlite3.connect(self._filename, isolation_level=None, timeout=3600)
        self.__db.execute('create table if not exists d (key blob primary key, value blob)')
        self.__db.execute('PRAGMA synchronous = off')
        return self.__db

    def __setitem__(self, key, value):
        k = pickle.dumps(key)
        v = zlib.compress(pickle.dumps(value))
        self._db.execute('insert into d (key,value) values ($0,$1) on conflict (key) do update set value=excluded.value;', (k, v,))

    def __getitem__(self, key):
        k = pickle.dumps(key)
        for row in self._db.execute('select value from d where key = $1 limit 1', (k,)):
            return pickle.loads(zlib.decompress(row[0]))
        raise KeyError(key)

    def __delitem__(self, key):
        k = pickle.dumps(key)
        self._db.execute('delete from d where key = $1', (k,))

    def __contains__(self, key):
        k = pickle.dumps(key)
        for row in self._db.execute('select exists(select 1 from d where key = $1)', (k,)):
            return row[0]

    def keys(self):
        for row in self._db.execute('select key from d'):
            yield pickle.loads(row[0])

    def __getstate__(self):
        return (self._filename,)

    def __setstate__(self, t):
        self._filename = t[0]
        self.__db = None