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.
Leave a Reply