0

I am trying to design a solution to Extentions of the Django docs' pizza example (screen for pizzas by topping) and have been working with Attempt 1.

So far my solution only works on two or three screening criteria before Django barfs and throws a DatabaseError: too many SQL variables. It is my current understanding that this is caused by a database limitation (in this case sqlite3). I am also of the impression that this error is a marker for poor code versus inadequate database configuration. But I simply don't know enough about SQL or QuerySet mechanics to know what actions a programmer can take to cause/avoid this error.

I now have the following questions:

1. What is a SQL variable in Django?

2. How do these SQL variables accumulate in Django?

3. How do these SQL variables get 'flushed' in Django?

4. What am I doing/not doing in the the Query Code section below such that this error is expected to be thrown?

models.py

from django.db import models

class Topping(models.Model):
  name = models.CharField(max_length=128, primary_key=True)
  units = models.CharField(max_length=4) # e.g. 'g' | 'kg' | 'lb' | 'oz' ...

  # On Python 3: def __str__(self):
  def __unicode__(self):
    return self.name

class ToppingAmount(models.Model):
  pizzaID = models.CharField(max_length=128)
  topping = models.ForeignKey(Topping)
  amount = models.FloatField()

  # On Python 3: def __str__(self):
  def __unicode__(self):
    return self.topping.name

class Pizza(models.Model):
  name = models.CharField(max_length=128, primary_key=True)
  toppings = models.ManyToManyField(ToppingAmount)

  # On Python 3: def __str__(self):
  def __unicode__(self):
    return self.name

Query Code

def screen_query(lte, gte):
  topping_amounts = ToppingAmount.objects.all()

  for c in lte:
    bad = topping_amounts.filter(topping__pk=c[0]).filter(amount__gt=c[1])
    for b in bad:
      topping_amounts = topping_amounts.exclude(pizzaID=b.pizzaID)

  for c in gte:
    topping_amountsg = ToppingAmount.objects.none()
    prima_good = topping_amounts.filter(topping__name=c[0]).filter(amount__gte=c[1])
    for g in prima_good:
      topping_amountsg = topping_amountsg | topping_amounts.filter(pizzaID=g.pizzaID)
    topping_amounts = topping_amountsg

  pizzas = Pizza.objects.none()
  for ta in topping_amounts:
    pizzas = pizzas | Pizza.objects.filter(pk=ta.pizzaID)

  return pizzas

Explanation of Code:

Constraints are of the form "amount , ">= | <=", " value" and are placed in a either a gte or lte list. As an example, the following lists indicate that we will screen the database for all Pizza objects such that they have:

  1. not more than 30.0 of "Pepperoni"
  2. not more than 63.813 of "Pineapple"
  3. at least 10.0 of "Bell Peppers"
  4. at least 55 of "Ham"
  5. at least 10.0 of "Bacon"
  6. at least 55 of "Chicken"
lte = [["Pepperoni", 30.0], ["Pineapple", 63.813]]
gte = [["Bell Peppers", 10.0], ["Ham", 55], ["Bacon", 10.0], ["Chicken", 55]]

We begin our query by considering all pizzas (via ToppingAmount) as candidates:

topping_amounts = ToppingAmount.objects.all()

Next, identify and exclude all Pizzas in contravention of the "amount , "<=", " value" constraint (via topping_amounts):

for c in lte:
  bad = topping_amounts.filter(topping__pk=c[0]).filter(amount__gt=c[1])
  for b in bad:
    topping_amounts = topping_amounts.exclude(pizzaID=b.pizzaID)

Now that we have identified all topping_amounts objects which comply with the constraints prescribed by lte, we can pick through those for the ones that also comply with our gte constraints. This is accomplished by creating an empty QuerySet topping_amountsg = ToppingAmount.objects.none() and filling it with allowable objects.

for c in gte:
  topping_amountsg = ToppingAmount.objects.none()
  prima_good = topping_amounts.filter(topping__name=c[0]).filter(amount__gte=c[1])

Our prima facie feasible ToppingAmount objects are now in prima_good. Recall however that a pizza has many toppings, and so we must collect all ToppingAmount objects associated with each prima facie feasible pizza.

  for g in prima_good:
    topping_amountsg = topping_amountsg | topping_amounts.filter(pizzaID=g.pizzaID)

