How to Connect to Microsoft SQL Server Using Python on macOS Catalina

Hello everyone, in this article, we will learn how to connect to Microsoft SQL Server using Python from a Mac. I am pretty sure that there are many ways to connect to a remote SQL Server using Python. But recently, I found an easy way to do this.

So in this example, I am going to connect to my remote Microsoft SQL Server 2017 installed on Ubuntu 20.04 Docker. I will show you how to build a simple yet powerful SQL Server database server using Docker next time.

Steps to Connect to SQL Server from macOS Catalina

Step 1. Install FreeTDS and UnixODBC

Ok first, we are going to install FreeTDS and UnixODBC. The easiest way is using brew. You can install brew in macOS Catalina with this command (skip if you have brew installed).

/bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install.sh)"

Now, install FreeTDS and UnixODBC with this command

brew update
brew install unixodbc freetds

Step 2. Edit freetds.conf file

Now we are going to edit the freetds.conf configuration file. To locate this file, you may need to execute this command

tsql -C

Output:

As you can see, my freetds.conf is located under /usr/local/etc directory.

nano /usr/local/etc/freetds.conf

Now add the MS SQL Server configuration at the bottom of the file.

[Ubuntu-Docker]
host = 192.168.100.52
port = 1433
tds version = 7.3

Step 3. Test the Connection

Now let’s try to connect to the SQL Server from Terminal

tsql -S Ubuntu-Docker -U myUser -P myPassword

If successfully connected, you will see the following

Step 4. Edit odbcinst.ini and odbc.ini

Next, we are going to edit the odbcinst.ini and odbc.ini configuration files. You can run this command below to find the exact location for those two configuration files.

odbcinst -j

Output:

Edit the file odbcinst.ini and then include these lines

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

Let’s edit the odbc.ini and then include the following lines to it. Please note the Driver name (FreeTDS) must be the same as the one we provided/created in the odbcinst.ini above. You can change it to anything you like.

[Ubuntu-Docker]
Description         = SQL Server on Ubuntu Docker
Driver              = FreeTDS
Servername          = Ubuntu-Docker

Now, let’s try to connect with this command

isql Ubuntu-Docker myUser myPassword

If all good, you will see the following

Step 5. Connect using PyODBC module

Ok so now we have all we need to start the python program to connect to SQL Server. First, we need to install pyodbc. You can use the following command to install pyodbc module in macOS

pip install pyodbc

or if you have python 3.x, use pip3 command

pip3 install pyodbc

Now, let’s create the python simple application

import pyodbc
myconn = pyodbc.connect('DSN=Ubuntu-Docker;UID=sa;PWD=mYPasswd.23')
mycursor = myconn.cursor()
myrows = mycursor.execute("SELECT @@VERSION").fetchall()
print(myrows)
mycursor.close()
myconn.close()

When run, it will produce something like this

Done. Now we have successfully created a python program to connect to the Microsoft SQL Server from macOS Catalina. It should works on other macOS versions as well.

Source

Be the first to comment

Leave a Reply

Your email address will not be published.


*