django-pyodbc-azure SQL Server参数限制

Question

Django: 1.11

我使用django-pyodbc-azure作为我的Django后端。它正在连接到Server 2012。Server的限制为2,100个参数。当我试图在返回超过2100个结果的查询集中使用prefetch_related时.

def object_search_list(request):
    objects = MyModel.objects.filter(some_field='filter value').select_related('another_field').prefetch_related('a_third_field')
    print(objects)
    return render(request, 'objects/object_list.html', {'objects':objects})

返回...this错误：

Exception Value: ('07002', '[07002] [Microsoft][SQL Server Native Client 11.0]COUNT field incorrect or syntax error (0) (SQLExecDirectW)')

它指向django-pyodbc-azure使用的pyodbc后端。当查看这些特性(server/pyodbc/features.py)时，没有max_query_params集。我试图手动添加max_query_params = 2100。这导致了同样的错误。将该值设置为2000和1000也会导致相同的错误。

如何调整django-pyodbc-azure以自动处理Server限制？

Django的Oracle后端具有如下特性：https://github.com/django/django/blob/master/django/db/backends/oracle/features.py

更新：

我意识到max_query_params名称对于Django 2.0来说是新的。因此，我尝试使用max_limit_in_size来匹配Django 1.11。仅仅是django-pyodbc-azure不支持控制参数计数，而base.py覆盖Django的默认base.py

更新2:

我将django-pyodbc-azure升级到Django 2.1并升级为match。然后，我编辑了features.py以添加max_query_params = 2000作为一个特性。max_query_params出现在Django本机后端的另一个地方是函数中的operations.py (Oracle示例)：

def bulk_batch_size(self, fields, objs):
    """Oracle restricts the number of parameters in a query."""
    if fields:
        return self.connection.features.max_query_params // len(fields)
    return len(objs)

django-pyodbc-azure也有这个函数，如下所示：

def bulk_batch_size(self, fields, objs):
    """
    Returns the maximum allowed batch size for the backend. The fields
    are the fields going to be inserted in the batch, the objs contains
    all the objects to be inserted.
    """
    objs_len, fields_len, max_row_values = len(objs), len(fields), 1000
    if (objs_len * fields_len) <= max_row_values:
        size = objs_len
    else:
        size = max_row_values // fields_len
    return size

它似乎已经处理了max_row_values设置为1000的批处理。但是，同样的错误仍然存在。我还尝试将1000更改为max_query_params，但没有结果。

以下是完整的回溯：

Traceback:

File "C:\Users\user\installed\anaconda\envs\django2\lib\site-packages\django\db\backends\utils.py" in _execute
  85.                 return self.cursor.execute(sql, params)

File "C:\Users\user\installed\anaconda\envs\django2\lib\site-packages\sql_server\pyodbc\base.py" in execute
  546.             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 "C:\Users\user\installed\anaconda\envs\django2\lib\site-packages\django\core\handlers\exception.py" in inner
  34.             response = get_response(request)

File "C:\Users\user\installed\anaconda\envs\django2\lib\site-packages\django\core\handlers\base.py" in _get_response
  126.                 response = self.process_exception_by_middleware(e, request)

File "C:\Users\user\installed\anaconda\envs\django2\lib\site-packages\django\core\handlers\base.py" in _get_response
  124.                 response = wrapped_callback(request, *callback_args, **callback_kwargs)

File "C:\Users\user\installed\anaconda\envs\django2\lib\site-packages\django\contrib\auth\decorators.py" in _wrapped_view
  21.                 return view_func(request, *args, **kwargs)

File "C:\Users\user\djangoproject\app\views.py" in object_list
  486.     return render(request, 'objects/object_list.html', {'objects':objects})

File "C:\Users\user\installed\anaconda\envs\django2\lib\site-packages\django\shortcuts.py" in render
  36.     content = loader.render_to_string(template_name, context, request, using=using)

File "C:\Users\user\installed\anaconda\envs\django2\lib\site-packages\django\template\loader.py" in render_to_string
  62.     return template.render(context, request)

File "C:\Users\user\installed\anaconda\envs\django2\lib\site-packages\django\template\backends\django.py" in render
  61.             return self.template.render(context)

File "C:\Users\user\installed\anaconda\envs\django2\lib\site-packages\template_timings_panel\panels\TemplateTimings.py" in timing_hook
  139.         result = func(self, *args, **kwargs)

File "C:\Users\user\installed\anaconda\envs\django2\lib\site-packages\django\template\base.py" in render
  171.                     return self._render(context)

File "C:\Users\user\installed\anaconda\envs\django2\lib\site-packages\django\test\utils.py" in instrumented_test_render
  96.     return self.nodelist.render(context)

File "C:\Users\user\installed\anaconda\envs\django2\lib\site-packages\django\template\base.py" in render
  937.                 bit = node.render_annotated(context)

File "C:\Users\user\installed\anaconda\envs\django2\lib\site-packages\django\template\base.py" in render_annotated
  904.             return self.render(context)

File "C:\Users\user\installed\anaconda\envs\django2\lib\site-packages\django\template\loader_tags.py" in render
  150.             return compiled_parent._render(context)

File "C:\Users\user\installed\anaconda\envs\django2\lib\site-packages\django\test\utils.py" in instrumented_test_render
  96.     return self.nodelist.render(context)

File "C:\Users\user\installed\anaconda\envs\django2\lib\site-packages\django\template\base.py" in render
  937.                 bit = node.render_annotated(context)

File "C:\Users\user\installed\anaconda\envs\django2\lib\site-packages\django\template\base.py" in render_annotated
  904.             return self.render(context)

