Executing queries from python

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?