Python SQLite - Where Clause

If you want to fetch, delete or, update particular rows of a table in SQLite, you need to use the where clause to specify condition to filter the rows of the table for the operation.

For example, if you have a SELECT statement with where clause, only the rows which satisfies the specified condition will be retrieved.

Syntax

Following is the syntax of the WHERE clause in SQLite −

SELECT column1, column2, columnN
FROM table_name
WHERE [search_condition]

You can specify a search_condition using comparison or logical operators. pke >, <, =, LIKE, NOT, etc. The following examples would make this concept clear.

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 statement retrieves the records whose age is greater than 35 −

sqpte> SELECT * FROM CRICKETERS WHERE AGE > 35;
First_Name   Last_Name    Age    Place_Of_B   Country
----------   ----------   ----   ----------   -----------
Jonathan     Trott        38     CapeTown     SouthAfrica
Kumara       Sangakkara   41     Matale       Srilanka
sqpte>

Where Clause Using Python

The Cursor object/class contains all the methods to execute queries and fetch data, etc. The cursor method of the connection class returns a cursor object.

Therefore, to create a table in SQLite database using python −

Estabpsh connection with a database using the connect() method.

Create a cursor object by invoking the cursor() method on the above created connection object.

Now execute the CREATE TABLE statement using the execute() method of the Cursor class.

Example

Following example creates a table named Employee and populates it. Then using the where clause it retrieves the records with age value less than 23.

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

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

#Doping EMPLOYEE table if already exists.
cursor.execute("DROP TABLE IF EXISTS EMPLOYEE")
sql =    CREATE TABLE EMPLOYEE(
   FIRST_NAME CHAR(20) NOT NULL,
   LAST_NAME CHAR(20),
   AGE INT,
   SEX CHAR(1),
   INCOME FLOAT
)   
cursor.execute(sql)

#Populating the table
cursor.execute(   INSERT INTO EMPLOYEE(FIRST_NAME, LAST_NAME, AGE, SEX, INCOME) 
   VALUES ( Ramya ,  Rama priya , 27,  F , 9000)   )
cursor.execute(   INSERT INTO EMPLOYEE(FIRST_NAME, LAST_NAME, AGE, SEX, INCOME) 
   VALUES ( Vinay ,  Battacharya , 20,  M , 6000)   )
cursor.execute(   INSERT INTO EMPLOYEE(FIRST_NAME, LAST_NAME, AGE, SEX, INCOME) 
   VALUES ( Sharukh ,  Sheik , 25,  M , 8300)   )
cursor.execute(   INSERT INTO EMPLOYEE(FIRST_NAME, LAST_NAME, AGE, SEX, INCOME) 
   VALUES ( Sarmista ,  Sharma , 26,  F , 10000)   )
cursor.execute(   INSERT INTO EMPLOYEE(FIRST_NAME, LAST_NAME, AGE, SEX, INCOME) 
   VALUES ( Tripthi ,  Mishra , 24,  F , 6000)   )

#Retrieving specific records using the where clause
cursor.execute("SELECT * from EMPLOYEE WHERE AGE <23")

print(cursor.fetchall())

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

#Closing the connection
conn.close()

Output

[( Vinay ,  Battacharya , 20,  M , 6000.0)]