TinyDB has a rich set of queries. We have ways to construct queries: the first way resembles the syntax of ORM tools and the second is the traditional way of using the ”Where” clause.

In this chapter, let”s understand these two ways of constructing a query in a TinyDB database.

The First Method: Importing a Query

The first method resembles the syntax of ORM tools in which first we need to import the query in the command prompt. After importing, we can use the query object to operate the TinyDB database. The syntax is given below −

from tinydb import Query
student = Query()

Here, ”student” is the name of our database. For the 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

Following is the query to retereive the data from the student database where the roll_no of the students are less than 3 −

>>> db.search(Query().roll_number < 3)

Or,

>>> student = Query()
>>> db.search(student.roll_number < 3)

The above search query will produce the following result −

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

Sometimes the file name is not a valid Python identifier. In that case, we would not be able to access that field. For such cases, we need to switch to dict access notation as follows −

student = Query();

# Invalid Python syntax
db.search(student.security-code == ''ABCD'')

# Use the following dict access notation
db.search(student[''security-code''] == ''ABCD'')

The Second Method: Using the “where” Clause

The second way is the traditional way of constructing queries that uses the “where” clause. The syntax is given below −

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

Example

TinyDB “where” clause for the subject field −

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

The above query will produce the following output −

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