Dynamically parse and build TinyDB queries

Question

Would it be possible to dynamically build queries in TinyDB? Its logical query operation is like this:

>>> from tinydb import TinyDB, where
>>> db = TinyDB('db.json')
>>> # Logical AND:
>>> db.search((where('int') == 1) & (where('char') == 'b'))
[{'int': 1, 'char': 'b'}]

But I need to build the query dynamically from user's input conditions. The only way I can figure out is to concatenate the conditions into a string and exec it like this:

#!/usr/bin/env python3
import shlex
from tinydb  import TinyDB, where

# create db sample
db = TinyDB('test.json')
db.insert({'id': '1', 'name': 'Tom', 'age': '10', 'grade': '4'})
db.insert({'id': '2', 'name': 'Alice', 'age': '9', 'grade': '3'})
db.insert({'id': '3', 'name': 'John', 'age': '11', 'grade': '5'})
db.close()

# query test
db = TinyDB('test.json')
q = input("query for name/age/grade: ")
# name='Tom' grade='4'
qdict = dict(token.split('=') for token in shlex.split(q))

result = []
query = "result = db.search("
qlen = len(qdict)
count = 0
for key, value in qdict.items():
    query += "(where('%s') == '%s')" % (key, value)
    count += 1
    if count < qlen:
        query += " & "

query += ')'
exec(query)
print(result)
# [{'age': '10', 'id': '1', 'grade': '4', 'name': 'Tom'}]

Is there a better and elegant way to do that? Thanks a lot.

score 4 · Accepted Answer · answered May 29 '15 at 14:10

Here is a minimal solution that supports the following operators:

==, !=, >=, <-, >, <

The syntax of queries are:

<key> <operator> <value>

You must separate each token by a space.

Code:

#!/usr/bin/env python3


from __future__ import print_function


try:
    import readline  # noqa
except ImportError:
    print("Warning: No readline support available!")


try:
    input = raw_input
except NameError:
    pass


import sys
from os import path
from operator import eq, ge, gt, le, lt, ne


from tinydb import TinyDB, where


ops = {
    "==": eq,
    "!=": ne,
    "<=": le,
    ">=": ge,
    "<": lt,
    ">": gt,
}


def isint(s):
    return all(map(str.isdigit, s))


def isfloat(s):
    return "." in s and isint(s.replace(".", ""))


def createdb(filename):
    db = TinyDB(filename)
    db.insert({"id": 1, "name": "Tom",   "age": 10, "grade": 4})
    db.insert({"id": 2, "name": "Alice", "age":  9, "grade": 3})
    db.insert({"id": 3, "name": "John",  "age": 11, "grade": 5})
    db.close()


def opendb(filename):
    return TinyDB(filename)


def parse_query(s):
    qs = []

    tokens = s.split("&")
    tokens = map(str.strip, tokens)

    for token in tokens:
        try:
            k, op, v = token.split(" ", 3)
        except Exception as e:
            print("Syntax Error with {0:s}: {1:s}".format(repr(s), e))
            return where(None)

        opf = ops.get(op, None)
        if opf is None:
            print("Unknown operator: {0:s}".format(op))
            return where(None)

        if isfloat(v):
            v = float(v)
        elif isint(v):
            v = int(v)

        qs.append(opf(where(k), v))

    return reduce(lambda a, b: a & b, qs)


def main():
    if not path.exists(sys.argv[1]):
        createdb(sys.argv[1])

    db = opendb(sys.argv[1])

    while True:
        try:
            s = input("Query: ")
            q = parse_query(s)
            print(repr(db.search(q)))
        except (EOFError, KeyboardInterrupt):
            break

    db.close()


if __name__ == "__main__":
    main()

Demo:

$ python foo.py test.json
Query: name == Tom
[{u'grade': 4, u'age': 10, u'id': 1, u'name': u'Tom'}]
Query: grade >= 3
[{u'grade': 4, u'age': 10, u'id': 1, u'name': u'Tom'}, {u'grade': 3, u'age': 9, u'id': 2, u'name': u'Alice'}, {u'grade': 5, u'age': 11, u'id': 3, u'name': u'John'}]
Query: grade == 3
[{u'grade': 3, u'age': 9, u'id': 2, u'name': u'Alice'}]
Query: age <= 13
[{u'grade': 4, u'age': 10, u'id': 1, u'name': u'Tom'}, {u'grade': 3, u'age': 9, u'id': 2, u'name': u'Alice'}, {u'grade': 5, u'age': 11, u'id': 3, u'name': u'John'}]
Query:

Notes:

I've only tested this on Python 2.7
I've used the most recent tinydb library
I changed your "test data" to include "real" data types

Most importantly though; this does not use eval() or exec in any way and tries to parse the input and build up the query object.

I really appreciate your elaborate code. It looks like the core magic is to use the `operator` module, which I have not known until now. BTW, Python 3 had moved `reduce()` into `functools` module, Guido in [https://docs.python.org/3.0/whatsnew/3.0.html ] (What’s New In Python 3.0) says, "Removed `reduce()`. Use `functools.reduce()` if you really need it; however, 99 percent of the time an explicit for loop is more readable." I suppose your code accounts for the 1 percent of the time. :-) — AlvaPan, May 29 '15 at 15:52

score 0 · Answer 2 · answered May 22 '23 at 02:24

Yes, looks like it is quite easy to dynamically build TinyDB queries.. Just ran into the same problem myself, and came up with this working solution:

from tinydb import TinyDB, Query

T = TinyDB('storage.tinydb').table('data')
Q = Query()


# creating some sample data..

T.truncate()
T.insert_multiple(
  [
    {'name': 'John', 'age': 22},
    {'name': 'John', 'age': 37},
    {'name': 'John', 'age': 45},
    {'name': 'Mike', 'age': 33},
    {'name': 'Paul', 'age': 40},
  ]
)


# here you can create you query units..

query1 = Q.name == 'John'

min_age = 30
query2 = Q.age >= min_age

person_name = 'Paul'
query3 = Q.name == person_name


# then those query units can be combined as you like..

combined_query = query1 & query2


print( T.search(query1) )

print( T.search(query1 & query2) )

print( T.search(combined_query) )

print( T.search(query1 | query3) )

print( T.search((query1 | query3) & query2) )

I am doing this using TinyDB version 4.7.1.

Cheers! :)

Dynamically parse and build TinyDB queries

2 Answers2