How to connect to Jira Align Enterprise Insights using Python

[#1]

Summary

How to connect to Enterprise Insights using Python

Environment

Tested in:

• Jira Align Enterprise Insights Version(s): 10.108
  
• Python v3.10

Solution

You will need:

• A SQL user for Enterprise Insights

• Python v3.10 (newer versions have not yet been tested. Feel free to test and update here!)

• Python module pyodbc
  Install the package of "pyodbc" which is ODBC Driver 17 for SQL Server as per this link:

Configure pyodbc Python environment - Python driver for SQL Server

import pyodbc 
​
stringConnection = pyodbc.connect("Driver={ODBC Driver 17 for SQL Server};"
"Server=xxxx;"
"Database=xxxx;"
"UID=xxxxx;"
"PWD=xxxxxxx;"
"AuthenticationType=SQLPassword;"
"minimalTlsVersion=1.3;"
"Trusted-Connection=yes;")
​
​
cursor = stringConnection.cursor()
cursor.execute('SELECT * FROM current_dw.Dependency where [Dependency ID] < 20')
​
for i in cursor:
print(i)

Related Content:

Use Python to query a database - Azure SQL Database & SQL Managed Instance

Connect and query - Azure SQL Database & SQL Managed Instance

With thanks to my co-author and tester @Luiz Felipe Arruda 

[edwin vasquez]

Great post! Thanks for sharing.

[Ronald Kager]

Example using interactive Entra Authentication

import os, pyodbc
###########################################################################################

AZ_DRV = "{ODBC Driver 18 for SQL Server}"
AZ_SRV="..... .database.windows.net"               #add your E.I. URL
AZ_DB=".."                                                        #add your E.I. DB name
UID = "...."                                                         #add your UID, eg.:  name.surname@yourorganization.com
AZ_PORT = "1433"
AZ_AUTH = 'ActiveDirectoryInteractive'           # other Types -> doc: https://learn.microsoft.com/en-us/sql/connect/odbc/using-azure-active-directory?view=sql-server-ver16

conn_string = f"DRIVER={AZ_DRV};SERVER=tcp{AZ_SRV};PORT={AZ_PORT};DATABASE={AZ_DB};Authentication={AZ_AUTH};UID={UID};Encrypt=yes;TrustServerCertificate=yes;Connection    Timeout=30;"

###########################################################################################

def get_conn( connection_string ):
    try:
       with pyodbc.connect( connection_string  ) as conn:
          print(".. DB connection established\n")
    except Exception as e:
       print(e)
       exit(1)
    return conn

def test_sql( db_conn_handle, SQL_string ):
       rows = []
       print(f"{SQL_string}\n")
       cursor = db_conn_handle.cursor()
       cursor.execute(SQL_string)
       rows = cursor.fetchall()
       for row in rows:
       print(row)
    return rows

def main()

    EI_handle = get_conn( conn_string )

    rows = test_sql( EI_handle, 'SELECT * FROM [export_dw].[Jira Project]' )

    exit(0)
#### end of Main ####

if __name__ == "__main__":
main()