CODEWITHSUNDEEP
pythonpandas
John
John

Reputation: 503

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

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!

Upvotes: 0

Views: 66

Answers (1)

Niek de Klein
Niek de Klein

Reputation: 8834

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()

Upvotes: 2

Related Questions