UnicodeDecodeError while executing query or in jsonpickle

Question

I post an arbitrary query to the server side, where it is executed and the result set is sent back to the client. A typical query looks like this:

select Наименование from sys_Атрибут where Наименование = 'Район'

As you can see, it contains non-Latin literals. This query is not executed. However, if I write it like this

select Наименование AS attr from sys_Атрибут where Наименование = 'Район'

Then, it's ok. The server side code looks like this:

#--coding: utf-8
from __future__ import unicode_literals
...
import pyodbc   # tried both of them
import pypyodbc #

def resultset(request):
    query = request.POST['query']
    query = u'{}'.format(query) 
    cnx = pyodbc.connect("DRIVER=FreeTDS;SERVER=192.168.0.1;PORT=1433;
    DATABASE = mydatabase;UID=sa;PWD=password;TDS_Version=7.0;ClientCharset=UTF8;")
    cursor = cnx.cursor()
    cursor.execute(query.encode('utf-8'))
    columns = [desc[0] for desc in cursor.description] # sometimes error happens at this point
    data = []
    output = []
    for row in cursor:
        data.append(dict(zip(columns, row)))            
    output = '{items:'
    output += jsonpickle.encode(data) # sometimes at that point
    output += '}'   
    return HttpResponse(output)

The whole problem is in the names of table fields. I guess, to solve this problem I should do this part of coding data.append(dict(zip(columns, row))) in a different manner.

Answer 1

To state the obvious, you shouldn't be sending raw queries down to the server. Second, using unicode_literals and u"" strings is strange. Third, using unicode string and then dumping out out as utf-8 is also strange. I'd suggest [reading up on encodings to start](http://kunststube.net/encoding/.

To solve the actual issue that's likely being presented, the fault is likely with the pyodbc library. What database are you connecting to and have you considered using a different driver? If the database supports the query you're trying to execute (select unicode from table where field = 'value') then it's likely the driver mucking it up.