As some of my older rage-filled articles indicated, we’re still running some services on SAP’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 and the official SQL Anywhere dialect for SQLAlchemy.
- 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 problem is the prepared statement leaks. 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.
