November 21, 2023
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.
- 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()
Please note that the above code was tested to run successfully under python 3.9