Persistent key value store for Python using SQLite3

This is a module to implement a key value store in Python using SQLite3. Originally written to allow sharing of configuration among multiple threads that are already sharing a SQLite3 database, I have generalized for most use cases. The interface in Python is as similar to dictionary objects as possible.

Differences from a normal python dictionary:

• a file and path must be provided on creation

• it's not possible to create a kvstore literally as you would a dict

The following dictionary methods are not implemented:

• fromkeys(seq[, value])

• viewitems()

• viewkeys()

• viewvalues()

I would be grateful for any review, particularly considering the following areas:

• Corner cases I've missed in my testing

• Anything that could be more pythonic

• Anything that could be more efficient (note that it is intended that the whole dictionary not be loaded into memory where possible, as large dictionaries / low memory systems are a potential target)

```
import sqlite3
import json
import UserDict

try:
from cPickle import Pickler, Unpickler
except ImportError:
from pickle import Pickler, Unpickler

try:
from cStringIO import StringIO
except ImportError:
from StringIO import StringIO

class sqlite3keyvaluestore(UserDict.DictMixin):

def __init__(self, filenameandpath, tablename='ApplicationConfiguration', serialisationtype='json'):
"""filenameandpath - the name and path to the file
tablename - the name of the table to create (or use) in the database (this allows the file to be used for other purposes with control over potential name clashes
serialisationtype - either 'json' or 'pickle', defaults to 'json' - the method used to serialise the value in the database"""
self.con = sqlite3.connect(filenameandpath)
self.cur = self.con.cursor()
self.filenameandpath = filenameandpath
self.serialisationtype = serialisationtype
self.tablename = tablename
query = "SELECT name FROM

First, the obligatory pointer to PEP 8. In particular, your class names and some docstrings do not follow it. Variable names and line lengths arguably as well.

Next, a couple of specific cases where you could make the code clearer or more efficient:

def has_key(self, key):
    query = "SELECT k FROM %s WHERE k = ?" % self.tablename
    self.cur.execute(query, (key,))
    return bool(self.cur.fetchone())

You should define the lower level method __contains__ instead. DictMixin will use that to define has_key and that way you are sure to get efficient in testing. Speaking of which:

def __delitem__(self, key):
    if not self.has_key(key):
        raise KeyError
    else:
        query = "DELETE FROM %s WHERE k = ?" % self.tablename
        self.cur.execute(query,(key,))
        self.con.commit()

I think more Pythonic would be:

def __delitem__(self, key):
    if key not in self:
        raise KeyError
    query = "DELETE FROM %s WHERE k = ?" % self.tablename
    self.cur.execute(query,(key,))
    self.con.commit()

However, beware. AFAICT, the item could have been deleted by another process in between checking whether it exists and deleting it. You should probably just delete the item and raise an error if that fails.

class databaseiterator:
    def __init__(self, filenameandpath, tablename, serialisationtype, query, resultfunction):
        """query should be in the form "SELECT  LIMIT 1 OFFSET "
        """
        self.con = sqlite3.connect(filenameandpath)
        self.cur = self.con.cursor()
        self.tablename = tablename
        self.query = query
        self.offset = 0
        self.resultfunction = resultfunction

You are saving some data that you never use. self.con, self.tablename never used after this. The whole parameter tablename is unused if you remove the assignment. serialisationtype already is. If you remove serializationtype from here, you can also remove self.serialisationtype from sqlite3keyvaluestore.__init__.

StackExchange Code Review Q#54960, answer score: 3