Retrieving Data
Now that our database has been created and populated, we can run queries to search for data that meets specified criteria. The general form of a query is as follows:
| | SELECT ColumnName , ... FROM TableName |
The TableName is the name of the table to get the data from and the column names specify which columns to get values from. For example, this query retrieves all the data in the table PopByRegion:
| | >>> cur.execute('SELECT Region, Population FROM PopByRegion') |
Once the database has executed this query for us, we can access the results one record at a time by calling the cursor’s fetchone method, just as we can read one line at a time from a file using readline:
| | >>> cur.fetchone() |
| | ('Central Africa', 330993) |
The fetchone method returns each record as a tuple (see Storing Data Using Tuples) whose elements are in the order specified in the query. If there are no more records, fetchone returns None.
Just as files have a readlines method to get all the lines in a file at once, database cursors have a fetchall method that returns all the data produced by a query that has not yet been fetched as a list of tuples:
| | >>> cur.fetchall() |
| | [('Southeastern Africa', 743112), ('Northern Africa', 1037463), ('Southern |
| | Asia', 2051941), ('Asia Pacific', 785468), ('Middle East', 687630), |
| | ('Eastern Asia', 1362955), ('South America', 593121), ('Eastern Europe', |
| | 223427), ('North America', 661157), ('Western Europe', 387933), ('Japan', |
| | 100562)] |
Once all of the data produced by the query has been fetched, any subsequent calls on fetchone and fetchall return None and the empty list, respectively:
| | >>> cur.fetchone() |
| | >>> cur.fetchall() |
| | [] |
Like a dictionary or a set (Chapter 11, Storing Data Using Other Collection Types), a database stores records in whatever order it thinks is most efficient. To put the data in a particular order, we could sort the list returned by fetchall. However, it is more efficient to get the database to do the sorting for us by adding an ORDER BY clause to the query like this:
| | >>> cur.execute('SELECT Region, Population FROM PopByRegion ORDER BY Region') |
| | >>> cur.fetchall() |
| | [('Asia Pacific', 785468), ('Central Africa', 330993), ('Eastern Asia', |
| | 1362955), ('Eastern Europe', 223427), ('Japan', 100562), ('Middle East', |
| | 687630), ('North America', 661157), ('Northern Africa', 1037463), ('South |
| | America', 593121), ('Southeastern Africa', 743112), ('Southern Asia', |
| | 2051941), ('Western Europe', 387933)] |
By changing the column name after the phrase ORDER BY, we can change the way the database sorts. As the following code demonstrates, we can also specify whether we want values sorted in ascending (ASC) or descending (DESC) order:
| | >>> cur.execute('''SELECT Region, Population FROM PopByRegion |
| | ORDER BY Population DESC''') |
| | <sqlite3.Cursor object at 0x102e3e490> |
| | >>> cur.fetchall() |
| | [('Southern Asia', 2051941), ('Eastern Asia', 1362955), ('Northern Africa', |
| | 1037463), ('Asia Pacific', 785468), ('Southeastern Africa', 743112), |
| | ('Middle East', 687630), ('North America', 661157), ('South America', |
| | 593121), ('Western Europe', 387933), ('Central Africa', 330993), ('Eastern |
| | Europe', 223427), ('Japan', 100562)] |
As we’ve seen, we can specify one or more columns by name in a query. We can also use * to indicate that we want all columns:
| | >>> cur.execute('SELECT Region FROM PopByRegion') |
| | <sqlite3.Cursor object at 0x102e3e490> |
| | >>> cur.fetchall() |
| | [('Central Africa',), ('Southeastern Africa',), ('Northern Africa',), |
| | ('Southern Asia',), ('Asia Pacific',), ('Middle East',), ('Eastern |
| | Asia',), ('South America',), ('Eastern Europe',), ('North America',), |
| | ('Western Europe',), ('Japan',)] |
| | >>> cur.execute('SELECT * FROM PopByRegion') |
| | <sqlite3.Cursor object at 0x102e3e490> |
| | >>> cur.fetchall() |
| | [('Central Africa', 330993), ('Southeastern Africa', 743112), |
| | ('Northern Africa', 1037463), ('Southern Asia', 2051941), ('Asia |
| | Pacific', 785468), ('Middle East', 687630), ('Eastern Asia', 1362955), |
| | ('South America', 593121), ('Eastern Europe', 223427), ('North America', |
| | 661157), ('Western Europe', 387933), ('Japan', 100562)] |
Query Conditions
Much of the time, we want only some of the data in the database. (Think about what would happen if you asked Google for all of the web pages it had stored.) We can select a subset of the data by using the keyword WHERE to specify conditions that the rows we want must satisfy. For example, we can get the regions with populations greater than one million using the greater-than operator:
| | >>> cur.execute('SELECT Region FROM PopByRegion WHERE Population > 1000000') |
| | <sqlite3.Cursor object at 0x102e3e490> |
| | >>> cur.fetchall() |
| | [('Northern Africa',), ('Southern Asia',), ('Eastern Asia',)] |
These are the relational operators that may be used with WHERE:
|
Operator |
Description |
|---|---|
|
= |
Equal to |
|
!= |
Not equal to |
|
> |
Greater than |
|
< |
Less than |
|
>= |
Greater than or equal to |
|
<= |
Less than or equal to |
Not surprisingly, they are the same as the ones that Python and other programming languages provide. As well as these relational operators, we can also use the AND, OR, and NOT operators. To get a list of regions with populations greater than one million that have names that come before the letter L in the alphabet, we would use this (we are using a triple-quoted string for the SQL statement so that it can span multiple lines):
| | >>> cur.execute('''SELECT Region FROM PopByRegion |
| | WHERE Population > 1000000 AND Region < "L"''') |
| | <sqlite3.Cursor object at 0x102e3e490> |
| | >>> cur.fetchall() |
| | [('Eastern Asia',)] |
WHERE conditions are always applied row by row—they cannot be used to compare two or more rows. We will see how to do that in Using Joins to Combine Tables.