def q_subquery(**kwargs):
pubs = DQ("(p.id) Publisher p", name="pubs") # noqa: F841
books = DQ("(b.name) Book{publisher in '@pubs'} b")
if kwargs.get("sql"):
print(books.query())
a = []
for rec in books.tuples():
a.append(rec)
def q_params(**kwargs):
DQ(
"""(b.id, b.name) Book{b.id == 1 or
regex(b.name, '$(mynamepattern)')} b """,
verbosity=kwargs["verbose"],
)
def q_sub_queryset(**kwargs):
qs = Book.objects.filter(name__startswith="B").only("id")
dq = DQ("(b.name, b.price) Book{id in '@qs_sub'} b", names={"qs_sub": qs})
a = []
for rec in dq.tuples():
a.append(rec)
def book_list(request):
"""Get the list of books based on search form values.
AJAX view
We assume this will be a POST.
"""
try:
# define optional expressions for each search value
c = (B("regex(b.name, '$(name)')")
& B("b.pages > '$(pages)'")
& B("b.rating > '$(rating)'")
& B("b.price > '$(price)'"))
# construct the query and get results as dicts, maximum of 20 records
books = list(
DQ("(b.name as name, b.price as price, b.rating as rating, b.pages as pages, b.publisher.name as publisher) Book b"
).conditions(c) # add our conditions here
.context(request.POST) # add our context data here
.limit(20).dicts())
# return the rendered html
return render(request, "book_list.html", {"books": books})
except Exception as e:
print(e)
logger.exception(e)
return HttpResponseServerError(e)
def q_rewind(**kwargs):
dq = DQ("(b.name) Book b")
a = []
for rec in dq.tuples():
a.append(rec)
a = []
for rec in dq.rewind().tuples():
a.append(rec)
def q_diff_avg_price(**kwargs):
a = []
dq = DQ("""
(Publisher.name, max(Book.price) -
avg(Book.price) as price_diff) Book b
""")
for rec in dq.tuples():
a.append(rec)
def sql_view(request):
q = request.POST.get("query")
try:
s = DQ(q).parse()
s = sqlparse.format(s, reindent=True, keyword_case="upper")
r = {"result": s}
return JsonResponse(r)
except Exception as e:
traceback.print_exc(file=sys.stdout)
return HttpResponseServerError(e)
def q_grouping(**kwargs):
dq = DQ(
"(b.id, b.name) Book{(b.id == 1 or b.id == 2) and b.id == 3} b ",
verbosity=kwargs["verbose"],
)
qs = Book.objects.filter(name__startswith="B").only("id")
[rec.id for rec in qs][:3]
a = []
for rec in dq.limit(10).tuples():
a.append(rec)
def handle(self, *args, **options):
q = DQ(
options.get("src"),
limit=options.get("limit"),
offset=options.get("offset"),
verbosity=options.get("verbosity"),
)
if options.get("format") == "dicts":
print(json.dumps(list(q.dicts()), cls=DjangoJSONEncoder, indent=4))
else:
for rec in getattr(q, options.get("format"))():
print(rec)
def q_sub_list(**kwargs):
qs = Book.objects.filter(name__startswith="B").only("id")
ids = [rec.id for rec in qs]
dq = DQ(
"(b.name, b.price) Book{id in '@qs_sub'} b",
names={"qs_sub": ids},
verbosity=kwargs.get("verbose"),
)
a = []
for rec in dq.tuples():
a.append(rec)
def query_view(request):
if request.method == "POST":
q = request.POST.get("query")
limit = request.POST.get("limit", 10)
offset = request.POST.get("offset", 0)
sql = request.POST.get("sql", False) == "true"
try:
if sql:
s = DQ(q, whitelist=wl()).offset(offset).limit(limit).parse()
s = sqlparse.format(s, reindent=True, keyword_case="upper")
r = {"result": s}
else:
try:
r = {
"result":
list(
DQ(q, whitelist=wl()).offset(offset).limit(
limit).dicts())
}
except Exception as e:
if e.__cause__ and e.__cause__.pgerror:
err = e.__cause__.pgerror
raise Exception(err)
else:
raise e
return JsonResponse(r)
except Exception as e:
traceback.print_exc(file=sys.stdout)
return HttpResponseServerError(e)
list_of_apps = [a.name for a in apps.get_app_configs()]
list_of_apps = filter(lambda a: a in list(wl().keys()), list_of_apps)
data = {"apps": list_of_apps}
return render(request, "query.html", data)
def queries(query_list, whitelist=None, validator=None):
if not query_list:
return list()
responses = list()
for data in query_list:
query_string = data.get("q")
offset = int(data.get("offset", 0))
limit = int(data.get("limit", 0))
context = data.get("context", dict())
responses.append(
list(
DQ(query_string,
whitelist=whitelist).context(context).validator(
validator).limit(limit).offset(offset).dicts()))
return responses
def q_ilike(**kwargs):
name_pattern = "C%" # noqa: F841
list(DQ("(b.name) Book{ilike(b.name, '$(name_pattern)')} b ").tuples())
def q_books_per_publisher(**kwargs):
a = []
dq = DQ("(Publisher.name, count(Book.id) as num_books) Book b")
for rec in dq.tuples():
a.append(rec)
def q_all_books(**kwargs):
dq = DQ("(b.id, b.name) Book b")
a = []
for rec in dq.tuples():
a.append(rec)
def q_books_avg_min_max(**kwargs):
a = []
dq = DQ("(avg(b.price), max(b.price), min(b.price)) Book b")
for rec in dq.tuples():
a.append(rec)
def q_avg_price(**kwargs):
dq = DQ("(avg(b.price)) Book b")
a = []
for rec in dq.tuples():
a.append(rec)
def q_count(**kwargs):
# print(DQ("(Book.id)").count())
DQ("(count(Book.id)) Book").value()
def q_sub_query(**kwargs):
dq_sub = DQ("(b.id) Book{name == 'B*'} b", name="dq_sub") # noqa: F841
list(DQ("(b.name, b.price) Book{id in '@dq_sub'} b").tuples())
def q_conditional_sum(**kwargs):
list(
DQ("""
(sum(iif(b.rating >= 3, b.rating, 0)) as below_3, sum(iif(b.rating > 3, b.rating, 0)) as above_3) Book b
""").tuples())
def q_implicit_model(**kwargs):
list(DQ("(Book.name, Book.id)").tuples())