sqlalchemy date type in 0.6 migration using mssql

Question

I'm connection to mssql server through pyodbc, via FreeTDS odbc driver, on linux ubuntu 10.04.

Sqlalchemy 0.5 uses DATETIME for sqlalchemy.Date() fields.

Now Sqlalchemy 0.6 uses DATE, but sql server 2000 doesn't have a DATE type. How can I make DATETIME be the default for sqlalchemy.Date() on sqlalchemy 0.6 mssql+pyodbc dialect?

I'd like to keep it as clean as possible.

Here's code to reproduce the issue:

import sqlalchemy
from sqlalchemy import Table, Column, MetaData, Date, Integer, create_engine

engine = create_engine(
    'mssql+pyodbc://sa:sa@myserver/mydb?driver=FreeTDS')

m = MetaData(bind=engine)

tb = sqlalchemy.Table('test_date', m, 
    Column('id', Integer, primary_key=True),
    Column('dt', Date())
)
tb.create()

And here is the traceback I'm getting:

Traceback (most recent call last):
  File "/tmp/teste.py", line 15, in <module>
    tb.create()
  File "/home/nosklo/.local/lib/python2.6/site-packages/sqlalchemy/schema.py", line 428, in create
    bind.create(self, checkfirst=checkfirst)
  File "/home/nosklo/.local/lib/python2.6/site-packages/sqlalchemy/engine/base.py", line 1647, in create
    connection=connection, **kwargs)
  File "/home/nosklo/.local/lib/python2.6/site-packages/sqlalchemy/engine/base.py", line 1682, in _run_visitor
    **kwargs).traverse_single(element)
  File "/home/nosklo/.local/lib/python2.6/site-packages/sqlalchemy/sql/visitors.py", line 77, in traverse_single
    return meth(obj, **kw)
  File "/home/nosklo/.local/lib/python2.6/site-packages/sqlalchemy/engine/ddl.py", line 58, in visit_table
    self.connection.execute(schema.CreateTable(table))
  File "/home/nosklo/.local/lib/python2.6/site-packages/sqlalchemy/engine/base.py", line 1157, in execute
    params)
  File "/home/nosklo/.local/lib/python2.6/site-packages/sqlalchemy/engine/base.py", line 1210, in _execute_ddl
    return self.__execute_context(context)
  File "/home/nosklo/.local/lib/python2.6/site-packages/sqlalchemy/engine/base.py", line 1268, in __execute_context
    context.parameters[0], context=context)
  File "/home/nosklo/.local/lib/python2.6/site-packages/sqlalchemy/engine/base.py", line 1367, in _cursor_execute
    context)
  File "/home/nosklo/.local/lib/python2.6/site-packages/sqlalchemy/engine/base.py", line 1360, in _cursor_execute
    context)
  File "/home/nosklo/.local/lib/python2.6/site-packages/sqlalchemy/engine/default.py", line 277, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (ProgrammingError) 
('42000', '[42000] [FreeTDS][SQL Server]Column or parameter #2: 
Cannot find data type DATE. (2715) 
(SQLExecDirectW)') 
'\nCREATE TABLE test_date (\n\tid INTEGER NOT NULL IDENTITY(1,1), 
\n\tdt DATE NULL, \n\tPRIMARY KEY (id)\n)\n\n' ()

Answer 1

This situation is supposed to be properly handled by sqlalchemy. See MS SQL - Date / Time Handling.

You can also this the extract of the implementation that handles this (see mssql\base.py):

def visit_date(self, type_):
    if self.dialect.server_version_info < MS_2008_VERSION:
        return self.visit_DATETIME(type_)
    else:
        return self.visit_DATE(type_)

My suggestion is to debug your code and check if this MSTypeCompiler is used and if you hit the visit_date(...) method.

Answer 2

I got it - my configuration was wrong.

Turns out that you have to configure freetds to make it use the version 7.0 or 8.0 of the TDS protocol. By default it uses 4.2, which yields weird results when querying for MS SQL Server version, leading SQLAlchemy to confusing behavior, as I described in my question.

I had it correctly set on the freetds.conf file, but this file was not being read, because it is only parsed when you use a DSN defined in the file, and I was using a connection string as in the example in the question.

Setting the variable TDSVER as described here solved the issue.