Retrieving Data - Python

We can now retrieve data from our table by querying our database with “SELECT.” Let's obtain all the values in our table:

>>> cursor.execute('SELECT * FROM names')

The cursor now contains the data from the table, and there are several ways that we can retrieve that data. The first way is a list of every single row that the query returned using the fetchall method:

>>> cursor.execute('SELECT * FROM names')
>>> print cursor.fetchall()
[(1, u'John Doe', u'jdoe@jdoe.zz'), (2, u'Mary Sue',
u'msue@msue.yy'), (3, u'Luke Skywalker', u'use@the.force')]

Notice how it returns a list full of tuples, one tuple for each row. Obviously, it's pretty easy to loop through everything. All that's required is a for loop. However, I should note that an even easier way to loop through returned data is to just iterate through the cursor, like this:

>> cursor.execute('SELECT * FROM names')
>>> for row in cursor:
                print '-'*10
                print 'ID:', row[0]
                print 'Name:', row[1]
                print 'E-Mail:', row[2]
                print '-'*10               

----------

ID: 1
Name: John Doe
E-Mail: jdoe@jdoe.zz

----------

----------

ID: 2
Name: Mary Sue
E-Mail: msue@msue.yy

----------

----------

ID: 3
Name: Luke Skywalker
E-Mail: use@the.force

----------

Iterating through the cursor works well if you don't plan to use the data later on. If you do intend to use it, it's best to just retrieve the list and then loop through that.

The fetchall method is very easy to work with, since you can get everything out of the cursor at once and put it all in a list, which can be used and manipulated in any way you see fit. Let's say, however, that you don't want to get all the rows that the cursor has returned all at once. In this case, the fetchmany method would be a better choice. It accepts a single integer that tells the cursor how many rows it needs to return. In the absence of such an integer, it simply returns one row at a time:

>>> cursor.execute('SELECT * FROM names')
>>> print cursor.fetchmany(2)
[(1, u'John Doe', u'jdoe@jdoe.zz'), (2, u'Mary Sue',
u'msue@msue.yy')]

We can then retrieve the next row when we need it:

>>> print cursor.fetchmany()
[(3, u'Luke Skywalker', u'use@the.force')]

Like the fetchall method, the fetchmany method returns a list of tuples, one for each row returned.

Finally, there's the fetchone method. This fetches one row from the cursor, but it differs from the fetchmany method in that it only returns the row's tuple. It doesn't return a list. It's useful for when you only want to return a single row, or perhaps only a single row at a time, but do not want to have the result contained in a list:

>>> cursor.execute('SELECT * FROM names')
>>> cursor.fetchone()
(1, u'John Doe', u'jdoe@jdoe.zz')

A method by the name of next also exists, and it works similar to fetchone in that it only returns one row at a time. However, instead of returning None when there's nothing left in the cursor, it raises a StopIteration error:

>>> cursor.execute('SELECT * FROM names')
>>> cursor.next()
(1, u'John Doe', u'jdoe@jdoe.zz')
>>> cursor.next()
(2, u'Mary Sue', u'msue@msue.yy')
>>> cursor.next()
(3, u'Luke Skywalker', u'use@the.force')
>>> cursor.next()
Traceback (most recent call last):
  File "<pyshell#201>", line 1, in -toplevel-
    cursor.next()
StopIteration