Hint

Opening multiple TinyDB instances on the same data (e.g. with theJSONStorage) may result in unexpected behavior due to query caching.Seequery_caching on how to disable the query cache.

Advanced queries¶

In theGetting Started you’ve learned about the basic comparisons(==,<,>, …). In addition to these TinyDB supports the followingqueries:

>>># Existence of a field:>>>db.search(User.name.exists())

>>># Regex:>>># Full item has to match the regex:>>>db.search(User.name.matches('[aZ]*'))>>># Case insensitive search for 'John':>>>importre>>>db.search(User.name.matches('John',flags=re.IGNORECASE))>>># Any part of the item has to match the regex:>>>db.search(User.name.search('b+'))

>>># Custom test:>>>test_func=lambdas:s=='John'>>>db.search(User.name.test(test_func))

>>># Custom test with parameters:>>>deftest_func(val,m,n):>>>returnm<=val<=n>>>db.search(User.age.test(test_func,0,21))>>>db.search(User.age.test(test_func,21,99))

Another case is if you have adict where you want to find all documentsthat match thisdict. We call this searching for a fragment:

>>>db.search(Query().fragment({'foo':True,'bar':False}))[{'foo': True, 'bar': False, 'foobar: 'yes!'}]

You also can search for documents where a specific field matches the fragment:

>>>db.search(Query().field.fragment({'foo':True,'bar':False}))[{'field': {'foo': True, 'bar': False, 'foobar: 'yes!'}]

When a field contains a list, you also can use theany andall methods.There are two ways to use them: with lists of values and with nested queries.Let’s start with the first one. Assuming we have a user object with a groups listlike this:

>>>db.insert({'name':'user1','groups':['user']})>>>db.insert({'name':'user2','groups':['admin','user']})>>>db.insert({'name':'user3','groups':['sudo','user']})

Now we can use the following queries:

>>># User's groups include at least one value from ['admin', 'sudo']>>>db.search(User.groups.any(['admin','sudo']))[{'name': 'user2', 'groups': ['admin', 'user']}, {'name': 'user3', 'groups': ['sudo', 'user']}]>>>>>># User's groups include all values from ['admin', 'user']>>>db.search(User.groups.all(['admin','user']))[{'name': 'user2', 'groups': ['admin', 'user']}]

In some cases you may want to have more complexany/all queries.This is where nested queries come in as helpful. Let’s set up a table like this:

>>>Group=Query()>>>Permission=Query()>>>groups=db.table('groups')>>>groups.insert({        'name': 'user',        'permissions': [{'type': 'read'}]})>>>groups.insert({        'name': 'sudo',        'permissions': [{'type': 'read'}, {'type': 'sudo'}]})>>>groups.insert({        'name': 'admin',        'permissions': [{'type': 'read'}, {'type': 'write'}, {'type': 'sudo'}]})

Now let’s search this table using nestedany/all queries:

>>># Group has a permission with type 'read'>>>groups.search(Group.permissions.any(Permission.type=='read'))[{'name': 'user', 'permissions': [{'type': 'read'}]}, {'name': 'sudo', 'permissions': [{'type': 'read'}, {'type': 'sudo'}]}, {'name': 'admin', 'permissions':        [{'type': 'read'}, {'type': 'write'}, {'type': 'sudo'}]}]>>># Group has ONLY permission 'read'>>>groups.search(Group.permissions.all(Permission.type=='read'))[{'name': 'user', 'permissions': [{'type': 'read'}]}]

As you can see,any tests if there isat least one document matchingthe query whileall ensuresall documents match the query.

The opposite operation, checking if a single item is contained in a list,is also possible usingone_of:

>>>db.search(User.name.one_of(['jane','john']))

Query modifiers¶

TinyDB also allows you to use logical operations to modify and combinequeries:

>>># Negate a query:>>>db.search(~(User.name=='John'))

>>># Logical AND:>>>db.search((User.name=='John')&(User.age<=30))

>>># Logical OR:>>>db.search((User.name=='John')|(User.name=='Bob'))

Recap¶

Let’s review the query operations we’ve learned:

Inserting data¶

As already described you can insert a document usingdb.insert(...).In case you want to insert multiple documents, you can usedb.insert_multiple(...):

>>>db.insert_multiple([        {'name': 'John', 'age': 22},        {'name': 'John', 'age': 37}])>>>db.insert_multiple({'int':1,'value':i}foriinrange(2))

Also in some cases it may be useful to specify the document ID yourself wheninserting data. You can do that by using theDocumentclass:

>>>db.insert(Document({'name':'John','age':22},doc_id=12))12

The same is possible when usingdb.insert_multiple(...):

>>>db.insert_multiple([    Document({'name': 'John', 'age': 22}, doc_id=12),    Document({'name': 'Jane', 'age': 24}, doc_id=14),])[12, 14]

Updating data¶

Sometimes you want to update all documents in your database. In this case, youcan leave out thequery argument:

>>>db.update({'foo':'bar'})

When passing a dict todb.update(fields,query), it only allows you toupdate a document by adding or overwriting its values. But sometimes you mayneed to e.g. remove one field or increment its value. In that case you canpass a function instead offields:

>>>fromtinydb.operationsimportdelete>>>db.update(delete('key1'),User.name=='John')

This will remove the keykey1 from all matching documents. TinyDB comeswith these operations:

• delete(key): delete a key from the document
• increment(key): increment the value of a key
• decrement(key): decrement the value of a key
• add(key,value): addvalue to the value of a key (also works for strings)
• subtract(key,value): subtractvalue from the value of a key
• set(key,value): setkey tovalue

Of course you also can write your own operations:

>>>defyour_operation(your_arguments):...deftransform(doc):...# do something with the document...# ......returntransform...>>>db.update(your_operation(arguments),query)

In order to perform multiple update operations at once, you can use theupdate_multiple method like this:

>>>db.update_multiple([...({'int':2},where('char')=='a'),...({'int':4},where('char')=='b'),...])

You also can use mix normal updates with update operations:

>>>db.update_multiple([...({'int':2},where('char')=='a'),...({delete('int'),where('char')=='b'),...])

Upserting data¶

In some cases you’ll need a mix of bothupdate andinsert:upsert.This operation is provided a document and a query. If it finds any documentsmatching the query, they will be updated with the data from the provided document.On the other hand, if no matching document is found, it inserts the provideddocument into the table:

>>>db.upsert({'name':'John','logged-in':True},User.name=='John')

This will update all users with the name John to havelogged-in set toTrue.If no matching user is found, a new document is inserted with both the name setand thelogged-in flag.

To use the ID of the document as matching criterion aDocumentwithdoc_id is passed instead of a query:

>>>db.upsert(Document({'name':'John','logged-in':True},doc_id=12))

Retrieving data¶

There are several ways to retrieve data from your database. For instance youcan get the number of stored documents:

>>>len(db)3

Then of course you can usedb.search(...) as described in theGetting Startedsection. But sometimes you want to get only one matching document. Instead of using

>>>try:...result=db.search(User.name=='John')[0]...exceptIndexError:...pass

you can usedb.get(...):

>>>db.get(User.name=='John'){'name': 'John', 'age': 22}>>>db.get(User.name=='Bobby')None

Often you don’t want to search for documents but only know whether they arestored in the database. In this casedb.contains(...) is your friend:

>>>db.contains(User.name=='John')

In a similar manner you can look up the number of documents matching a query:

>>>db.count(User.name=='John')2

Recap¶

Let’s sum up the way TinyDB supports working with IDs:

Default Table¶

TinyDB uses a table named_default as the default table. All operationson the database object (likedb.insert(...)) operate on this table.The name of this table can be modified by setting thedefault_table_nameclass variable to modify the default table name for all instances:

>>>#1: for a single instance only>>>db=TinyDB(storage=SomeStorage)>>>db.default_table_name='my-default'>>>#2: for all instances>>>TinyDB.default_table_name='my-default'

Query Caching¶

TinyDB caches query result for performance. That way re-running a query won’thave to read the data from the storage as long as the database hasn’t beenmodified. You can optimize the query cache size by passing thecache_sizeto thetable(...) function:

>>>table=db.table('table_name',cache_size=30)

Storage Types¶

TinyDB comes with two storage types: JSON and in-memory. Bydefault TinyDB stores its data in JSON files so you have to specify the pathwhere to store it:

>>>fromtinydbimportTinyDB,where>>>db=TinyDB('path/to/db.json')

To use the in-memory storage, use:

>>>fromtinydb.storagesimportMemoryStorage>>>db=TinyDB(storage=MemoryStorage)

>>>db=TinyDB('db.json',sort_keys=True,indent=4,separators=(',',': '))

To modify the default storage for allTinyDB instances, set thedefault_storage_class class variable:

>>>TinyDB.default_storage_class=MemoryStorage

In case you need to access the storage instance directly, you can use thestorage property of your TinyDB instance. This may be useful to callmethod directly on the storage or middleware:

>>>db=TinyDB(storage=CachingMiddleware(MemoryStorage))<tinydb.middlewares.CachingMiddleware at 0x10991def0>>>>db.storage.flush()

Middleware¶

Middleware wraps around existing storage allowing you to customize theirbehaviour.

>>>fromtinydb.storagesimportJSONStorage>>>fromtinydb.middlewaresimportCachingMiddleware>>>db=TinyDB('/path/to/db.json',storage=CachingMiddleware(JSONStorage))

>>>db=TinyDB('/path/to/db.json',                storage=FirstMiddleware(SecondMiddleware(JSONStorage)))

# Using a context manager:withdatabaseasdb:# Your operations

# Using the close functiondb.close()