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. – 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()

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.

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


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

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)

    # render the template
    return render(
            "transactions": transactions.select_related("account"),

How to do it with htmx and live search

I am using htmx to get live search to ensure that my query is correct. This shows the filtered queryset to me as I type, then I can click “Create Rule” to save this query to the database.


hx-post="{% url 'transactions:perform_categorization' %}"
    hx-post="{% url 'transactions:categorize' %}"
    hx-trigger="keyup changed delay:250ms"
    hx-post="{% url 'transactions:categorize' %}"
    hx-trigger="keyup changed delay:250ms"
    <span class="htmx-indicator">...</span>
    <input id="category" type="text" name="category" placeholder="new category...">
    <button type="submit">Make a Rule</button>

<div id="tables" class="stack">
  {% include "transactions/tables.html" %}

  src="[email protected]"

  document.body.addEventListener('htmx:configRequest', (event) => {
    event.detail.headers['X-CSRFToken'] = '{{ csrf_token }}';

I’ve got one view tied to {% url 'transactions:categorize' %} that will display the list of transactions. The htmx live active search works there.

Then I have another view tied to {% url 'transactions:perform_categorization' %} which actually saves the query to the database by creating a new Rule. The code is the same as above.

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()
>>> # 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
>>> # 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()

Hope this helps!

Leave a Reply