2

I am using sqlalchemy and python3 to query a database on Microsoft SQL Server.

Since I do not have access to SQL Server Management Studio I am resorting to using SHOWPLAN options in my queries. I found this outlined in answer here: How do I obtain a Query Execution Plan?

The problem is the output of using STATISTICS XML ON is not human friendly. I generally use sqlalchemy to retrieve data in pandas dataframes so please forgive my ignorance if I am missing something obvious to interpret this in a friendlier manner.

Please find my python3 code below:

from sqlalchemy import create_engine
import os

database_credentials = config.settings['database_credentials']

# Retrieve database credentials from config
db_name = os.environ.get(database_credentials['db_name'])
db_host = os.environ.get(database_credentials['db_host'])
db_port = os.environ.get(database_credentials['db_port'])
db_id = os.environ.get(database_credentials['db_id'])
db_pwd = os.environ.get(database_credentials['db_pwd'])

# Concatenate strings to create database url
db_engine = create_engine('mssql+pymssql://' + db_id + ':' + db_pwd + '@' + db_host + ':' + db_port + '/' + db_name)

# Connect to database
db_conn = db_engine.connect()

# Example query with SHOW Plan options
query = 'SET STATISTICS XML ON\n SELECT TOP 5 * FROM Table'

# Execute the query 
result = db_engine.execute(query)

Is there a way to format result into something that is readable to understand the query plan?

Thanks for your help

ZeroStack
  • 1,049
  • 1
  • 13
  • 25
  • Your result currently holds the actual result set (top 5 rows from the table) and the xml for the plan, right? You could try to save the `xml` as a file with the `".sqlplan"` extension on a local folder and then open it from there. Would this be a possibility? – Rigerta Jul 30 '18 at 06:03
  • @RigertaDemiri Thanks for your comment. After using the method `fetchall()` on the `result` variable it is in a list within python. I have some questions on your suggestion, how would I save the `result` variable as an `.sqlplan` in python? It is of class `'sqlalchemy.engine.result.ResultProxy'`, what program would you open this file with? Is it `SQL Server Management Studio`? This is a windows only program I would prefer something programmatic and in python. I am also using macOS. – ZeroStack Jul 30 '18 at 06:38
  • Hey, as to how to save it (or if it's even an option) I don't know. I do not work with Python, that's why I asked you if it could be a possibility. As for how to open it, SSMS is not cross platform but [SQL Operations Studio](https://learn.microsoft.com/en-us/sql/sql-operations-studio/download?view=sql-server-2017) is. You could use that on Mac. – Rigerta Jul 30 '18 at 06:44
  • If it is just an `XML` formatted string that is returned from the query you could parse it using [`ET.fromstring`](https://docs.python.org/3/library/xml.etree.elementtree.html#parsing-xml) and then save to a file following [this](https://stackoverflow.com/questions/28813876/how-do-i-get-pythons-elementtree-to-pretty-print-to-an-xml-file/28814053) question. – SuperShoot Jul 30 '18 at 07:49

0 Answers0