Easier way to create a JSON object from an SQLObject
Question: EDIT -- took the code from below and made it so it can handle ForiegnKeys,
Decimal numbers (although i'm doing a very forced float conversion). It
returns a dict now so it can be recursive.

    
    
    from sqlobject import SQLObject
    from decimal import Decimal
    
    def sqlobject_to_dict(obj):
        json_dict = {}
        cls_name = type(obj)
        for attr in vars(cls_name):
            if isinstance(getattr(cls_name, attr), property):
                attr_value = getattr(obj, attr)
                attr_class = type(attr_value)
                attr_parent = attr_class.__bases__[0]
                if isinstance(getattr(obj, attr), Decimal):
                    json_dict[attr] = float(getattr(obj, attr))
                elif attr_parent == SQLObject:
                    json_dict[attr] = sqlobject_to_dict(getattr(obj, attr))
                else:
                    json_dict[attr] = getattr(obj, attr)
    
        return json_dict
    

EDIT -- changed to add the actual data model -- there are generated values
that need to be accessed and Decimal() columns that need dealing with as well.

So I've seen this: [return SQL table as JSON in
python](http://stackoverflow.com/questions/3286525/return-sql-table-as-json-
in-python) but it's not really what I'm looking for -- that's "brute force" --
you need to know the names of the attributes of the object in order to
generate the JSON response.

What I'd like to do is something like this (the name of the class and it's
attributes are not-important)

    
    
    class BJCPStyle(SQLObject):
        name = UnicodeCol(length=128, default=None)
        beer_type = UnicodeCol(length=5, default=None)
        category = ForeignKey('BJCPCategory')
        subcategory = UnicodeCol(length=1, default=None)
        aroma = UnicodeCol(default=None)
        appearance = UnicodeCol(default=None)
        flavor = UnicodeCol(default=None)
        mouthfeel = UnicodeCol(default=None)
        impression = UnicodeCol(default=None)
        comments = UnicodeCol(default=None)
        examples = UnicodeCol(default=None)
        og_low = SGCol(default=None)
        og_high = SGCol(default=None)
        fg_low = SGCol(default=None)
        fg_high = SGCol(default=None)
        ibu_low = IBUCol(default=None)
        ibu_high = IBUCol(default=None)
        srm_low = SRMCol(default=None)
        srm_high = SRMCol(default=None)
        abv_low = DecimalCol(size=3, precision=1, default=None)
        abv_high = DecimalCol(size=3, precision=1, default=None)
        versions = Versioning()
    
        def _get_combined_category_id(self):
            return "%s%s" % (self.category.category_id, self.subcategory)
    
        def _get_og_range(self):
            low = self._SO_get_og_low()
            high = self._SO_get_og_high()
    
            if low == 0 and high == 0:
                return "varies"
            else:
                return "%.3f - %.3f" % (low, high)
    
        def _get_fg_range(self):
            low = self._SO_get_fg_low()
            high = self._SO_get_fg_high()
    
            if low == 0 and high == 0:
                return "varies"
            else:
                return "%.3f - %.3f" % (low, high)
    
        def _get_srm_range(self):
            low = self._SO_get_srm_low()
            high = self._SO_get_srm_high()
    
            if low == 0 and high == 0:
                return "varies"
            else:
                return "%.1f - %.1f" % (low, high)
    
        def _get_abv_range(self):
            low = self._SO_get_abv_low()
            high = self._SO_get_abv_high()
    
            if low == 0 and high == 0:
                return "varies"
            else:
                return "%.2f%% - %.2f%%" % (low, high)
    
        def _get_ibu_range(self):
            low = self._SO_get_ibu_low()
            high = self._SO_get_ibu_high()
    
            if low == 0 and high == 0:
                return "varies"
            else:
                return "%i - %i" % (low, high)    
    

Is there an easy way, pythonic way to write that magic to_json() function?


Answer: You can use the python [json module](http://docs.python.org/library/json.html)
with the SQLObject [sqlmeta](http://sqlobject.org/SQLObject.html#class-
sqlmeta) class. Like this:

    
    
    def to_json(obj):
        return json.dumps(dict((c, getattr(obj, c)) for c in obj.sqlmeta.columns))
    

When I run this with your class `Foo` I get:

    
    
    >>> print to_json(f)
    {"bar": "test", "lulz": "only for the", "baz": true}
    

**Edit:** if you want to include [magic
attributes](http://sqlobject.org/SQLObject.html#adding-magic-attributes-
properties) in your json string and you don't mind using something of a hack,
you could abuse the fact that the attributes of your object are python
properties. For example, if I add a magic attribute `foo` to your original
sample class:

    
    
    class Foo(SQLObject):
        bar = UnicodeCol(length=128)
        baz = BoolCol(default=True)
        lulz = UnicodeCol(length=256)
    
        def _get_foo(self):
            return "foo"
    

Then I can define the `to_json()` function like this:

    
    
    def to_json(obj):
        cls = type(obj)
        d = dict((c, getattr(obj, c)) for c in vars(cls) if isinstance(getattr(cls, c), property))
        return json.dumps(d)
    

Now, if I do this:

    
    
    f = Foo(bar = "test", lulz = "only for the")
    print to_json(f)
    

I get the following result:

    
    
    {"baz": true, "lulz": "only for the", "bar": "test", "foo": "foo"}
    

