Connect to MS SQL on MacOS using Python

Microsoft has released a beta version of its ODBC driver for MacOS. Here is a quick and easy guide to connect to your MS SQL using python.

The first thing you need is to install Homebrew.

Enter the following command in a terminal window to install Homebrew:

/usr/bin/ruby -e "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install)"

Afterward, enter following commands in a terminal window to install Microsoft ODBC driver 13 for MacOS:

brew tap microsoft/msodbcsql https://github.com/Microsoft/homebrew-msodbcsql-preview
brew update
brew install msodbcsql

Next item you need to install is pyodbc by entering following command in a terminal:

pip install pyodbc

or depending on your python installation:

python3 -m pip install pyodbc

Now you have all the prerequisites to connect to your MS SQL database in python.

Now navigate to unixodbc folder using the following command:

cd /usr/local/Cellar/unixodbc/2.3.4

Here you have two files:

1-odbc.ini

2-odbcinst.ini

Open odbc.ini using the following command:

nano odbcinst.ini

There you will see following information:

[ODBC Driver 13 for SQL Server]
Description=Microsoft ODBC Driver 13 for SQL Server
Driver=/usr/local/lib/libmsodbcsql.13.dylib
UsageCount=1

You need the copy the content in the square brackets which in my case is “ODBC Driver 13 for SQL Server”.

Exit the editor and open a new file like this:

nano ~/tempfile

Add the following lines to this file:

[MSSQL]
Description = Test to SQLServer
Driver = ODBC Driver 13 for SQL Server
Trace = No
Server = YourSQLServerAddress

Replace “ODBC Driver 13 for SQL Server” with the content you copied in the square brackets. Also, write your SQL server address instead of “YourSQLServerAddress“. Save the file and exit Nano editor.

Now, enter the following command in terminal and enter your password:

sudo odbcinst -i -s -f ~/tempfile -l

To test your connection, open python editor and run the following script:

import pyodbc
a=pyodbc.connect('DSN=MSSQL;UID=yourUserName;PWD=yourPassWord')
print(a)

DSN is the name you used in the temp file. Replace yourUserName and yourPassWord with the ones you use for your SQL server.

You will see an output similar to:

<pyodbc.Connection object at 0xfffffffff>

Connect to and manage MS SQL from MacOS

In order to connect to Azure SQL Database from MacOS (OS X) for free, you need two things:

1- Oracle SQL Developer (Download)

2- JTDS driver (Download)

Download these files and install Oracle SQL Developer. Then extract the JTDS driver zip file in a desired location (e.g., \Users\”Your User Name”\JTDS\).

Now open your Oracle SQL Developer and from the menu open preference as following figure shows:

SQL Developer

SQL Developer

From the Preference window, open Database > Third Party JDBC Drivers:

Click on the photo to open the full size image!

SQL Developer

SQL Developer

Here click on Add Entry and find your JTDS folder and choose jtds-1.3.1.jar as the driver path and click on Select and then OK.

Note: Before moving on from this part, make sure you have added your IP address in the firewall setting of your Azure Portal’s desired database.

Now you are ready to add your database to the program. From the connection pane on the left, click on the “+” button.

SQL Developer

SQL Developer

Now click on “SQLServer” tab and add your database connection information:

SQL Developer

SQL Developer

Click on connect. Don’t forget to retrieve your database here. Now save this profile. Now your database is ready to be inquired by transact SQL.