python - Django 查询集批处理

Question

我有一个在 SQL Server 上运行的 Django 应用程序。SQL Server 允许在用户定义的函数中最多包含 2,100 个参数。我有一个带有查询集的 Django 视图，调用时应该返回 10,000 个结果：

def result_list(request):
    results = MyModel.objects.filter(~(Q(importantField='') | Q(importantField='Some Text'))).select_related('field','field2','field3','field4').prefetch_related('anotherField')
    return render(request, 'results/result_list.html', {'results':results})

我有一个模板试图返回这 10,000 个结果

{% for result in results %}
    <!-- put results into an HTML table -->
{% endfor %}

错误：

Request Method: GET
Request URL: https://server/results/result_list

Django Version: 1.11.3
Python Version: 3.6.3

Traceback:

File "D:\Python\Anaconda3\envs\django\lib\site-packages\django\db\backends\utils.py" in execute
  65.                 return self.cursor.execute(sql, params)

File "D:\Python\Anaconda3\envs\django\lib\site-packages\sql_server\pyodbc\base.py" in execute
  545.             return self.cursor.execute(sql, params)

The above exception (('07002', '[07002] [Microsoft][SQL Server Native Client 11.0]COUNT field incorrect or syntax error (0) (SQLExecDirectW)')) was the direct cause of the following exception:

File "D:\Python\Anaconda3\envs\django\lib\site-packages\django\core\handlers\exception.py" in inner
  41.             response = get_response(request)

File "D:\Python\Anaconda3\envs\django\lib\site-packages\django\core\handlers\base.py" in _get_response
  187.                 response = self.process_exception_by_middleware(e, request)

File "D:\Python\Anaconda3\envs\django\lib\site-packages\django\core\handlers\base.py" in _get_response
  185.                 response = wrapped_callback(request, *callback_args, **callback_kwargs)

File "D:\Python\Anaconda3\envs\django\lib\site-packages\django\contrib\auth\decorators.py" in _wrapped_view
  23.                 return view_func(request, *args, **kwargs)

File "D:\Websites\project\app\views.py" in result_list
  476.     return render(request, 'results/result_list.html', {'results':results})

File "D:\Python\Anaconda3\envs\django\lib\site-packages\django\shortcuts.py" in render
  30.     content = loader.render_to_string(template_name, context, request, using=using)

File "D:\Python\Anaconda3\envs\django\lib\site-packages\django\template\loader.py" in render_to_string
  68.     return template.render(context, request)

File "D:\Python\Anaconda3\envs\django\lib\site-packages\django\template\backends\django.py" in render
  66.             return self.template.render(context)

File "D:\Python\Anaconda3\envs\django\lib\site-packages\django\template\base.py" in render
  207.                     return self._render(context)

File "D:\Python\Anaconda3\envs\django\lib\site-packages\django\test\utils.py" in instrumented_test_render
  107.     return self.nodelist.render(context)

File "D:\Python\Anaconda3\envs\django\lib\site-packages\django\template\base.py" in render
  990.                 bit = node.render_annotated(context)

File "D:\Python\Anaconda3\envs\django\lib\site-packages\django\template\base.py" in render_annotated
  957.             return self.render(context)

File "D:\Python\Anaconda3\envs\django\lib\site-packages\django\template\loader_tags.py" in render
  177.             return compiled_parent._render(context)

File "D:\Python\Anaconda3\envs\django\lib\site-packages\django\test\utils.py" in instrumented_test_render
  107.     return self.nodelist.render(context)

File "D:\Python\Anaconda3\envs\django\lib\site-packages\django\template\base.py" in render
  990.                 bit = node.render_annotated(context)

File "D:\Python\Anaconda3\envs\django\lib\site-packages\django\template\base.py" in render_annotated
  957.             return self.render(context)

File "D:\Python\Anaconda3\envs\django\lib\site-packages\django\template\loader_tags.py" in render
  72.                 result = block.nodelist.render(context)

File "D:\Python\Anaconda3\envs\django\lib\site-packages\django\template\base.py" in render
  990.                 bit = node.render_annotated(context)

File "D:\Python\Anaconda3\envs\django\lib\site-packages\django\template\base.py" in render_annotated
  957.             return self.render(context)

