When writing your python code, you will undoubtedly find yourself in a situation where you need to store some structured data. You could just write the data to a file, but that data might take a bit of work to parse later on. Setting up a MySQL server takes a bit of work, and might be overkill for what you are trying to do. Today we will discuss how to use TinyDB in your python application to store structured data in JSON format. And then easily retrieve that data without any fancy coding gymnastics
What is TinyDB?
TinyDB is a lightweight NoSQL engine you can use to store structured data in your Python applications. It supports storing data as JSON files in JSON on your hard disk. Or you can store your JSON data in memory for faster access times. I would not consider TinyDB a fully featured database engine. But it does just enough to be useful. You can think of it is as a free NoSQL alternative to SQL Lite.
How to install TinyDB
As with all Python projects, it is a good idea, but not required, to set up a virtual environment before proceeding. For information on how to set up virtual environments, see our article on Virtual Environments: Click Here.
Once your environment is ready, Installing TinyDB is extremely easy. Just run:
Pip install tinydb
– Or –
Pip3 install TinyDB
This will depend on if you are using Python2 or Python3.
If you don’t have pip installed, Or if you are not sure which version of Python you are using, see the article on Python Basics.
Using TinyDB
TinyDB does everything using JSON. The physical file on your hard disk is formatted in JSON, when you insert data it is with JSON, and when you query data it is in JSON. The main thing to know at this time is it uses key/value pairs separated by a colon. For example “CarColor”:”Red”
Today we are going to use TinyDB to store information for a to-do list application. The information we care about is:
- What is the task we need to complete?
- When do we need to complete the task by?
- Is the task Complete?
- How would we categorize this task?
The first thing to do is import tinyDB and the required submodules:
from tinydb import TinyDB, Query
Next, we need to declare a storage location for our database:
db = TinyDB('todolist.json')Next, let’s declare a couple of records to store in our to-do list application:
Item1 = {'Status':'New','DueDate': '5/12/18', 'Category': 'Work','Description':'Send that Email'}
Item2 ={{'Status':'New','DueDate': '5/11/18', 'Category': 'Home','Description':'Do the Laundry'}
Item3={{'Status':'New','DueDate': '5/11/18', 'Category': 'Home','Description':'Do the Dishes'}Notice each of the three records above have 4 key/value pairs:
- Status
- DueDate
- Category
- Description
Each key is encased in quotes. Then a Colon separates the key from the value finally the value associated with each of those items is also encased in quotes after the colon.
Inserting Records
Now that we have described the items to add to our list, we will insert them into our TinyDB database using the insert function:
db.insert(Item1) db.insert(Item2) db.insert(Item3)
If we don’t already have a variable handy to insert, you can simply add your JSON in between the curly braces:
db.insert({‘Status’:’New’,’DueDate’: ‘5/14/18’, ‘Category’: ‘Work’,’Description’:’Request a Promotion’})
You can verify your insert task worked by asking TinyDB to show everything in the database by using the db.all() function.
print(db.all())
Searching for Records
Returning everything in the database is not that useful, especially as we start adding more items to our list. If we want to search the database for a specific record or set of records, we can use the search function:
db.search(Todo.Category == 'Home')
You can search for multiple criteria at the same time, for example, you can search for everything that is in the work category, and has a due date of 5/14/18:
db.search(Todo.Category == 'Home' & Todo.DueDate == ‘5/14/18’)
You can search for all records which either have a category of Home or has the due date of 5/14/18:
db.search(Todo.Category == 'Home' | Todo.DueDate == ‘5/14/18’)
You can set a variable equal to the search results:
Results = db.search(Todo.Category == 'Home')
You can then use a for loop to iterate through them:
For result in results: print(result)
Updating Records
Once we have completed a task, we need to mark it as done. We do that using the Update function. The update function contains two parts. First, you do a search, then you update all the records that are returned as part of that search. In the example below, we will search for all tasks in the Home category, and then change the Status to Done:
db.update({Status:'Done'}, Todo.Category.search('Home'))Deleting Records
After we mark a task as done, we may want to clear it out of the database. To delete a record, we call the db.remove function. We then pass in a search query. Any records returned by the query will be deleted from the database.
The line below will do a search for all records where the status is set to done, then delete them:
db.remove(Todo.status.search('Done’'))We can delete everything from the database with the db.purge() function:
db.purge()
This is useful for databases that are meant to be short-lived. It gives you an easy way to clear everything out and start over again. When I am doing testing, I sometimes put this up at the top of my script so I clear out any old test data before I start.
Summary
In this article, we have talked about how to insert update and retrieve data from a TinyDB database. Below is a short python script that ties everything together using the examples above.
#Import the TinyDB module and submodules
from tinydb import TinyDB, Query
#Declare our database variable and the file to store our data in
db = TinyDB('todolist.json')
#Declare a few variables and populate them with data to be inserted into the database
Item1 = {'Status':'New','DueDate': '5/12/18', 'Category': 'Work','Description':'Send that Email'}
Item2 ={{'Status':'New','DueDate': '5/11/18', 'Category': 'Home','Description':'Do the Laundry'}
Item3={{'Status':'New','DueDate': '5/11/18', 'Category': 'Home','Description':'Do the Dishes'}
#Insert 4 reords into our todo list database
db.insert(Item1)
db.insert(Item2)
db.insert(Item3)
db.insert({'Status':'New','DueDate': '5/14/18', 'Category': 'Work','Description':'Request a Promotion'})
#Show all records in the database
print(db.all())
#Set all records with a category of Home to to a status of Done
db.update({Status:'Done'}, Todo.Category.search('Home'))
#Search for all records where the category is Home. Then use a For loop to display the results
results = db.search(Todo.Category == 'Home')
For result in results:
print(result)
#Remove all records with a status of Done
db.remove(Todo.status.search('Done’'))
#Show all records in database after removing “Done” records
print(db.all)
