The SQL contains 0 parameter markers, but 1 parameters were supplied

[mpwoodward]

Getting this error on Mac OS 10.9, Python 2.7.5, Django 1.5.4, SQL 2008:

DatabaseError
Exception Value: ('The SQL contains 0 parameter markers, but 1 parameters were supplied', 'HY000')
Exception Location: /Users/mwoodward/.virtualenvs/stockadmin/lib/python2.7/site-packages/django_pyodbc/base.py in execute, line 361

Happens on any view function that has any database interaction (i.e. isn't limited to a specific database operation). Happy to provide additional details as I can.

[dlo]

Sorry about the delay in getting back (I blame the holidays). Can you print the parameters referenced in that exception and your database configuration?

[mpwoodward]

Thanks for getting back to me. The error seems to be thrown on a simple get on a model, e.g. Foo.objects.get(foo='bar').

Here's my db configuration (with some of the real values changed for the purposes of this post):

DATABASES = {
    'default': {
        'ENGINE': 'django_pyodbc',
        'NAME': 'mydb',
        'USER': 'user',
        'PASSWORD': 'pass',
        'HOST': 'mysqlserver.mydomain.com',
        'PORT': 1433,
        'OPTIONS': {
            'driver': 'FreeTDS',
            'host_is_server': True,
            'extra_params': 'TDS_VERSION=8.0'
        }
    }
}

Let me know if that wasn't what you were asking for, and thanks again for the response.

[kvnn]

I am getting the same error while attempting a syncdb. Have you made any progress, @mpwoodward ? Any ideas @dlo ?

→ python manage.py syncdb
Creating tables ...
Creating table django_admin_log
Creating table auth_permission
Creating table auth_group_permissions
Creating table auth_group
Creating table auth_user_groups
Creating table auth_user_user_permissions
Creating table auth_user
Creating table django_content_type
Creating table django_session
Traceback (most recent call last):
  File "manage.py", line 10, in <module>
    execute_from_command_line(sys.argv)
  File "/Users/kriggen/.virtualenvs/pta/lib/python2.7/site-packages/django/core/management/__init__.py", line 399, in execute_from_command_line
    utility.execute()
  File "/Users/kriggen/.virtualenvs/pta/lib/python2.7/site-packages/django/core/management/__init__.py", line 392, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/Users/kriggen/.virtualenvs/pta/lib/python2.7/site-packages/django/core/management/base.py", line 242, in run_from_argv
    self.execute(*args, **options.__dict__)
  File "/Users/kriggen/.virtualenvs/pta/lib/python2.7/site-packages/django/core/management/base.py", line 285, in execute
    output = self.handle(*args, **options)
  File "/Users/kriggen/.virtualenvs/pta/lib/python2.7/site-packages/django/core/management/base.py", line 415, in handle
    return self.handle_noargs(**options)
  File "/Users/kriggen/.virtualenvs/pta/lib/python2.7/site-packages/django/core/management/commands/syncdb.py", line 112, in handle_noargs
    emit_post_sync_signal(created_models, verbosity, interactive, db)
  File "/Users/kriggen/.virtualenvs/pta/lib/python2.7/site-packages/django/core/management/sql.py", line 216, in emit_post_sync_signal
    interactive=interactive, db=db)
  File "/Users/kriggen/.virtualenvs/pta/lib/python2.7/site-packages/django/dispatch/dispatcher.py", line 185, in send
    response = receiver(signal=self, sender=sender, **named)
  File "/Users/kriggen/.virtualenvs/pta/lib/python2.7/site-packages/django/contrib/auth/management/__init__.py", line 82, in create_permissions
    ctype = ContentType.objects.db_manager(db).get_for_model(klass)
  File "/Users/kriggen/.virtualenvs/pta/lib/python2.7/site-packages/django/contrib/contenttypes/models.py", line 47, in get_for_model
    defaults = {'name': smart_text(opts.verbose_name_raw)},
  File "/Users/kriggen/.virtualenvs/pta/lib/python2.7/site-packages/django/db/models/manager.py", line 154, in get_or_create
    return self.get_queryset().get_or_create(**kwargs)
  File "/Users/kriggen/.virtualenvs/pta/lib/python2.7/site-packages/django/db/models/query.py", line 373, in get_or_create
    return self.get(**lookup), False
  File "/Users/kriggen/.virtualenvs/pta/lib/python2.7/site-packages/django/db/models/query.py", line 301, in get
    num = len(clone)
  File "/Users/kriggen/.virtualenvs/pta/lib/python2.7/site-packages/django/db/models/query.py", line 77, in __len__
    self._fetch_all()
  File "/Users/kriggen/.virtualenvs/pta/lib/python2.7/site-packages/django/db/models/query.py", line 854, in _fetch_all
    self._result_cache = list(self.iterator())
  File "/Users/kriggen/.virtualenvs/pta/lib/python2.7/site-packages/django/db/models/query.py", line 220, in iterator
    for row in compiler.results_iter():
  File "/Users/kriggen/.virtualenvs/pta/lib/python2.7/site-packages/django/db/models/sql/compiler.py", line 713, in results_iter
    for rows in self.execute_sql(MULTI):
  File "/Users/kriggen/.virtualenvs/pta/lib/python2.7/site-packages/django/db/models/sql/compiler.py", line 786, in execute_sql
    cursor.execute(sql, params)
  File "/Users/kriggen/.virtualenvs/pta/lib/python2.7/site-packages/django/db/backends/util.py", line 69, in execute
    return super(CursorDebugWrapper, self).execute(sql, params)
  File "/Users/kriggen/.virtualenvs/pta/lib/python2.7/site-packages/django/db/backends/util.py", line 53, in execute
    return self.cursor.execute(sql, params)
  File "/Users/kriggen/.virtualenvs/pta/lib/python2.7/site-packages/django_pyodbc/base.py", line 416, in execute
    raise utils.DatabaseError(*e.args)
