>>> curs.execute('select * from people')
>>> names = [rec[0] for rec in curs.fetchall()]
>>> names
['Bob', 'Sue', 'Ann', 'Tom', 'Kim', 'pat']The fetchall call we’ve used so far fetches the entire query result table all at once, as
a single sequence (an empty sequence comes back, if the result is empty). That’s con-
venient, but it may be slow enough to block the caller temporarily for large result tables
or generate substantial network traffic if the server is running remotely (something
could easily require a parallel thread in GUI). To avoid such a bottleneck, we can also
grab just one row, or a bunch of rows, at a time with fetchone and fetchmany. The
fetchone call returns the next result row or a None false value at the end of the table:
>>> curs.execute('select * from people')
>>> while True:
... row = curs.fetchone()
... if not row: break
... print(row)
...
('Bob', 'dev', 5000)
('Sue', 'mus', 70000)
('Ann', 'mus', 60000)
('Tom', 'mgr', 100000)
('Kim', 'adm', 30000)
('pat', 'dev', 90000)The fetchmany call returns a sequence of rows from the result, but not the entire table;
you can specify how many rows to grab each time with a parameter or rely on the default
as given by the cursor’s arraysize attribute. Each call gets at most that many more rows
from the result or an empty sequence at the end of the table:
>>> curs.execute('select * from people')
>>> while True:
... rows = curs.fetchmany() # size=N optional argument
... if not rows: break
... for row in rows:
... print(row)
...
('Bob', 'dev', 5000)
('Sue', 'mus', 70000)
('Ann', 'mus', 60000)
('Tom', 'mgr', 100000)
('Kim', 'adm', 30000)
('pat', 'dev', 90000)For this module at least, the result table is exhausted after a fetchone or fetchmany
returns a False value. The DB API says that fetchall returns “all (remaining) rows,”
so you generally need to call execute again to regenerate results before fetching
new data:
>>> curs.fetchone()
>>> curs.fetchmany()
[]SQL Database Interfaces| 1337