Setting up ODBC Data Source for scripting

• Author: Devin Hepburn
• Published: 03/22/2022
• Applies to: Historian (Multiple Versions)

Goal

After completing this how-to you will have a configured ODBC connection configured for easy referencing in python and other scripted connections.

First Step

Opening ODBC Data Connections 

Click "Add" to configure a new connection. Select the driver. We will use ODBC Driver 17 for SQL Server for this example.

Set the reference Alias, Description, and Server Name for the Database. The values in the image are just there as an example. Please use the values that match your setup needs.

Set up Authentication to the DB. Shown below will used Windows Integrated Authentication. This assumes that all windows users that need to access the database have been given permission to do so.

Set the default database to simplify queries during coding. Since we are attaching to the Historian Server in this example, we will default to the Runtime database.

Test the connection and close out.

Second Step/

This connection can now be referenced using ODBC connection modules in scripting using only the ODBC Alias. The highlighted code shows how a connection is defined in Python using the PYODBC module.

#Define SQL Connection
#The DSN Connection MUST be set up in the ODBC connection interface in the control panel.
#Trusted connection means to use Windows Domain credentials for SQL DB connection
conn = sql.connect('DSN=Historian;Trusted_Connection=yes;')

As you can, defining the connection now is as simple as using the Alias that we set up. This makes it easy to manage multiple connections while keeping code clean and legible.

What's Next

An easily referenced database connection was created. This data can be used to create visualizations or loaded into another database for use. Please reference PYODBC documentation for further guidance on using this powerful tool.