This was… not straightforward. There’s a couple of Python modules out there for this. I ended up using pyodbc and sqlalchemy. I needed to edit some files in /usr/local/etc, and then symlink them to /etc.

I think that for brew, I installed the following:

brew install unixodbc
brew install freetds --with-unixodbc

Then for pip, I installed:

python3 -m pip install pyodbc sqlalchemy

Most guides will tell you that you’ll need to modify /etc/odbc.ini and /etc/odbcinst.ini, but since you installed stuff with brew, these files are in /usr/local/etc. I linked these files, just in case.

sudo ln -s /usr/local/etc/odbcinst.ini /etc/odbcinst.ini
sudo ln -s /usr/local/etc/odbc.ini /etc/odbc.ini

After that, I was able to open a connection with sqlalchemy:

from sqlalchemy.sql import text
from sqlalchemy import create_engine, inspect

engine = create_engine('mssql+pyodbc://{}:{}@MYDSN'.format("username", "password"))
conn = engine.connect()

inspector = inspect(engine)

for table_name in inspector.get_table_names():
    print("\nTable: "+table_name)
    for column in inspector.get_columns(table_name):
        print("Column: %s" % column['name'])

I also started to use pyodbc:

import pyodbc
conn = pyodbc.connect('DSN=MYDSN;UID=username;PWD=password')

The MYDSN variable is set in /usr/local/etc/odbc.ini:

[MYDSN]
Driver = FreeTDS
Server = <server-address>
Port = 1433

References:

https://gist.github.com/Bouke/10454272
https://stackoverflow.com/questions/44527452/cant-open-lib-odbc-driver-13-for-sql-server-sym-linking-issue
https://github.com/mkleehammer/pyodbc/wiki/Getting-started
https://stackoverflow.com/questions/21310549/list-database-tables-with-sqlalchemy/21346185