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('---------------------------------------------------------------------------------------')

Why not just add `input("press any key")` at the and of `for` loop? — Arnial, Jan 18 '17 at 10:25
Related: http://stackoverflow.com/q/24527006/1639625 Apply this to your `self.results` iterator. — tobias_k, Jan 18 '17 at 10:27
If i put input("press any key"), i will have to hit enter 130 times, this isn't what i'm looking for. I use pymyql — , Jan 18 '17 at 10:27

tobias_k · Accepted Answer · 2017-01-18T10:51:00.670

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

score 1 · Answer 2 · edited May 23 '17 at 12:24

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

@tobias_k No coincidence, I'll add the attribution. I have other details to edit in as well. — Tagc, Jan 18 '17 at 10:39
Also, note that his prints one superfluous "press enter" at the end (that's why I did not use it in the end) and you can prevent the `StopIteration` by using `for` loop instead of `while` and `next` — tobias_k, Jan 18 '17 at 10:40
@tobias_k I've resolved the `StopIteration` error, but I don't believe it's possible for me to rely on for loops or prevent the final "Press enter" if I want to maintain *lazy generation* of the results. Unless you know a way? +1 to your answer by the way; it's succinct. — Tagc, Jan 18 '17 at 10:50
Instead of using `try/while/next/except` you can really just use a `for` loop. I also explained this in my original answer. `for res in generator: print_results(res)` — tobias_k, Jan 18 '17 at 10:52
@tobias_k Ah right yeah, I'm being stupid. Fixed that now. That will still give the final "Press enter" message, which I believe is unavoidable (with lazily generated data) as it won't know that the data sequence has been exhausted until it tries to fetch the next chunk. — Tagc, Jan 18 '17 at 10:56
@tobias_k I was wrong about it not being possible for lazily generated data. I saw your solution to the problem below and implemented it that way - it's clever. I've provided attribution. I feel pretty dumb now but on the bright side I'll remember these things for future use. — Tagc, Jan 18 '17 at 11:07

Python fetchall() with pagination

2 Answers2