django.db.utils.DatabaseError: ('The SQL contains 0 parameter markers, but 2 parameters were supplied', 'HY000')

[raypendergraph]

Not sure if this is the exact scenario but we started getting this error immediately after upgrading to Django 1.6.7 from 1.5 (finally). The feature that was causing this error for us was using the limiting syntax in the following way:

ModelObject.objects.filter(user=request.user).order_by('-time')[1:]

which used to give me all the model objects except the newest one but in 1.6.7 I get this exception. Something in this notation generates some very strange, nested SQL for this simple query. The Django site did not mention the deprecation of any slicing syntax so maybe this is a bug in Django.

[ghost]

Same error in django 1.7 with pyodbc 3.0.8 and django-pyodbc 0.2.5.

[reggieriser]

I think a regression may have been introduced between django-pyodbc versions 0.2.4 and 0.2.5. I did some simple tests using the Django tutorial project. With 0.2.5, the following produce the error mentioned in this thread:

Poll.objects.filter(question__icontains='what')[1:2]
Poll.objects.filter(question__icontains='what')[1:]
Poll.objects.filter(question__icontains='what')[1]

However this worked:

Poll.objects.filter(question__icontains='what')[:2]

Downgrading to 0.2.4, all the above four worked fine.

Using Django 1.6.7, pyodbc 3.0.7, SQL Server 2008 R2, Mac OS X 10.9.5.

[dlo]

Most of the changes between 0.2.4 and 0.2.5 are in this commit: 5edfafa

I think the ideal thing for this project would be to get it in the hands of a maintainer who uses SQL Server on a daily basis--as of late most of my stack has been PostgreSQL-based. If anyone would like to volunteer and / or submits a PR, I'll gladly hand over the reigns.

[reggieriser]

I logged the SQL generated from my earlier examples that failed in 0.2.5. The only difference is this phrase:

0.2.5:
... LIKE UPPER({'_placeholder_1': u'[polls_poll].[question]'}) ...

0.2.4:
... LIKE UPPER(%s) ...

@dlo, I don't have time at the moment to dig into the source, but I'll try to get back to it at some point and see what may have happened. I do appreciate your efforts on this project, though.

[WilliamQLiu]

