Error "ODBC data type -150 is not supported" when connecting sqlalchemy to mssql
<p>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. </p>

<p>I have tried connecting sqlalchemy two different ways:</p>

<ol>
<li><p>using full connection string:</p>

<pre><code>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()
</code></pre></li>
<li><p>using DSN:</p>

<pre><code>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()
</code></pre></li>
</ol>

<p>Both methods return the same error: </p>

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

<p>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". </p>

<p>Is there any way to get around this? </p>

<p>This is most certainly a bug introduced in <a href="https://bitbucket.org/zzzeek/sqlalchemy/issues/3814">Issue 3814</a>, new in SQLAlchemy 1.1.0, where they introduce <code>SELECT  SERVERPROPERTY('ProductVersion')</code> to fetch server version for the pyodbc MSSQL driver. Downgrading to 1.0.15 will make the code work again, but hopefully the SQLAlchemy devs will make the new version lookup scheme work better in a new patch release.</p>

<p>(There is <a href="https://bitbucket.org/zzzeek/sqlalchemy/issues/3820/connection-fails-against-sql-server-2016">an issue</a> already reported in the SQLAlchemy issue tracker, I would add this comment there, but bitbucket can't log me in.)</p>

python sql-server sqlalchemy pyodbc