Snowflake Python connector only returns two query results

user_2627218 0
Viewed 929

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.

snowflake-cloud-data-platform sql python
1 Answers

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)
user_1324380
Related