python + elixir + pyyaml == yay!

And so... I'm playing with a little toy script to ease some pain at work. We've got a bunch of bills in a very large database, the bill data our application reads/modifies is split across a number of tables.

At the moment, the developers manually run SQL scripts to peek at various bits of data. Definitely a huge waste of time.

So, I'm playing with Python + Elixir + PyYAML to get a script that I can just pass in the bill ID and it'll query the 5-6 tables and serialize the bill to something more human readable. If I play my cards right, I'll never have to deal with Toad again.

Elixir works its magic and I can query my database. The autoload didn't work, and there are a couple tables with 50-100+ columns. Serializing it to YAML without repeating myself is now the trick.

I'm sure there's a better way to do this, but here's what I came up with... applied to the Elixir tutorial. Not the most exciting thing ever. But, it's the start of building a better testing framework. Doing the same for the real tables will make it much easier to compare a bill before and after running through our application.

#!/usr/bin/python

# -*- coding: latin-1 -*-



from elixir import *



from yaml import load, dump

try:

    from yaml import CLoader as Loader

    from yaml import CDumper as Dumper

except ImportError:

    from yaml import Loader, Dumper





def _toYamlRep(ent):

    """

    Given an elixir entity, query the entity's members via its __dict__ 

    and return a dict

    """

    ret = {}

    for (k,v) in ent.__dict__.items():

        if k.startswith('_') or k == 'row_type':

            # don't print out the 'hidden' keys

            continue

        if v:

            ret[k] = str(v)

    return ret



class YamlEntity(Entity):

    def toYamlRep(self):

        """

        Wrap the _toYamlRep() dict in another dict, use the class' name as the header.

        """

        return {self.__class__.__name__ : _toYamlRep(self) }



class Movie(YamlEntity):

    title = Field(String(30))

    year = Field(Integer())

    description = Field(Text())

    director = ManyToOne('Director')



    def __repr__(self):

        return '<Movie: "%s" (%d)>'%(self.title,self.year)





class Director(YamlEntity):

    name = Field(String(60))

    movies = OneToMany('Movie')



    def __repr__(self):

        return '<Director: "%s">'%(self.name)





def main():

    metadata.bind = "sqlite://"

    setup_all()

    create_all()



    rscott = Director(name="Ridley Scott")

    glucas = Director(name="George Lucas")

    alien = Movie(title="Alien", year=1979, director=rscott)

    swars = Movie(title="Star Wars", year=1977, director=glucas)

    brunner = Movie(title="Blade Runner", year=1982, director=rscott)

    

    session.flush()



    for m in Movie.query().all():

        print dump(m.toYamlRep(),Dumper=Dumper,default_flow_style=False)



    cleanup_all()



if __name__ == '__main__':

    main()

And, here's the output:

Movie:
  director: '<Director: "George Lucas">'
  director_id: '2'
  id: '3'
  title: Star Wars
  year: '1977'

Movie:
  director: '<Director: "Ridley Scott">'
  director_id: '1'
  id: '4'
  title: Alien
  year: '1979'

Movie:
  director: '<Director: "Ridley Scott">'
  director_id: '1'
  id: '5'
  title: Blade Runner
  year: '1982'