我试图用django做一个原始查询,但它似乎不起作用
当我在table plus(用于数据库的IDE)中执行相同的查询时,它正在工作
这是我的Django代码:
class AllCustomerOwnerTransactionView(APIView):
permission_classes = [IsAuthenticated]
authentication_classes = [JWTAuthentication]
def get(self, request):
query = f"""
SELECT u_c.name,
sum(CASE
WHEN h_te.to_pay_to_customer > h_te.pay_from_customer THEN
h_te.to_pay_to_customer - h_te.pay_from_customer
WHEN h_te.pay_from_customer > h_te.to_pay_to_customer THEN
h_te.pay_from_customer - h_te.to_pay_to_customer
ELSE 0
end) as Total,
u_c.owner_id
FROM home_transactionentries h_te
INNER JOIN home_transaction h_t
ON h_te.transaction_id = h_t.id
INNER JOIN users_customer u_c
ON u_c.id = h_t.customer_id
WHERE u_c.owner_id = {request.user.id}
GROUP BY u_c.name,
u_c.owner_id;
"""
query_set = TransactionEntries.objects.raw(query)
这是我得到的回溯:
Traceback (most recent call last):
File "/home/__neeraj__/.local/lib/python3.9/site-packages/django/core/handlers/exception.py", line 47, in inner
response = get_response(request)
File "/home/__neeraj__/.local/lib/python3.9/site-packages/django/core/handlers/base.py", line 181, in _get_response
response = wrapped_callback(request, *callback_args, **callback_kwargs)
File "/home/__neeraj__/.local/lib/python3.9/site-packages/django/views/decorators/csrf.py", line 54, in wrapped_view
return view_func(*args, **kwargs)
File "/home/__neeraj__/.local/lib/python3.9/site-packages/django/views/generic/base.py", line 70, in view
return self.dispatch(request, *args, **kwargs)
File "/home/__neeraj__/.local/lib/python3.9/site-packages/rest_framework/views.py", line 509, in dispatch
response = self.handle_exception(exc)
File "/home/__neeraj__/.local/lib/python3.9/site-packages/rest_framework/views.py", line 469, in handle_exception
self.raise_uncaught_exception(exc)
File "/home/__neeraj__/.local/lib/python3.9/site-packages/rest_framework/views.py", line 480, in raise_uncaught_exception
raise exc
File "/home/__neeraj__/.local/lib/python3.9/site-packages/rest_framework/views.py", line 506, in dispatch
response = handler(request, *args, **kwargs)
File "/home/__neeraj__/Documents/programming/merokarobarClone/AccountsBackend/home/views.py", line 73, in get
for db_data in query_set:
File "/home/__neeraj__/.local/lib/python3.9/site-packages/django/db/models/query.py", line 1484, in __iter__
self._fetch_all()
File "/home/__neeraj__/.local/lib/python3.9/site-packages/django/db/models/query.py", line 1471, in _fetch_all
self._result_cache = list(self.iterator())
File "/home/__neeraj__/.local/lib/python3.9/site-packages/django/db/models/query.py", line 1499, in iterator
raise exceptions.FieldDoesNotExist(
django.core.exceptions.FieldDoesNotExist: Raw query must include the primary ke
这是我在table plus中执行的查询:
SELECT u_c.name,
sum(CASE
WHEN h_te.to_pay_to_customer > h_te.pay_from_customer THEN
h_te.to_pay_to_customer - h_te.pay_from_customer
WHEN h_te.pay_from_customer > h_te.to_pay_to_customer THEN
h_te.pay_from_customer - h_te.to_pay_to_customer
ELSE 0
end) as Total,
u_c.owner_id
FROM home_transactionentries h_te
INNER JOIN home_transaction h_t
ON h_te.transaction_id = h_t.id
INNER JOIN users_customer u_c
ON u_c.id = h_t.customer_id
WHERE u_c.owner_id = 1
GROUP BY u_c.name,
u_c.owner_id;
注意:
h_te
是home_transactionentries
表
h_t
是home_transaction
表
u_c
是user_customer
表
如果要执行未映射到返回模型实例的原始查询(这是异常告诉您的),则需要使用^{} :
注意使用
%s
占位符来避免SQL注入漏洞另外,看起来可以将
Total
列的计算简化为相关问题 更多 >
编程相关推荐