[Webware-discuss] DB Connection Organization

[Jay L. <js...@js...>]

Baasad asked, and Tom started to reply, about how to use a DB in 
Webware.  I thought I would post some code on how I'm doing it.  I 
realized that the code I posted just a second ago contained at least one 
hack that might need an explanation.

Here are two files.  The first is the Context package initialization 
code.  This is the file __init__.py, that is located at the top of the 
Context directory.  By Context directory, I mean in my 
Application.config, I have listed this directory as a context and 
assigned it a name to be used in URLs.

This code just adds the MiscUtils directory, which contains the DBPool 
class, as a directory to search for Cans, and then creates a DBPool 
instance as a Can, using MySQLdb as the db module.  It then tells 
application to use the "Content" directory as the source for 
Servlets/PSPs for this context.

The hack here is that I created and initialized a Can in a nonStandard 
manner.  Normally, as Tom pointed out, you would need to store all this 
connection info in each servlet that wanted to use the Can, just in case 
the Can hadn't been set up yet.  What I'm doing here is just doing what 
Page.getCAn would normally do, but I'm doing it only once, by calling 
Application._canFactory.createCan, and the storing the CAn myself in the 
container I want, Application.  I suppose I should make this a public 
function. 
_____________________________________________
#__init__.py
#for the WKNews Context

__version__ = "0.0"

import MySQLdb

import os

def contextInitialize(app,location):
   app.addCanDir('path/to/Webware/MiscUtils')
    app.addCanDir(os.path.join(location,'Cans'))

    #Initialize a MySQLdb connection pool.  See MiscUtils.DBPool.py for 
more info on the DBPool class.
   
can=app._canFactory.createCan("DBPool",MySQLdb,5,host="dellp200.jslove.org",user="myusername",passwd="mypassword",db="WKNews")  

 
   #Set the Can to be stored in Application.
   app.setCan("WKNewsDBPool",can)

   #Tell Application that content should be served from the "Content" 
subdirectory.   
   return {'ContentLocation':os.path.join(location,'Content'),}

_______________________________________________________

Now, here's a page that uses the WKNewsDBPool can to add an article to a 
database.

_________________________________________________________
# SubmitArticle.py

from Page import Page
import MySQLdb  #needed for TimeStampFromTicks function, not for the 
connection
import time

class SubmitArticle(Page):

   def writeBody(self):
       req = self.request()
       res = self.response()
       app = self.application()

       #grab the DBPool instance that was stored as a Can in the Context 
initialization.
       dbpool = app.getCan("WKNewsDBPool")
       conn = dbpool.getConnection()

       stime = MySQLdb.TimestampFromTicks(time.time())
       author = req.field('author')
       subject=req.field('subject')
       teaser=req.field('teaser')
       full=req.field('fulltext')

       cursor = conn.cursor()
       cursor.execute("INSERT into articles 
(author,postdate,subject,teaser,full_article) values(%s,%s,%s,%s,%s)" % 
(author,stime,subject,teaser,full))
       conn.commit()
       dbpool.returnConnection(conn)
      


So, that's how I'm doing it.  Works fine so far.  No connection code 
stuck around in random pages.  I'm sure others are doing different 
things.  We probably aren't ready to standardize on a "correct" way just 
yet.

I'll add an interface to creating Cans this way, if it seems useful.

Jay