Test
import pandas as pd
import pyodbc
from concurrent.futures import ThreadPoolExecutor
#==============================================================================
# Function to establish a database connection
#==============================================================================
def get_db_connection():
return pyodbc.connect("Driver={SQL Server Native Client 11.0};"
"Server=LPRDISSDM.cftc.gov;"
"Database=ISS_DB;"
"Trusted_Connection=yes;")
#==============================================================================
# Function to read SQL from the file
#==============================================================================
def read_sql_from_file(file_path):
with open(file_path, 'r') as file_object:
sql_statement = file_object.read()
return sql_statement
#==============================================================================
# Function to execute a SQL query and return the DataFrame
#==============================================================================
def execute_sql(file_path):
conn = get_db_connection() # Create a new connection for this thread
try:
query = read_sql_from_file(file_path)
return pd.read_sql_query(query, conn)
finally:
conn.close() # Ensure the connection is closed
#==============================================================================
# File paths for the SQL queries
#==============================================================================
file_location = 'D:/Data/OneDrive/ipradhan/OneDrive - CFTC/Ish-OneDrive/_00-01-SQL_tool/ParallelProcessing/'
file_name_option = 'OptionsCTE-Filler.sql'
file_name_futures = 'FuturesCTE-Filler.sql'
full_file_name_option = file_location + file_name_option
full_file_name_futures = file_location + file_name_futures
#==============================================================================
# Execute the queries in parallel
#==============================================================================
with ThreadPoolExecutor() as executor:
future_option = executor.submit(execute_sql, full_file_name_option)
future_futures = executor.submit(execute_sql, full_file_name_futures)
# Wait for results
df_option = future_option.result()
df_futures = future_futures.result()
#==============================================================================
# Output or process results
#==============================================================================
print("df_option Results:")
print(df_option.head())
print("\ndf_futures Results:")
print(df_futures.head())