Match multiple keys values to database entry in TinyDB?

Question

I am having a hard time finding out if I can check multiple key values at once with TinyDB. Currently I can check multiple fields by using and in an if statement like this:

def check_table(FNAME="NULL", LNAME="NULL", MNAME="NULL"):
    if (HHK_STAFF.search(Query().FNAME == FNAME)) != [] \
    and (HHK_STAFF.search(Query().MNAME == MNAME)) != [] \
    and (HHK_STAFF.search(Query().LNAME == LNAME)) != []:
        print(HHK_STAFF.search(Query().FNAME == FNAME)[0])
    else:
        print("user does not exist")

check_table(FNAME="Some", MNAME="Random", LNAME="Person")

It does what I want however it seams bulky. I am hoping there is something built into TinyDB that can perform the same function without the need for many and statements.

I am trying to do something akin to:

HHK_STAFF.search(where(("FNAME", "MNAME", "LNAME")) == (FNAME, MNAME, LNAME)))

Question:

1. Is anyone aware of a way to Query() the table for multiple key values instead of just one at a time?

2. Can you list a link to this information? I have had a hard time locating this bit of info if it exist.

Here is a simple version of what I am ding with TinyDB:

from tinydb import TinyDB, Query


#~~~~~~~~~~~~~~~~~~~~~~< CURRENT DBPATH >~~~~~~~~~~~~~~~~~~~~~~
DB = TinyDB('./DB_PATH/HHK_DB.json')
#~~~~~~~~~~~~~~~~~~~~~~< CURRENT TABLES >~~~~~~~~~~~~~~~~~~~~~~
HHK_STAFF = DB.table("HHK_STAFF")

HHK_STAFF.insert({'EMPLOYEE_ID':'00000001', 'FNAME': 'Some', 'LNAME':'Person', 'MNAME':'Random'})


def check_table(FNAME="NULL", LNAME="NULL", MNAME="NULL"):
    if (HHK_STAFF.search(Query().FNAME == FNAME)) != [] \
    and (HHK_STAFF.search(Query().MNAME == MNAME)) != [] \
    and (HHK_STAFF.search(Query().LNAME == LNAME)) != []:
        print(HHK_STAFF.search(Query().FNAME == FNAME)[0])
    else:
        print("user does not exist")

check_table(FNAME="Some", MNAME="Random", LNAME="Person")

Results:

{'EMPLOYEE_ID': '00000001', 'FNAME': 'Some', 'LNAME': 'Person', 'MNAME': 'Random'}

Answer 1

According to Advanced Usage — TinyDB 3.8.1.post1 documentation, a logical AND would look like this:

q = Query()
HHK_STAFF.search((q.FNAME == FNAME) & (q.MNAME == MNAME) & (q.LNAME == LNAME))

According to git blame of tinydb/queries.py, it's been available always, since the very first release.

Answer 2

You can use the python builtin all to achieve a shorter, more flexible work-alike to your sample code:

def check_table2(**query):
    if all(HHK_STAFF.search(getattr(Query(), k) == v) 
           for k, v in query.items()):
        print(HHK_STAFF.search(Query().FNAME == query['FNAME'])[0])
    else:
        print("user does not exist")

But you should be sure this is actually what you want. The English equivalent would be,

"If someone with this last name exists, and someone with this first name exists, and someone with this middle name exists, regardless of whether they're all the same someone, return the person with the matching first name."

In other words, If I now add another person to your database

HHK_STAFF.insert({'EMPLOYEE_ID':'00000002', 
                  'FNAME': 'Anne', 
                  'LNAME':'Person',
                  'MNAME':'Other'})

Your function above will return something for the query

check_table(FNAME="Some", MNAME="Other", LNAME="Person")

Even though Some's middle name is "Random" not "Other", just because somebody else exists on the system whose middle name is "Other"

You may instead want to take advantage of Query's overridden __and__ operator to find a single person that has all the names you're querying:

q = Query()
HHK_STAFF.search((q.FNAME=="Anne") & 
                 (q.MNAME=="Other") & 
                 (q.LNAME=="Person"))

Or, for something using key-value like I did above, using functools.reduce:

from functools import reduce

def user_matching_all(**query):
    q = Query()
    out = HHK_STAFF.search(reduce(lambda x, y: x & y,
                                  [getattr(q, k) == v
                                   for k, v in query.items()]))
    if out:
        print(out)
    else:
        print("user does not exist")