How to connect SQL Server from Python on macOS?

In this article we will see how to connect SQL Server from a Python program on macOS using Visual Studio Code as the development environment. This involves several steps including installation of Microsoft ODBC driver in macOS and pyodbc Python ODBC module in the python virtual environment. Let us see these steps in detail and retrieve data from a table in SQL Server database using a sample Python program.

Tools & Technologies used

macOS Catalina.
SQL Server 2019 on Docker.
Microsoft ODBC driver v17 for SQL Server on macOS.
Visual Studio Code 1.50.
Python 3.8.5.
pyodbc package 4.0.30.

Prerequisites

Read the below articles for setting up Visual Studio Code for Python development and installing SQL Server on macOS:

Steps to connect SQL Server from Python

Let’s dive into the steps to connect an SQL Server database from a Python program on macOS.

1. Install Microsoft ODBC driver for SQL Server on macOS

The first step is to install an ODBC driver on macOS. For SQL Server 2019, we have to use Microsoft ODBC driver version 17 (msodbcsql17). Here is the Homebrew command for installing msodbcsql17. Run this command in terminal.app of macOS.

/usr/bin/ruby -e "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install)"
brew tap microsoft/mssql-release https://github.com/Microsoft/homebrew-mssql-release
brew update
HOMEBREW_NO_ENV_FILTERING=1 ACCEPT_EULA=Y brew install msodbcsql17 mssql-tools

This command may take several minutes to complete. In the process, it may install or upgrade Homebrew and then install the MS ODBC driver. For more details on Microsoft ODBC Driver 17, see this Microsoft Docs Article.

2. Install pyodbc module

The next step is to install an ODBC driver module in Python virtual environment. There are several modules or packages available in Python to connect SQL Server. However Microsoft’s recommends using the open source driver pyodbc. Follow these steps to install the pyodbc SQL Server driver module in the Python project’s virtual environment.

Open the python project in VS Code.
Make sure the appropriate virtual environment. (For this illustration I am using the virtual environment with Python 3.8.5 64-bit.)
Now, open the Command Palette, by pressing Shift, Command and P keys.
Search and start Terminal: Create New Integrated Terminal. The terminal panel will open at the bottom.
In the terminal run this comment python3 -m pip install pyodbc

Install pyodbc package in VS Code Python project virtual environment

pyodbc module is now installed to your virtual environment.

3. Get the database connection details

Get the database connection details. Below are the connection details of the SQL Server Database I’m using for this illustration.

SQL Server Name: localhost
Database Name: WideWorldImporters
Table Name: Application.People
Username: sa
Password: MyPassword

4. Connecting to SQL Server Database

Finally create a simple Python program which connects to SQL Server Database and fetch some data. The connection to SQL Server can be established using the the pyodbc.connect() function with the connection string. The connection string should be in this format:

DRIVER={ODBC Driver 17 for SQL Server}; 
SERVER=<<SQL_Server_Name>>; DATABASE=<<Database_Name>>; 
UID=<<User_Name>>; PWD=<<Password>>

Here {ODBC Driver 17 for SQL Server} is the ODBC driver which supports SQL Servers from 2008 to 2019.

Then using the cursor.execute() we can execute the SQL statement and retrieve the data.

Here is the sample code to connect to SQL Server Database and fetch few rows form a table and print the data:

import pyodbc as po

# Connection variables
server = 'localhost'
database = 'WideWorldImporters'
username = 'sa'
password = 'MyPassword'

# Connection string
cnxn = po.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER=' +
        server+';DATABASE='+database+';UID='+username+';PWD=' + password)
cursor = cnxn.cursor()

# Fetch data into a cursor
cursor.execute("SELECT TOP (10) PersonID, FullName, PhoneNumber, \
    EmailAddress FROM WideWorldImporters.Application.People ORDER BY PersonID DESC;")

# iterate the cursor
row = cursor.fetchone()
while row:
    # Print the row
    print(str(row[0]) + ", " + str(row[1] or '') + ", " + str(row[2] or '') + ", " + str(row[3] or ''))
    row = cursor.fetchone()

# Close the cursor and delete it
cursor.close()
del cursor

# Close the database connection
cnxn.close()

Connect SQL Server From Python Program On MacOS

Reference

Read more about pyodbc module at GitHub.