Simple db using list comprehensions

[Frank Millman]

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])

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

 

[Paddy McCarthy]

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