Setup - I'm experiencing the same issues as above using Python 2.7.8 with the following libraries in my virtualenv. Additional details of my setup are on the answer here (Note: I can connect to the MS SQL Server through isql and tsql so the connection isn't the issue)

Django==1.6.8
django-pyodbc==0.2.6
pyodbc==3.0.7
python-dateutil==2.3
pytz==2014.10
six==1.8.0
wsgiref==0.1.2

Error Message - I can do a 'manage.py runserver' just fine, but ran into the same issue with 'manage.py syncdb' and got this error:

File "/Users/williamliu/.virtualenvs/mha_api/lib/python2.7/site-packages/django_pyodbc/base.py", line 425, in execute
 raise utils.DatabaseError(*e.args)
 django.db.utils.DatabaseError: ('The SQL contains 0 parameter markers, but 2 parameters were supplied', 'HY000')

Troubleshoot - I put a pdb.set_trace() on the line before crashing and here's the values from the CursorWrapper(object) from the execute(self, sql, params=()): function on the file 'django_pyodbc/base.py'.

->raise utils.DatabaseError(*e.args)
(Pdb) l
 420                e = sys.exc_info()[1]
 421                raise utils.IntegrityError(*e.args)
 422            except DatabaseError:
 423                e = sys.exc_info()[1]
 424                pdb.set_trace()
 425  ->                raise utils.DatabaseError(*e.args)
 426
 427        def executemany(self, sql, params_list):
 428            sql = self.format_sql(sql)
 429            # pyodbc's cursor.executemany() doesn't support an empty param_list
 430            if not params_list:
(Pdb) dir()
['e', 'params', 'self', 'sql']
(Pdb) args
self = <django_pyodbc.base.CursorWrapper object at 0x10035e950>
sql = SELECT * FROM SYSIBM.COLUMNS FETCH FIRST 1 ROWS ONLY
params = ()
(Pdb) print e
('42000', '[42000] [FreeTDS][SQL Server]Invalid usage of the option FIRST in the FETCH statement. (153) (SQLExecDirectW)')

Looking up this type of error here I see that SQLState 42000 has error state 'Syntax error or access violation' with a description of:

 (*StatementText contained an SQL statement that was not preparable or contained a syntax error.
The user did not have permission to execute the SQL statement contained in *StatementText.)

So I'm not sure where to go from here. Any suggestions? Thanks!

[adriannye]

I also have this problem on Mac running OSX 10.9. I can access the db using tsql and isql.
I have the problem with one REST api call to a single database, but another REST api call to a separate Microsoft SQL database is working properly.

I've been able to narrow it down some:

• The same code with a virtualenv containing all the same package versions works fine on redhat linux (both REST calls).
• with the broken REST call on Mac, using a parameter that causes a filtered query set, I'm seeing 'SQL contains 0 parameter markers, but 1 parameters was supplied' and the query and parameter are both valid. This message comes back from MSSQL so for some reason it is not getting the SQL in the proper format.
• with the broken REST call on Mac using an unfiltered queryset, I get data back but all the strings have '\u0000' between each character.
• I've tried several different versions of django, django-pyodbc, and pyodbc and the problem was present in all.

I believe the problem is likely a double conversion to/from utf-8 somewhere in the code, but I'm not really sure which layer to look in.

[dlo]

@adriannye thanks for looking into this. if you have any ideas, or have the capability to test out some hypotheses, please do if you have the time. I no longer am working on any projects with a SQL Server DB, so it's a bit tough for me to make any progress here :)

[adriannye]

A little more information...the working web service is accessing SQL Server 2005, while the broken one is accessing SQL Server 2008.

[adriannye]

Seems the issue is a regression in pyodbc. On Mac only it returns UTF16 in a case that should be UTF8. I changed a cpp file in the pyodbc 3.0.7 source and everything now works for me. See this ticket:

https://code.google.com/p/pyodbc/issues/detail?can=2&start=0&num=100&q=&colspec=ID%20Type%20Status%20Priority%20Milestone%20Owner%20Summary&groupby=&sort=&id=78