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.
It hurts a little bit less now. We’re using the official sqlanydb driver in production.
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.
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.
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.