Django: 1.11
I am using django-pyodbc-azure as my Django backend. It is connecting to SQL Server 2012. SQL Server has a limit of 2,100 parameters. When I attempt to use prefetch_related on a queryset that will return more than 2,100 results...
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 error is returned:
Exception Value: ('07002', '[07002] [Microsoft][SQL Server Native Client 11.0]COUNT field incorrect or syntax error (0) (SQLExecDirectW)')
It points to the pyodbc backend used by django-pyodbc-azure. When looking at the features (https://github.com/michiya/django-pyodbc-azure/blob/azure-2.1/sql_server/pyodbc/features.py), there is no max_query_params set. I attempted to manually add max_query_params = 2100. This resulted in the same error. Setting that value to 2000 and 1000 resulted in the same error also.
How do I adjust django-pyodbc-azure to automatically handle this SQL Server limitation?
The Oracle backend for Django has a feature like this: https://github.com/django/django/blob/master/django/db/backends/oracle/features.py
UPDATE:
I realized that the max_query_params name was new to Django 2.0. Therefore, I tried the max_limit_in_size to match Django 1.11. Is it simply that django-pyodbc-azure does not support controlling parameter counts and the base.py overrides Django's default base.py?
UPDATE 2:
I upgraded to Django 2.1 and upgraded django-pyodbc-azure to match. I then edited features.py to add max_query_params = 2000 as a feature. The other place where max_query_params appears in Django native backends is operations.py in this function (Oracle example):
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 has this function also, and it looks like this:
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
It seems like it would already take care of batching with max_row_values set to 1000. However, the same error persists. I also tried changing the 1000 to max_query_params to no avail.
Here's the full traceback:
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)')
It doesn't look like it has been added to
django-pyodbc-azure:https://github.com/michiya/django-pyodbc-azure/blob/azure-2.1/sql_server/pyodbc/features.py#L4
I've added it to my fork here:
https://github.com/FlipperPA/django-pyodbc-azure/blob/azure-2.1/sql_server/pyodbc/features.py#L33
Can you give it a test? You'll need to upgrade to Django 2.1, and then you should be able to
pip install git+https://github.com/FlipperPA/[email protected]to see if it is as straight forward as adding that setting. At a cursory glance of Django's source, it looks like that might be all it takes. If it works, I'll issue Michaya a pull request.