Simple db using list comprehensions

[Paddy McCarthy]

frank at chagford.com (Frank Millman) wrote in message news:<246a4e07.0404050626.153ff525 at posting.google.com>...
> Hi all
> 
> From time to time there is a request for a simple way of storing and
> accessing data without using a full SQL database.
> 
> I had this requirement recently, and I was pleasantly surprised by how
> much I could achieve with a simple Python list and list
> comprehensions.
> 
> Assume the need to store data consisting of first name, surname and
> phone number. To ensure uniqueness, I use an additional column to
> store a unique id, which can just be a 'next number'.
> 
> To create the table -
> 	table = []
> 
> To initialise it with a couple of rows -
> 	table.append([1,'Frank','Millman',12345])
> 	table.append([2,'John','Smith',54321])

I like the idea but think you should have a definition of something 
like - 
  INDEX,FIRSTNAME,SURNAME,PHONE = range(4)
You should use the names instead of all the 'magic' constants 
below when referring to fields in rows.

Cheers, Paddy.

> 
> To get the last id used -
> 	last_id = max([row[0] for row in table])
> 
> Alternatively, if you know the rows are in id sequence -
> 	last_id = table[-1][0]
> 
> To add a new row -
> 	firstname = 'Fred'
> 	surname = 'Jones'
> 	phone = 23456
> 
> First, ensure first name and surname are unique -
> 	rows = [row for row in table if row[1] == firstname and row[2] ==
> surname]
> 	if len(rows):
> 		errmsg = 'Already exists'
> 
> If ok, add the row -
> 	last_id += 1
> 	table.append([last_id,firstname,surname,phone])
> 
> To select all rows according to some criteria (eg surnames starting
> with 'M') -
> 	rows = [row for row in table if row[2][0] == 'M']
> 
> If you need a copy of the rows -
> 	rows = [row[:] for row in table if row[2][0] == 'M']
> 
> To sort the rows in surname sequence -
> 	rows = [[row[2],row] for row in rows]  # prepend surname to row for
> sorting
> 	rows.sort()
> 	rows = [row[1] for row in rows]  # remove prepended surname
> 
> To select and sort at the same time -
> 	rows = [[row[2],row] for row in table if row[2][0] == 'M']
> 	rows.sort()
> 	rows = [row[1] for row in rows]
> 
> To amend a phone number if you know the first name and surname -
> 	rows = [row for row in table if row[1] == 'Fred'  and row[2] ==
> 'Jones']
>       if not rows:
> 		errmsg = 'not found'
> 	elif len(rows) > 1:
> 		errmsg = 'more than one row found'
> 	else:
> 		rows[0][3] = phone
> 
> To delete a row if you know the first name and surname -
> 	rows = [row for row in table if row[1] == 'Fred'  and row[2] ==
> 'Jones']
>       if not rows:
> 		errmsg = 'not found'
> 	elif len(rows) > 1:
> 		errmsg = 'more than one row found'
> 	else:
> 		pos = [row[0] for row in table].index(rows[0][0])
> 		del table[pos]
> 
> To delete all rows with a phone number of 0 -
> 	ids = [row[0] for row in table]
> 	rows = [row[0] for row in table if row[3] == 0]
> 	for row in rows:
> 		pos = ids.index(row)
> 		del table[pos]
> 		del ids[pos]
> 
> I have not tested all of these, but you get the idea.
> 
> I did not have to save the data, but if you need to, I am sure you can
> pickle it.
> 
> Hope this is of interest.
> 
> Frank Millman