Most of our projects lately are now using JSON data storage in the database. This makes it very efficient for storing additional data without having to run alters are gigantic data sets. It also allows us to store different data for different kinds of sets within the same table.
Doing this in Django we originally created a JSONField. A simple custom field which would convert back and from from JSON to a Python object. Mostly this was going from a dictionary to a text-based form in the database (JSON). Recently in one project I took the time to update the field to fix a couple things as well as add support for it in the forms, and thus making it usable within the admin.
So, for the wanting, here’s our updated JSONField and the related form field.
from django.db import models from django import forms from django.utils import simplejson as json class JSONWidget(forms.Textarea): def render(self, name, value, attrs=None): if not isinstance(value, basestring): value = json.dumps(value, indent=2) return super(JSONWidget, self).render(name, value, attrs) class JSONFormField(forms.CharField): def __init__(self, *args, **kwargs): kwargs['widget'] = JSONWidget super(JSONFormField, self).__init__(*args, **kwargs) def clean(self, value): if not value: return try: return json.loads(value) except Exception, exc: raise forms.ValidationError(u'JSON decode error: %s' % (unicode(exc),)) class JSONField(models.TextField): __metaclass__ = models.SubfieldBase def formfield(self, **kwargs): return super(JSONField, self).formfield(form_class=JSONFormField, **kwargs) def to_python(self, value): if isinstance(value, basestring): value = json.loads(value) return value def get_db_prep_save(self, value): if value is None: return return json.dumps(value) def value_to_string(self, obj): value = self._get_val_from_obj(obj) return self.get_db_prep_value(value)
9 Responses to "Cleaning up with JSON and SQL in Django"
I’m having a trouble imagining a use-case here.
If your schema does change that often how about a table for key/value pairs or something like that?
Why does your schema keep changing? Is there a better way to model the data that might avoid the problem?
@Andy Baker
Because it is a ridiculously large dataset. The schema doesn’t keep changing, just different records store a little bit of extra custom data.
I can attest that in the real world frequently there are requests for ‘1 more field’ or frequently unused data types that may only exist on a few objects.
Traditional database design doesn’t fit well in many scenarios.
Yep I can give you two great examples:
1) Lifestream
We store custom data based on each extension, in one table. The extension then decides how to handle this data. It’s a smart denormalization.
2) iBegin
We have way too many rows and an alter requires downtime. This metadata doesn’t change often, but it’s just that, metadata. If we need to build indexes on it we can do that in another fashion.
It’s much more efficient using it out of a JSONField rather than doing more SQL queries for a set of key/value pairs.
A good use case for JSON fields are user preferences in my opinion.
The djblets projects has a similar field called JSONField. It is also used in their siteconfig app (which works great for my needs).
Peter, Yes, siteconfig app is great and very useful
Wouldn’t a document-based database make more sense?
I am currently working on a custom django field that doesnt store the data in the database, “redirects” to a couchdb entry.
@ashwoods The only downside to things like couchdb is (last I looked) they haven’t been proven. I’d love to use a non-relational database but there’s really no great solution out there yet.
Leave A Reply