1

I am trying to query an instance of a mapped class with sqlalchemy that contains a column with a sql_variant data type. However, when executing my query, I get the following error:

sqlalchemy.exc.ProgrammingError: (pyodbc.ProgrammingError) ('ODBC SQL type -150 is not yet supported.  column-index=3  type=-150', 'HY106')

My mapped class goes like this:

class ParamValue(Base):
    __table__ = Table('param', autoload=True)

It contains 4 columns:

id (PK, int, not null)
param_set_id (FK, int, not null)
param_id (FK, int, not null)
value (sql_variant, null)

And my query looks like this (simple query):

session.query(ParamValue).all()

Anything could help, thanks

Eric B
  • 1,635
  • 4
  • 13
  • 27
  • 2
    Related https://stackoverflow.com/questions/17354042/error-loading-sql-variant-data-type-using-python – Ilja Everilä Dec 03 '17 at 16:16
  • Thank you! Yeah I saw that question but it was left unanswered..! I saw that `sqlalchemy` has a `SQL_VARIANT` object, so what I will try to do is to redefine completely the columns of my `mapped class` and force the column to `SQL_VARIANT` datatype. Will post here if I find anything interesting. – Eric B Dec 03 '17 at 17:35
  • One option might be to `CREATE VIEW param_view AS SELECT id, param_set_id, param_id, CONVERT(NVARCHAR, value) AS value FROM param` on the server and then use `__table__ = Table('param_view', autoload=True)` – Gord Thompson Dec 04 '17 at 01:02
  • Alright, I will look into this tomorrow. I ran some additional tests today and could not figure out so this might help. I ran into similar issues trying to use `pandas` `read_sql` method. – Eric B Dec 04 '17 at 04:13

0 Answers0