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

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 SetCOVID-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

  1. 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.
  2. 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() 

Limitations

Please note that the above code was tested to run successfully under python 3.9

Additional Resources

Python SQL Driver - pyodbc

Connect to an ODBC Data Source (SQL Server Import and Export Wizard

How to Connect Python to SQL Server using pyodbc

Python SQL driver

Connecting to SQL using pyodbc

Go to KB0039849 in the IS Service Desk