Connecting to SQL Server from SQLAlchemy on a Mac

This post explains how to connect to SQL Server using SQLAlchemy, pyodbc, UnixODBC and FreeTDS on a Mac

Install UnixODBC & FreeTDS

1. Install via Homebrew

   brew install unixodbc
   brew install freetds --with-unixodbc
   

2. Test with FreeTDS from terminal

   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>
   

Configure odbc.ini & odbcinst.ini

1. Open /usr/local/etc/odbcinst.ini, point to the driver at libtdsodbc.so</h3>

   [FreeTDS]
   Driver = /usr/local/lib/libtdsodbc.so
   Setup = /usr/local/lib/libtdsodbc.so
   FileUsage = 1
   

2. Open /usr/local/etc/odbc.ini and create new DSN

   [MSSQL_DSN]
   Driver=FreeTDS
   Server=10.110.0.200
   Port=1433
   Database=my_database_name
   

3. Test the DSN

   isql MSSQL_DSN username password -v
   +---------------------------------------+
   | Connected!                            |
   |                                       |
   | sql-statement                         |
   | help [tablename]                      |
   | quit                                  |
   |                                       |
   +---------------------------------------+
   SQL> ^D
   

Install pyodbc

pip install pyodbc

Connect with sqlalchemy

from sqlalchemy import create_engine

e = create_engine("mssql+pyodbc://username:password@MSSQL_DSN")

Hopefully you can quickly get connected to your SQL Server instance via Python with the steps above. For me, it has been notoriously more difficult to connect to than other databases like MySQL and PostgreSQL. In fact, I built an open-sourced tool to migrate an entire database (or a handful of tables) from SQL Server, to any other RDBMS like MySQL.

Check out etlalchemy here on github

A couple of notes...

1. Depending on the SQL Server version you are connecting to, you may need to specify a TDS Protocol Version in /usr/local/etc/freetds.conf:

   [global]
       # Uncomment below to guess protocol
       # tds version = auto
       tds version = 7.3
   

Full Compatability Matrix (from http://freetds.org)