Home arrow Python arrow Page 4 - Using SQLite in Python

Retrieving Data - Python

SQLite is a small C library that implements a self-contained SQL database engine. This article examines pysqlite, one of a number of libraries in Python, that is used as an interface with SQLite.

TABLE OF CONTENTS:
  1. Using SQLite in Python
  2. Connecting to a Database
  3. Writing Data
  4. Retrieving Data
  5. Adapting and Converting
By: Peyton McCullough
Rating: starstarstarstarstar / 132
April 12, 2006

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

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



 
 
>>> More Python Articles          >>> More By Peyton McCullough
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

PYTHON ARTICLES

- Python Big Data Company Gets DARPA Funding
- Python 32 Now Available
- Final Alpha for Python 3.2 is Released
- Python 3.1: String Formatting
- Python 3.1: Strings and Quotes
- Python 3.1: Programming Basics and Strings
- Tuples and Other Python Object Types
- The Dictionary Python Object Type
- String and List Python Object Types
- Introducing Python Object Types
- Mobile Programming using PyS60: Advanced UI ...
- Nested Functions in Python
- Python Parameters, Functions and Arguments
- Python Statements and Functions
- Statements and Iterators in Python

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: