A Short Summary on Sybase SQL Anywhere and Python

As some of my older rage-filled articles indicated, we’re still running some services on Sybase’s SQL Anywhere. Since it cost me many hours and sanity wrangling, I think it may be helpful to others to summarize the current situation for Python developers.

Abstract

It hurts a little bit less now. We’re using the official sqlanydb driver in production.

Options

There are four ways known to me to access a SQL Anywhere database in Python:

  • Their official sqlanydb driver that requires their client libraries. Used by sqlany-django which gives you Django’s ORM with SQL Anywhere.
  • Their official ODBC drivers via pyodbc.
  • The python-sybase module which also needs a pretty complete SQL Anywhere installation to build. Supported by SQLAlchemy.
  • FreeTDS via pyodbc.

Problems

Historically, there have been problems with all of them:

  • sqlanydb crashed.
  • Their ODBC drivers leaked semaphores which led to a denial of service eventually.
  • sqlanydb seems to leak prepared statements on exceptions which leads to Resource Governor errors.
  • I never got python-sybase working and it seems abandonware.
  • FreeTDS randomly hangs in queries, doesn’t get a lot of attention either.

In our experience so far, it seems like the first two problems have been remedied as of sqlanydb 1.0.5 (which also added support for Python 3.3) and SQL Anywhere 12.0.1. 3873.

The only lasting problems are the prepared statement leaks and the annoyance that sqlanydb is still not on PyPI. There is one catch left you should be aware of though: when using sqlanydb with uwsgi, you have to set the lazy-apps option – otherwise the driver will just fail to work without any useful error message. I’ve experienced weird fork() behavior of the driver in the past.

FreeTDS

I strongly advise against using FreeTDS in production.

I’ve talked to other people and it seems FreeTDS’s behavior is the same for MS SQL too. Since Microsoft released an official Linux ODBC driver for MS SQL you may want to keep your hands off FreeTDS altogether.