TinyDB - Searching

TinyDB provides the search() method to help you search any data from a document. Along with the query() object, the search() method can be used to find the data in a JSON file. We have various ways in which we can use the search() method on a TinyDB database.

Method 1: TinyDB search() with Existence of a Field

We can search the data from a database based on the existence of a field. Let's understand it with an example. For this and other examples, we will be using 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

The search query based on the existence of a field is as follows −

from tinydb import Query
student = Query()
db.search(student.address.exists())

The above query will retrieve the following data from the student file −

[
   {
      "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"
   }
]

Method 2: TinyDB search() with Regular Expression

We can search for a particular data from a database using regular expression (Regex). Let's understand how it works with a couple of examples.

Example 1

Full item search matching the Regular Expression −

from tinydb import Query
student = Query()
db.search(student.st_name.matches('[aZ]*'))

This query will produce the following output −

[
   {
      "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-2

Case-sensitive search with Regular Expression −

from tinydb import Query
import re
student = Query()
db.search(student.st_name.matches('lakan', flags=re.IGNORECASE))

It wil produce the following output −

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

Example-3

Any part of the item matching with Regular Expression −

from tinydb import Query
student = Query()
db.search(student.st_name.search('r+'))

This query will produce the following output −

[{
   'roll_number': 5,
   'st_name': 'karan',
   'mark': 275,
   'subject': 'TinyDB',
   'address': 'benglore'
}]

Method 3: TinyDB search() using a Substring

We can also use a substring while searching for a particular data from a TinyDB database. Let's understand how it works with a couple of examples −

Example-1

Take a look at this query; it will fetch the all the rows where the "address" field is "delhi".

from tinydb import Query
student = Query()
db.search(student['address'] == 'delhi')

It will produce the following output −

[
   {
      "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"
   }
]

Example-2

In this query, we have used a slightly different syntax for the search() method.

from tinydb import Query
student = Query()
db.search(student.address.search('mumbai'))

It will fetch all the rows where the "address" field is "mumbai".

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