Python Database API v2 > Iteration over result set stops if a query executed during the iteration returns no results
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 # 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?作者: Andrew Kirk 的来源 发布者: 2016 年 5 月 17 日
The cursor stores the state of the last SELECT. So you have to create a new cursor for a parallel SELECT:
作者: Daniel 发布者: 17.05.2016 06:45
cur = cnx.cursor() cur.execute("SELECT * FROM foobar") for foobar in cur: foobarId = foobar cur2 = cnx.cursor() cur2.execute("SELECT * FROM foobaz WHERE foobarId = %s", (foobarId,)) for foobaz in cur2: # Iterate over foobaz here