Python Database API v2 > Iteration over result set stops if a query executed during the iteration returns no results

Question

I'm using the Snowflake Connector for Python (which implements support for the Python Database API v2 specification) in a script which pulls a number of records from one table, iterates over the result set and, for each row returned, queries another table that may, or may not, return any results.

In the case that no results are returned for a secondary query, iteration over the results returned for the first query stops, even though no error is thrown.

The following code demonstrates the issue I'm running into...

cur = cnx.cursor()
foobars = cur.execute("SELECT * FROM foobar")
    for foobar in foobars:
        foobarId = foobar[0]

        # Iteration over foobars stops if no records are returned for the following 
        foobaz = cur.execute("SELECT * FROM foobaz WHERE foobarId = %s", (foobarId))
        if foobaz.rowcount != 0:
            # Iterate over foobaz here

If an exception were thrown, I could handle this scenario by capturing the error and moving on to the next result. Given that no exception is thrown, how would one handle this scenario?

Answer 1

The cursor stores the state of the last SELECT. So you have to create a new cursor for a parallel SELECT:

cur = cnx.cursor()
cur.execute("SELECT * FROM foobar")
for foobar in cur:
    foobarId = foobar[0]

    cur2 = cnx.cursor()
    cur2.execute("SELECT * FROM foobaz WHERE foobarId = %s", (foobarId,))
    for foobaz in cur2:
        # Iterate over foobaz here