1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
# Runs stored procedure and returns results in an excel file
# Import required libraries
import pyodbc
import pandas as pd
import os
from sqlalchemy import create_engine, text
import datetime

# Database connection parameters
server = 'mySQLServer.local'
database = 'TestGoLive'
username = 'testuser'
password = 'MySuperSecretPassword1234'
storedproc = 'exec sp_rpt_loss_ratio_parent_loss3'

# This query works perfectly fine When running from the SQL Server Management Studio
sql_query =  "SELECT TOP 10 claim_id AS ClaimNumber, custact_id AS CustActionNumber FROM dbo.lossratio"

# Excel file name
excel_file = 'results.xlsx'

# Establish a connection to the database
conn = pyodbc.connect('DRIVER={SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+password)
# Create a SQLAlchemy engine
engine = create_engine(f'mssql+pyodbc://{username}:{password}@{server}/{database}?driver=SQL+Server')

# Execute the SQL query and retrieve the results into a pandas DataFrame
with engine.connect() as connection:
    # Befor executing the query, first run the stored procedure: exec sp_rpt_loss_ratio_parent_loss2 462, '1/1/2022', '" & Date & "', '', '', '', '*'
    # to get the temporary table created
    # first set variable TodayDate to today's date
    TodayDate = datetime.date.today().strftime('%m/%d/%Y')
    #This stored prodcedure runs fine from SQL Server Management Studio but not from python.
    #When running from python, it clearly runs when observing the SQL Server Profiler 2016
    #That is, when I execute the python code, I can see it running in the trace, but when I look for the table that the stored procedure creates, it is not there.
    #When I go to the SQL Server Management Studio, log in as the user that I created, run the stored procedure with the same parameters
    #as the python code, the table that the stored procedure creates is there.
    exec_string = text(storedproc+" 462, '1/1/2020', '" + TodayDate + "', '', '', '', '*'")
    #exec_string = "sp_rpt_loss_ratio_parent_loss2 462, '1/1/2012', '" + TodayDate + "', '', '', '', '*'"
    connection.execute(exec_string)
    # Next, run a query 
    df = pd.read_sql(sql_query, connection)
    # results in an empty dataframe

# Check if the file already exists and is open
if os.path.isfile(excel_file):
    try:
        with pd.ExcelWriter(excel_file, mode='w', engine='openpyxl') as writer:
            df.to_excel(writer, sheet_name='Sheet1', index=False)
    except PermissionError as e:
        print(f"Error: {excel_file} is already open by another process.")
else:
    df.to_excel(excel_file, index=False)