Retrieving Data from database (SQLite3)

In the previous post we learned how to create and populate database. We created employees DB and a Sales table. Now we will learn how to retrieve data from DB. Data retrieval is also done using SQL statements and the syntax is as follows:

SELECT << ColumnName >>,..... FROM << TableName >>

where the TableName  is the name of the table to get the data from and the column names specify which columns to get values from. Thus to retrieve the names of Regions from the sales table we can say:

SELECT Region FROM sales

This query can be used in our program with the execute() as shown below:

cur.execute('SELECT Region FROM sales ')

The following program prints the names of all regions in the Sales table:

import sqlite3

con = sqlite3.connect('employees.db')

cur = con.cursor()

regions = cur.execute('SELECT Region FROM sales ')

for region in regions:

print(region)

The output of the program is shown below:

('EAST',)

('WEST',)

('NORTH',)

('SOUTH',)

------------------

(program exited with code: 0)

Press any key to continue . . .

Instead of the appraoch shown in the above program we use the cursor's fetchone() and fetchall() methods to retrieve data from the DB. The fetchone() method returns each record as a tuple whose elements are in the order specified in the query. If there are no more records fetchone() method returns None.

import sqlite3

con = sqlite3.connect('employees.db')

cur = con.cursor()

cur.execute('SELECT Region FROM sales ')

print(cur.fetchone())

The output of the program is shown below:

('EAST',)

------------------

(program exited with code: 0)

Press any key to continue . . .

The fetchall() method returns all the data produced by a query that has not yet been fetched as a list of tuples. If we use only fetchall() method in the above program the output will be as shown below:

[('EAST',), ('WEST',), ('NORTH',), ('SOUTH',)]

------------------

(program exited with code: 0)

Press any key to continue . . .

If we use both fetchone() and fetchall() methods in the above program the output will be as shown below:

('EAST',)

[('WEST',), ('NORTH',), ('SOUTH',)]

------------------

(program exited with code: 0)

Press any key to continue . . .

Once all of the data produced by the query has been fetched, any subsequent calls on fetchone() and fetchall() methods return None and the empty list, respectively.  See the following program:

import sqlite3

con = sqlite3.connect('employees.db')

cur = con.cursor()

cur.execute('SELECT Region FROM sales ')

print(cur.fetchone())

print(cur.fetchall())

print(cur.fetchone())

print(cur.fetchall())

Since we use both fetchone() and fetchall() methods in the above program twice the output will be as shown below:

('EAST',)

[('WEST',), ('NORTH',), ('SOUTH',)]

None

[]

------------------

(program exited with code: 0)

Press any key to continue . . .

A database stores records in whatever order it thinks is most efficient. To put the data in a particular order, we can either sort the list returned by fetchall() method or get the database to do the sorting for us by adding an ORDER BY clause to the query. Thus our query to get the names of Region in a sorted order would become:

SELECT Region FROM sales ORDER BY Region

Let's use this in our program to print the names of regions in a sorted order. See the following code:

import sqlite3

con = sqlite3.connect('employees.db')

cur = con.cursor()

cur.execute('SELECT Region FROM sales ORDER BY Region ')

print(cur.fetchall())

The output of the program is shown below:

[('EAST',), ('NORTH',), ('SOUTH',), ('WEST',)]

------------------

(program exited with code: 0)

Press any key to continue . . .

In our query we can use more than one columns to retrieve their values, thus to get the values in both the columns in the Sales table in a sorted order we can use:

SELECT Region,total_employees FROM sales ORDER BY Region

The following program fetches the records from both the columns in the sales table and sort then according to their Region names:

import sqlite3

con = sqlite3.connect('employees.db')

cur = con.cursor()

cur.execute('SELECT Region,total_employees FROM sales ORDER BY Region ')

print(cur.fetchall())

The output of the program is shown below:

[('EAST', 150), ('NORTH', 180), ('SOUTH', 300), ('WEST', 200)]

------------------
(program exited with code: 0)

Press any key to continue . . .

By changing the column name after the phrase ORDER BY, we can change the way the database sorts. See the following program:

import sqlite3

con = sqlite3.connect('employees.db')

cur = con.cursor()

cur.execute('SELECT Region,total_employees FROM sales ORDER BY total_employees ')

print(cur.fetchall())

Here we are sorting based on total_employees column. The output of the program is shown below:

[('EAST', 150), ('NORTH', 180), ('WEST', 200), ('SOUTH', 300)]

------------------
(program exited with code: 0)

Press any key to continue . . .

It is also possible to specify whether we want values sorted in ascending (ASC) or descending (DESC) order as per requirement. In the above program we modify our query as follows to get the results in the descending order:

SELECT Region,total_employees FROM sales ORDER BY total_employees DESC

The output of the program is shown below:

[('SOUTH', 300), ('WEST', 200), ('NORTH', 180), ('EAST', 150)]

------------------
(program exited with code: 0)

Press any key to continue . . .

In case the number of columns are less we can use column names to fetch the data but in case if the table as more columns we can use the following query to fetch data from all the columns of the table:

SELECT * FROM <<tableName>> 

Thus to fetch the complete data in the Sales table we can use the following query:

SELECT * FROM Sales

The following program prints the complete data in the Sales table:

import sqlite3

con = sqlite3.connect('employees.db')

cur = con.cursor()

cur.execute('SELECT * FROM Sales')

print(cur.fetchall())

The output of the program is shown below:

[('EAST', 150), ('WEST', 200), ('NORTH', 180), ('SOUTH', 300)]

------------------
(program exited with code: 0)

Press any key to continue . . .

Conditional queries

Sometimes it is required to fetch some specific data from the DB for eg. we need to find those regions where number of employees are more than 150. For such these kind of data retrieval we can use the WHERE clause and specify conditions that the rows we want must satisfy. We can use the following relational operators with the WHERE:

=      Equal to
!=     Not equal to
>      Greater than
<       Less than
>=    Greater than or equal to
<=     Less than or equal to


We can also use the AND, OR, and NOT operators. See the following program which prints the regions where number of employees are more than 150:

import sqlite3

con = sqlite3.connect('employees.db')

cur = con.cursor()

cur.execute('SELECT Region FROM Sales WHERE total_employees > 150')

print(cur.fetchall())

The output of the program is shown below:

[('WEST',), ('NORTH',), ('SOUTH',)]

------------------
(program exited with code: 0)

Press any key to continue . . .

Here I am ending today's discussion. In the next post we'll focus on updating and deleting data from the database. Till we meet next keep practicing and learning Python as Python is easy to learn!