Error "ODBC data type -150 is not supported" when connecting sqlalchemy to mssql

Question

I keep running into an odd error when attempting to connect python sqlalchemy to a msssql server/database. I need to use sqlalchemy as it is (from what I've been told) the only way to connect pandas dataframes to mssql.

I have tried connecting sqlalchemy two different ways:

1. using full connection string:

   import sqlalchemy as sa
   import urllib.parse as ulp
   
   usrCnnStr = r'DRIVER={SQL Server};SERVER=myVoid\MYINSTANCE;Trusted_Connection=yes;'
   usrCnnStr = ulp.quote_plus(usrCnnStr)
   usrCnnStr = "mssql+pyodbc:///?odbc_connect=%s" % usrCnnStr
   
   engine = sa.create_engine(usrCnnStr)
   connection = engine.connect()
   connection.execute("select getdate() as dt from mydb.dbo.dk_rcdtag")
   connection.close()
   

2. using DSN:

   import sqlalchemy as sa
   import urllib.parse as ulp
   
   usrDsn = 'myDb'
   params = ulp.quote_plus(usrDsn)
   engine = sa.create_engine("mssql+pyodbc://cryo:pass@myDb")
   conn = engine.connect()
   conn.execute('select getdate() as dt')
   conn.close()
   

Both methods return the same error:

sqlalchemy.exc.DBAPIError: (pyodbc.Error) ('ODBC data type -150 is not supported.  Cannot read column .', 'HY000') [SQL: "SELECT  SERVERPROPERTY('ProductVersion')"]

I am not sure how to get around this error; when I execute the "SELECT SERVERPROPERTY('ProductVersion')" in mssql, it works fine but comes back with a data type of "sql_variant".

Is there any way to get around this?

Answer 1

I upgraded to sqlalchemy 1.1 today and ran into a similar issue with connections that were working before. Bumped back to 1.0.15 and no problems. Not the best answer, more of a workaround, but it may work if you are on 1.1 and need to get rolling.

If you are unsure of your version:

>>import sqlalchemy
>>sqlalchemy.__version__