[Django]-How to obtain and/or save the queryset criteria to the DB?

You can store the sql generated by the query using the queryset’s _as_sql() method. The method takes a database connection as an argument, so you’d do:

from app.models import MyModel
from django.db import connection

qs = MyModel.filter(pk__gt=56, published_date__lt=datetime.now())
store_query(qs._as_sql(connection))

You can use http://github.com/denz/django-stored-queryset for that

You can pickle the Query object (not the QuerySet):

>>> import pickle
>>> query = pickle.loads(s)     # Assuming 's' is the pickled string.
>>> qs = MyModel.objects.all()
>>> qs.query = query            # Restore the original 'query'.

Docs: https://docs.djangoproject.com/en/dev/ref/models/querysets/#pickling-querysets

But: You can’t share pickles between versions

you can create your own model to store your queries.
First field can contains fk to ContentTypes
Second field can be just text field with your query etc.

And after that you can use Q object to set queryset for your model.

The current answer was unclear to me as I don’t have much experience with pickle. In 2022, I’ve found that turning a dict into JSON worked well. 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"),
        },
    )

That’s pretty much it!

My example here is a little contrived, so please forgive any errors.

How to do it with pickle

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

Going even further beyond

I found a way to make this all work with my htmx live search, allowing me to see the results of my query on the front end of my site before saving.

This answer is already too long, so here’s a link to a post if you care about that: Saving a Django Query to the Database.