Python 3 unicode problem in CursorWrapper.format_sql.

Hello,

I have been trying to get django-pyodbc working to connect to a SQL Server 2000 MSDE instance. I am using Django 1.6.1, Python 3.3 on Ubuntu 12.04.

Trying to do a simple operation such as

from django.db import connections
cursor = connections['default'].cursor()
result = cursor.execute('select * from customer')

causes the following to happen

File "/.../python3.3/dist-packages/django_pyodbc/base.py", line 410, in execute
    return self.cursor.execute(sql, params)
TypeError: The first argument to execute must be a string or unicode query.

Someone else experienced the same problem except they were running on Windows. They asked a question on StackOverflow here:

http://stackoverflow.com/questions/21272895/cant-query-sql-server-from-django-using-django-pyodbc

I tracked the problem down to line 367 in base.py:

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

    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')

It seems that in Python 3, str.encode returns a bytes which is not a string type causing the TypeError to occur.

See this comment on StackOverflow for information on how the string/bytes changed from Python 2 to 3.

http://stackoverflow.com/a/11596746/1040695

This only seems to occur for the first query getting the product version. I need to do more analysis to see if happens later on.

  File "/.../python3.3/dist-packages/django/db/backends/__init__.py", line 159, in cursor
    cursor = util.CursorWrapper(self._cursor(), self)
  File "/.../python3.3/dist-packages/django_pyodbc/base.py", line 290, in _cursor
    if self.ops.sql_server_ver < 2005:
  File "/.../python3.3/dist-packages/django_pyodbc/operations.py", line 31, in _get_sql_server_ver
    cur.execute("SELECT CAST(SERVERPROPERTY('ProductVersion') as varchar)")
  File "/.../python3.3/dist-packages/django/db/backends/util.py", line 51, in execute
    return self.cursor.execute(sql)
  File "/.../python3.3/dist-packages/django_pyodbc/base.py", line 410, in execute
    return self.cursor.execute(sql, params)
TypeError: The first argument to execute must be a string or unicode query.

Anyway, my quick fix was to comment out lines 364-367 in base.py.

Alternatively, the bytes could be converted back to a string by changing line 367 to

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

I hope this helps someone workaround this bug. I don't know enough about django-pyodbc to be able to fix this properly.

Michael.

Any update on this?

[adriannye]

The problem is still present with django 1.7.7 and python 3.4 on MacOSX, connecting to MSSQL 2004.
Using django-pyodbc 0.2.6

[adriannye]

I tried the above suggestion to comment out the utf-8 encoding, and it did not solve the problem. After that change the query for the product version returns None, which breaks the code in operations.py line 67 that branches according to MSSQL version.

[theoriginalgri]

I just tried for curiosity and got no problem using django-pyodbc 0.3.0 with python 3.4, Mac OSX, freetds 0.95.21, pyodbc 3.0.10 and django 1.7.10 when connecting to mssql express 2014.

First the same error in format_sql you cite above happened but setting driver_supports_utf8=True in the database connection OPTIONS solved it.

Strange errors like corrupted transactions or even not being able to install django-pyodbc using pip happened on ubuntu server 14.04.3 but that seems to be another story 😕

[codyc4321]

I would like to fix pyodbc package to support Python 3 and I will open PR when it's ready. Can any maintainers please prime me on what process to take as I attempt this, thank you

[RossRogers]

We have the test suite for django 1.4, 1.5, and 1.6. They may be old, but they exercise the codebase. Do the conversion and see what shakes out from those tests. Then, submit a pull request and we will review it.

[codyc4321]

Thanks mr Rogers
On Apr 12, 2016 8:58 AM, "Ross Rogers" notifications@github.com wrote:

We have the test suite for django 1.4, 1.5, and 1.6. They may be old, but
they exercise the codebase. Do the conversion and see what shakes out from
those tests. Then, submit a pull request and we will review it.

—
You are receiving this because you commented.
Reply to this email directly or view it on GitHub
#47 (comment)

[RossRogers]

No problem.

[codyc4321]

hey, this is github, no jokes allowed :)

[codyc4321]

