[Tutor] Creating a database

Tue Apr 1 12:09:02 2003

At Sun, 30 Mar 2003 11:10:44 -0600 Billie wrote:
>Well, I'm off on a project, and want to know if Python will do the trick.

Certainly. I just made something similar to keep track of our
whisky tasting notes. :) http://www.thinkware.se/whisky/whisky.cgi

>I'm wanting to create a web page that lists names, addresses and phone
>numbers, and I want it to be storable.

Since I had access to MySQL at my web site, and know SQL well, I
used that, but there are many options. Relational databases like
MySQL etc are able to handle several simultaneous users, but that
might not be an issue. It depends on your situation.

A solution which is rather neat if the list is fairly small, is
to use pickle, to turn python objects into something you can store
directly in a file. It's explained well in Patrick O'Brien's article
at IBM http://www-106.ibm.com/developerworks/library/l-pypers.html ,
but I'll give you a small example that you can play with.

First, we make an initial pickle of a list.

 >>> f = file('test.pkl', 'w') # Open file for writing
 >>> import cPickle as pickle
 >>> l = []
 >>> help(pickle.dump)
Help on built-in function dump:

dump(...)
     dump(object, file, [binary]) --Write an object in pickle format to the 
given
  file

     If the optional argument, binary, is provided and is true, then the
     pickle will be written in binary format, which is more space and
     computationally efficient.

 >>> pickle.dump(l,f)

Lets use the ASCII format for now. Clarity is more important then
performance most of the time. The file now looks like this:

(lp1
.

Not much, but it's just an empty list... Now, lets make a
program that adds something to the list, and another that
reads it. Naturally, they can be combined if we wish.

Reading seems simple, lets start with that...

# pickleprint.py
import cPickle as pickle

f = file('test.pkl', 'r')
l = pickle.load(f)
for e in l:
     print e

I think it's working. It does seem to print nothing when
I run it, and that's what I expect. Let's add something to
the pickle, and see if I'm right!

# pickleadd.py
import cPickle as pickle
import sys

f = file('test.pkl', 'r')
l = pickle.load(f)
f.close()

l.append(sys.argv[1])

f = file('test.pkl', 'w')
pickle.dump(l, f)

Let's try it out:

H:\python>pickleadd.py "Hello There"

H:\python>pickleprint.py
Hello There

H:\python>pickleadd.py "Add some more?"

H:\python>pickleprint.py
Hello There
Add some more?

H:\python>pickleadd.py "This seems to work"

H:\python>pickleprint.py
Hello There
Add some more?
This seems to work

There! We now have a basic persistence mechanism.
So, what does our file look like?

H:\python>more test.pkl
(lp1
S'Hello There'
aS'Add some more?'
aS'This seems to work'
p2
a.

Not too bad, but it's nice that we don't have to care... :)

In your case, you would probably want to have a list
of tuples, something like this:

[('Al', 'Capone', 'Gangster', 'al@chicago.danger.com'),
  ('Billie', 'Holliday', 'Singer', 'billie@hmv.co.uk')]

If you have some kind of well defined key, you would use
a dictionary instead.

I'd suggest than you put all the functions involved in
pickle in a separate file, i.e. module. Let's call it
pickletab.py. Give it a generic interface, and you will
be able to switch to another storage method (maybe SQL?)
later, without changing more than that file.

(Modularity is a good thing)

With just one simple table, I can imagine an API looking
like this:

import pickletab as db
...
db.add(aTuple)
db.remove(aTuple)
db.list(sortColumn)

This is the minimal version. (Where updating means remove
old version followed by add new version.)

Of course, I couldn't help myself, but wrote such a module.
It just took me 20 minutes or so--it shouldn't be impossible
for a beginner, but tell me if you want to "cheat" ;) There's
a lot lets before you are done...

>  Being new, first I'm sure it's
>possible, second, is it very difficult (I shouldn't think so, but am still
>asking).  I remember making sorted lists in BASIC, but I want this where the
>user clicks and chooses their preference.

For sorting on varying columns, it's most convenient to use
the so-called Decorate-Sort-Undecorate method. I think Id use
a single integer as parameter, with its absolute value indicating
column, and its sign indicating direction. So db.list(2) would
mean "sort on the second column, first to last), and db.list(-3)
would mean "sort on third column, last to first".

But let's leave this for later.

>Am I taking on more than I'm ready for?  I like challenges, but must have a
>bit of reality in check, also.

Think modular. Solve one piece at a time. I think you can
do this. I'd start with something that works on the command
line first, and add web features later.

CGI-scripts are a bit tricky to debug, since they are run
from the webserver with different settings and permissions
than your normal session. (The new cgitb module is very
helpful though...)

-- 
Magnus Lycka, Thinkware AB
Alvans vag 99, SE-907 50 UMEA, SWEDEN
phone: int+46 70 582 80 65, fax: int+46 70 612 80 65
http://www.thinkware.se/  mailto:magnus@thinkware.se