TinyDB get all IDs in list query

Question

I'm using TinyDB in my python project. However, in order to make the logic effective, I need to be able to get multiple objects in a query. My preference would be to use a list

listOfIDs = ['123', '456']

I am using the latest Python version and TinyDB implementation.

I have the following implementation:

from tinydb import TinyDB, Query
db = TinyDB('db.json')
myDBQuery= Query()
db.insert({'id': '123', 'name': 'bob'})
db.insert({'id': '456', 'name': 'mary'})
result = db.search(myDBQuery.id.all(['123', '456']))
print(result)

However, I keep getting the following as a result:

[]

I know my implementation is working because when I do the following, I get the expected results:

result = db.search((myDBQuery.id == '123') | myDBQuery.id == '456'))

Anyone know how to query with a list in TinyDB?

EDIT

For those of you wondering, there isn't a way to do it with the current implementation. My only workaround is to create something like the following

def getAllObjectsMatchingId(listOfIds):

tempList = []

for idMember in tempList:
    result = db.search(myDBQuery.id == idMember)
    tempList.append(result)

return tempList

score 4 · Accepted Answer · edited Dec 26 '16 at 03:19

4

.all returns true only if all the elements of the list match the query

.any might work instead

result = db.search(myDBQuery.id.any(['123', '456']))

EDIT FROM QUESTION OP: Please see my edit above

edited Dec 26 '16 at 03:19

booky99

1,436
4
27
45

answered Dec 26 '16 at 02:49

v.coder

1,822
2
15
24

I appreciate the help but that doesn't seem to work either. This is awfully strange as it should be able to accept that due to the documentation: http://tinydb.readthedocs.io/en/latest/usage.html – booky99 Dec 26 '16 at 02:51
Please take a look in advacnced queries section http://tinydb.readthedocs.io/en/latest/usage.html#advanced-queries. It says the field must be a list. – v.coder Dec 26 '16 at 03:03
I might be interpreting incorrectly but it says a User could be and Admin or User in the query. So it should be able to retrieve all group types matching that query – booky99 Dec 26 '16 at 03:06
In the recap section, it states: Query().field.all(query | list) If given a query, matches all elements where all elements in the list field match the query. If given a list, matches all elements where all elements in the list field are a member of the given list Query().field.any(query | list) If given a query, matches all elements where at least one element in the list field match the query. If given a list, matches all elements where at least one elements in the list field are a member of the given list – v.coder Dec 26 '16 at 03:08
Also the field must be a list to use 'all' or 'any', here the field it is just integer. – v.coder Dec 26 '16 at 03:09
Ah, I see. So I'm SOL when it comes to saying the SQL equivalent of SELECT * FROM User WHERE Id IN ('123', '456') – booky99 Dec 26 '16 at 03:10

score 0 · Answer 2 · answered Jul 02 '20 at 03:09

0

This seems the best solution to me:

db.search(myDBQuery.id.test(lambda x: x in listOfIDs))

test() Matches any document for which the function returns True

answered Jul 02 '20 at 03:09

Reinaldo Gomes

153
8

score 0 · Answer 3 · answered Jul 22 '20 at 19:28

If anyone still comes accross with this, an alternative to Reinaldo's implementation:

listOfIds = ['1','2','3']
q = Query()
db.search(q.id.one_of(listOfIds))

Taken from Query's API:

one_of(items: List[Any]) → tinydb.queries.QueryInstance
    Check if the value is contained in a list or generator.

You can also negate the query (which was what I needed) to get the items that are NOT in the list:

listOfIds = ['4','5']
q = Query()
db.search(~q.id.one_of(listOfIds))

TinyDB get all IDs in list query

3 Answers3