Using Web.py's database library for data munging

Thursday, 23 October 2008

The more I use web.py, the more I appreciate its compact libraries. Web.py's database library is very handy in writing scripts that do the manipulation and heavy lifting. Let's cut to the chase and see how to use web.py's database library.

Consider the simple use-case of normalising all the tags for articles stored in articles table to a standard format of lower__case_words (often called a slug). The current tags have a mix of upper case words and whitespace, which does not look very neat (Ah! geek OCD).

import web
db = web.database(dbn=os.environ.get('DATABASE_ENGINE', 'mysql'),
                  db='devdb', user="user", passwd="passwd")


def cleanup_blog_tags():
    tags = db.select('tags')
    ccount = 0
    for t in tags:
        name = t.name
        slug = slugify(name)
        print name, slug
        db.update('tags', where="id=$id", _test=False,
            vars={'id':t.id}, slug=slug, name=name)
    print '%s tags updated' % (ccount, )

def main():
    cleanup_blog_tags()

if __name__ == '__main__':
  main()