Python fetchall() with pagination

Question

Can someone help me with the following code? I have more than 130 records, and using fetchall() gives me all of them which makes the data impossible to read. Instead I would prefer to fetch 10 records at a time (using the Enter key for instance).

This is my code:

def getAllPerguntasDB(self):
    query = "SELECT p.id_pergunta,t.tema,d.grau,p.pergunta,p.op1,p.op2,p.op3,p.op4,p.correto FROM perguntas AS p INNER JOIN temas  AS t ON (p.id_tema = t.id_tema) INNER JOIN dificuldade AS d ON (p.id_grau = d.id_grau) ORDER BY t.tema, d.grau ASC;"
    self.cur.execute(query)
    self.result = self.cur.fetchall()
    for row in self.result:
         id_pergunta = row[0]
         tema = row[1]
         grau = row[2]
         pergunta = row[3]
         op1 = row[4]
         op2 = row[5]
         op3 = row[6]
         op4 = row[7]
         correto= row[8]
         print('---------------------------------------------------------------------------------------')
         print("id: ", id_pergunta)
         print("Tema:",tema,"\tGrau:",grau)
         print("Pergunta: ", pergunta)
         print("Opção 1: ", op1, "\tOpção 2: ", op2, "\tOpção 3: ", op3, "\tOpção 4: ", op4)
         print("Resposta Correta: ", correto)
         print('---------------------------------------------------------------------------------------')

Answer 1

The simplest way would probably be to count the results using enumerate and show an input line or similar every 10 lines, using the modulo operator %.

for i, row in enumerate(self.result, start=1):
    print row
    if i % 10 == 0:
        input("Hit enter to show more")

Note: This will show an additional "hit enter" line if you have exactly a multiple of 10 of results. To fix this, you can use this slight variation of above approach:

for i, row in enumerate(self.result):
    if i > 0 and i % 10 == 0:
        input("Hit enter to show more")
    print row

Answer 2

I haven't used any sort of database querying in Python before, but I'm treating this simply as a problem of generating a sequence of data in fixed-sized chunks, waiting for user input in-between chunks.

The advantage of using generators here is that it may allow the program to behave more efficiently if the database results (i.e. the output of self.cur.fetchall()) are lazily generated as well; not all database results will be calculated unless the user specifically requests for them (by hitting Enter enough times). Note that I can't guarantee this is as I don't know how fetchall works.

The batching logic is derived from tobias_k's solution here. The method to avoid printing the final "Press enter" message was also based on his solution to your question here.

In the code below, I mock out your database results with a fixed range of values from 0 to 29. However you should be able to adapt the principle of its operation for use with the data you're extracting from the database.

from itertools import islice, chain


def get_data():
    # query = "SELECT p.id_pergunta,t.tema,d.grau,p.pergunta,p.op1,p.op2,p.op3,p.op4,p.correto FROM perguntas AS p INNER JOIN temas  AS t ON (p.id_tema = t.id_tema) INNER JOIN dificuldade AS d ON (p.id_grau = d.id_grau) ORDER BY t.tema, d.grau ASC;"
    # self.cur.execute(query)
    # self.result = self.cur.fetchall()

    result = range(0, 30)

    yield from result


def batch(iterable, batch_size):
    iterator = iter(iterable)
    for first in iterator:
        yield chain([first], islice(iterator, batch_size - 1))


def print_results(batched_results):
    for result in batched_results:
        print(result)


if __name__ == '__main__':  
    for i, batched_results in enumerate(batch(get_data(), batch_size=10)):
        if i > 0:
            input('Press enter for more results')
        print_results(batched_results)

Output

0
1
2
3
4
5
6
7
8
9
Press enter for more results
10
11
12
13
14
15
16
17
18
19
Press enter for more results
20
21
22
23
24
25
26
27
28
29