cryocaustik cryocaustik - 1 year ago 193
Python Question

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

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")

  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')

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 Source

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
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download