Category: tinydb

TinyDB – Modifying the Data ”; Previous Next We have already discussed the update query with the help of which we can modify the values as well as handle the data in our database. But the update query such as db.update(fields, query) allows us to update a document by adding or overwriting its values. But sometimes, we would like to remove one field or need to increment its value. In such cases, we can pass a function instead of fields. We can use the following operations with the update query − The Increment Query The increment query, as its name implies, is used to increment the value of a key in the database. The syntax of increment query is as follows − from tinydb.operations import increment db.update(increment(”key”)) The Add Query The add query is used to add value to the value of a key. It works for the strings as well. The syntax of add query is as follows − from tinydb.operations import add db.update(add(key, value)) The Set Query This query is used to set the key to the value of the data. The syntax of set query is as follows − from tinydb.operations import set db.update(set(key, value)) The Decrement Query The decrement query is used to decrement the value of a key. The syntax of decrement query is as follows − from tinydb.operations import decrement db.update(decrement(key)) The Subtract Query The subtarct query is used to subtract value from the value of a key. The syntax of subtract query is as follows − from tinydb.operations import subtract db.update(subtract(key, value)) The Delete Query The delete query is used to delete a key from a document. The syntax of delete query is as follows − from tinydb.operations import delete db.update(delete(key)) Let”s take a couple of examples to demonstrate how you can use these operations along with the update query. We will use the same student database that we have used in all the previous chapters. Example 1 Let”s see how we can increment the marks of a student in our student table − from tinydb import TinyDB, Query db = TinyDB(”student.json”) from tinydb.operations import increment db.update(increment(”mark”), Query().mark == 275) It will produce the following output − [5] The above output shows that it has updated the record whose document ID is 5. Example 2 Let”s see how we can add 5 marks to the marks of a student in our student table − from tinydb import TinyDB, Query db = TinyDB(”student.json”) from tinydb.operations import add db.update(add(”mark”, 5), Query().mark == 200) It will produce the following output − [4] The above output shows that it has updated the record whose document ID is 4. Example 3 Let”s see how we can set the marks to 259 where the marks of a student are 250 in our student table − from tinydb import TinyDB, Query db = TinyDB(”student.json”) from tinydb.operations import add db.update(add(”mark”, 259), Query().mark == 250) It will produce the following output − [1] The above output shows that it has updated the record whose document ID is 1. Example 4 Let”s see how we can decrement the marks of a student in our student table − from tinydb import TinyDB, Query db = TinyDB(”student.json”) from tinydb.operations import decrement db.update(decrement(”mark”), Query().mark == 205) It will produce the following output − [4] The above output shows that it has updated the record whose document ID is 4. Example 5 Let”s see how we can subtract 5 marks to the marks of a student in our student table − from tinydb import TinyDB, Query db = TinyDB(”student.json”) from tinydb.operations import add db.update(add(”mark”, 5), Query().mark == 204) It will produce the following output − [4] The above output shows that it has updated the record whose document ID is 4. Example 6 Let”s see how we can subtract 5 marks to the marks of a student in our student table − from tinydb import TinyDB, Query db = TinyDB(”student.json”) from tinydb.operations import delete db.update(delete(”mark”), Query().mark == 209) It will produce the following output − [4] The above output shows that it has updated the record whose document ID is 4. It will delete the mark field from the database where the value is 209. Example 7 Let”s see how we can update multiple values in a table with a single query − from tinydb import TinyDB, Query db = TinyDB(”student.json”) from tinydb import where db.update_multiple([ ({”st_name”:”Eliana”}, where (”roll_number”)==1), ({”mark”:20}, where (”roll_number”) ==2) ]) It will produce the following output − [1,2] The above output shows that it has updated two records whose document IDs are 1 and 2. Print Page Previous Next Advertisements ”;

