Iterating over table multiple times Python SQLAlchemy

Question

I queried two databases to get two relations. I terate over those relations once to form maps, and then again to perform some calculations. However, when I attempt iterate over the same relations a second time, I find that no iteration is actually occurring. Here is the code:

dev_connect = dev_engine.connect()
prod_connect = prod_engine.connect() # from a different database
Relation1 = dev_engine.execute(sqlquery1)
Relation2 = prod_engine.execute(sqlquery)

before_map = {}
after_map = {}
for row in Relation1:
    before_map[row['instrument_id']] = row
for row2 in Relation2:
    after_map[row2['instrument_id']] = row2

update_count = insert_count = delete_count = 0

change_list = []
count =0
for prod_row in Relation2:
    count += 1
    result = list(prod_row)
    ...
    change_list.append(result)

count2 = 0
for before_row in Relation1:
    count2 += 1
    result = before_row
    ...

print count, count2 # prints 0

before_map and after_map are not empty, so Relation1 and Relation2 definitely have tuples in them. Yet count and count2 are 0, so the prod_row and before_row 'for loops' aren't actually occurring. Why can't I iterate over Relation1 and Relation2 a second time?

wkl · Accepted Answer · 2016-01-04 18:02:01Z

4

When you call execute on a SQL Alchemy engine, you get back a ResultProxy, which is a facade to a DBAPI cursor to the rows your query returns.

Once you iterate over all the results of the ResultProxy, it automatically closes the underlying cursor so you can't use the results again by just iterating over it, as documented on the SQLAlchemy page:

The returned result is an instance of ResultProxy, which references a DBAPI cursor and provides a largely compatible interface with that of the DBAPI cursor. The DBAPI cursor will be closed by the ResultProxy when all of its result rows (if any) are exhausted.

You can solve your problem a couple ways:

Store the results in a list. Just do a list-comprehension against the rows returned:

Relation1 = dev_engine.execute(sqlquery1)
relation1_items = [r for r in Relation1]
# ...
# now you can iterate over relation1_items as much as you want

Do everything you need to in one pass through each row set returned. I don't know if this option is feasible for you since I don't know if the full extent of your calculations require cross-referencing between your before_map and after_map objects.

answered Jan 4, 2016 at 18:02

wkl

80.4k16 gold badges171 silver badges178 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Jeremy Fisher Over a year ago

Is there no way to reopen the cursor?

wkl Over a year ago

@JeremyFisher - As far as I know, SQL Alchemy/DBAPI don't offer support for scrollable cursors, so the cursor you get back is a use-once-and-forget deal.

holdenweb Over a year ago

A rather more Pythonic way to compute the same result would be relation1_items = list(Relation1).

Collectives™ on Stack Overflow

Iterating over table multiple times Python SQLAlchemy

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related