Add instructions for setting up unixodbc / FreeTDS

[dlo]

No description provided.

[gcavalcante8808]

if possible, instructions to setup unixodbc / Microsoft ODBC Native Driver would be good as well =D

[WilliamQLiu]

Hi, I was passing through and happened to have some instructions for setting up unixODBC and FreeTDS on a Mac with Homebrew (Note: the credit should go to this blog, who did the setup with MacPorts). Hopefully this helps and if not feel free to delete.

Summary

I'm using a Mac on Yosemite Version 10.10.1 trying to connect to a MS SQL Server database. I searched and couldn't find an updated detailed answer so here's a writeup that is mostly from this amazing article here. I'm adding it on stackoverflow in case the link dies. The idea is that we'll have the following layers to setup/connect.

Layers

• PART 1 - pyodbc
• PART 2 - unixODBC (can check with isql)
• PART 3 - freeTDS (can check with tsql)
• PART 4 - MS SQL (can check with a regular python program)

Steps

1. Install Homebrew here - this is a package manager for Mac OSX. The article shows how to use another package manager 'MacPorts'. For my instructions, they're with homebrew. Basically homebrew has a folder 'cellar' that holds different versions of packages. Instead of modifying your normal files, it instead points to these homebrew packages.

2. Install Pyodbc with pip install https://pyodbc.googlecode.com/files/pyodbc-3.0.7.zip (this installs pyodbc - I had issues with the regular pip install pyodbc command)

3. Install unixODBC with brew install unixodbc (mac's use iODBC as the driver by default, but I had issues with it recognizing so I'm also using unixODBC)

4. Setup your unixODBC config files, which includes odbcinst.ini (driver configuration), and odbc.ini (DSN configuration file). By default, my files were in: /Library/ODBC (Note: NOT my user library aka /Users/williamliu/Library)

5. Open up your 'odbcinst.ini' file and then add the following (Note: Different if you use MacPorts. For Homebrew, this file is a link to the homebrew version e.g. mine is in '/usr/local/Cellar/freetds/0.91_2/lib/libtdsodbc.so'):

   [FreeTDS]
   Description=FreeTDS Driver for Linux & MSSQL on Win32
   Driver=/usr/local/lib/libtdsodbc.so
   Setup=/usr/local/lib/libtdsodbc.so
   UsageCount=1
   

6. Open up your 'odbc.ini' and then add the following (Note: Different if you use MacPorts or if versions change):

   [MYSERVER]
   Description         = Test to SQLServer
   Driver              = FreeTDS
   Trace               = Yes
   TraceFile           = /tmp/sql.log
   Database            = MYDATABASE
   Servername          = MYSERVER
   UserName            = MYUSER
   Password            = MYPASSWORD
   Port                = 1433
   Protocol            = 7.2
   ReadOnly            = No
   RowVersioning       = No
   ShowSystemTables    = No
   ShowOidColumn       = No
   FakeOidIndex        = No
   

7. Verify unixODBC installed correctly with: isql MYSERVER MYUSER MYPASSWORD. If you get an error that you cannot connect, then add -v to check what the verbose output is and fix it. Otherwise, you should see this:

   +---------------------------------------+
   | Connected!                            |
   |                                       |
   | sql-statement                         |
   | help [tablename]                      |
   | quit                                  |
   |                                       |
   +---------------------------------------+ 
   

8. Install FreeTDS with brew install freetds --with-unixodbc (FreeTDS is the driver that sits between the Mac ODBC and MS SQL Server, this chart here shows which version of TDS you should be using based on your specific Microsoft Server version; e.g. tds protocol 7.2 for Microsoft SQL Server 2008).

9. Configure freetds.conf file (The file should be in '/usr/local/etc/freetds.conf', which for Homebrew is a link to say '/usr/local/Cellar/freetds/0.91_2/etc', but yours might be somewhere different depending on version). I edited the global and added my database info to the end (for some reason 'tds version = 7.2' would throw an error, but still work, while 8.0 just works):

   [global]
   # TDS protocol version
   tds version = 8.0
   
   [MYSERVER]
   host = MYSERVER
   port = 1433
   tds version = 8.0
   

10. Verify FreeTDS installed correctly with: tsql -S myserver -U myuser -P mypassword (you should see a prompt like this if it worked)

    locale is "en_US.UTF-8"
    locale charset is "UTF-8"
    using default charset "UTF-8"
    1>
    

11. Now verify pyodbc works with a python program. Run python in the shell or a .py file with this and you should get your query back:

    import pyodbc
    import pandas
    import pandas.io.sql as psql
    
    cnxn = pyodbc.connect('DSN=MYSERVER;UID=MYUSER;PWD=MYPASSWORD')
    cursor = cnxn.cursor()
    sql = ("SELECT * FROM dbo.MYDATABASE")
    df = psql.frame_query(sql, cnxn)
    

[dlo]

Thanks @WilliamQLiu!

[dlo]

BTW--please feel free to add this to the wiki :)

[sarahlunette]

Hello - it works fine till I import pyodbc - I have an import error. Before that even, osql commands do not work and I was able to make pyodbc import before but still got an error [iOBDC] instead of [unixODBC]

[WilliamQLiu]

Maybe you can try pymssql instead of pyodbc. I had this as a stackoverflow
answer at
http://stackoverflow.com/questions/11678696/sql-server-python-and-os-x/27239553#27239553
and that's what someone recommended.

On Wed, Jun 17, 2015 at 10:46 AM, sarahlunette [email protected]
wrote:

Hello - it works fine till I import pyodbc - I have an import error.
Before that even, osql commands do not work and I was able to make pyodbc
import before but still got an error [iOBDC] instead of [unixODBC]

—
Reply to this email directly or view it on GitHub
#10 (comment)
.

[sarahlunette]

Thanks ... Unfortunately I had already done that and it still did not work. It clearly is the connection between pyodbc and unixodbc .. either where the files are located or juste the script for pyodbc setup needs more changes ... Any clue ? thanks a lot