TinyDB – Retrieving Data ”; Previous Next After creating a database, we need to frequently retrieve the data from that database. Following are the ways by which we can reterive the data from a database − The len() Query With the help of this query, we can get the number of documents in a database. Its syntax is as follows − len(db) The get Query The get query is used to reterive specific documents matching a query. Its syntax is as follows − db.get(query) The contains Query The contains query is used to check whether the database contains a matching value or not. Its syntax is as follows − db.contains(query) The count Query The count query is used to retrieve the number of matching documents from a database. Its syntax is as follows − db.count(query) Let”s take a few examples to understand how these queries work in TinyDB. We will use the same student database that we have used in all the previous chapters. Example 1 Let”s see how we can use the len() query to get the number of documents in our database − from tinydb import TinyDB db = TinyDB(”student.json”) print (“Number of documents in student db: “, len(db)) It will show the number of documents present in the specified database − Number of documents in student db: 5 Example 2 Let”s see how we can use the get() query to get a specific document from our database − from tinydb import TinyDB, Query db = TinyDB(”student.json”) db.get(Query().address == ”delhi”) This query will fetch the row where the “address” field has the value “delhi”. { ”roll_number”: 1, ”st_name”: ”elen”, ”mark”: 250, ”subject”: ”TinyDB”, ”address”: ”delhi” } Example 3 Let”s see how we can use the contains() query to verify if our database contains a specific value − from tinydb import TinyDB, Query db = TinyDB(”student.json”) db.contains(Query().address == ”delhi”) The contains() query returns a Boolean value, based on the existence of the specified value in the given database. In this case, it will return “True” because our database does have a “address” key with the value “delhi”. True Example 4 Let”s see how we can use the count() query to get the number of documents for which a given condition is True − from tinydb import TinyDB, Query db = TinyDB(”student.json”) print (db.count(Query().subject == ”NoSQL”)) It will return the following output − 3 It means there are 3 documents in the database where the “subject” key has the value “NoSQL”. Print Page Previous Next Advertisements ”;

TinyDB – Default Table ”; Previous Next TinyDB provides a default table in which it automatically saves and modifies the data. We can also set a table as the default table. The basic queries, methods, and operations will work on that default table. In this chapter, let”s see how we can see the tables in a database and how we can set a table of our choice as the default table − Showing the Tables in a Database To get the list of all the tables in a database, use the following code − from tinydb import TinyDB, Query db = TinyDB(“student.json”) db.tables() It will produce the following output: We have two tables inside “student.json”, hence it will show the names of these two tables − {”Student_Detail”, ”_default”} The output shows that we have two tables in our database, one is “Student_Detail” and the other “_default”. Displaying the Values of the Default Table If you use the all() query, it will show the contents of the default table − from tinydb import TinyDB db = TinyDB(“student.json”) db.all() To show the contents of the “Student_Detail” table, use the following query − from tinydb import TinyDB db = TinyDB(“student.json”) print(db.table(“Student_Detail”).all()) It will show the contents of the “Student_Detail” table − [{ ”roll_number”: 1, ”st_name”: ”elen”, ”mark”: 250, ”subject”: ”TinyDB”, ”address”: ”delhi” }] Setting a Default Table You can set a table of your choice as the default table. For that, you need to use the following code − from tinydb import TinyDB db = TinyDB(“student.json”) db.default_table_name = “Student_Detail” It will set the “Student_Detail” table as the default table for our database. Print Page Previous Next Advertisements ”;

