[Django]-Can't query SQL Server from django using django-pyodbc

by

2đź‘Ť

âś…

Edit: driver_supports_utf8=True as mention in other answers would be the correct fix.

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

👤Michael Miller

2đź‘Ť

You can also fix this in your configuration options. I fought with this for a while. Try changing your DB to be config’d like this for 1.6:

DATABASES = {
    'default': {
        'ENGINE': 'django_pyodbc',
        'NAME': 'db_name',
        'USER': 'db_user',
        'PASSWORD': 'your_password',            
        'HOST': 'database.domain.com,1433',
        'PORT': '1433',
        'OPTIONS': {
            'host_is_server': True,
            'autocommit': True,
            'unicode_results': True,
            'extra_params': 'tds_version=8.0'
        },
    }
}

If you’re on Windows, the “extra_params” gets ignored, but it makes it portable to Linux.

👤FlipperPA

0đź‘Ť

This issue solved for us by adding 'driver_supports_utf8': True,'

'characteristics': {
    'ENGINE': "django_pyodbc",
    'HOST': "ourdbhost.com",
    'PORT': 5555,
    'NAME': "OurDB",
    'USER': "ouruser",
    'PASSWORD': "acoolpw",
    'OPTIONS': {
        'driver_supports_utf8': True,
        'host_is_server': True,
        'extra_params': 'TDS_Version=7.1',
    },
},

worked as suggested by gri on https://github.com/lionheart/django-pyodbc/issues/47

adding 

        'autocommit': True,
        'unicode_results': True,

to OPTIONS did not fix it, but did not break anything either

👤codyc4321

Leave a comment