CODEWITHSUNDEEP
pythonsqlpandas
NoobVB
NoobVB

Reputation: 1033

SQL multiple queries using the One Connection on Python

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)

Upvotes: 0

Views: 931

Answers (1)

Parfait
Parfait

Reputation: 107737

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"]
...

Upvotes: 1

Related Questions