How to handle SQL_VARIANT data type in SQLAlchemy?

My need is to fetch all data from a table created in sql_server through python. The issue I faced is that the datatype of one column in sql_server is of "sql_variant", After search/R&D on sql_varinat data type, I came to know that the "sql_variant is able to hold any of the data(date, datetime, string, int, float).

In my case the column contain datetime, int and float numbers.

So when i try to fetch the data in python, I got the below error:- sqlalchemy.exc.ProgrammingError: (pyodbc.ProgrammingError) ('ODBC SQL type -150 is not yet supported. column-index=6 type=-150', 'HY106') (Background on this error at: https://sqlalche.me/e/20/f405)

I had tried all of the datatype(string, int, float, datatime) in python to fetch the data. But none of them is worked.

The sql_server code is:- create table table_name( [fan_attribute.value] sql_variant NULL );

The python code is:- from sqlalchemy import Column, Integer, String, DateTime from sqlalchemy.ext.declarative import declarative_base

from datetime import datetime
from sqlmodel import Field, PrimaryKeyConstraint, SQLModel


Base = declarative_base()

class ModelName(Base):
    __table_args__ = ({"schema": "can't disclose"})
    __tablename__ = "table_name"
    value = Column("fan_attribute.value", String)

Is there any way to fetch the sql_variant column in python.

Solution

As stated in the question, pyodbc is currently unable to retrieve SQL_VARIANT columns. The issue is discussed on GitHub here, and a patch has been submitted here but it has not yet been merged.

pymssql does not throw an exception when accessing SQL_VARIANT columns, but it suffers from the same issue as pyodbc in the issue cited above in that it only returns raw bytes with no indication of what the actual (variant) data type is:

from sqlalchemy import create_engine

connection_url = "mssql+pymssql://scott:tiger^5HHH@192.168.0.199/test"
engine = create_engine(connection_url)

with engine.begin() as conn:
    conn.exec_driver_sql("DROP TABLE IF EXISTS sqlvariant_test")
    conn.exec_driver_sql("CREATE TABLE sqlvariant_test (val_type varchar(20) primary key, val sql_variant)")
    conn.exec_driver_sql("INSERT INTO sqlvariant_test (val_type, val) VALUES ('datetime2', CAST('2024-08-25 12:13:14.01234' AS datetime2))")
    conn.exec_driver_sql("INSERT INTO sqlvariant_test (val_type, val) VALUES ('int', 1685221191)")
    conn.exec_driver_sql("INSERT INTO sqlvariant_test (val_type, val) VALUES ('varchar', 'Gord')")

with engine.begin() as conn:
    result = conn.exec_driver_sql("SELECT val_type, val FROM sqlvariant_test")
    for row in result:
        print(row.val)
"""
b'\x08\x8bynf\x00\x00\x00\xd7\xb1\x00\x00\x00\x00\x07`'
b'Gord'
b'Gord'
"""

However, the sqlalchemy-pytds dialect does seem to return the correct types:

from sqlalchemy import create_engine

connection_url = "mssql+pytds://scott:tiger^5HHH@192.168.0.199/test"
engine = create_engine(connection_url)

with engine.begin() as conn:
    conn.exec_driver_sql("DROP TABLE IF EXISTS sqlvariant_test")
    conn.exec_driver_sql("CREATE TABLE sqlvariant_test (val_type varchar(20) primary key, val sql_variant)")
    conn.exec_driver_sql("INSERT INTO sqlvariant_test (val_type, val) VALUES ('datetime2', CAST('2024-08-25 12:13:14.01234' AS datetime2))")
    conn.exec_driver_sql("INSERT INTO sqlvariant_test (val_type, val) VALUES ('int', 1685221191)")
    conn.exec_driver_sql("INSERT INTO sqlvariant_test (val_type, val) VALUES ('varchar', 'Gord')")

with engine.begin() as conn:
    result = conn.exec_driver_sql("SELECT val_type, val FROM sqlvariant_test")
    for row in result:
        print(row.val)
"""
2024-08-25 12:13:14.012340
1685221191
Gord
"""

Note that the sqlalchemy-pytds dialect is not currently tested or endorsed by the SQLAlchemy team.