SQLAlchemy returning strings instead of datetime objects

Question

I'm using SQLAlchemy to connect to a SQL Server database. The table I'm querying has a DATETIME column. On Windows, the resulting attribute on the SQLAlchemy object is a Python datetime object. On Linux, it's a string.

My SQLAlchemy class looks like this

class MyTable(Model):
    id_ = db.Column('Id', db.Integer, primary_key=True, index=True)
    as_of_date = db.Column('AsOfDate', db.DateTime, nullable=False, server_default=db.FetchedValue())
    # Other columns

On Windows, I get this

>>db.session.query(MyTable.as_of_date).first()
datetime.datetime(2006, 11, 30, 0, 0)

On Linux, I get this

>>db.session.query(MyTable.as_of_date).first()
('2006-11-30 00:00:00.00000000',)

My connection strings look like this

Linux
mssql+pyodbc:///?odbc_connect=DRIVER={FreeTDS};Server=my_server;Port=1433;Database=my_database;UID=my_user;PWD=my_password;TDS_Version=8.0;

Windows
mssql+pyodbc://my_user:my_password@my_server/my_database?driver=SQL Server Native Client 11.0

I'm guessing this has something to do with the FreeTDS driver. Although, SQLAlchemy documentation states that

DATE and TIME are supported. Bind parameters are converted to datetime.datetime() objects as required by most MSSQL drivers, and results are processed from strings if needed. The DATE and TIME types are not available for MSSQL 2005 and previous - if a server version below 2008 is detected, DDL for these types will be issued as DATETIME.

How can I fix this so that the Linux calls return datetime objects?

Answer 1

The issue was that the column was actually DATETIME2 (not DATETIME) and the Linux box had FreeTDS 0.91 installed. In order for FreeTDS to fully support DATETIME2 columns you need to be running FreeTDS 0.95 or newer and using TDS protocol version 7.3 or 7.4.

(Note that TDS_Version "8.0" is really just an alias for version 7.1, so it is not newer than 7.3. Ref: here.)

Of course, your other option is to switch from FreeTDS_ODBC to the Microsoft ODBC Driver for SQL Server on Linux. That, combined with pyodbc, is a configuration that is officially supported by Microsoft.