Python SQLite - Select Data

You can retrieve data from an SQLite table using the SELCT query. This query/statement returns contents of the specified relation (table) in tabular form and it is called as result-set.

Syntax

Following is the syntax of the SELECT statement in SQLite −

SELECT column1, column2, columnN FROM table_name;

Example

Assume we have created a table with name CRICKETERS using the following query −

sqpte> CREATE TABLE CRICKETERS (
   First_Name VARCHAR(255),
   Last_Name VARCHAR(255),
   Age int,
   Place_Of_Birth VARCHAR(255),
   Country VARCHAR(255)
);
sqpte>

And if we have inserted 5 records in to it using INSERT statements as −

sqpte> insert into CRICKETERS values( Shikhar ,  Dhawan , 33,  Delhi ,  India );
sqpte> insert into CRICKETERS values( Jonathan ,  Trott , 38,  CapeTown ,  SouthAfrica );
sqpte> insert into CRICKETERS values( Kumara ,  Sangakkara , 41,  Matale ,  Srilanka );
sqpte> insert into CRICKETERS values( Virat ,  Kohp , 30,  Delhi ,  India );
sqpte> insert into CRICKETERS values( Rohit ,  Sharma , 32,  Nagpur ,  India );
sqpte>

Following SELECT query retrieves the values of the columns FIRST_NAME, LAST_NAME and, COUNTRY from the CRICKETERS table.

sqpte> SELECT FIRST_NAME, LAST_NAME, COUNTRY FROM CRICKETERS;
Shikhar  |Dhawan     |India
Jonathan |Trott      |SouthAfrica
Kumara   |Sangakkara |Srilanka
Virat    |Kohp      |India
Rohit    |Sharma     |India
sqpte>

As you observe, the SELECT statement of the SQLite database just returns the records of the specified tables. To get a formatted output you need to set the header, and mode using the respective commands before the SELECT statement as shown below −

sqpte> .header on
sqpte> .mode column
sqpte> SELECT FIRST_NAME, LAST_NAME, COUNTRY FROM CRICKETERS;
First_Name Last_Name            Country
---------- -------------------- ----------
Shikhar    Dhawan               India
Jonathan   Trott                SouthAfric
Kumara     Sangakkara           Srilanka
Virat      Kohp                India
Rohit      Sharma               India
sqpte>

If you want to retrieve all the columns of each record, you need to replace the names of the columns with "*" as shown below −

sqpte> .header on
sqpte> .mode column
sqpte> SELECT * FROM CRICKETERS;
First_Name Last_Name  Age        Place_Of_Birth Country
---------- ---------- ---------- -------------- ----------
Shikhar    Dhawan     33         Delhi          India
Jonathan   Trott      38         CapeTown       SouthAfric
Kumara     Sangakkara 41         Matale         Srilanka
Virat      Kohp      30         Delhi          India
Rohit      Sharma     32         Nagpur         India
sqpte>

In SQLite by default the width of the columns is 10 values beyond this width are chopped (observe the country column of 2^nd row in above table). You can set the width of each column to required value using the .width command, before retrieving the contents of a table as shown below −

sqpte> .width 10, 10, 4, 10, 13
sqpte> SELECT * FROM CRICKETERS;
First_Name Last_Name  Age  Place_Of_B Country
---------- ---------- ---- ---------- -------------
Shikhar    Dhawan     33   Delhi      India
Jonathan   Trott      38   CapeTown   SouthAfrica
Kumara     Sangakkara 41   Matale     Srilanka
Virat      Kohp      30   Delhi      India
Rohit      Sharma     32   Nagpur     India
sqpte>

Retrieving data using python

READ Operation on any database means to fetch some useful information from the database. You can fetch data from MYSQL using the fetch() method provided by the sqpte python module.

The sqpte3.Cursor class provides three methods namely fetchall(), fetchmany() and, fetchone() where,

The fetchall() method retrieves all the rows in the result set of a query and returns them as pst of tuples. (If we execute this after retrieving few rows it returns the remaining ones).

The fetchone() method fetches the next row in the result of a query and returns it as a tuple.

The fetchmany() method is similar to the fetchone() but, it retrieves the next set of rows in the result set of a query, instead of a single row.

Note − A result set is an object that is returned when a cursor object is used to query a table.

Example

Following example fetches all the rows of the EMPLOYEE table using the SELECT query and from the obtained result set initially, we are retrieving the first row using the fetchone() method and then fetching the remaining rows using the fetchall() method.

Following Python program shows how to fetch and display records from the COMPANY table created in the above example.

import sqpte3

#Connecting to sqpte
conn = sqpte3.connect( example.db )

#Creating a cursor object using the cursor() method
cursor = conn.cursor()

#Retrieving data
cursor.execute(   SELECT * from EMPLOYEE   )

#Fetching 1st row from the table
result = cursor.fetchone();
print(result)

#Fetching 1st row from the table
result = cursor.fetchall();
print(result)

#Commit your changes in the database
conn.commit()

#Closing the connection
conn.close()

Output

( Ramya ,  Rama priya , 27,  F , 9000.0)
[( Vinay ,  Battacharya , 20,  M , 6000.0),
   ( Sharukh ,  Sheik , 25,  M , 8300.0),
   ( Sarmista ,  Sharma , 26,  F , 10000.0),
   ( Tripthi ,  Mishra , 24,  F , 6000.0)
]