First, install the following libraries:
$ brew install unixodbc
$ brew install freetds --with-unixodbc
FreeTDS should already work now, without configuration:
$ tsql -S [IP or hostname] -U [username] -P [password]
locale is "en_US.UTF-8"
locale charset is "UTF-8"
using default charset "UTF-8"
1> ^D
Onto unixODBC, we need to link to the driver, edit /usr/local/etc/odbcinst.ini:
[FreeTDS]
Description = TD Driver (MSSQL)
Driver = /usr/local/lib/libtdsodbc.so
Setup = /usr/local/lib/libtdsodbc.so
FileUsage = 1
The test command we're using requires configuring a DSN, so edit /usr/local/etc/odbc.ini:
[MYDSN]
Driver = FreeTDS
Server = [IP address]
Port = 1433
The configuration for your DNS might vary, you might need the TDS_Version or Servername directives. The above worked for me for SQL Server 2008 R2. Now, run the test command:
$ isql MYDSN [username] [password] -v
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL> ^D
If the test succeeded, you can continue onto installing the Python library pyodbc. Version 3.0.7 and before didn't link with unixODBC, so a change had to be made to setup.py, follow the steps at the bottom instead. The current version (3.0.10) has resolve this issue, so you can go ahead and install from pypi:
pip install pyodbc
Now pyodbc should work:
import pyodbc
pyodbc.connect('DSN=MYDSN;UID=[username];PWD=[password]')
You don't need to have your DSN configured in odbc.ini, so clear that file. You probably want to select a database on connect, so change your connect line to read:
pyodbc.connect('DRIVER=FreeTDS;SERVER=[IP address];PORT=1433;DATABASE=[database];UID=[username];PWD=[password]')
Note that you could also link to the library file of FreeTDS instead of using odbcinst.ini, like this:
pyodbc.connect('DRIVER=/usr/local/lib/libtdsodbc.so;SERVER=[IP address];PORT=1433;DATABASE=[database];UID=[username];PWD=[password]')
Version 3.0.7 didn't link with unixODBC on OS X, so a change had to be made to setup.py. Note that the current version of writing (3.0.10) doesn't have this issue. Follow these steps only to install the old version. Download the source package and extract it somewhere. Find the following lines (146-147):
elif sys.platform == 'darwin':
# OS/X now ships with iODBC.
And change this line:
settings['libraries'].append('iodbc')
into:
settings['libraries'].append('odbc')
Then run the following command to install:
> python install .
👍
a
brew rm unixodbcand abrew rm freetdshelps beforehand