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 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?

srgerg · Accepted Answer · 2011-12-09T00:21:26.777

You can use the python json module with the SQLObject 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 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"}

This works, but I should have posted my entire data model -- there's a lot more going on with it than sqlmeta.columns will help. Note the _get_*_range() methods. The Decimal columns aren't actually needed in the JSON results so those are easy to filter out with an if statement — tkone, Dec 08 '11 at 15:48
I've edited my answer with a hack that will include the magic attributes. — srgerg, Dec 08 '11 at 23:58
accepted and upvoted! crap i had never put the type thing together -- it's how i was filtering out the decimal fields (which actually generate that derived data). I'd give you more rep if i could! — tkone, Dec 09 '11 at 14:54

csoria · Answer 2 · 2014-08-11T20:39:16.317

0

import json

json.dumps(obj_instance.sqlmeta.asDict())

In my case this object contained datetimes which json doesn't serialize, so I did something like this:

json.dumps(dict((k, str(v)) for (k,v) in obj_instance.sqlmeta.asDict().items()))

edited Aug 11 '14 at 20:39

answered Aug 11 '14 at 20:33

csoria

643
5
9

user373839 · Answer 3 · 2017-03-11T02:22:59.197

Something like this ...

class MyTable( sqlobject.SQLObject ):
    # ... your columns ... 

json.dumps({
    'MyTable': [row.sqlmeta.asDict() for row in MyTable.select()]
}, indent=4, sort_keys=True )

Suppose you have a list of sqlobject.SQLObject derived classes called 'Tables'

Tables = [MyTable, ...]

def dump():
    r={}
    for t in Tables:
        r[t.__name__] = [row.sqlmeta.asDict() for row in t.select()]
    return json.dumps(r, indent=4, sort_keys=True)

Easier way to create a JSON object from an SQLObject

3 Answers3