MS SQL < FreeTDS < iODBC < Pyodbc < Python < MAC OS X |
Last Updated April 2015.
|
This page is dedicated to my struggle with accessing MS SQL from Mac OS X using Python. The way to do this is an ODBC driver called FreeTDS which is accessible through Python via Pyodbc. The Mac OS X ODBC system is either iODBC (default install) or unixODBC. Most instructions I've found concentrate on the latter because it is the default on Linux systems, but my instructions are mainly for iODBC users.
Pyodbc SQL "Memory error"
This occurs when Decimal values in the result set are NULL, particularly amounts that you have converted automatically by Django. To get around this you can use CASE WHEN to convert NULL's on specific columns to default values:
SELECT customer_id, CASE WHEN customer_balance IS NULL THEN 0 ELSE customer_balance
Links to bug reports:
I recently went down the rabbit hole on this one to figure it out, going so far as to fork the code on GitHub and build it myself. Imagine my surprise when I discovered the bug is not in the latest code! At the time of writing this, there is no newer version than 3.0.7 so to install the latest code using PIP:
sudo pip install 'git+https://github.com/mkleehammer/pyodbc'
Woohoo!
SELECT customer_id, CASE WHEN customer_balance IS NULL THEN 0 ELSE customer_balance
Links to bug reports:
- https://code.google.com/p/pyodbc/issues/detail?id=307
- https://github.com/mkleehammer/pyodbc/issues/35
I recently went down the rabbit hole on this one to figure it out, going so far as to fork the code on GitHub and build it myself. Imagine my surprise when I discovered the bug is not in the latest code! At the time of writing this, there is no newer version than 3.0.7 so to install the latest code using PIP:
sudo pip install 'git+https://github.com/mkleehammer/pyodbc'
Woohoo!
Django+Pyodbc "Previous SQL was not a query"
Passing Unicode strings to Pyodbc queries will give you this. It occurs commonly in Django because Unicode strings are the default and its database models utilize them. An easy way around this is to wrap your query string in str().
DatabaseError: ('The SQL contains 0 parameter markers, but 2016 parameters were supplied', 'HY000')
This is a Unicode encoding issue. When you pass a Python u"string" into the driver along with parameters you'll get this error. Determine the character set used by your database (usually "utf-8" or "latin1") and encode to that. Conversely, whatever strings you receive you can decode them to unicode using the same charset, assuming you got it right.
Ref: https://github.com/lionheart/django-pyodbc/issues/35
Ref: https://github.com/lionheart/django-pyodbc/issues/35
Notes
I need to be able to connect to MS SQL Server 2012 directly by host/ip and port from Python 2.7 on OSX 10.8.5 (Lion). My attempts to set this up with instructions for MacPorts and Homebrew have been unsuccessful.
For the answer I'm going to assume that you're using a SSH tunnel from your localmachine
DRIVER=/usr/local/lib/libtdsodbc.so;SERVER=127.0.0.1;Port=1433;UID=username;PWD=password;TDS_Version=8.0;Database=dbname
Use the following command from your terminal to test pyodbc.
python -c 'import pyodbc; pyodbc.connect("SERVER=host_or_ip;Port=1433;Database=dbname;UID=username;PWD=password;TDS_Version=8.0;DRIVER=/usr/local/lib/libtdsodbc.so").cursor().execute("SELECT COUNT(*) FROM tablename")'
Remember to fill in your own values for host_or_ip, the port (if different from 1433), username, password, dbname, and tablename. If it works then you'll get no output, otherwise you'll see an error such as:
pyodbc.ProgrammingError: ('42000', "[42000] [FreeTDS][SQL Server]Login failed for user 'neilo'. (18456) (SQLDriverConnect)")
If you get a Login failure then you're further than you think and the issue might just be character escaping at the terminal.
If you tried using a unixODBC DSN-less connection string then you might have gotten this:
pyodbc.Error: ('00000', '[00000] [iODBC][Driver Manager]dlopen(FreeTDS, 6): image not found (0) (SQLDriverConnect)')
Notice that iODBC is the issue here and it's trying to directly load the DRIVER value. Thus if you pass DRIVER=FreeTDS it calls dlopen('FreeTDS'). Whoops. This is the biggest difference I've found between unixODBC and iODBC.
http://stackoverflow.com/questions/13772342/python-script-to-guess-which-odbc-driver-manager-is-installed
http://stackoverflow.com/questions/11963122/connecting-to-ms-sql-server-with-iodbc-on-mac
http://www.macports.org/
http://stackoverflow.com/questions/17271319/installing-pip-on-mac-os-x
http://www.freetds.org/
http://stackoverflow.com/questions/11963122/connecting-to-ms-sql-server-with-iodbc-on-mac
Chances are the problem you're having with any of these instructions is that the freetds version you're getting is built for unixODBC while OS X comes with iODBC. That means that freetds is inaccessible to any code trying to reach it through your built-in ODBC driver manager.
The answer to this problem is downloading, building, and installing FreeTDS yourself:
Afterwards you will have the following files on your system (I've ordered these by what I consider the most important):
To verify FreeTDS is installed for iODBC, run tsql -C and you should see a line that says "iODBC: yes". If you see "unixODBC: yes" then the installation did not go correctly. TODO: remove FreeTDS entirely and start over.
Also check the "freetds.conf directory" in the output. If it's not /usr/local/etc/freetds.conf then another tool installed FreeTDS for you. I do recommend removing all other versions of FreeTDS and this will give you a clue. For example, if you see /usr/local/Cellar/freetds/0.91/etc/ then it was installed with Homebrew. TODO: how to remove Homebrew and/or its version of FreeTDS.
There is more to pyodbc than meets the eye: it is an ODBC-based wrapper so on OS X it uses iODBC or unixODBC and connecting to MS SQL uses freetds.
I did eventually figure out why MacPorts didn't work for me. The reason is two-fold: one is the driver manager with FreeTDS already mentioned and the other is that MacPorts maintains a separate tree for Python where it does all its business.
For the answer I'm going to assume that you're using a SSH tunnel from your localmachine
DRIVER=/usr/local/lib/libtdsodbc.so;SERVER=127.0.0.1;Port=1433;UID=username;PWD=password;TDS_Version=8.0;Database=dbname
Use the following command from your terminal to test pyodbc.
python -c 'import pyodbc; pyodbc.connect("SERVER=host_or_ip;Port=1433;Database=dbname;UID=username;PWD=password;TDS_Version=8.0;DRIVER=/usr/local/lib/libtdsodbc.so").cursor().execute("SELECT COUNT(*) FROM tablename")'
Remember to fill in your own values for host_or_ip, the port (if different from 1433), username, password, dbname, and tablename. If it works then you'll get no output, otherwise you'll see an error such as:
pyodbc.ProgrammingError: ('42000', "[42000] [FreeTDS][SQL Server]Login failed for user 'neilo'. (18456) (SQLDriverConnect)")
If you get a Login failure then you're further than you think and the issue might just be character escaping at the terminal.
If you tried using a unixODBC DSN-less connection string then you might have gotten this:
pyodbc.Error: ('00000', '[00000] [iODBC][Driver Manager]dlopen(FreeTDS, 6): image not found (0) (SQLDriverConnect)')
Notice that iODBC is the issue here and it's trying to directly load the DRIVER value. Thus if you pass DRIVER=FreeTDS it calls dlopen('FreeTDS'). Whoops. This is the biggest difference I've found between unixODBC and iODBC.
http://stackoverflow.com/questions/13772342/python-script-to-guess-which-odbc-driver-manager-is-installed
http://stackoverflow.com/questions/11963122/connecting-to-ms-sql-server-with-iodbc-on-mac
http://www.macports.org/
http://stackoverflow.com/questions/17271319/installing-pip-on-mac-os-x
http://www.freetds.org/
http://stackoverflow.com/questions/11963122/connecting-to-ms-sql-server-with-iodbc-on-mac
Chances are the problem you're having with any of these instructions is that the freetds version you're getting is built for unixODBC while OS X comes with iODBC. That means that freetds is inaccessible to any code trying to reach it through your built-in ODBC driver manager.
The answer to this problem is downloading, building, and installing FreeTDS yourself:
- Install Xcode and its Command Line Tools ( http://stackoverflow.com/questions/9353444/how-to-use-install-gcc-on-mac-os-x-10-8-xcode-4-4 )
- Download FreeTDS (0.91 has been the latest stable version for years now).
- Unzip, configure, make: http://freetds.schemamania.org/userguide/config.htm
- ./configure
- make
- sudo make install
- Construct DSN to refer directly to shared object in DRIVER parameter.
Afterwards you will have the following files on your system (I've ordered these by what I consider the most important):
- /usr/local/lib/libtdsodbc.so ... Shared library (this is FreeTDS). It is probably a file pointer to libtdsodbc.0.so.
- /usr/local/etc/freetds.conf ... Configuration file.
- /usr/local/lib/libtdsodbc.a ... Static library.
- /usr/local/lib/libtdsodbc.la ... Libtool information text file.
- /usr/local/etc/locales.conf ... Locale-specific configuration.
- /usr/local/etc/pool.conf ... Connection-pooling configuration.
- /usr/local/share/doc/freetds-0.91/ ... Documentation
- /usr/local/bin/tsql ... Adhoc testing tool.
- /usr/local/bin/freebcp ... ?
- /usr/local/bin/bsqldb ... ?
- /usr/local/bin/defncopy ... ?
- /usr/local/bin/datacopy ... ?
- /usr/local/bin/bsqlodbc ... ?
To verify FreeTDS is installed for iODBC, run tsql -C and you should see a line that says "iODBC: yes". If you see "unixODBC: yes" then the installation did not go correctly. TODO: remove FreeTDS entirely and start over.
Also check the "freetds.conf directory" in the output. If it's not /usr/local/etc/freetds.conf then another tool installed FreeTDS for you. I do recommend removing all other versions of FreeTDS and this will give you a clue. For example, if you see /usr/local/Cellar/freetds/0.91/etc/ then it was installed with Homebrew. TODO: how to remove Homebrew and/or its version of FreeTDS.
There is more to pyodbc than meets the eye: it is an ODBC-based wrapper so on OS X it uses iODBC or unixODBC and connecting to MS SQL uses freetds.
I did eventually figure out why MacPorts didn't work for me. The reason is two-fold: one is the driver manager with FreeTDS already mentioned and the other is that MacPorts maintains a separate tree for Python where it does all its business.