python 如何在Django中将参数传递给原始SQL

我试图通过我的view.py在Django中使用原始SQL查询创建一个简单的分类帐。如果我直接添加参数值，我会得到所需的结果，但如果我按照这里的教程操作，我会得到下面的错误消息。

错误信息

ProgrammingError at /books/ledger/1005068200/
not enough arguments for format string
Request Method: GET
Request URL:    http://127.0.0.1:8000/books/ledger/1005068200/
Django Version: 4.1.7
Exception Type: ProgrammingError
Exception Value:    
not enough arguments for format string
Exception Location: c:\xampp\htdocs\tammgo\app-env\Lib\site-packages\MySQLdb\cursors.py, line 203, in execute....

代码我已经尝试views.py

from django.shortcuts import render
from django.core.paginator import Paginator
from django.contrib import messages
from django.db.models import Q
from django.shortcuts import redirect, render, reverse
from django.urls import reverse_lazy
from django.http import HttpResponse
from books.forms import VoucherForm, GlForm, PersonForm, GlgroupForm, AccountForm
from . models import Voucher, Gl, Persons, Glgroup, Account
from django.views import generic
from django.views.generic import (
    CreateView,
    DetailView,
    View,
)

class BookLedgerView(DetailView):
    model = Voucher
    template_name = "books/Vourcher_ledger.html"

    def get(self, request, *args, **kwargs):
        sql = '''SELECT datecreated, accountnumber, vtype, id, accountnumber, datecreated, debit, credit, @balance:= @balance + debit - credit AS balance
            FROM (SELECT datecreated, id, accountnumber, vtype, amount, @balance:=0,
                SUM(CASE WHEN vtype='dr' THEN amount ELSE 0 END) AS debit, 
                SUM(CASE WHEN vtype='cr' THEN amount ELSE 0 END) AS credit
            FROM books_voucher v WHERE accountnumber = %s', [accountnumber]
            GROUP BY id) v
            WHERE id>=id
            ORDER BY id DESC'''
        context = {}
        ledger = Voucher.objects.raw(sql)[:20]
        context = {"ledger": ledger}
        return render(request, "books/vourcher_ledger.html", context=context)

vourcher_ledger.html* 这是我的模板

{% extends './base.html' %} 
{% load static i18n%} 
{% load bootstrap5 %} 
{% block content %}
{% load humanize %}
      <table class="table table-bordered table-striped table-sm">
        <thead>
          <tr>
            <th>Date Created</th>
            <th>Description</th>
            <th>Debit</th>
            <th>Crebit</th>
            <th>Balance</th>
          </tr>
        </thead>
        <tbody>
          {% for vourcher in ledger %}
          <tr>
            <td>{{ vourcher.datecreated }}</td>
            <td>{{ vourcher.description }}</td>
            <td class="text-end">{{ vourcher.debit|floatformat:"2"|intcomma }}</td>
            <td class="text-end">{{ vourcher.credit|floatformat:"2"|intcomma }}</td>
            <td class="text-end">{{ vourcher.balance|floatformat:"2"|intcomma }}</td>
          </tr>
          {% endfor %}
        </tbody>
      </table>
      {% endblock content %}

网址.py*

from django.contrib.auth.decorators import login_required
from django.urls import path

from books import views

urlpatterns = [
   path("", views.IndexView.as_view(), name="index"),
   path(
        "books/ledger/<accountnumber>/",
        login_required(views.BookLedgerView.as_view()),
        name="book_ledger",
    ),
]

直接输入参数值时的结果*http://127.0.0.1:8000/books/ledger/1005068200/*

从SQL查询中删除[accountnumber]参数。您可以将accountnumber从kwargs中解包，如self.kwargs['accountnumber']，并将其分配给一个新变量，例如：accountnumber = self.kwargs['accountnumber']然后将这个新变量传递到SQL查询中，如下所示：

sql = '''SELECT datecreated, accountnumber, vtype, id, accountnumber, datecreated, debit, credit, @balance:= @balance + debit - credit AS balance
            FROM (SELECT datecreated, id, accountnumber, vtype, amount, @balance:=0,
                SUM(CASE WHEN vtype='dr' THEN amount ELSE 0 END) AS debit, 
                SUM(CASE WHEN vtype='cr' THEN amount ELSE 0 END) AS credit
            FROM books_voucher v WHERE accountnumber = %s
            GROUP BY id) v
            WHERE id>=id
            ORDER BY id DESC'''

用下面更新的代码替换你的BookLedgerView视图类：

class BookLedgerView(DetailView):
    model = Voucher
    template_name = "books/Vourcher_ledger.html"

    def get(self, request, *args, **kwargs):
        accountnumber = self.kwargs['accountnumber']
        sql = '''SELECT datecreated, accountnumber, vtype, id, accountnumber, datecreated, debit, credit, @balance:= @balance + debit - credit AS balance
            FROM (SELECT datecreated, id, accountnumber, vtype, amount, @balance:=0,
                SUM(CASE WHEN vtype='dr' THEN amount ELSE 0 END) AS debit, 
                SUM(CASE WHEN vtype='cr' THEN amount ELSE 0 END) AS credit
            FROM books_voucher v WHERE accountnumber = %s
            GROUP BY id) v
            WHERE id>=id
            ORDER BY id DESC'''
        context = {}
        ledger = Voucher.objects.raw(sql, [accountnumber])[:20]
        context = {"ledger": ledger}
        return render(request, "books/vourcher_ledger.html", context=context)

注意：通常不建议在Django视图中使用原始SQL查询。

python 如何在Django中将参数传递给原始SQL

1条答案

相关问题

热门标签

最新问答