def retrieve_by_template(table, t, fields=None, limit=None, offset=None, orderBy=None):
if t is not None:
w = templateToWhereClause(t)
else:
w = ""
if orderBy is not None:
o = "order by " + ",".join(orderBy['fields']) + " " + orderBy['direction'] + " "
else:
o = ""
if limit is not None:
w += " LIMIT " + str(limit)
if offset is not None:
w += " OFFSET " + str(offset)
if fields is None:
fields = " * "
else:
fields = " " + ",".join(fields) + " "
# Check the Redis cache before executing a query
# redis.cleanup()
r = redis.check_query_cache(table, t, fields)
if r is None or len(r) <= 0:
q = "SELECT " + fields + " FROM " + table + " " + w + ";"
r = db.run_q(cnx, q, None, fetch=True, commit=True)
redis.add_to_query_cache(table, t, fields, r)
return r
def test2():
'''
test add to query cache
'''
result = data_cache.add_to_query_cache("people",{"playerID": "willite01", "nameLast": "Williams", "bats": "R"}, \
['nameLast', "birthCity"],[{'nameLast':'Williams',"birthCity":'San Diego'}])
print('cache insert', result)
def retrieve_by_template(table,
t,
fields=None,
limit=None,
offset=None,
orderBy=None):
original_fields = copy.deepcopy(fields)
r = data_cache.check_query_cache(table, t, fields)
if r is not None and len(r) > 0:
print("CACHE HIT")
return r
else:
print("CACHE MISS")
if t is not None:
w = templateToWhereClause(t)
else:
w = ""
if orderBy is not None:
o = "order by " + ",".join(
orderBy['fields']) + " " + orderBy['direction'] + " "
else:
o = ""
if limit is not None:
w += " LIMIT " + str(limit)
if offset is not None:
w += " OFFSET " + str(offset)
if fields is None:
fields = " * "
else:
fields = " " + ",".join(fields) + " "
cursor = cnx.cursor()
q = "SELECT " + fields + " FROM " + table + " " + w + ";"
r = db.run_q(cnx, q, None, fetch=True, commit=True)
print("CACHE INSERT")
data_cache.add_to_query_cache(table, t, original_fields, r)
return r
def add_to_cache(table, tmp, fields, limit, offset, orderBy, q_result):
cache_tmp = copy.copy(tmp)
if limit:
cache_tmp['limit'] = limit
if offset:
cache_tmp['offset'] = offset
if orderBy:
cache_tmp['orderBy'] = orderBy
result = dc.add_to_query_cache(table, cache_tmp, fields, q_result)
return result
def retrieve_by_template(table,
t,
fields=None,
limit=None,
offset=None,
orderBy=None):
result = data_cache.check_query_cache(table, t, fields)
if result is not None:
print('cache hit')
return result
else:
if t is not None:
w = templateToWhereClause(t)
else:
w = ""
if orderBy is not None:
o = "order by " + ",".join(
orderBy['fields']) + " " + orderBy['direction'] + " "
else:
o = ""
if limit is not None:
w += " LIMIT " + str(limit)
if offset is not None:
w += " OFFSET " + str(offset)
if fields is None:
field = " * "
else:
field = " " + ",".join(fields) + " "
cursor = cnx.cursor()
q = "SELECT " + field + " FROM " + table + " " + w + ";"
r = db.run_q(cnx, q, None, fetch=True, commit=True)
# r is json
key = data_cache.add_to_query_cache(table, t, fields, r)
print('cache miss')
print("cache inserted: ", key)
return r
def test3():
data_cache.add_to_query_cache("people", template, fields,query_result)
result = data_cache.check_query_cache("people", template,fields)
print("Result = ", result)