Can't connect to SQL Server DB from Pyodbc

Question

I am having a difficult time trying to connect to a SQL Server DB on Linux, using pyodbc. I have a ODCINI file entry created. I started with this:

import pyodbc

conn = pyodbc.connect('DSN=DSN;Database=DB;UID=UID;PWD=PWD')

cursor = conn.cursor()
cursor.execute('SELECT count(*) FROM dbo.tableA')
for row in cursor.fetchall():
    print(row)

which throws this error:

RuntimeError: Unable to set SQL_ATTR_CONNECTION_POOLING attribute.

I googled that error and added this line after reading some recommendations:

pyodbc.pooling=False

So script changed to this:

import pyodbc

pyodbc.pooling=False

conn = pyodbc.connect('DSN=DSN;Database=DB;UID=UID;PWD=PWD')

cursor = conn.cursor()
cursor.execute('SELECT count(*) FROM dbo.tableA')
for row in cursor.fetchall():
    print(row)

Which resulted in this:

pyodbc.InterfaceError: ('IM003', '[IM003] 䑛瑡䑡物捥嵴佛䉄⁃楬嵢匠数楣楦摥搠楲敶\u2072潣汵\u2064潮⁴敢氠慯敤d\uffff\uffff㢸ꔻ罱\x00\ue5b8鮫罱\x00㳰ꔻ罱\x00\uffff\uffff罱\x00\x00\x00\x00\x00鳭ꕞ罱\x00塰ꕉ罱 (0) (SQLDriverConnect)')

At the suggestion of a coworker I added these 2 lines AFTER the pyodbc.connect line:

conn.setdecoding(pyodbc.SQL_CHAR, encoding='latin1', to=str)
conn.setencoding(str, encoding='latin1')

I tried that with both latin1 and utf-8. Neither work, still throws the same interface error with Chinese characters.

Any ideas?

Answer 1

I had the similar issue with same description RuntimeError: Unable to set SQL_ATTR_CONNECTION_POOLING attribute. I had no clue what is happening and why is happening. After lot of debugging i was able to figure it out why.

Simple answer is :
Reinstall the unixODBC drivers or/and SQL drivers.

Reason why :
When install the ODBC Drivers first and then SQL related drivers, sometimes it override the symlinks in Unix system. You can find out more info on this from pyodbc official GitHub issue#847 .

Answer 2

you can simply uninstall and then do:

conda install unixodbc