Set the topping_amounts QuerySet equal to the pirma facie feasible ToppingAmount objects and filter against the next constraint in gte.

  topping_amounts = topping_amountsg

Once all gte constraints have been inflicted on topping_amounts, we will have our feasible QuerySet.

Extentions of the Django docs' pizza example (screen for pizzas by topping) is concerned with obtaining the QuerySet of feasible Pizza objects, so we extract those from our feasible ToppingAmount objects in topping_amounts.

pizzas = Pizza.objects.none()
for ta in topping_amounts:
  pizzas = pizzas | Pizza.objects.filter(pk=ta.pizzaID)

Putting all of the pieces together in a screening-query function:

def screen_query(lte, gte):
  topping_amounts = ToppingAmount.objects.all()

  for c in lte:
    bad = topping_amounts.filter(topping__pk=c[0]).filter(amount__gt=c[1])
    for b in bad:
      topping_amounts = topping_amounts.exclude(pizzaID=b.pizzaID)

  for c in gte:
    topping_amountsg = ToppingAmount.objects.none()
    prima_good = topping_amounts.filter(topping__name=c[0]).filter(amount__gte=c[1])
    for g in prima_good:
      topping_amountsg = topping_amountsg | topping_amounts.filter(pizzaID=g.pizzaID)
    topping_amounts = topping_amountsg

  pizzas = Pizza.objects.none()
  for ta in topping_amounts:
    pizzas = pizzas | Pizza.objects.filter(pk=ta.pizzaID)

  return pizzas

If you run this code in the shell with

lte = [["Pepperoni", 30.0], ["Pineapple", 63.813]]
gte = [["Bell Peppers", 10.0], ["Ham", 55], ["Bacon", 10.0], ["Chicken", 55]]

you will get

File "/usr/lib/python2.7/site-packages/django/db/backends/sqlite3/base.py", line 344, in execute return Database.Cursor.execute(self, query, params) DatabaseError: too many SQL variables

Community
  • 1
  • 1
user0055AA
  • 21
  • 4
  • This may be [another good link](http://stackoverflow.com/questions/7106016/too-many-sql-variables-error-in-django-witih-sqlite3) to explain the problem. Have you identified which line in your code is failing? (provide a full stack trace). Read up on [how to display what query is being executed](http://stackoverflow.com/questions/971667/django-orm-how-to-view-or-log-the-executed-query). – Joseph Paetz Sep 10 '13 at 13:44
  • Unfortunately the first link does not explain my problem as the asker repeatedly makes clear that his problem is not observed in the shell. It is also acknowledged in the answers to the problem posted in that link that there is a SQLite limitation and hence the third sentence of http://stackoverflow.com/questions/18713512/. Sentence four of http://stackoverflow.com/questions/18713512/ pertains to the fact that the discussion at http://stackoverflow.com/questions/7106016 suggests that the problem is not with SQLite but with the queries. – user0055AA Sep 10 '13 at 20:37
  • As far as the exact query that is causing the problem, it is any of the {for c in lte:} or {for c in gte:} loops if the respective outer list has approx four elements. The database only has 100 test pizzas and 17 test toppings. The system being built is supposed to have over 10,000 "pizzas" and 157 "toppings". Outer Query lists {lte} and {gte} will not have four but possibly 100+ elements. – user0055AA Sep 10 '13 at 20:41
  • I threw a counter on the {for c in gte:} loop and ran it with 6 elements in the {gte} list. I found that {for g in prima_good: topping_amountsg = topping_amountsg | topping_amounts.filter(pizzaID=g.pizzaID)} runs 96 times before grinding to a halt and crashing. – user0055AA Sep 10 '13 at 21:41
  • After testing a handful of approaches to the query, I have been able to increase the number of pizzas in the SQLite3 database to over 10,000 with the number of query criteria above 20. The query now runs in less than one second. Running the query 5,000 times takes only 58.61 seconds to complete and produces no "Too many SQL variables" errors. I will only provide details and solution to http://stackoverflow.com/questions/18583609 for fellow Googlers on the condition that the four very valid questions at http://stackoverflow.com/questions/18713512 are answered first. – user0055AA Sep 11 '13 at 10:19

0 Answers0