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','Mill man',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,firstna me,surname,phon e])
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
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','Mill man',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,firstna me,surname,phon e])
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