- TinyDB Tutorial
- TinyDB - Home
- TinyDB - Introduction
- TinyDB - Environmental Setup
- TinyDB - Insert Data
- TinyDB - Retrieve Data
- TinyDB - Update Data
- TinyDB - Delete Data
- TinyDB - Querying
- TinyDB - Searching
- TinyDB - The where Clause
- TinyDB - The Exists() Query
- TinyDB - The Matches() Query
- TinyDB - The Test() Query
- TinyDB - The Any() Query
- TinyDB - The All() Query
- TinyDB - The one_of() Query
- TinyDB - Logical Negate
- TinyDB - Logical AND
- TinyDB - Logical OR
- TinyDB - Handling Data Query
- TinyDB - Modifying the Data
- TinyDB - Upserting Data
- TinyDB - Retrieving Data
- TinyDB - Document ID
- TinyDB - Tables
- TinyDB - Default Table
- TinyDB - Caching Query
- TinyDB - Storage Types
- TinyDB - Middleware
- TinyDB - Extend TinyDB
- TinyDB - Extensions
- TinyDB Useful Resources
- TinyDB - Quick Guide
- TinyDB - Useful Resources
- TinyDB - Discussion
TinyDB - Querying
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" }]
To Continue Learning Please Login
Login with Google