aidan aidan - 7 months ago 60
Python Question

Can't query SQL Server from django using django-pyodbc

I'm trying to sync an SQL Server 2008 R2 database running remotely on IIS 7, to a django 1.6 app running python 3.3 on Windows 7, using

manage.py syncdb
. However I am being met with the error,

TypeError: The first argument to execute must be a string or unicode query.


I have django-pyodbc 0.2.3 and pyodbc 3.0.7 installed, with my settings.py
DATABASES
as,

{
'default': {
'ENGINE': 'django_pyodbc',
'HOST': '...',
'NAME': '...',
'OPTIONS': {
'host_is_server': True
}
}
}


As you may guess,
USER
and
PASSWORD
are omitted since I need
Integrated_Security=Yes
and
Trusted_Connection=Yes
for the connection.
OPTIONS
appears to have to be non-empty due to the way django-pyodbc initialises the class
DatabaseWrapper
, even though
host_is_server
is irrelevant on Windows.

The full error I'm receiving is:

Traceback (most recent call last):
File "Z:\python\ns_reports_server\manage.py", line 10, in <module>
execute_from_command_line(sys.argv)
File "C:\Python33\lib\site-packages\django-1.6.1-py3.3.egg\django\core\management\__init__.py", line 399, in execute_from_command_line
utility.execute()
File "C:\Python33\lib\site-packages\django-1.6.1-py3.3.egg\django\core\management\__init__.py", line 392, in execute
self.fetch_command(subcommand).run_from_argv(self.argv)
File "C:\Python33\lib\site-packages\django-1.6.1-py3.3.egg\django\core\management\base.py", line 242, in run_from_argv
self.execute(*args, **options.__dict__)
File "C:\Python33\lib\site-packages\django-1.6.1-py3.3.egg\django\core\management\base.py", line 285, in execute
output = self.handle(*args, **options)
File "C:\Python33\lib\site-packages\django-1.6.1-py3.3.egg\django\core\management\base.py", line 415, in handle
return self.handle_noargs(**options)
File "C:\Python33\lib\site-packages\django-1.6.1-py3.3.egg\django\core\management\commands\syncdb.py", line 57, in handle_noargs
cursor = connection.cursor()
File "C:\Python33\lib\site-packages\django-1.6.1-py3.3.egg\django\db\backends\__init__.py", line 157, in cursor
cursor = self.make_debug_cursor(self._cursor())
File "C:\Python33\lib\site-packages\django_pyodbc-0.2.3-py3.3.egg\django_pyodbc\base.py", line 290, in _cursor
File "C:\Python33\lib\site-packages\django_pyodbc-0.2.3-py3.3.egg\django_pyodbc\operations.py", line 31, in _get_sql_server_ver
File "C:\Python33\lib\site-packages\django-1.6.1-py3.3.egg\django\db\backends\util.py", line 69, in execute
return super(CursorDebugWrapper, self).execute(sql, params)
File "C:\Python33\lib\site-packages\django-1.6.1-py3.3.egg\django\db\backends\util.py", line 51, in execute
return self.cursor.execute(sql)
File "C:\Python33\lib\site-packages\django_pyodbc-0.2.3-py3.3.egg\django_pyodbc\base.py", line 410, in execute
TypeError: The first argument to execute must be a string or unicode query.


If you look at the source code for the tenth call from the top, django_pyodbc/operations.py is querying the connection for the SQL Server version,

cur.execute("SELECT CAST(SERVERPROPERTY('ProductVersion') as varchar)")


Yet, by the end of the call stack, this sql to be executed has disappeared. django_pyodbc/base.py has,

return self.cursor.execute(sql, params)


for which the first argument is not being recognised as a 'string or unicode query'.

Python, django, and SQL Server are all new for me so the answer might be obvious, but I can't for the life of me work it out. Cheers.

Answer

It looks like this is problem with django-pyodbc and Python 3.

In base.py, line 367 is

sql = sql.encode('utf-8')

This line turns a string into bytes which is what is causing the TypeError. As you are on Windows, I am assuming the driver can handle unicode. I suggest you comment out lines 364 to 367 (the first 4 in the format_sql function). This way your unicode strings will stay unicode and you won't get the TypeError.

https://github.com/lionheart/django-pyodbc/blob/master/django_pyodbc/base.py

def format_sql(self, sql, n_params=None):
    if not self.driver_supports_utf8 and isinstance(sql, text_type):
        # Older FreeTDS (and other ODBC drivers?) don't support Unicode yet, so
        # we need to encode the SQL clause itself in utf-8
        sql = sql.encode('utf-8')
    # pyodbc uses '?' instead of '%s' as parameter placeholder.
    if n_params is not None:
        try:
            sql = sql % tuple('?' * n_params)
        except:
            #Todo checkout whats happening here
            pass
    else:
        if '%s' in sql:
            sql = sql.replace('%s', '?')
    return sql

I have raised an issue with django-pyodbc which goes into a little more detail.

https://github.com/lionheart/django-pyodbc/issues/47

Comments