Cursor description do not show columns name

Question

I have a web2py API application that I recently upgraded from python 2.7.x to python 3.6.3. The API functions queries a Microsoft SQL database.

When I execute a SQL statement with the option 'as_dict=True', the cursor object use its description attribute to get the columns names from the db table to do some checks, but for some reason, the columns names returned from the database are 'corrupted'.

for example:

DB table:

webDataTbl:
_key      |      _value

WEB_NAME        NAME
WEB_VER         '1.2.3'

SP:

SELECT * FROM  webDataTbl WHERE _key LIKE 'WEB_%'

The function in web2py:

sql_cmd = ("EXEC getWebData")
result = db.executesql(sql_cmd, as_dict=True)
return {"result": result}

executesql method executing the following code (when as_dict=True):

columns = adapter.cursor.description
fields = colnames or [f[0] for f in columns]
if len(fields) != len(set(fields))
    raise RuntimeError

As source code states, cursor.description returns a list of 7-item tuples, one tuple for each column returned from the DB, when the first field is the column name (or alias). But, when I print the tuple, I get this:

[(b'c', <class 'str'>, 64, 64, 64, 0, False), (b'c', <class 'str'>, 256, 256, 256, 0, True)]

I thought maybe the issue is DB encoding or something, but it didnt help changing the encoding in the sql connection. I haven't change anything in my code, DB data, SP etc. The only thing I did is to upgrade the version. notice that the data that fetched is OK. When I don't use the 'as_dict' option and the curser doesn't use the description attribute, everything works fine.

Answer 1

I had exactly the same problem trying to do the same, PY27-> PY38 and Web2PY version update 2.14-> 2.21 . Web2py apparently has his own pyodbc. As I connected, I did not realize this, until I saw that I did not have installed in the system (FreeBSD) pyodbc. Once installed everything worked correctly

Jose

EDIT: What I tried to explain here is that the error happens because Web2py has (at least I think) an outdated pyodbc driver. The solution is install in the system or virtualenv pyodbc.