Documentation Home
X DevAPI User Guide for MySQL Shell in Python Mode
Download this Manual
PDF (US Ltr) - 1.2Mb
PDF (A4) - 1.2Mb


9.4 Fetching All Data Items at Once

In addition to the pattern of using fetchOne() explained at Section 9.3, “Working with Data Sets”, which enables applications to consume data items one by one, X DevAPI also provides a pattern using fetchAll(), which passes all data items of a data set as a list to the application. The different X DevAPI implementations use appropriate data types for their programming language for the list. Because different data types are used, the language's native constructs are supported to access the list elements. The following example assumes that the test schema exists and that the employee table exists in myTable.

myResult = myTable.select(['name', 'age']) \
  .where('name like :name').bind('name','L%') \
  .execute()

myRows = myResult.fetch_all()

for row in myRows:
  print("%s is %s years old." % (row.name, row.age))

When mixing fetchOne() and fetchAll() to read from one data set keep in mind that every call to fetchOne() or fetchAll() consumes the data items returned. Items consumed cannot be requested again. If, for example, an application calls fetchOne() to fetch the first data item of a data set, then a subsequent call to fetchAll() returns the second to last data item. The first item is not part of the list of data items returned by fetchAll(). Similarly, when calling fetchAll() again for a data set after calling it previously, the second call returns an empty collection.

The use of fetchAll() forces a Connector to build a list of all items in memory before the list as a whole can be passed to the application. The life time of the list is independent from the life of the data set that has produced it.

Asynchronous query executions return control to caller once a query has been issued and prior to receiving any reply from the server. Calling fetchAll() to read the data items produced by an asynchronous query execution may block the caller. fetchAll() cannot return control to the caller before reading results from the server is finished.