Python write program/script (not just output) to excel

Question

for documentation/reporting purposes I would like to memorialize the script that I run for a report as part of the output to an excel tab. Is this possible?

for example, if i have the below code:

import pyodbc
import pandas as pd

cnxn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL 
Server};SERVER=relevantserver.com;UID=####;PWD=####; 
Trusted_Connection=yes')
cursor = cnxn.cursor()
script="""
  SELECT ID
    ,Type
    ,SubType
    ,Name
    ,RequestorLOB
    FROM db123
    ;
"""
cursor.execute(script)
columns = [desc[0] for desc in cursor.description]
data = cursor.fetchall()
df = pd.read_sql_query(script, cnxn)
writer = pd.ExcelWriter('C:/Users/myname/Desktop/testoutput.xlsx')
df.to_excel(writer, index=False, sheet_name='test1')
writer.save()

this will take the output from the sql script and create a dataframe and then create an excel file and put that data frame on sheet 'test1'.

I would like to create a second tab that contains all of the code that was utilized within the python script (basically creating an audit trail).

Any help you can provide would be appreciated!

Yes, you can write anything to the Excel sheets (e.g. write code into dataframe and do df.to_excel or use other libraries). But wouldn't logging be better in this case? — Ricky Kim
– Ricky Kim, Commented Jan 7, 2019 at 15:20
thanks Ricky! I will look into logging, but as of right now, I need this to be in the output file in excel- any suggestions for how to do this? How would you write code into a dataframe? — John
– John, Commented Jan 7, 2019 at 15:44

Niek de Klein · Accepted Answer · 2019-01-07 15:51:13Z

2

I made an example how to add the code of the script to the second sheet, should be able to replace the part under # First sheet, use your SQL data with your SQL part.

import pandas as pd

# First sheet, use your SQL data
d = {'col1': [1, 2], 'col2': [3, 4]}
df_sheet_1 = pd.DataFrame(data=d)

# Second sheet, read the code of Python script, split it up on new lines, add to dataframe
with open(__file__) as input_file:
    python_script = input_file.read()

d = {'code':python_script.split('\n')}
python_df_sheet_2 = pd.DataFrame(data=d)


writer = pd.ExcelWriter('testoutput.xlsx')
df_sheet_1.to_excel(writer, index=False, sheet_name='test1')
python_df_sheet_2.to_excel(writer, index=False, sheet_name='python_code')
writer.save()

answered Jan 7, 2019 at 15:51

Niek de Klein

8,86422 gold badges77 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

John Over a year ago

Niek de Klein, I will test later today, thank you for your help!

Collectives™ on Stack Overflow

Python write program/script (not just output) to excel

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related