[FIXED] Read_sql returning results even though SQL table not present

Issue

I think I've lost my mind. I have created a python script to read a temp table in SQL SSMS. While testing, we found out that python is able to query and read the table even when it's not there/querable in SSMS. I believe the DF is storing in cache or something but let me break down the problem into steps:

1. Starting point, the temp table is present in SSMS, MAIN_DF = python.read_sql('SELECT Statement') and stored in DF and saved to excel file (using ExcelWriter)

2. We delete the temp table in SQL, then run the python script again. To make sure, we use THE SAME 'SELECT' statement found in the python script in SSMS and it displays 'Invalid object name' which is correct because the table has been dropped. BUT when I run the python script again, it is able to query the table and get the same results it had before! It should be throwing the same error as SSMS because the table isn't there! Why isn't python starting from scratch when I run it? It seems to be holding information over from the initial run. How do I ensure I am starting from scratch every time I run it?

I have tried many things including starting the script with blank DF's so they should not have anything held over. 'MAIN_DF = pd.DataFrame()'. I have tried deleting the DF's at the end as well. 'del MAIN_DF'

I don't understand what is happening..

try:
    conn = pyodbc.connect(r'Driver={SQL Server};Server=GenericServername;Database=testdb;Trusted_Connection=yes;')
    print('Connected to SQL: ' + str(datetime.now()))
    MAIN_DF = pd.read_sql('SELECT statement',conn)
    print('Queried Main DF: ' + str(datetime.now()))

Solution

It's because I didn't close the connection conn.close() so it was cached in the memory and SQL didn't perform / close it

Answered By - Elliot Gitter