notes on mssql+unix+python

pymssql vs pyodbc

Choose pyodbc, even if it takes you longer to setup FreeTDS, unixodbc and all these exotic configuration files, trust me, it will be better on the long run.

An SQLAlchemy query is unable to find a column

You are probably running on the old TDS protocol. Either edit the "tds version" variable in your freetds.conf file or you can:

  • Rebuild FreeTDS with --with-tdsver=7.0, or
  • Set the environment variable TDSVER=7.0 before starting Python, or
  • Add TDS_Version=7.0; to your connection string (easiest) or
  • Use Servername in your connection string.

Reference: http://lists.ibiblio.org/pipermail/freetds/2008q2/023126.html

Here is an example of SQLAlchemy connection string using this:

mssql+pyodbc://user:password@localhost/MyDatabase?port=1443&tds_version=8.0

DATEPART()

If you are using SQLAlchemy, you can do the following to query components of a DateTime column:

from sqlalchemy.sql.expression import extract

year_col = extract("year", meta.table.c.createddate)
meta.session.query(year_col).all()
Bertrand Janin <b@janin.com> · Powered by the spirit of the UNIX gods.
This page was last updated: 2011-02-15