TinyDB - The where Clause

TinyDB provides the "where" clause that you can use while searching for a particular data. The "where" clause helps by filtering the unwanted data out. With the help of the "where" clause, you can access specific data quickly.

Before using the 'where' clause, we need to first import it. The syntax of "where" clause is given below −

from tinydb import where
db.search(where('field') == 'value')

Let's understand the use of 'where' clause with the help of a couple of examples.

The Student Database

For the examples, we will use the following student database.

[
   {
      "roll_number":1,
      "st_name":"elen",
      "mark":250,
      "subject":"TinyDB",
      "address":"delhi"
   },
   {
      "roll_number":2,
      "st_name":"Ram",
      "mark":[
         250,
         280
      ],
      "subject":[
         "TinyDB",
         "MySQL"
      ],
      "address":"delhi"
   },
   {
      "roll_number":3,
      "st_name":"kevin",
      "mark":[
         180,
         200
      ],
      "subject":[
         "oracle",
         "sql"
      ],
      "address":"keral"
   },
   {
      "roll_number":4,
      "st_name":"lakan",
      "mark":200,
      "subject":"MySQL",
      "address":"mumbai"
   },
   {
      "roll_number":5,
      "st_name":"karan",
      "mark":275,
      "subject":"TinyDB",
      "address":"benglore"
   }
]

Example 1

Let's use the "where" clause for the subject field −

db.search(where('subject') == 'MySQL')

This query will fetch all the rows where the "subject" field is "MySQL".

[{
   'roll_number': 4,
   'st_name': 'lakan',
   'mark': 200, 
   'subject': 'MySQL',
   'address': 'mumbai'
}]

Example 2

Let's see another use of the "where" clause with the "not equal to" condition −

db.search(where('mark') != 275)

This query will fetch all the rows where the "mark" field is not equal to "275" −

[
   {
      "roll_number":1,
      "st_name":"elen",
      "mark":250,
      "subject":"TinyDB",
      "address":"delhi"
   },
   {
      "roll_number":2,
      "st_name":"Ram",
      "mark":[
         250,
         280
      ],
      "subject":[
         "TinyDB",
         "MySQL"
      ],
      "address":"delhi"
   },
   {
      "roll_number":3,
      "st_name":"kevin",
      "mark":[
         180,
         200
      ],
      "subject":[
         "oracle",
         "sql"
      ],
      "address":"keral"
   },
   {
      "roll_number":4,
      "st_name":"lakan",
      "mark":200,
      "subject":"MySQL",
      "address":"mumbai"
   }
]
Advertisements