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()

score 1 · Answer 1 · answered May 22 '22 at 21:50

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()

IMO the best way would be to create a function to run queries. This could for instance create a connection, receive the query / file with the query and output a dataframe directly. With this you could easily use a loop or even faster run multiplies queries at the same time using eg map from multiprocessing.Pool — Daquisu, May 22 '22 at 23:24
Sorry..could you provide more information on how will that look,perhaps with code ? TIA — biggboss2019, May 22 '22 at 23:29
Python docs is good for this: https://docs.python.org/3/library/multiprocessing.html You create a pool of workers which will execute your function with different arguments at the same time. No need to change your function. If you are going to use multiple arguments, you can refer to this SO: https://stackoverflow.com/q/5442910/9964778 — Daquisu, May 22 '22 at 23:32

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

1 Answers1