How to execute multiple cursors and fetch the data into two different Pandas dataframe using Python?

Question

I am connecting to Snowflake and using below query I can fetch the data for one table. I want to fetch the data for multiple table. So I tried cursor.exceute for table2. However, dataframe only printed the result for last query.I need to fetch data from 2 tables into 2 different dataframe.

Thanks in advance!

Python Code:

import pandas as pd
import snowflake.connector
import os

tables = ['CUSTOMER', 'CALL_CENTER', 'CUSTOMER_ADDRESS','CATALOG_PAGE','CUSTOMER_DEMOGRAPHICS']
filename = 'output.txt'

def main():  
        conn = snowflake.connector.connect(
            user="my_usdr",
            password="pswd",
            account="my_account",
            warehouse="my_WH",
            database="SNOWFLAKE_SAMPLE_DATA",
            schema="INFORMATION_SCHEMA",
            role="SYSADMIN")
        cur = conn.cursor()
        try:
            cur.execute(f"""SELECT TABLE_SCHEMA,TABLE_TYPE,TABLE_NAME, ROW_COUNT, DATE(CREATED) AS "TABLE_CREATED_DATE", DATE(LAST_ALTERED) AS "LAST_ALTERED_DATE" FROM TABLES WHERE TABLE_TYPE='BASE TABLE' 
    AND TABLE_SCHEMA='TPCDS_SF100TCL' AND TABLE_NAME IN ({','.join("'" + x + "'" for x in tables)})
    UNION
    SELECT TABLE_SCHEMA,TABLE_TYPE,TABLE_NAME, ROW_COUNT, DATE(CREATED) AS "TABLE_CREATED_DATE", DATE(LAST_ALTERED) AS "LAST_ALTERED_DATE" FROM TABLES WHERE TABLE_TYPE='BASE TABLE' 
    AND TABLE_SCHEMA='TPCDS_SF10TCL' 
    AND TABLE_NAME IN ({','.join("'" + x + "'" for x in tables)})""")
             cur.execute (f"""select * from TPCDS_SF100TCL.CALL_CENTER""")
            df = cur.fetch_pandas_all()
           print(df) # Print in same screen after execution
          
            cur.close()
            conn.close()
        except Exception as e:
            print(e)
          #  sys.exit(1)
            cur.close()
            conn.close()
                                
if __name__ == "__main__":
    main()     

Answer 1

After the second cur.execute, the query result in the variable cur was overwritten. You can try the solution below, where I save the results after the first query in the variable df1 and the second query in the variable df2

import pandas as pd
import snowflake.connector
import os

tables = ['CUSTOMER', 'CALL_CENTER', 'CUSTOMER_ADDRESS','CATALOG_PAGE','CUSTOMER_DEMOGRAPHICS']
filename = 'output.txt'

def main():  
        conn = snowflake.connector.connect(
            user="my_usdr",
            password="pswd",
            account="my_account",
            warehouse="my_WH",
            database="SNOWFLAKE_SAMPLE_DATA",
            schema="INFORMATION_SCHEMA",
            role="SYSADMIN")
        cur = conn.cursor()
        try:
            cur.execute(f"""SELECT TABLE_SCHEMA,TABLE_TYPE,TABLE_NAME, ROW_COUNT, DATE(CREATED) AS "TABLE_CREATED_DATE", DATE(LAST_ALTERED) AS "LAST_ALTERED_DATE" FROM TABLES WHERE TABLE_TYPE='BASE TABLE' 
    AND TABLE_SCHEMA='TPCDS_SF100TCL' AND TABLE_NAME IN ({','.join("'" + x + "'" for x in tables)})
    UNION
    SELECT TABLE_SCHEMA,TABLE_TYPE,TABLE_NAME, ROW_COUNT, DATE(CREATED) AS "TABLE_CREATED_DATE", DATE(LAST_ALTERED) AS "LAST_ALTERED_DATE" FROM TABLES WHERE TABLE_TYPE='BASE TABLE' 
    AND TABLE_SCHEMA='TPCDS_SF10TCL' 
    AND TABLE_NAME IN ({','.join("'" + x + "'" for x in tables)})""")
            df1 = cur.fetch_pandas_all()
            cur.execute (f"""select * from TPCDS_SF100TCL.CALL_CENTER""")
            df2 = cur.fetch_pandas_all()
            print(df1) # Print first result
            print(df2) # Print second result
          
            cur.close()
            conn.close()
        except Exception as e:
            print(e)
          #  sys.exit(1)
            cur.close()
            conn.close()
                                
if __name__ == "__main__":
    main()