How to Connect to Sql Server Database in Python

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:

• Setting-up VS Code Python environment on MacOS.
• Creating virtual environment and Installing Python packages on Visual Studio Code.
• Installing SQL Server on macOS using Docker Desktop.

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.

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

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()                  

Reference

• Read more about pyodbc module at GitHub.

Related Articles & Tools

How to Connect to Sql Server Database in Python

Source: https://www.mytecbits.com/internet/python/connect-sql-server-from-python-on-macos

Share this post