django:1.11
我使用django-pyodbc-azure
作为我的Django后端。它连接到SQL Server2012。SQL Server的限制为2,100个参数。当我尝试在将返回2100多个结果的QuerySet上使用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})
...返回此错误:
Exception Value: ('07002', '[07002] [Microsoft][SQL Server Native Client 11.0]COUNT field incorrect or syntax error (0) (SQLExecDirectW)')
它指向django-pyodbc-azure
使用的PYODBC后端。查看功能时(https://github.com/michiya/django-pyodbc-azure/blob/azure-2.1/sql_server/pyodbc/features.py),没有max_query_params
设置。我尝试手动添加max_query_params = 2100
。这导致了相同的错误。将该值设置为2000和1000也会导致相同的错误。
如何调整django-pyodbc-azure
以自动处理此SQL服务器限制?
django的甲骨文后端具有这样的功能:https://github.com/django/django/blob/blob/master/master/django/django/django/django/django/ddb/backends/oracle/oracle/features.py.py
更新:
我意识到max_query_params
名称是Django 2.0的新名称。因此,我尝试了max_limit_in_size
匹配Django 1.11。仅仅是django-pyodbc-azure
不支持控制参数计数,而base.py
覆盖了Django的默认base.py
?
更新2:
我升级到Django 2.1,并升级了django-pyodbc-azure
匹配。然后,我编辑了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:Usersuserinstalledanacondaenvsdjango2libsite-packagesdjangodbbackendsutils.py" in _execute
85. return self.cursor.execute(sql, params)
File "C:Usersuserinstalledanacondaenvsdjango2libsite-packagessql_serverpyodbcbase.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:Usersuserinstalledanacondaenvsdjango2libsite-packagesdjangocorehandlersexception.py" in inner
34. response = get_response(request)
File "C:Usersuserinstalledanacondaenvsdjango2libsite-packagesdjangocorehandlersbase.py" in _get_response
126. response = self.process_exception_by_middleware(e, request)
File "C:Usersuserinstalledanacondaenvsdjango2libsite-packagesdjangocorehandlersbase.py" in _get_response
124. response = wrapped_callback(request, *callback_args, **callback_kwargs)
File "C:Usersuserinstalledanacondaenvsdjango2libsite-packagesdjangocontribauthdecorators.py" in _wrapped_view
21. return view_func(request, *args, **kwargs)
File "C:Usersuserdjangoprojectappviews.py" in object_list
486. return render(request, 'objects/object_list.html', {'objects':objects})
File "C:Usersuserinstalledanacondaenvsdjango2libsite-packagesdjangoshortcuts.py" in render
36. content = loader.render_to_string(template_name, context, request, using=using)
File "C:Usersuserinstalledanacondaenvsdjango2libsite-packagesdjangotemplateloader.py" in render_to_string
62. return template.render(context, request)
File "C:Usersuserinstalledanacondaenvsdjango2libsite-packagesdjangotemplatebackendsdjango.py" in render
61. return self.template.render(context)
File "C:Usersuserinstalledanacondaenvsdjango2libsite-packagestemplate_timings_panelpanelsTemplateTimings.py" in timing_hook
139. result = func(self, *args, **kwargs)
File "C:Usersuserinstalledanacondaenvsdjango2libsite-packagesdjangotemplatebase.py" in render
171. return self._render(context)
File "C:Usersuserinstalledanacondaenvsdjango2libsite-packagesdjangotestutils.py" in instrumented_test_render
96. return self.nodelist.render(context)
File "C:Usersuserinstalledanacondaenvsdjango2libsite-packagesdjangotemplatebase.py" in render
937. bit = node.render_annotated(context)
File "C:Usersuserinstalledanacondaenvsdjango2libsite-packagesdjangotemplatebase.py" in render_annotated
904. return self.render(context)
File "C:Usersuserinstalledanacondaenvsdjango2libsite-packagesdjangotemplateloader_tags.py" in render
150. return compiled_parent._render(context)
File "C:Usersuserinstalledanacondaenvsdjango2libsite-packagesdjangotestutils.py" in instrumented_test_render
96. return self.nodelist.render(context)
File "C:Usersuserinstalledanacondaenvsdjango2libsite-packagesdjangotemplatebase.py" in render
937. bit = node.render_annotated(context)
File "C:Usersuserinstalledanacondaenvsdjango2libsite-packagesdjangotemplatebase.py" in render_annotated
904. return self.render(context)
File "C:Usersuserinstalledanacondaenvsdjango2libsite-packagestemplate_timings_panelpanelsTemplateTimings.py" in timing_hook
139. result = func(self, *args, **kwargs)
File "C:Usersuserinstalledanacondaenvsdjango2libsite-packagesdjangotemplateloader_tags.py" in render
62. result = block.nodelist.render(context)
File "C:Usersuserinstalledanacondaenvsdjango2libsite-packagesdjangotemplatebase.py" in render
937. bit = node.render_annotated(context)
File "C:Usersuserinstalledanacondaenvsdjango2libsite-packagesdjangotemplatebase.py" in render_annotated
904. return self.render(context)
File "C:Usersuserinstalledanacondaenvsdjango2libsite-packagesdjangotemplateloader_tags.py" in render
188. return template.render(context)
File "C:Usersuserinstalledanacondaenvsdjango2libsite-packagestemplate_timings_panelpanelsTemplateTimings.py" in timing_hook
139. result = func(self, *args, **kwargs)
File "C:Usersuserinstalledanacondaenvsdjango2libsite-packagesdjangotemplatebase.py" in render
173. return self._render(context)
File "C:Usersuserinstalledanacondaenvsdjango2libsite-packagesdjangotestutils.py" in instrumented_test_render
96. return self.nodelist.render(context)
File "C:Usersuserinstalledanacondaenvsdjango2libsite-packagesdjangotemplatebase.py" in render
937. bit = node.render_annotated(context)
File "C:Usersuserinstalledanacondaenvsdjango2libsite-packagesdjangotemplatebase.py" in render_annotated
904. return self.render(context)
File "C:Usersuserinstalledanacondaenvsdjango2libsite-packagesdjangotemplatedefaulttags.py" in render
166. len_values = len(values)
File "C:Usersuserinstalledanacondaenvsdjango2libsite-packagesdjangodbmodelsquery.py" in __len__
250. self._fetch_all()
File "C:Usersuserinstalledanacondaenvsdjango2libsite-packagesdjangodbmodelsquery.py" in _fetch_all
1188. self._prefetch_related_objects()
File "C:Usersuserinstalledanacondaenvsdjango2libsite-packagesdjangodbmodelsquery.py" in _prefetch_related_objects
723. prefetch_related_objects(self._result_cache, *self._prefetch_related_lookups)
File "C:Usersuserinstalledanacondaenvsdjango2libsite-packagesdjangodbmodelsquery.py" in prefetch_related_objects
1569. obj_list, additional_lookups = prefetch_one_level(obj_list, prefetcher, lookup, level)
File "C:Usersuserinstalledanacondaenvsdjango2libsite-packagesdjangodbmodelsquery.py" in prefetch_one_level
1699. all_related_objects = list(rel_qs)
File "C:Usersuserinstalledanacondaenvsdjango2libsite-packagesdjangodbmodelsquery.py" in __iter__
268. self._fetch_all()
File "C:Usersuserinstalledanacondaenvsdjango2libsite-packagesdjangodbmodelsquery.py" in _fetch_all
1186. self._result_cache = list(self._iterable_class(self))
File "C:Usersuserinstalledanacondaenvsdjango2libsite-packagesdjangodbmodelsquery.py" in __iter__
54. results = compiler.execute_sql(chunked_fetch=self.chunked_fetch, chunk_size=self.chunk_size)
File "C:Usersuserinstalledanacondaenvsdjango2libsite-packagesdjangodbmodelssqlcompiler.py" in execute_sql
1065. cursor.execute(sql, params)
File "C:Usersuserinstalledanacondaenvsdjango2libsite-packagesdebug_toolbarpanelssqltracking.py" in execute
186. return self._record(self.cursor.execute, sql, params)
File "C:Usersuserinstalledanacondaenvsdjango2libsite-packagesdebug_toolbarpanelssqltracking.py" in _record
124. return method(sql, params)
File "C:Usersuserinstalledanacondaenvsdjango2libsite-packagesdjangodbbackendsutils.py" in execute
100. return super().execute(sql, params)
File "C:Usersuserinstalledanacondaenvsdjango2libsite-packagesdjangodbbackendsutils.py" in execute
68. return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
File "C:Usersuserinstalledanacondaenvsdjango2libsite-packagesdjangodbbackendsutils.py" in _execute_with_wrappers
77. return executor(sql, params, many, context)
File "C:Usersuserinstalledanacondaenvsdjango2libsite-packagesdjangodbbackendsutils.py" in _execute
85. return self.cursor.execute(sql, params)
File "C:Usersuserinstalledanacondaenvsdjango2libsite-packagesdjangodbutils.py" in __exit__
89. raise dj_exc_value.with_traceback(traceback) from exc_value
File "C:Usersuserinstalledanacondaenvsdjango2libsite-packagesdjangodbbackendsutils.py" in _execute
85. return self.cursor.execute(sql, params)
File "C:Usersuserinstalledanacondaenvsdjango2libsite-packagessql_serverpyodbcbase.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)')
它看起来并未添加到 django-pyodbc-azure
:
https://github.com/michiya/django-pyodbc-azure/blob/azure-2.1/sql_server/pyodbc/features.py#l4
我在这里添加了我的叉子:
https://github.com/flipperpa/django-pyodbc-azure/blob/azure-2.1/sql_server/pyodbc/features.py#l33
您可以进行测试吗?您需要升级到Django 2.1,然后您应该能够pip install git+https://github.com/FlipperPA/django-pyodbc-azure.git@azure-2.1
查看它是否像添加该设置一样直截了当。粗略地看着Django的来源,看起来可能就是全部。如果有效,我将向Michaya发出拉动请求。