CODEWITHSUNDEEP
pythonsqlsnowflake-cloud-data-platform
Hamed
Hamed

Reputation: 1213

Snowflake Python connector only returns two query results

I am using snowflake.connector to run four queries one after another. All queries should return some results, but only the first two return results. If I comment out the first two, then the next two queries would successfully return results. It seems like there is a soft limit of two somewhere. Any idea what is wrong?

self.cnxn = snowflake.connector.connect(
            user=self.username,
            password=self.password,
            account=self.account,
            warehouse=self.warehouse)
self.cursor = self.cnxn.cursor()

self.cursor.execute("SELECT * FROM db.schema.table1")
rows = self.cursor.fetchall()

self.cursor.execute("SELECT * FROM db.schema.table2")
rows = self.cursor.fetchall()

self.cursor.execute("SELECT * FROM db.schema.table3")
rows = self.cursor.fetchall()  # returns empty resultset 

self.cursor.execute("SELECT * FROM db.schema.table4")
rows = self.cursor.fetchall()  # returns empty resultset

Note that I don't get any error messages.

Upvotes: 1

Views: 1957

Answers (1)

Felipe Hoffa
Felipe Hoffa

Reputation: 59175

I can not reproduce. What versions are you using?

This worked well for me:

cnxn = snowflake.connector.connect(
            user=user,
            password=password,
            account=account,
            warehouse='s')
cursor = cnxn.cursor()
cursor.execute("""SELECT * FROM "KNOEMA_ECONOMY_DATA_ATLAS"."ECONOMY"."EPUUI2019Apr" LIMIT 3""")
print(cursor.fetchall())
cursor.execute("""SELECT * FROM "KNOEMA_ECONOMY_DATA_ATLAS"."ECONOMY"."BEANIPA" LIMIT 3""")
print(cursor.fetchall())
cursor.execute("""SELECT * FROM "KNOEMA_ECONOMY_DATA_ATLAS"."ECONOMY"."BLSLA" LIMIT 3""")
print(cursor.fetchall())
cursor.execute("""SELECT * FROM "KNOEMA_ECONOMY_DATA_ATLAS"."ECONOMY"."DP_LIVE" LIMIT 3""")
print(cursor.fetchall())

My config is the Snowflake provided Sagemaker image.

Now, if the queries are independent of each other and each takes time to run, your code might benefit from running them asynchronously:

import time
start = time.time()

cnxn = snowflake.connector.connect(
            user=user,
            password=password,
            account=account,
            warehouse='s')

cursor = cnxn.cursor()
cursor.execute_async(
    """SELECT * FROM "KNOEMA_ECONOMY_DATA_ATLAS"."ECONOMY"."EPUUI2019Apr" LIMIT 3""")
query_id_1 = cursor.sfqid
cursor.execute_async(
    """SELECT * FROM "KNOEMA_ECONOMY_DATA_ATLAS"."ECONOMY"."BEANIPA" LIMIT 3""")
query_id_2 = cursor.sfqid
cursor.execute_async(
    """SELECT * FROM "KNOEMA_ECONOMY_DATA_ATLAS"."ECONOMY"."BLSLA" LIMIT 3""")
query_id_3 = cursor.sfqid
cursor.execute_async(
    """SELECT * FROM "KNOEMA_ECONOMY_DATA_ATLAS"."ECONOMY"."DP_LIVE" LIMIT 3""")
query_id_4 = cursor.sfqid

for id in [query_id_1, query_id_2, query_id_3, query_id_4]:
    while cnxn.is_still_running(cnxn.get_query_status(id)):
      time.sleep(1)
    cursor.get_results_from_sfqid(id)
    print(cursor.fetchall())
print(time.time() - start)

Upvotes: 3

Related Questions