How to iterate PYODBC variable multiple times?-CodePudding

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?

CodePudding user response：

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

CodePudding user response：

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
"""