Wednesday, December 09, 2009

pywebsite.sqlitepickle. sqlite VS pickle

The sqlite and pickle modules that come with python are quite useful. So lets mix them together and see what comes out.

SQLITE Vs PICKLE

pywebsite.sqlitepickle is a little module I just made which combines sqlite and pickle for persistence. Useful since it works with python3, and both pickle and sqlite are included with pythons (including pypy).

Import the module.
>>> from pywebsite import sqlitepickle

An in memory db.
>>> db = sqlitepickle.SQLPickle()
>>> db.save('key', 'value')
>>> db.get('key')
'value'

Can also save to a file. So we first get a temp file name.
>>> import tempfile
>>> f = tempfile.NamedTemporaryFile()
>>> fname = f.name

>>> db = sqlitepickle.SQLPickle(fname)
>>> db.save('key', 'value')
>>> db.get('key')
'value'

>>> db.close()


The issues with this are that sqlite does not like sharing connections from multiple threads. To get around that I just create a new connection in each thread, or do db stuff from one thread. Also using pickles in persistent data can be a security issue.

Speed? ok speed. Pickle isn't the fastest, and neither is sqlite... but they are both kind of ok.

http://pypi.python.org/pypi/pywebsite
https://launchpad.net/pywebsite
http://www.pywebsite.org/

8 comments:

Antoine said...

Hi,

Quite an interesting module ...

But such a shame to document this misshandling of the tempfile module to get a tempname, knowing how hard it is to educate programmer into secure temporary file handling.

bw
+++
tonio

illume said...

Hi Antoine,

updated the tempfile handling in the example. What do you think now?

cu,

brentp said...

and you can use both together with pod Pickled-Object-Database

illume said...

Thanks for the link Brent.

This sqlitepickle module is only a key-value store really. Where the keys can be strings/ints, and the values any pickled object. It's only about 100 lines of code, but doesn't do all that much.

Whereas pickled-object-database is more like an object database with many more features - similar to ZODB and Durus.

Another one to look at is shove:
http://pypi.python.org/pypi/shove/

I considered using shove, but it had too much stuff I didn't need, and does seem to have python3.1 support.

rute said...

FYI i made some similar to sqlitepickle based on Raymond Hettinger's recipe http://code.activestate.com/recipes/576638/ (Py3k only)

My adaptation is on: http://dpaste.com/hold/122439/

This is used as a persistence on disk cache together with a LRU cache: http://dpaste.com/hold/131313/

I have not compared the speed of this to any of the other offerings available.

Runar

Antoine said...

Hi illume,

Many thanks to take care of this even it it was not the point of your post. It now looks good.

For people who might miss the point of my previous ranting, one can have look at "temporary file security" on google.

cu
+++
tonio

Ben said...

What's the advantage of this over the builtin shelve module?

illume said...

Hi Ben,

only that it uses sqlite rather than a file. You get ACID, and concurrency that you don't get with shelve. Also you can store more objects before it starts to slow down.

Shelve can also use a dbm backend to get around these problems. If you have pybsddb shelve has some other niceties. However bsddb doesn't come with python by default anymore.

I guess the main reason I didn't choose shelve is that I've had trouble with shelve in the past.

There is a proposed sqlite backend for shelve on the python bug tracker(and linked to here in the comments). Hopefully the python 3.2 may include it.


cheers,