Django中向Raw 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 %}

urls.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/

问题原因及修复方案 #

错误原因 #

1. SQL字符串语法错误：将传参的[accountnumber]和多余单引号写入SQL语句，导致SQL格式混乱，数据库无法解析占位符%s。
2. 未正确获取URL参数：未从视图kwargs中提取URL里的accountnumber值。
3. Raw SQL传参方式错误：Django的raw()方法需要将参数作为第二个参数传入，而非嵌入SQL字符串。

修正后的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):
        # 从URL kwargs中获取accountnumber参数
        accountnumber = kwargs.get('accountnumber')
        # 修正SQL字符串，移除错误符号并清理重复字段
        sql = '''SELECT datecreated, accountnumber, vtype, id, 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
            ORDER BY id DESC'''
        # 将参数作为第二个参数传给raw()方法
        ledger = Voucher.objects.raw(sql, [accountnumber])[:20]
        context = {"ledger": ledger}
        return render(request, "books/vourcher_ledger.html", context=context)

额外优化点 #

• 模板中<th>Crebit</th>为拼写错误，建议改为<th>Credit</th>，避免显示异常。
• SQL中重复选择了accountnumber和datecreated字段，已移除重复项以减少数据传输。

内容的提问来源于stack exchange，提问作者Mukoro Godwin