[Master Guide] How to Connect to SQL using ODBC from Python Using the Enclave Windows VDI

Get Help

Posted on November 20, 2022

Updated on

November 21, 2024

Introduction

This document details how to create a database connection to different relational data sources from Python, in the Windows Enclave Virtual Desktop using ODBC (i.e. Complete PDSR Curated Data Set, COVID-19 Mart, etc.)

The Microsoft ODBC Drivers for SQL Server are stand-alone ODBC drivers which provide an application programming interface (API) implementing the standard ODBC interfaces to Microsoft SQL Server.

Requirements

Access to Enclave Platform should already be granted and your workspace provisioned. If you do not have approval to access the Enclave Platform, complete and submit the Access Request Form.
You should be logged on to your Enclave Windows Virtual Desktop. You cannot access the Enclave Platform resources from outside the platform.

Step 1: Create a DSN

The simplest way to make a connection from Python is using a Data Source Name (DSN). You will need to create a DSN for the data source you are trying to connect to (i.e. Complete PDSR Curated Data Set, COVID19 Mart, etc.)

Such connections, are created using the user's MGB credentials, and are normally used throughout an active session, as shown in the example below. Please, follow the step-by-step instructions in the document, How to Create a Connection to a Data Source in Windows Enclave VDI, to create a DSN for your application.

Step 2: Creating a Database Connection from Python Programs

Open your favorite Python IDE (VS Code or PyCharm)

Please, note that the next example will not run if you don't install pyodbc. Here is the installation of dependent packages from a terminal using PIP

pip3 install pyodbc

Now, paste and run the python code snippet below.

Make sure you replace <DSN_Name> with the actual name of the Data Source Name (DSN) from Step 1 (i.e. "PDSR_Monthly")

import pyodbc,sys dsn = '<DSN_Name>' conn = pyodbc.connect('DSN='+dsn) curr=conn.cursor() res=curr.execute("select getdate()") for x in res:   print(x) curr.close() conn.close()