We discussed the update and insert queries, but sometimes, we a need a mix of both update and insert. In such cases, TinyDB provides a unique upsert query. This query helps us to insert and update data at a time as per the condition. It works in two ways −

• If data is available, then it chooses the update operation.

• If data is not available, then it chooses the insert operation.

Syntax

The syntax of upsert query is as follows −

db.upsert({ ''key'' : ''value'', ''logged - in'' : True}, regular expression)

Let”s take a couple of examples to demonstrate how you can use this upsert query 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 upsert query to change the address of a student to “surat”, whose roll numbe is 2. In this case, we we have a matching user, hence it will update with the address to have logged-in set to True −

from tinydb import TinyDB, Query
db = TinyDB(''student.json'')
db.upsert({''address'':''Surat''}, Query().roll_number==2)

It will produce the following output, which means record number “2” got updated.

[2]

Use the following code to verify whether record number “2” got updated or not −

db.get(doc_id=2)

It will show the updated data −

{
   ''roll_number'': 2,
   ''st_name'': ''Ram'',
   ''mark'': [250, 280],
   ''subject'': [''TinyDB'', ''MySQL''],
   ''address'': ''Surat''
}

Example 2

Let”s see how we can use the upsert query for unavailable data in our table −

from tinydb import TinyDB, Query
db = TinyDB(''student.json'')
db.upsert({''E-mail'':''[email protected]'',''logged-in'': True},
Query().roll_number==2)

It will show the following output, which means the document with the ID “2” got updated.

[2]

Use the following code to verify the updated values −

db.get(doc_id=2)

It will produce the following output −

{
   "roll_number":2,
   "st_name":"Ram",
   "mark":[
      250,
      280
   ],
   "subject":[
      "TinyDB",
      "MySQL"
   ],
   "address":"Surat",
   "logged-in":true,
   "E-mail":"[email protected]"
}

Notice that we used the upsert query to create a new key (E-mail) which was non-existent and supplied it with the value “[email protected]“.