Start Coding Now

Save a Django Query to the Database

I found a StackOverflow question on saving a Django query to the database, but I was left super unclear on what to do. There’s all this talk about pickling and serialization that I’m sure makes a ton of sense to someone smarter than me.

The current answer is old, however, and in 2022, I found another solution: turning a dict into JSON.

Plus, I found a way to make it work with my htmx live search, allowing me to view the results of my query on the front end of my site (read: pretty) to double-check that things look okay before saving.

I’ll show you what I did below.

I believe pickling still works, so at the end I will show some more thoughts there.

models.py - example database structure

class Transaction(models.Model):
    id = models.CharField(max_length=24, primary_key=True)
    date = models.DateField(null=False)
    amount = models.IntegerField(null=False)
    info = models.CharField()
    account = models.ForiegnKey(Account, on_delete=models.SET_NULL, null=True)
    category = models.ForeignKey(Category, on_delete=models.SET_NULL, null=True, blank=False, default=None)

class Account(models.Model):
    name = models.CharField()
    email = models.EmailField()

class Category(models.Model):
    name = models.CharField(unique=True)

class Rule(models.Model):
    category = models.ForeignKey(Category, on_delete=models.SET_NULL, blank=False, null=True, default=None)
    criteria = models.JSONField(default=dict)  # this will hold our query

My models store financial transactions, the category the transaction fits into (e.g., salaried income, 1099 income, office expenses, labor expenses, etc…), and a rule to save a query to automatically categorize future transactions without having to remember the query every year when doing taxes.

I know, for example, that all my transactions with my consulting clients should be marked as 1099 income. So I want to create a rule for clients that will grab each monthly transaction and mark it as 1099 income.

Making the query the old-fashioned way

>>> from transactions.models import Category, Rule, Transaction
>>>
>>> client1_transactions = Transaction.objects.filter(account__name="Client One")
<QuerySet [<Transaction: Transaction object (1111111)>, <Transaction: Transaction object (1111112)>, <Transaction: Transaction object (1111113)...]>
>>> client1_transactions.count()
12

Twelve transactions, one for each month. Beautiful.

But how do we save this to the database?

Save query to database in JSONField

We now have Django 4.0 and a bunch of support for JSONField.

I’ve been able to grab the filtering values out of a form POST request, then add them in view logic.

urls.py

from transactions import views
app_name = "transactions"
urlpatterns = [
    path("categorize", views.categorize, name="categorize"),
    path("", views.list, name="list"),
]

transactions/list.html

<form action="{% url 'transactions:categorize' %}" method="POST">
  {% csrf_token %}
  <label for="info">Info field contains...</label>
  <input id="info" type="text" name="info" />
  <label for="account">Account name contains...</label>
  <input id="account" type="text" name="account" />
  <label for="category">New category should be...</label>
  <input id="category" type="text" name="category" />
  <button type="submit">Make a Rule</button>
</form>

views.py

def categorize(request):
    # get POST data from our form
    info = request.POST.get("info", "")
    account = request.POST.get("account", "")
    category = request.POST.get("category", "")

    # set up query
    query = {}
    if info:
        query["info__icontains"] = info
    if account:
        query["account__name__icontains"] = account

    # update the database
    category_obj, _ = Category.objects.get_or_create(name=category)
    transactions = Transaction.objects.filter(**query).order_by("-date")
    Rule.objects.get_or_create(category=category_obj, criteria=query)
    transactions.update(category=category_obj)

    # render the template
    return render(
        request,
        "transactions/list.html",
        {
            "transactions": transactions.select_related("account"),
        },
    )

How to do it with pickle?

So, I actually lied before. I have a little experience with pickle and I do like it, but I am not sure on how to save it to the database. My guess is that you’d then save the pickled string to a BinaryField.

Perhaps something like this:

>>> # imports
>>> import pickle  # standard library
>>> from transactions.models import Category, Rule, Transaction  # my own stuff
>>>
>>> # create the query
>>> qs_to_save = Transaction.objects.filter(account__name="Client 1")
>>> qs_to_save.count()
12
>>>
>>> # create the pickle
>>> saved_pickle = pickle.dumps(qs_to_save.query)
>>> type(saved_pickle)
<class 'bytes'>
>>>
>>> # save to database
>>> # make sure `criteria = models.BinaryField()` above in models.py
>>> # I'm unsure about this
>>> test_category, _ = Category.objects.get_or_create(name="Test Category")
>>> test_rule = Rule.objects.create(category=test_category, criteria=saved_pickle)
>>>
>>> # remake queryset at a later date
>>> new_qs = Transaction.objects.all()
>>> new_qs.query = pickle.loads(test_rule.criteria)
>>> new_qs.count()
12

Hope this helps!

Get Notified of New Posts

Sign up for the newsletter and I'll send you an email when there's a new post.