Christoph, I was assigned this ticket at work and on django 1.7.7, python 3.4 on ubuntu 14.04, connecting to MSSQL 2004 using django-pyodbc 0.2.6 (same stack as Adrian except I use ubuntu) I was able to get our API running with only 'driver_supports_utf8': True,'

I saw a second solution suggesting
'autocommit': True,
'unicode_results': True,

which doesn't break it but doesn't fix it without your driver_supports_utf8 suggestion. I would close this ticket with an explanation to explicitly set driver_supports_utf8=True, I don't think this is a bug

I saw gonna dive in and change your source but changing this in settings.py was sufficient

[devanroberts]

I'm struggling to get a project going. I'm new to python and django.

Is django_pyodbc supported on Python 3.5.1 and django 1.9? If not could you suggest a combination of python & django that is known to work with MS SQL Server 2012?

Here are the details: I've set up a virtual environment with windows 7 64 bit, python 3.5.1, and these packages installed:

Django (1.9)
django-pyodbc (0.4.1)
pip (8.1.2)
pyodbc (3.0.10)
pywin32 (219)
setuptools (19.1)
wheel (0.26.0)

The database definition in settings.py:

DATABASES = {
    'default': {
        'ENGINE': 'django_pyodbc',
        'HOST': '192.168.10.47',
        'PORT': '1433',
        'USER': '*********',
        'PASSWORD': '*******',
        'NAME': 'my_db',
        'OPTIONS' : {
            'driver' : 'SQL Server Native Client 11.0',
            'MARS_Connection' : True,
            'driver_supports_utf8' : True,
        },
    }
}

When I try to run the development server I'm getting these errors:

(my_db) PS F:\my_db\my_db> python .\manage.py runserver
Performing system checks...

System check identified no issues (0 silenced).
DRIVER={SQL Server Native Client 11.0};SERVER=192.168.10.47;PORT=1433;UID=*****;PWD=******;DATABASE=my_db;MARS_Connection=yes
Unhandled exception in thread started by <function check_errors.<locals>.wrapper at 0x0000000004000D90>
Traceback (most recent call last):
  File "C:\Users\eroberts\AppData\Local\Programs\Python\Python35\lib\site-packages\django\utils\autoreload.py", line 226, in wrapper
    fn(*args, **kwargs)
  File "C:\Users\eroberts\AppData\Local\Programs\Python\Python35\lib\site-packages\django\core\management\commands\runserver.py", line 117, in inner_run
    self.check_migrations()
  File "C:\Users\eroberts\AppData\Local\Programs\Python\Python35\lib\site-packages\django\core\management\commands\runserver.py", line 163, in check_migrations
    executor = MigrationExecutor(connections[DEFAULT_DB_ALIAS])
  File "C:\Users\eroberts\AppData\Local\Programs\Python\Python35\lib\site-packages\django\db\migrations\executor.py", line 20, in __init__
    self.loader = MigrationLoader(self.connection)
  File "C:\Users\eroberts\AppData\Local\Programs\Python\Python35\lib\site-packages\django\db\migrations\loader.py", line 49, in __init__
    self.build_graph()
  File "C:\Users\eroberts\AppData\Local\Programs\Python\Python35\lib\site-packages\django\db\migrations\loader.py", line 176, in build_graph
    self.applied_migrations = recorder.applied_migrations()
  File "C:\Users\eroberts\AppData\Local\Programs\Python\Python35\lib\site-packages\django\db\migrations\recorder.py", line 65, in applied_migrations
    self.ensure_schema()
  File "C:\Users\eroberts\AppData\Local\Programs\Python\Python35\lib\site-packages\django\db\migrations\recorder.py", line 56, in ensure_schema
    with self.connection.schema_editor() as editor:
  File "C:\Users\eroberts\AppData\Local\Programs\Python\Python35\lib\site-packages\django\db\backends\base\base.py", line 604, in schema_editor
    'The SchemaEditorClass attribute of this database wrapper is still None')
NotImplementedError: The SchemaEditorClass attribute of this database wrapper is still None

[alokshenoy]

Similar setup (not same) as @devanroberts here. Same error as his (NotImplementedError....).

[bartmika]

Major +1