how to re-initialize the row index count in result set of a SELECT query in sqlite3?

Question

I m Executing a SELECT Query. I m getting a result set. The Problem is if I iterate once , I can't Iterate Again.I think the counter needs to be reinitialized. But I can't do it.

In my code , The first FOR LOOP is working but second is not. Please Help.

I have Searched on stackoverflow for this But I couldn't find any answer

CODE

class ItemList(Resource):

 def get(self):
    connection  = sqlite3.connect("data.db")
    cursor = connection.cursor()
    select_query = "SELECT * FROM items"
    rows = cursor.execute(select_query)
    items=[]
    for row in rows:
        print(row)
    if rows:
        for row in rows:
            items.append({'name':row[0],'price':row[1]})
    connection.commit()
    connection.close()
    return {'items':items},200
    

The Expected Result is [{'name': 'chair', 'price': 23.456}, {'name': 'table', 'price': 3333}, {'name': 'van', 'price': 1234}] for items Array

Answer 1

If you "treat the cursor as an iterator", as is done here rows = cursor.execute(select_query), it does the fetching "under the hood".

From the sqlite3 API doc [emphasis added]:

To retrieve data after executing a SELECT statement, you can either treat the cursor as an iterator, [or] call the cursor’s fetchone() method to retrieve a single matching row, or call fetchall() to get a list of the matching rows.

If you change rows = cursor.execute(select_query) to rows = cursor.execute(select_query).fetchall(), rows will be an array containing the results and you can iterate over it as many times as necessary.