Database in memory

[Nicko]

Jim wrote:
> I have an application that will maintain an in-memory database in the
> form of a list of lists.  Does anyone know of a way to search for and
> retreive "records" from such a structure?

The answer very much depends on the manner in which you want to do the
look-up.  If you only need to do exact-match look-up for items with
unique keys (e.g. find the single record where SSN=1234567890) then
using a dictionary is by far the best solution.  It's fast and it's
easy.

If you expect to do exact-match look-up where the keys are not unique
then build a dictionary containing 'set' objects which are the sets of
records which have the given key. This lets you neatly find the
intersection of selections on multiple criteria (e.g. matches =
zipcode_index["94101"] & hometype_index["condo"] ).

If you need to do range matching (e.g. 20000 <= salary < 50000) then
your best bet is to keep a list of the records sorted in the ordering
of the key, do a binary search to find where the lower and upper
bounds lie within the sorted list and then take a slice.  If you also
have some index dictionaries containing sets then you can combine
these two methods with something like 'matches =
set(salary_index[lo_sal:hi_sal]) & zipcode_index["81435"] '

Having said all that, if you think that there is any possibility that
you might ever want to expand the functionality of your program to
require either (a) more complex and flexible searching and/or (b)
putting the database somewhere else, then I would strongly suggest
that you use PySQLite.  SQLite is an efficient in-memory database with
an SQL engine and the Python interface conforms to the DB-API spec, so
you won't need to change your code (much) if you want to move the
database to some MySQL, Oracle, Sybase or DB2 server at a later date.
Furthermore SQLite is included in Python 2.5 as standard.