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.
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:
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.