TinyDB – Logical AND ”; Previous Next The “Logical AND” operator combines multiple conditions and evaluates to True if all the conditions are met. TinyDB Logical AND operates on two queries of a database. If both the queries are True, TinyDB will fetch the required data. On the other hand, if any one of the queries is False, it will return a blank. Syntax The syntax of TinyDB Logical AND is as follows − db.search((Query().(query1) & (Query().(query2) Here, field represents the part of data that we want to access. Query() is the object created of our JSON table named student. It will fetch the data if both the conditions met, otherwise it will return a blank. Let”s take a couple examples and see how Logial AND works. We will use the same student database that we have used in all the previous chapters. Example 1 Let”s see what our TinyDB Student database returns when we apply Logical AND on “st_name=lakhan” and “subject=MYSQL” field − from tinydb import TinyDB, Query db = TinyDB(”student.json”) db.search ((Query().st_name == ”lakhan”) & (Query().subject == ”MySQL”)) This query will fetch only those rows where the student name is “lakhan” and the “subject” is “MySQL”. [{ ”roll_number”: 4, ”st_name”: ”lakhan”, ”mark”: 200, ”subject”: ”MySQL”, ”address”: ”mumbai” }] Example 2 In this example, let”s apply Logical AND on the “subject” and “roll_number” fields − from tinydb import TinyDB, Query student = Query() db = TinyDB(”student.json”) db.search((student.subject.search(”M”)) & (student.roll_number < 5)) This query will fetch all the rows where the roll_number is less than “4” and “subject” starts with the letter “M”. [{ ”roll_number”: 4, ”st_name”: ”lakhan”, ”mark”: 200, ”subject”: ”MySQL”, ”address”: ”mumbai” }] Print Page Previous Next Advertisements ”;

TinyDB – The one_of() Query ”; Previous Next For matching the subfield data, TinyDB provides a method called one_of(). This method searches a single category and gets at least one similar value. It will match if the field is contained in the provided list. Syntax The syntax of TinyDB one_of() is as follows − db.search(Query().field.one_of(list) Here, field represents the part of data that we want to access. Query() is the object created of our JSON table named student. It will fetch either single or multiple values of one category. Let”s understand how it works with the help of a couple examples. We will use the same student database that we have used in all the previous chapters. Example 1 Let”s see how we can find the fields from our student table where address is either “delhi” or “pune” − from tinydb import TinyDB, Query db = TinyDB(”student.json”) db.search(Query().address.one_of([”delhi”, ”pune”])) It will fetch all the rows where the “address” field contains either “delhi” or “pune”. [ { “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 Let”s see another example with ”subject” field − from tinydb import TinyDB, Query db = TinyDB(”student.json”) db.search(Query().subject.one_of([”TinyDB”, ”MySQL”])) It will fetch all the rows where the “subject” field contains either “TinyDB” or “MySQL”. [ { “roll_number”:1, “st_name”:”elen”, “mark”:250, “subject”:”TinyDB”, “address”:”delhi” }, { “roll_number”:4, “st_name”:”lakhan”, “mark”:200, “subject”:”MySQL”, “address”:”mumbai” }, { “roll_number”:5, “st_name”:”karan”, “mark”:275, “subject”:”TinyDB”, “address”:”benglore” } ] Print Page Previous Next Advertisements ”;

TinyDB – Logical OR ”; Previous Next The “Logical OR” operator combines multiple conditions and evaluates to True only if either of the condition is met. TinyDB Logical OR operates on two queries of a database. If any one of the queries is True, TinyDB will fetch the required data. On the other hand, if both the queries are False, it will return a blank. Syntax The syntax of TinyDB Logical OR is as follows − db.search((Query().(query1) | (Query().(query2) Here, field represents the part of data that we want to access. Query() is the object created of our JSON table named student. It will fetch the data if any one of the conditions is met, otherwise it will return a blank. Let”s take a couple of examples and see how it works. We will use the same student database that we have used in all the previous chapters. Example 1 Let”s see what our TinyDB Student database returns when we apply Logical OR on the “st_name” and “subject” fields and check the following conditions: “st_name=lakhan” and “subject=TinyDB” − from tinydb import TinyDB, Query db = TinyDB(”student.json”) db.search ((Query().st_name == ”lakan”) | (Query().subject == ”TinyDB”)) This query will fetch the following rows − [ { “roll_number”:1, “st_name”:”elen”, “mark”:250, “subject”:”TinyDB”, “address”:”delhi” }, { “roll_number”:4, “st_name”:”lakhan”, “mark”:200, “subject”:”MySQL”, “address”:”mumbai” }, { “roll_number”:5, “st_name”:”karan”, “mark”:275, “subject”:”TinyDB”, “address”:”benglore” } ] Example 2 In this example, let”s apply Logical OR on the “subject” and “roll_number” fields − from tinydb import TinyDB, Query db = TinyDB(”student.json”) db.search((student.subject.search(”M”)) | (student.roll_number < 5)) This query will fetch all the rows where the “subject” field starts with the letter “M” or the “roll_number” is less than “5”. [ { “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”:”lakhan”, “mark”:200, “subject”:”MySQL”, “address”:”mumbai” } ] Print Page Previous Next Advertisements ”;

TinyDB – Handling Data Query ”; Previous Next TinyDB – Storing Multiple Data We have already discussed how you can use the ”insert” query to store data in a database. On a similar note, you can use the ”insert_multiple” query to store multiple data items simultaneously. Here is the syntax of ”insert_multiple” query in TinyDB: db.insert_multiple ([ { key1 : value1, key2 : value2, …, keyN : valueN}, { key1 : value1, key2 : value2, …, keyN : valueN } ]) Let”s take a couple of examples to demonstrate how the “insert_multiple” query works. We will use the same student database that we have used in all the previous chapters. Example 1 Let”s see how we can insert two records of students in our ”student” table using the insert_multiple query − from tinydb import TinyDB, Query db = TinyDB(”student.json”) db.insert_multiple([ { “roll_number”:6, “st_name”:”Siya”, “mark”:240, “subject”:”NoSQL”, “address”:”pune” }, { “roll_number”:7, “st_name”:”Adam”, “mark”:210, “subject”:”oracle”, “address”:”Keral” } ]) It will display the document IDs of the newly saved records − [6, 7] Let”s check whether the new records are saved in the database or not? Use the all() method, as shown below − db.all() It will show all the records stored in the given table − [ { “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” }, { “roll_number”:6, “st_name”:”Siya”, “mark”:240, “subject”:”NoSQL”, “address”:”pune” }, { “roll_number”:7, “st_name”:”Adam”, “mark”:210, “subject”:”oracle”, “address”:”Keral” } ] You can see the two new records of students have been saved in the database. Example 2 Let”s see how we can use insert_multiple with a for loop to insert multiple values simultaneously in a table. Use the following code − db.insert_multiple({”roll_number”: 10, ”numbers”: r} for r in range(3)) It will return the document IDs of the newly saved records − [8, 9, 10] Again, use the all() method to verify whether the new records have been saved in the database or not? db.all() It will fetch all the records stored in the given student table − [ { “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” }, { “roll_number”:6, “st_name”:”Siya”, “mark”:240, “subject”:”NoSQL”, “address”:”pune” }, { “roll_number”:7, “st_name”:”Adam”, “mark”:210, “subject”:”oracle”, “address”:”Keral” }, { “roll_number”:10, “numbers”:0 }, { “roll_number”:10, “numbers”:1 }, { “roll_number”:10, “numbers”:2 } ] Notice the last three rows. We have used the insert_multiple method with a for loop to insert three new entries. Print Page Previous Next Advertisements ”;

TinyDB – Tables ”; Previous Next In TinyDB, we can work with multiple tables. These tables have the same properties as the TinyDB class. Let”s see how we can create tables in TinyDB and apply various operations on them − Creating Tables It’s very easy to create a table in TinyDB. Here”s its syntax − table_object = db.table(”table name”) Inserting Values in a Table To insert data in a specific table, use the following syntax − table_object.insert({ ”key” : value }) Retreiving Values from a Table To retrieve values from a table, use the following query − table_object.all() Deleting a Table from a Database To delete a table from a database, use the drop_table() query. Here is its syntax − db.drop_table(”table name”) Delete Multiple Tables from a Database To delete multiple tables from a database, use the following query − db.drop_tables() Let”s understand how to use these queries with the help of a few examples. We will use the same student database that we have used in all the previous chapters. Example 1 Use the following code to create a new table called Student_Detail − from tinydb import TinyDB, Query db = TinyDB(“student.json”) table_object = db.table(”Student_Detail”) Example 2 Next, let”s insert values in this new table Student_Detail − from tinydb import TinyDB, Query db = TinyDB(“student.json”) table_object = db.table(”Student_Detail”) table_object.insert({ ”roll_number”: 1, ”st_name”: ”elen”, ”mark”: 250, ”subject”: ”TinyDB”, ”address”: ”delhi” }) It will return the doc_id of the record inserted in the table. [1] To verify, use the following code − from tinydb import TinyDB, Query db = TinyDB(“student.json”) table_object = db.table(”Student_Detail”) table_object.all() It will show data contained in the Student_Detail table − {”roll_number”: 1, ”st_name”: ”elen”, ”mark”: 250, ”subject”: ”TinyDB”, ”address”: ”delhi”} Example 3 To see all the tables present in the database, use the following query − from tinydb import TinyDB, Query db = TinyDB(“student.json”) print(db.tables()) There are two tables inside “student.json”. It will show the names of these two tables − {”Student_Detail”, ”_default”} Example 4 Let”s see how we can reterive all the values from a table − from tinydb import TinyDB, Query db = TinyDB(“student.json”) table_object = db.table(“Student_Detail”) print(table_object.all()) It will show the following output − [{ ”roll_number”: 1, ”st_name”: ”elen”, ”mark”: 250, ”subject”: ”TinyDB”, ”address”: ”delhi” }] Example 5 Let”s see how we can remove a table from a database − from tinydb import TinyDB, Query db = TinyDB(“student.json”) db.drop_table(”Student_Detail”) It will remove the “Student_Detail” table from the database. To remove all the tables from a database, use the “drop_tables()” query − db.drop_tables() It will remove all the tables from the database. Print Page Previous Next Advertisements ”;

TinyDB – The where Clause ”; Previous Next 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” } ] Print Page Previous Next Advertisements ”;

TinyDB – The matches() Query ”; Previous Next The matches() query matches the data from a JSON file with a given condition (in the form of a regular expression) and returns the results accordingly. It will return a blank value if the condition does not match with the data in the file. Syntax The syntax of TinyDB matches() is as follows − db.search(Query().field.matches(regular expression)) Here, field represents the part of data that we want to access. Query() is the object created of our JSON table named student. Let”s understand how it works with the help of a couple of examples. We will use the same student database that we have used in all the previous chapters. Example 1 Let”s see how we can use matches() for full item search. from tinydb import Query student = Query() db.search(student.st_name.matches(”[aZ]*”)) This query will fetch all the rows − [ { “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 Let”s see how we can use matches() for case-sensitive search. from tinydb import Query import re student = Query() db.search(student.st_name.matches(”ram”, flags=re.IGNORECASE)) This query will fetch the rows where the student name matches the string “ram”. Observe that we have used a flag to ignore the case while matching the strings. [{ ”roll_number”: 2, ”st_name”: ”Ram”, ”mark”: [250, 280], ”subject”: [”TinyDB”, ”MySQL”], ”address”: ”delhi” }] Example 3 Let”s see how we can use matches() for a particular item. student = Query() db.search(student.address.matches(”keral”)) This query will fetch the rows where the address matches the string “keral”. [{”roll_number”: 3, ”st_name”: ”kevin”, ”mark”: [180, 200], ”subject”: [”oracle”, ”sql”], ”address”: ”keral”}] Example 4 Let”s see what matches() would return when it does not find a particular item − student = Query() db.search(student.address.matches(”Ratlam”)) There are no rows where the “address” field matches the string “Ratlam”, hence it will return a blank value − [] Print Page Previous Next Advertisements ”;