File "D:\Python\Anaconda3\envs\django\lib\site-packages\django\template\loader_tags.py" in render
  216.                 return template.render(context)

File "D:\Python\Anaconda3\envs\django\lib\site-packages\django\template\base.py" in render
  209.                 return self._render(context)

File "D:\Python\Anaconda3\envs\django\lib\site-packages\django\test\utils.py" in instrumented_test_render
  107.     return self.nodelist.render(context)

File "D:\Python\Anaconda3\envs\django\lib\site-packages\django\template\base.py" in render
  990.                 bit = node.render_annotated(context)

File "D:\Python\Anaconda3\envs\django\lib\site-packages\django\template\base.py" in render_annotated
  957.             return self.render(context)

File "D:\Python\Anaconda3\envs\django\lib\site-packages\django\template\defaulttags.py" in render
  173.             len_values = len(values)

File "D:\Python\Anaconda3\envs\django\lib\site-packages\django\db\models\query.py" in __len__
  232.         self._fetch_all()

File "D:\Python\Anaconda3\envs\django\lib\site-packages\django\db\models\query.py" in _fetch_all
  1120.             self._prefetch_related_objects()

File "D:\Python\Anaconda3\envs\django\lib\site-packages\django\db\models\query.py" in _prefetch_related_objects
  675.         prefetch_related_objects(self._result_cache, *self._prefetch_related_lookups)

File "D:\Python\Anaconda3\envs\django\lib\site-packages\django\db\models\query.py" in prefetch_related_objects
  1469.                 obj_list, additional_lookups = prefetch_one_level(obj_list, prefetcher, lookup, level)

File "D:\Python\Anaconda3\envs\django\lib\site-packages\django\db\models\query.py" in prefetch_one_level
  1599.     all_related_objects = list(rel_qs)

File "D:\Python\Anaconda3\envs\django\lib\site-packages\django\db\models\query.py" in __iter__
  250.         self._fetch_all()

File "D:\Python\Anaconda3\envs\django\lib\site-packages\django\db\models\query.py" in _fetch_all
  1118.             self._result_cache = list(self._iterable_class(self))

File "D:\Python\Anaconda3\envs\django\lib\site-packages\django\db\models\query.py" in __iter__
  53.         results = compiler.execute_sql(chunked_fetch=self.chunked_fetch)

File "D:\Python\Anaconda3\envs\django\lib\site-packages\django\db\models\sql\compiler.py" in execute_sql
  886.             raise original_exception

File "D:\Python\Anaconda3\envs\django\lib\site-packages\django\db\models\sql\compiler.py" in execute_sql
  876.             cursor.execute(sql, params)

File "D:\Python\Anaconda3\envs\django\lib\site-packages\django\db\backends\utils.py" in execute
  80.             return super(CursorDebugWrapper, self).execute(sql, params)

File "D:\Python\Anaconda3\envs\django\lib\site-packages\django\db\backends\utils.py" in execute
  65.                 return self.cursor.execute(sql, params)

File "D:\Python\Anaconda3\envs\django\lib\site-packages\django\db\utils.py" in __exit__
  94.                 six.reraise(dj_exc_type, dj_exc_value, traceback)

File "D:\Python\Anaconda3\envs\django\lib\site-packages\django\utils\six.py" in reraise
  685.             raise value.with_traceback(tb)

File "D:\Python\Anaconda3\envs\django\lib\site-packages\django\db\backends\utils.py" in execute
  65.                 return self.cursor.execute(sql, params)

File "D:\Python\Anaconda3\envs\django\lib\site-packages\sql_server\pyodbc\base.py" in execute
  545.             return self.cursor.execute(sql, params)

Exception Type: Error at /djangoProject/results/result_list
Exception Value: ('07002', '[07002] [Microsoft][SQL Server Native Client 11.0]COUNT field incorrect or syntax error (0) (SQLExecDirectW)')

如何更改视图和/或模板以批处理 ORM 查询以绕过参数限制？

我知道iterator存在，但我相信为每条记录单独查询数据库，而不是为每 1,000 或 2,000 条记录批量查询。

我可以通过添加到末尾来对查询集进行切片[:1000]，但我不确定如何将其合并到将所有切片联合在一起的循环中。

标签： pythonsqlsql-serverdjangopython-3.x