TinyDB In Python

Introduction

The article explains TinyDB, TinyDB is a lightweight document-oriented database that is suitable for personal or small projects. The API is super simple and working on TinyDB is fun, the API of TinyDB is written in Python. It’s very straightforward to learn TinyDB and the article highlight’s all the basics of TinyDB which is important from a learning perspective. The article covers

  1. How to Install TinyDB
  2. How to use TinyDB, with Insert, Update, Search and Delete example

Installation

Installation of TinyDB is straightforward, ensure the Python version is 3.6+

pip install tinydb

If using Anaconda, then use the ‘conda’ command, the command on Windows

conda install -c conda-forge tinydb

How to use TinyDB

For our learning, we will create a simple employee database, which stores employee information like ‘name’, ‘age’, ‘department’ etc.  First, we need to create a database, it’s in the form of “.json” and the file will be present on the disk

from tinydb import TinyDB    
db = TinyDB("employee_db.json")

At this point, the ‘employee_db.json’ file is created on the disk.

Inserting Data

Since we are dealing with JSON as TinyDB created ‘employees.json’ for us, the data will be inserted in the form of a dictionary.

employee = {"name": "John", "age": 25, "department":"IT"}
db.insert(new_item) # 1

First, we created a new dictionary named ‘employee’ with fields ‘name’, ‘age’, ‘department’ and through the ‘insert()’ method of TinyDB class we inserted the data into the Database. The ‘insert’ method returns 1, which is the id of the new object, and on the disk, the ‘employees_db.json’ is created and it has now 1 record which is

{"_default": {"1": {"name": "John", "age": 25, "department": "IT"}}}

For inserting multiple objects to the database, the ‘insert_multiple’ method should be used which accepts the List of dictionary objects.

empList = [{"name": "David", "age": 35, "department":"Finance"}, {"name": "Sam", "age": 55, "department":"IT"}]
db.insert_multiple(empList) # 2, 3

The employees_db.json file,

{"_default": {"1": {"name": "John", "age": 25, "department": "IT"}, "2": {"name": "David", "age": 35, "department": "Finance"}, "3": {"name": "Sam", "age": 55, "department": "IT"}}}

Searching Data

TinyDB class has a few methods for querying data, like ‘search’, ‘get’, ‘contains’, ‘count’, ‘all’, and ‘len’.

Let’s retrieve all the documents from the Database like ‘SELECT * FROM Table’, ‘all’ method must be used for such scenarios

db.all() #returns, [{'name': 'John', 'age': 25, 'department': 'IT'}, {'name': 'David', 'age': 35, #'department': 'Finance'},{'name': 'Sam', 'age': 55, 'department': 'IT'}]

finding the count of documents in DB like SELECT COUNT(*) FROM Table, ‘len()’ method should be used.

len(db) #3 

Returns 3 as the count of documents in DB currently is 3.

For specific search queries, for example, SELECT * FROM Table WHERE name = ‘John’. ‘search’ method should be used, but for that another class ‘Query’ needs to be instantiated and this object contains all the keys through which we can query.

from tinydb import Query
empKeys = Query()

db.search(empKeys.name == 'John') # [{'name': 'John', 'age': 25, 'department': 'IT'}]

The search method returns a list of elements if found else it returns an empty list

db.search(empKeys.name == 'John1') # returns []

Another variation for searching document is ‘get’ but it returns only a single document if found, else returns nothing.

db.get(empKeys.name == 'John') # returns {'name': 'John', 'age': 25, 'department': 'IT'}

TinyDB also has ‘count’ method, which helps us in retrieving the number of documents matching our query, something like ‘SELECT COUNT(*) WHERE name = ‘John’

db.count(empKeys.age > 25) # returns 2, as we have 2 records with age greater than 25. 

Update

The ‘update’ method is used for updating records, update method takes the new dictionary object against a condition,

db.update({"age": 27}, empKeys.name == 'John')

The above query is updating the age of an employee from 25 to 27 and it returned [1], indicating the id = 1 is modified. For updating all the documents, we don’t have to supply the query argument

db.update({"department": "IT"}) # returns [1,2,3] indicating all the records are modified. 

Delete

Deletion of documents must be done through the ‘remove’ method,  the method can take remove the document based on condition, the method also takes the document-ids as the parameter for removal.

db.remove(empKeys.name == 'John') #returns [1] as a indicator of successful deletion. 
db.remove(doc_ids=[2,3]) #returns [2, 3]

For deleting the entire database ‘truncate()’ method should be used.

Summary

A few important closing notes, TinyDB is a simple API library on which any beginner can also work it’s that simple, we should not be using TinyDB in production or bigger projects, it’s an ideal candidate for Integration Tests.