SQL multiple queries using the One Connection on Python

Question

I am quite new in Python, any advice or link will help.

I have created two python scripts, - Main.py which calls SQLcon.py.

SQLcon.py only creates connection to SQL server and downloads data based on multiple queries. Later,

Main.py code reads/creates pandas dataframes from excel files which are downloaded by SQLcon and does calculations and etc and etc.

the File for the SQL connection and queries in the SQLcon.py has the main structure as below

Problems:

A) Quite a lot of queries are done and quite a lot of temporary files are created.

B) I do not want to keep the SQL related code on the Main file

Wanted Outcome:

I want to use dfX = pd.read_sql_query(qryX, engine) (or similar) in the main file and to get rid of part for saving/reading excel files. Also, - would be nice to keep one connection during all these queries as multiple re-connections will slow down the code.

I am not sure how to start... Thinking of putting main SQL connection into the function and call it from Main... But it will create multiple re-connections...

import sqlalchemy as sa  # and other imports

load_dotenv()
# .env passwords and etc.
'''...'''
# creating SQL connection via sqlalchemy
connection_url = URL.create("mssql+pyodbc", query={"odbc_connect": connection_string})
engine = sa.create_engine(connection_url)
engine.echo = False

# creating dfs
df1 = pd.read_sql_query(qry1, engine)
dfA = pd.read_sql_query(qryA, engine)
dfZ = pd.read_sql_query(qryZ, engine)
engine.dispose() #not sure if dispose() is needed

# saving dfs
df1.to_excel(r'C:\Test\df1_tbl_Data.xlsx', index=False)
dfA.to_excel(r'C:\Test\dfA_tbl_Data.xlsx', index=False)
dfZ.to_excel(r'C:\Test\dfZ_tbl_Data.xlsx', index=False)

My question is probably not clear enough... as the block which is shown, creates lots of temporary files, which are later read by the Main code... I am thinking, of somehow, start One Connection from Main, - pass multiple queries from Main, and shut it down later... Most likely multithreading will be needed to keep connection live... Still not sure — NoobVB
– NoobVB, Commented Apr 23, 2022 at 14:41
Why not use a defined method from SQLcon.py to pull all needed data on same connection then return a tuple/list/dict of many items for Main.py to use? No need for I/O writes and reads. BTW- why Excel formats? Use Python pickles and avoid re-parsing data back into data frames. — Parfait
– Parfait, Commented Apr 23, 2022 at 14:51
Thanks @Parfait, - very interesting. Answers to all your "why" questions: I am quite new in coding and self-taught. Thanks a lot, I will most likely go ahead with your idea of passing the list of dataframes to Main. P.S. why excel? - this was temp solution, and for inspection mainly, and I've never heard of pickles before, - the aim is to get rid of any temporary files at all. Thanks a lot, - at least I do see some light at the end of the tunnel now! :) — NoobVB
– NoobVB, Commented Apr 23, 2022 at 15:07

Parfait · Accepted Answer · 2022-04-23 16:09:54Z

1

Consider building a collection of your data pulls in a user defined method. Then, call it whenever needed by main or other scripts:

SQLcon.py

import sqlalchemy as sa
# and other 
imports load_dotenv()
# .env passwords and etc. '''...''' 

def pull_data():
    # creating SQL connection via sqlalchemy 
    connection_url = URL.create(
        "mssql+pyodbc", 
        query={"odbc_connect": connection_string}
    ) 
    engine = sa.create_engine(connection_url) 
    engine.echo = False 

    # creating dfs 
    df_dict = {
        "df1": pd.read_sql_query(qry1, engine),
        "dfA": pd.read_sql_query(qryA, engine),
        "dfZ": pd.read_sql_query(qryZ, engine) 
    }

    # releasing engine
    engine.dispose()

    return df_dict

Main.py (import above as a module)

from SQLcon import pull_data

...

# CALL AS NEEDED
df_dict = pull_data()

# ACCESS DICT ELEMENTS
df_dict["df1"]
df_dict["dfA"]
df_dict["dfZ"]
...

answered Apr 23, 2022 at 16:09

Parfait

108k19 gold badges102 silver badges138 bronze badges

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

2 Comments

NoobVB Over a year ago

Zzzuper, you have saved me a day or two! :) Thanks a lot! just a quick question, if SQL login credentials are left outside the function, will they be triggered? Anyway, will test and I am 99.99% sure it will work as per example

Parfait Over a year ago

Yes, method uses all its environment when called including import lines.

Collectives™ on Stack Overflow

SQL multiple queries using the One Connection on Python

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related