File "C:\Users\user\installed\anaconda\envs\django2\lib\site-packages\template_timings_panel\panels\TemplateTimings.py" in timing_hook
  139.         result = func(self, *args, **kwargs)

File "C:\Users\user\installed\anaconda\envs\django2\lib\site-packages\django\template\loader_tags.py" in render
  62.                 result = block.nodelist.render(context)

File "C:\Users\user\installed\anaconda\envs\django2\lib\site-packages\django\template\base.py" in render
  937.                 bit = node.render_annotated(context)

File "C:\Users\user\installed\anaconda\envs\django2\lib\site-packages\django\template\base.py" in render_annotated
  904.             return self.render(context)

File "C:\Users\user\installed\anaconda\envs\django2\lib\site-packages\django\template\loader_tags.py" in render
  188.             return template.render(context)

File "C:\Users\user\installed\anaconda\envs\django2\lib\site-packages\template_timings_panel\panels\TemplateTimings.py" in timing_hook
  139.         result = func(self, *args, **kwargs)

File "C:\Users\user\installed\anaconda\envs\django2\lib\site-packages\django\template\base.py" in render
  173.                 return self._render(context)

File "C:\Users\user\installed\anaconda\envs\django2\lib\site-packages\django\test\utils.py" in instrumented_test_render
  96.     return self.nodelist.render(context)

File "C:\Users\user\installed\anaconda\envs\django2\lib\site-packages\django\template\base.py" in render
  937.                 bit = node.render_annotated(context)

File "C:\Users\user\installed\anaconda\envs\django2\lib\site-packages\django\template\base.py" in render_annotated
  904.             return self.render(context)

File "C:\Users\user\installed\anaconda\envs\django2\lib\site-packages\django\template\defaulttags.py" in render
  166.             len_values = len(values)

File "C:\Users\user\installed\anaconda\envs\django2\lib\site-packages\django\db\models\query.py" in __len__
  250.         self._fetch_all()

File "C:\Users\user\installed\anaconda\envs\django2\lib\site-packages\django\db\models\query.py" in _fetch_all
  1188.             self._prefetch_related_objects()

File "C:\Users\user\installed\anaconda\envs\django2\lib\site-packages\django\db\models\query.py" in _prefetch_related_objects
  723.         prefetch_related_objects(self._result_cache, *self._prefetch_related_lookups)

File "C:\Users\user\installed\anaconda\envs\django2\lib\site-packages\django\db\models\query.py" in prefetch_related_objects
  1569.                 obj_list, additional_lookups = prefetch_one_level(obj_list, prefetcher, lookup, level)

File "C:\Users\user\installed\anaconda\envs\django2\lib\site-packages\django\db\models\query.py" in prefetch_one_level
  1699.     all_related_objects = list(rel_qs)

File "C:\Users\user\installed\anaconda\envs\django2\lib\site-packages\django\db\models\query.py" in __iter__
  268.         self._fetch_all()

File "C:\Users\user\installed\anaconda\envs\django2\lib\site-packages\django\db\models\query.py" in _fetch_all
  1186.             self._result_cache = list(self._iterable_class(self))

File "C:\Users\user\installed\anaconda\envs\django2\lib\site-packages\django\db\models\query.py" in __iter__
  54.         results = compiler.execute_sql(chunked_fetch=self.chunked_fetch, chunk_size=self.chunk_size)

File "C:\Users\user\installed\anaconda\envs\django2\lib\site-packages\django\db\models\sql\compiler.py" in execute_sql
  1065.             cursor.execute(sql, params)

File "C:\Users\user\installed\anaconda\envs\django2\lib\site-packages\debug_toolbar\panels\sql\tracking.py" in execute
  186.         return self._record(self.cursor.execute, sql, params)

File "C:\Users\user\installed\anaconda\envs\django2\lib\site-packages\debug_toolbar\panels\sql\tracking.py" in _record
  124.             return method(sql, params)

File "C:\Users\user\installed\anaconda\envs\django2\lib\site-packages\django\db\backends\utils.py" in execute
  100.             return super().execute(sql, params)

File "C:\Users\user\installed\anaconda\envs\django2\lib\site-packages\django\db\backends\utils.py" in execute
  68.         return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)

File "C:\Users\user\installed\anaconda\envs\django2\lib\site-packages\django\db\backends\utils.py" in _execute_with_wrappers
  77.         return executor(sql, params, many, context)

File "C:\Users\user\installed\anaconda\envs\django2\lib\site-packages\django\db\backends\utils.py" in _execute
  85.                 return self.cursor.execute(sql, params)

File "C:\Users\user\installed\anaconda\envs\django2\lib\site-packages\django\db\utils.py" in __exit__
  89.                 raise dj_exc_value.with_traceback(traceback) from exc_value

File "C:\Users\user\installed\anaconda\envs\django2\lib\site-packages\django\db\backends\utils.py" in _execute
  85.                 return self.cursor.execute(sql, params)

File "C:\Users\user\installed\anaconda\envs\django2\lib\site-packages\sql_server\pyodbc\base.py" in execute
  546.             return self.cursor.execute(sql, params)

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

Answer 1

它看起来没有被添加到django-pyodbc-azure中

server/pyodbc/features.py#L4

我把它加到我的叉子上了

server/pyodbc/features.py#L33

你能给它做个测试吗？您需要升级到Django 2.1，然后您应该能够通过pip install git+https://github.com/FlipperPA/django-pyodbc-azure.git@azure-2.1查看它是否与添加该设置一样直接。粗略地看一下Django的源代码，它看起来可能就够了。如果成功的话，我会向Michaya发出拉的请求。