-1

I am querying a SQL Server table from Python like following:

query = "SELECT PN, PD, VT FROM inpat"
data = cursor.execute(query)

Next, I am iterating data like following

for i in data:
    print(i)

Above loop works. However, I want to iterate data multiple time like following

for j in data:
    print(j)

The second for loop is not printing anything. How I can iterate data for multiple times?

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
Beautiful Mind
  • 5,828
  • 4
  • 23
  • 42
  • 1
    You exhaust the cursor after looping through `data`. If you want to use the data multiple times, store it into a list like `l = list(data)` and then you can iterate through it repeatedly (without having to re-query the db). – wkl Aug 19 '22 at 19:26

2 Answers2

0

It's a cursor, not an in-memory collection. As you iterate it results are pulled from the server and consumed. To re-iterate, you must re-run the query with

data = cursor.execute(query)

But instead of running the query multiple times you should probably just load the data into a Pandas DataFrame, eg Read data from pyodbc to pandas

David Browne - Microsoft
  • 80,331
  • 6
  • 39
  • 67
0

You can also iterate through the result set multiple times if you use .fetchall() to pull it into a list of pyodbc.Row objects:

data = crsr.execute(
    """\
SELECT 'foo' AS thing
UNION ALL
SELECT 'bar' AS thing
"""
).fetchall()

for row in data:
    print(row.thing)
"""
foo
bar
"""

for row in data:
    print(row.thing)
"""
foo
bar
"""
Gord Thompson
  • 116,920
  • 32
  • 215
  • 418