We call the Django connection cursor approximately like this:
from django.db import connections
cursor = connections['default']
cursor.execute(sql, context_dict)
When we execute the resulting SQL query, named parameters are used. You must name your parameters. Positional parameters are not passed:
oldest = '2000-01-01'
DQ("Book", "id").where("pub_date >= '{oldest}").context({"oldest": oldest}).tuples()
Notice that any parameterised value must be represented in the query expression in curly braces. Note as well, this is not an f-string!
{myparam}
Therefore, when you add subqueries, their parameters have to be supplied at the same time.
Note what is happening here:
name_search = 'Bar.*'
DQ("Book", "id").where("regex(b.name, {name_search}").context(locals()).tuples()
To get all books starting with ‘Bar’. Or:
DQ("Book", "name").where("like(upper(name), upper({name_search})").context(request.POST)
Provided that request.POST
has a name_search
key/value.
You can provide a validation class that will return context variables. The default class used is called ContextValidator()
. You can override this to provide a validator that raises exceptions if data is not valid or mutates the context data, like coercing types from str
to int
:
class MyContextValidator(ContextValidator):
def get(self, key, value):
if key == 'order_no':
return int(value)
return value
def context(self):
if not 'order_no' in self.data:
raise Exception("Need order no")
self.super().context()
Then add the validator:
order_no = "12345"
DQ("Orders", "order_no, customer").where("order_no == {order_no}")
.validator(MyContextValidator)
.context(locals())
.tuples()
You can set your own validator class in Django settings:
DJAQ_VALIDATOR = MyValidator
The request
parameter of the API view is added to the context and will be available to the validator as request
.