[workshop] generic sql storage in python

[Andy Todd]

Michal Wallace wrote:
> (crossposted to innnercircle and workshop lists)
> 
> I'll be covering this in detail in an upcoming 
> webAppWorkshop lesson, but if anyone's interested
> you might want to check out the storage module:
> 
> http://cvs.sabren.com/sixthdev/cvsweb.cgi/storage/Storage.py?rev=1.1.1.1
> http://cvs.sabren.com/sixthdev/cvsweb.cgi/storage/MySQLStorage.py?rev=1.1.1.1
> 
> Give it a database connection in the constructor,
> it'll let you fetch and save individual records as
> python dictionaries. The only requirement is that
> you have a key named "ID"...
> 
> It's a fairly simple piece of code. Storage is the
> generic case, MySQLStorage is for MySQL. 
> 
> There's also a MockStorage which works like an in-memory 
> database (probably only useful for testing purposes):
> 
> http://cvs.sabren.com/sixthdev/cvsweb.cgi/storage/MockStorage.py?rev=1.1.1.1
> 
> 
> Sincerely,
> 
> Michal J Wallace
> Sabren Enterprises, Inc.
> -------------------------------------
> contact: michal@sabren.com
> hosting: http://www.cornerhost.com/
> my site: http://www.sabren.net/
> --------------------------------------
> 

Got it, tried it, loved it.

Just a couple of points though ;-)

1. There is a bug (I think) in MySQLStorage.py. To make it work I had to 
add an extra line to the _update method (at line 48 in the CVS file), 
making it look like;

     def _update(self, table, **row):

         sql = "UPDATE " + table + " SET"
         for col,val in row.iteritems():
             sql += " " + col + "='" + str(val) + "',"
         sql = sql[:-1]
         sql += " WHERE ID = %d" % row["ID"]

         self.cur.execute(sql)
         return self.fetch(table, row["ID"]

Without the "WHERE" clause it always trys to update every row in the table.

2. A trap for young players, when Michal said 'The only requirement is 
that you have a key named "ID"...' I didn't realise the full 
implications of what he said. I just created a column called 'ID'.

This perplexed me for quite a while (which isn't that hard) until I 
realised that this column HAD to be the primary key *and* had to be an 
AUTO_INCREMENT column, e.g.;

CREATE TABLE test_storage ( ID INTEGER AUTO_INCREMENT, ..., PRIMARY KEY 
(ID) );

Where ... is your other column definitions. Still it got me reading the 
manual to find out what on earth cursor._insert_id returned so thats a 
good thing as far as I'm concerned.

It also took me a couple of passes to get my syntax right, when using 
the 'store' method, you need to pass the data in as a series of 
parameters, e.g.;

 >>> db = MySQLdb.connect( db='test' )
 >>> storage = MySQLStorage( db )
 >>> storage.store('test_storage', COL1='COL1 Value', COL2='Col2 Value')

Don't - as I did - try and pass in a dictionary of column name:value 
pairs because that just confuses everyone.

Regards,
Andy
-- 
----------------------------------------------------------------------
 From the desk of Andrew J Todd esq - http://www.halfcooked.com