There have been many questions asked about using Python to run SQL queries and I’ve tried to read and understand them all. My problem is I don’t think I’ve seen what I’m trying to answer in any of them.
What I have: I’ve got a series of queries that all stem from a top-level query which calls several other queries to build temp tables from which it will aggregate a final table. Currently, I initiate this in SQL by turning SQLCmd Mode on and letting it run. After it is complete I then take that table and put it into an excel file which has VBA set up to perform calculations on the aggregated data.
This is largely a manual process, what I would like to do using Python is to run the queries, paste the data and execute the calculations where I would then complete the process and send out the various reports.
From a high level what I was thinking would be a function that opens connection with nested functions to perform the individual queries. For example:
import stuff
import more stuff
def main():
connection open
def sub1():
execute query 1
def sub2():
execute query2
table = select from (temp tables of sub1 & 2)
connection close
return table
My question, can I do this with just the 1 set of open and close statements from the top level function?