Database question.....

[Jamey Cribbs]

John D. wrote:
> I have a small (fewer than a few thousand records) database with the
> following properties:
> 
> Each record has four fields: A, B, C, D.
> Fields A and B are unique:
>         there is one B for every A and one A for every B.
> Fields C and D are not unique.
> Sometimes I need to use A as the key, other times B is the key:
>         I use A to return B, C, D and also B to return A, C, D.
> The items may all be represented as short ASCII strings.
> 
> Currently I am storing this in a 100KB file and converting this to two
> dictionaries. I am reading and writing the entire file to update single
> records, which is inefficient. I am thinking about using anydbm, but then
> how do I do the two-way dictionary lookup? Would I have to store the whole
> database twice? What if I had ten items, would I have to store it ten
> times? Is this what a relational database allows?
> 
> John
> 
> 
> 
John,

I wrote a Python module, called KirbyBase, that might be a solution to 
your problem.  KirbyBase is a pure-python, text-file based, database 
management system.  In KirbyBase, you could create a table to hold your 
records.  Then to select a record using field A as the key, you would write:

result = db.select('file.tbl',['A'],['key to search for'],['B','C','D'])

This line says, "Search the table contained in file 'file.tbl'.  Find 
all records where field 'A' matches the 'key to search for'.  When you 
find a match, return fields 'B','C', and 'D'.

To use field 'B' as the key, simply change the line to this:

result = db.select('file.tbl',['B'],['key to search for'],['A','C','D'])

Now, if you want to add a new record and you want to make sure that 
fields 'A' and 'B' of the new record are unique, you could do the following:

if db.select('file.tbl',['A'],['key for new record']) > 0:
     print 'Value of A field in new record not unique!'
elif db.select('file.tbl',['B'],['key for new record']) > 0:
     print 'Value of B field in new record not unique!'
else:
     db.insert('file.tbl',['A value','B value','C value','D value']

To update a single record (if you are not changing the value of 'A' or 
'B', then you can do:

db.update('file.tbl',['A'],['key of record to update'],['C','D'],['new 
   value of C','new value of D'])

If this sounds like it might work for you, you can find KirbyBase at:

http://www.netpromi.com/kirbybase.html

Sorry for the long reply.  I got a little carried away.  :)

Jamey Cribbs