def query(self, one_name="?root_subject",
above_type=None,
above_uri=None,
filter_clause=""):
ret = """
BASE <http://smartplatforms.org/>
PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
CONSTRUCT { $construct_triples }
FROM $context
WHERE {
{ $query_triples }
$filter_clause
}
"""
q = QueryBuilder(self, one_name)
if (above_type and above_uri):
q.require_above(above_type, above_uri)
b = q.build()
ret = ret.replace("$construct_triples", q.construct_triples())
ret = ret.replace("$query_triples", b)
ret = ret.replace("$filter_clause", filter_clause)
return ret
def push_data_keywords(pub_ids=None, index=None):
""" Go through all the publications and their datatables and move data
keywords from tables to their parent publications. """
if not pub_ids:
body = {"query": {"match_all": {}}}
results = es.search(index=index, doc_type=CFG_PUB_TYPE, body=body, _source=False)
pub_ids = [i["_id"] for i in results["hits"]["hits"]]
for pub_id in pub_ids:
query_builder = QueryBuilder()
query_builder.add_child_parent_relation(
"publication", relation="parent", must=True, related_query={"match": {"recid": pub_id}}
)
tables = es.search(index=index, doc_type=CFG_DATA_TYPE, body=query_builder.query, _source_include="keywords")
keywords = [d["_source"].get("keywords", None) for d in tables["hits"]["hits"]]
# Flatten the list
keywords = [i for inner in keywords for i in inner]
# Aggregate
agg_keywords = defaultdict(list)
for kw in keywords:
agg_keywords[kw["name"]].append(kw["value"])
# Remove duplicates
for k, v in agg_keywords.items():
agg_keywords[k] = list(set(v))
body = {"doc": {"data_keywords": dict(agg_keywords)}}
try:
es.update(index=index, doc_type=CFG_PUB_TYPE, id=pub_id, body=body)
except Exception as e:
log.error(e.message)
def testSqlite3(self):
import sqlite3
builder = QueryBuilder()
conn = sqlite3.connect('tests/Chinook_Sqlite_AutoIncrementPKs.sqlite')
builder.add_table_with_fields('album', 'a', conn)
query = builder.select(['albumid', 'artistid'])
self.assertQuery(query, 'select a.albumid albumid, a.artistid artistid from album a')
class ClientThread(Thread):
def __init__(self, qq, address, sock=None):
Thread.__init__(self)
if sock is None:
self.sock = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
else:
self.sock = sock
self.addr = address
self.qb = QueryBuilder(
'https://www.tutti.ch/api/v10/list.json?aggregated=1',
'https://www.tutti.ch/de/li/', qq)
# self.qbre = QueryBuilderRealEstate('https://www.tutti.ch/api/v10/mapsearch/real_estate_search.json',
# 'https://www.tutti.ch/de/immobilien')
def _get_query(self, req):
if req['req'] == 'get_query':
return self.qb.get_query()
elif req['req'] == 'next_page':
return self.qb.get_next_page()
elif req['req'] == 'next_money':
return self.qb.next_money_range()
elif req['req'] == 'set_money':
return self.qb.set_delta_money_range(int(req['data']))
# elif req['req'] == 're_get_query':
# return self.qbre.get_query()
# elif req['req'] == 're_next_page':
# return self.qbre.get_next_page()
# elif req['req'] == 're_next_money':
# return self.qbre.next_money_range()
# elif req['req'] == 're_next_area':
# return self.qbre.next_area_range()
# elif req['req'] == 're_set_money':
# return self.qbre.set_delta_money_range(int(req['data']))
# elif req['req'] == 're_set_area':
# return self.qbre.set_delta_area_range(int(req['data']))
else:
return None
def run(self):
while True:
req = self.sock.recv(1024)
reqDecoded = req.decode('utf-8')
if reqDecoded == '':
break
reqJson = json.loads(reqDecoded)
print(f'[Client {self.addr}] request: {reqJson}')
query = self._get_query(reqJson)
if query is None:
print(f'[Client {self.addr}] bad request: {reqJson}')
query = {'res': 'False', 'msg': 'Bad request'}
res = json.dumps({'res': query}).encode('utf-8')
sent = self.sock.send(res)
if sent == 0:
break
print(f'[Client {self.addr}] disconnected')
def __init__(self, qq, address, sock=None):
Thread.__init__(self)
if sock is None:
self.sock = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
else:
self.sock = sock
self.addr = address
self.qb = QueryBuilder(
'https://www.tutti.ch/api/v10/list.json?aggregated=1',
'https://www.tutti.ch/de/li/', qq)
def handle_search(request, resource_type):
'''
handle FHIR search operation
'''
query_builder = QueryBuilder(request.authorizer)
search_query = query_builder.build_query(resource_type, request.args)
ttam_resource = None
if (resource_type in ('Patient', 'Sequence') and
g.ttam_client is not None):
ttam_resource = resource_type
resp_bundle = FHIRBundle(search_query, request, ttam_resource=ttam_resource)
return resp_bundle.as_response()
def handle_search(request, resource_type):
'''
handle FHIR search operation
'''
query_builder = QueryBuilder(request.authorizer)
search_query = query_builder.build_query(resource_type, request.args)
ttam_resource = None
if (resource_type in ('Patient', 'Sequence') and
g.ttam_client is not None):
ttam_resource = resource_type
resp_bundle = FHIRBundle(search_query, request, ttam_resource=ttam_resource)
return resp_bundle.as_response()
def suggest(request):
query = request.GET.get('query')
if (query is None) or (len(query.strip()) == 0):
return HttpResponseBadRequest(json.dumps({'response': 'bad request'}))
else:
size = request.GET.get('size')
query_obj = QueryBuilder(query, size)
results = query_obj.fetch_result()['hits']['hits']
data = []
for result in results:
data.append({'cin': result['_id'], 'name': result['_source']['name']})
response = HttpResponse(json.dumps(data))
response['Access-Control-Allow-Origin'] = '*'
return response
def get(self):
query = self.get_argument('query')
if (query is None) or (len(query.strip()) == 0):
self.set_status(400)
self.write(json.dumps('bad request'))
else:
size = self.get_argument('size', 5)
query_obj = QueryBuilder(query, size)
results = query_obj.fetch_result()['hits']['hits']
data = []
for result in results:
data.append({'cin': result['_id'], 'name': result['_source']['name']})
self.write(json.dumps(data))
def get_n_latest_records(n_latest, field="last_updated", index=None):
""" Gets latest N records from the index """
query = {"size": n_latest, "query": QueryBuilder.generate_query_string(), "sort": [{field: {"order": "desc"}}]}
query_result = es.search(index=index, doc_type=CFG_PUB_TYPE, body=query)
return query_result["hits"]["hits"]
def testPostgresql(self):
import psycopg2
builder = QueryBuilder()
conn = psycopg2.connect("dbname=test host=%s port=%s user=%s password=%s" % ( \
self.config.get('postgresql', 'host'),
self.config.get('postgresql', 'port'),
self.config.get('postgresql', 'user'),
self.config.get('postgresql', 'passwd')))
c = conn.cursor()
c.execute('drop table if exists album')
c.execute('''create table album
(AlbumId int, Title char(100), ArtistId int)''')
c.close()
builder.add_table_with_fields('album', 'a', conn)
query = builder.select(['albumid', 'artistid'])
self.assertQuery(query, 'select a.albumid albumid, a.artistid artistid from album a')
def testMySql(self):
import mysql.connector as mdb
builder = QueryBuilder()
conn = mdb.connect(
host=self.config.get('mysql', 'host'),
port=self.config.get('mysql', 'port'),
user=self.config.get('mysql', 'user'),
passwd=self.config.get('mysql', 'passwd'))
c = conn.cursor()
c.execute('drop table if exists test.album')
c.execute('''create table if not exists test.album
(AlbumId int, Title varchar(100), ArtistId int)''')
c.close()
builder.add_table_with_fields('test.album', 'a', conn)
query = builder.select(['albumid', 'artistid'])
self.assertQuery(query, 'select a.albumid albumid, a.artistid artistid from test.album a')
def push_data_keywords(pub_ids=None, index=None):
""" Go through all the publications and their datatables and move data
keywords from tables to their parent publications. """
if not pub_ids:
body = {'query': {'match_all': {}}}
results = es.search(index=index,
doc_type=CFG_PUB_TYPE,
body=body,
_source=False)
pub_ids = [i['_id'] for i in results['hits']['hits']]
for pub_id in pub_ids:
query_builder = QueryBuilder()
query_builder.add_child_parent_relation(
'publication',
relation='parent',
must=True,
related_query={'match': {
'recid': pub_id
}})
tables = es.search(index=index,
doc_type=CFG_DATA_TYPE,
body=query_builder.query,
_source_include='keywords')
keywords = [
d['_source'].get('keywords', None) for d in tables['hits']['hits']
]
# Flatten the list
keywords = [i for inner in keywords for i in inner]
# Aggregate
agg_keywords = defaultdict(list)
for kw in keywords:
agg_keywords[kw['name']].append(kw['value'])
# Remove duplicates
for k, v in agg_keywords.items():
agg_keywords[k] = list(set(v))
body = {"doc": {'data_keywords': dict(agg_keywords)}}
try:
es.update(index=index, doc_type=CFG_PUB_TYPE, id=pub_id, body=body)
except Exception as e:
log.error(e.message)
def main():
url = "https://nhlstatisticsforohtu.herokuapp.com/players.txt"
reader = PlayerReader(url)
stats = Statistics(reader)
#matcher = And(
# HasAtLeast(5, "goals"),
# HasAtLeast(5, "assists"),
# PlaysIn("PHI")
#)
query = QueryBuilder()
matcher = query.playsIn("NYR").hasAtLeast(5, "goals").build() #
#print(typeof(matcher))
for player in stats.matches(matcher):
print(player)
def suggest(request):
query = request.GET.get('query')
if (query is None) or (len(query.strip()) == 0):
return HttpResponseBadRequest(json.dumps({'response': 'bad request'}))
else:
size = request.GET.get('size')
query_obj = QueryBuilder(query, size)
results = query_obj.fetch_result()['hits']['hits']
data = []
for result in results:
data.append({
'cin': result['_id'],
'name': result['_source']['name']
})
response = HttpResponse(json.dumps(data))
response['Access-Control-Allow-Origin'] = '*'
return response
def get(self):
query = self.get_argument('query')
if (query is None) or (len(query.strip()) == 0):
self.set_status(400)
self.write(json.dumps('bad request'))
else:
size = self.get_argument('size', 5)
query_obj = QueryBuilder(query, size)
results = query_obj.fetch_result()['hits']['hits']
data = []
for result in results:
data.append({
'cin': result['_id'],
'name': result['_source']['name']
})
self.write(json.dumps(data))
def setUp(self):
self.builder = QueryBuilder()
self.builder.add_table('table_a', 'a')
self.builder.add_field('sum(a.f1)', 'fa')
self.builder.add_field('a.f2', 'ga')
self.builder.add_table('table_b', 'b')
self.builder.add_field('count(b.f)', 'fb')
self.builder.link_tables('a.id = b.id')
self.builder.add_field('sum(a.f1) / count(b.f)', 'fab')
self.builder.add_field('concat(a.f2, b.f)', 'cab')
self.builder.add_field('0', 'c')
def print_results():
if QueryBuilder.print_setting == "":
for r in QueryBuilder.cursor_results:
#print(r)
print(str(r['NAME']) + " " + str(r['MANA_COST']))
print(r['TYPE'])
print(r['CARD_TEXT'])
print()
if QueryBuilder.print_setting == 'count_bare':
try:
print(len(QueryBuilder.cursor_results))
except TypeError:
print(len([r for r in QueryBuilder.cursor_results]))
if QueryBuilder.print_setting == 'custom':
#Code from: https://docs.python.org/3.5/library/stdtypes.html#str.format
class Default(dict):
def __missing__(self, key):
return key
for r in QueryBuilder.cursor_results:
print(QueryBuilder.custom_print_str.format_map(Default(r)))
if QueryBuilder.debug_options["PRINT_QUERY"]:
print("===QUERY===")
QueryBuilder.print_query()
print("===========")
def get_n_latest_records(n_latest, field="last_updated", index=None):
""" Gets latest N records from the index """
query = {
"size": n_latest,
"query": QueryBuilder.generate_query_string(),
"sort": [{
field: {
"order": "desc"
}
}]
}
query_result = es.search(index=index, doc_type=CFG_PUB_TYPE, body=query)
return query_result['hits']['hits']
def setUp(self):
# table relationships
self.builder = QueryBuilder()
self.builder.add_table('table_a', 'a')
self.builder.add_field('a.f', 'fa')
self.builder.add_table('table_b', 'b')
self.builder.link_tables('a.id = b.id')
self.builder.add_field('b.f', 'fb')
self.builder.add_table('table_c', 'c')
self.builder.link_tables('a.id = c.id')
self.builder.add_field('c.f', 'fc')
self.builder.add_table('table_d', 'd')
self.builder.link_tables('c.id = d.id')
self.builder.add_field('d.f', 'fd')
def answer_question(self, question):
query_builder = QueryBuilder()
pipeline_steps = [
dissect_sentence, capital_filter.process,
independence_date_filter.process, country_code_filter.process,
national_symbol_filter.process, urban_areas_filter.process,
religion_filter.process, geographic_coordinates_filter.process,
national_anthem_filter.process, unemployment_rate_filter.process,
population_growth_rate_filter.process, total_area_filter.process,
population_filter.process, telephone_lines_filter.process,
language_filter.process, public_debt_filter.process,
illicit_drugs_filter.process, industries_filter.process,
climate_filter.process, importation_filter.process,
inflation_rate_filter.process,
electricity_production_filter.process, death_rate_filter.process,
birth_rate_filter.process, internet_users_filter.process,
exportation_filter.process, self.fetch_answer
]
pipeline = combine_pipeline(question, query_builder, pipeline_steps)
consume(pipeline)
def setUp(self):
self.builder = QueryBuilder()
def main():
args = get_arguments()
password = args.password
query_source = []
if "file" in args:
with open(args.file, encoding='utf-8') as json_file:
query_source = json.load(json_file)
else:
query_source.append({
"namespace": args.metric_name,
"query": args.query
})
try:
sql = sql_client.SqlClient.SqlClient(args.host, args.username,
password, args.database)
except (pypyodbc.DatabaseError, pypyodbc.DataError) as e:
print("error connecting")
print(type(e))
print(e)
sys.exit(errno.EACCES)
query_builder = QueryBuilder.QueryBuilder()
queries = query_builder.check(query_source)
result = []
for query in queries:
try:
result_query = sql.run_query(query["query"])
except:
print("Error executing namespace {} - query: {} ".format(
query["namespace"], query["query"]))
result_query = []
if result_query is None:
result_query = 0
single_value = isinstance(result_query, int)
if not single_value:
for k, v in result_query:
current_namespace = (".".join(
[query["namespace"],
k.replace(".", "_")])).lower()
result.append({"namespace": current_namespace, "value": v})
print("{}: {}".format(current_namespace, v))
else:
result.append({
"namespace": query["namespace"].lower(),
"value": result_query
})
print("{}: {}".format(query["namespace"].lower(), result_query))
if args.datadog_apikey:
try:
for metric in result:
datadog_metrics.submit_custom_metric(metric["namespace"],
metric["value"],
args.datadog_apikey,
args.datadog_appkey)
except Exception as e:
print("ERROR sending to datadog: %s" % e)
sys.exit(errno.EACCES)
# Conference Manager App
# Author: Piotr Maślankowski
# Databases, UWr, June 2017
import sys
import json
from database import Database
from query import Query
from query_builder import QueryBuilder
db = Database(False)
openline = sys.stdin.readline()
opendict = json.loads(openline)['open']
db.open(opendict['baza'], opendict['login'], opendict['password'])
if db.opened:
db.create_tables()
builder = QueryBuilder()
for line in sys.stdin:
try:
query = builder.build_query(line)
db.execute(query)
query.parse_result()
print(query.result)
except NotImplementedError:
print(r'{"status": "NOT IMPLEMENTED"}')
db.close()
class QueryBuilderTest(unittest.TestCase):
def setUp(self):
self.builder = QueryBuilder()
def test_select_column_from_table(self):
self.builder.select('account_name').from_table('account')
sql = self.builder.generate_sql()
self.assertEqual('SELECT account_name FROM account;', sql)
def test_select_star_from_table(self):
self.builder.select('*').from_table('account')
sql = self.builder.generate_sql()
self.assertEqual('SELECT * FROM account;', sql)
def test_column_defaults_to_star(self):
self.builder.from_table('account')
sql = self.builder.generate_sql()
self.assertEqual('SELECT * FROM account;', sql)
def test_column_can_take_list(self):
self.builder.select(['col1', 'col2']).from_table('account')
sql = self.builder.generate_sql()
self.assertEqual('SELECT col1, col2 FROM account;', sql)
def test_select_with_where_clause(self):
self.builder.select('account_name').from_table('account').where(
'account_id', 13977)
sql = self.builder.generate_sql()
self.assertEqual(
'SELECT account_name FROM account WHERE account_id=13977;', sql)
class QueryBuilderTest(unittest.TestCase):
def setUp(self):
self.builder = QueryBuilder()
def test_select_column_from_table(self):
self.builder.select("account_name").from_table("account")
sql = self.builder.generate_sql()
self.assertEqual("SELECT account_name FROM account;", sql)
def test_select_star_from_table(self):
self.builder.select("*").from_table("account")
sql = self.builder.generate_sql()
self.assertEqual("SELECT * FROM account;", sql)
def test_column_defaults_to_star(self):
self.builder.from_table("account")
sql = self.builder.generate_sql()
self.assertEqual("SELECT * FROM account;", sql)
def test_column_can_take_list(self):
self.builder.select(["col1", "col2"]).from_table("account")
sql = self.builder.generate_sql()
self.assertEqual("SELECT col1, col2 FROM account;", sql)
def test_select_with_where_clause(self):
self.builder.select("account_name").from_table("account").where("account_id", 13977)
sql = self.builder.generate_sql()
self.assertEqual("SELECT account_name FROM account WHERE account_id=13977;", sql)
def search(query,
index=None,
filters=list(),
size=10,
include="*",
exclude="authors",
offset=0,
sort_field=None,
sort_order='',
post_filter=None):
""" Perform a search query.
:param query: [string] query string e.g. 'higgs boson'
:param index: [string] name of the index. If None a default is used
:param filters: [list of tuples] list of filters for the query.
Currently supported: ('author', author_fullname),
('collaboration', collaboration_name), ('date', date)
:param size: [int] max number of hits that should be returned
:param offset: [int] offset for the results (used for pagination)
:param sort_by: [string] sorting field. Currently supported fields:
"title", "collaboration", "date", "relevance"
:param sort_order: [string] order of the sorting either original
(for a particular field) or reversed. Supported:
'' or 'rev'
:return: [dict] dictionary with processed results and facets
"""
# If empty query then sort by date
if query == '' and not sort_field:
sort_field = 'date'
query = HEPDataQueryParser.parse_query(query)
# Build core query
data_query = get_query_by_type(CFG_DATA_TYPE, query)
pub_query = get_query_by_type(CFG_PUB_TYPE, query)
authors_query = get_authors_query(query)
query_builder = QueryBuilder()
query_builder.add_child_parent_relation(
CFG_DATA_TYPE,
relation="child",
related_query=data_query,
other_queries=[pub_query, authors_query])
# Add additional options
query_builder.add_pagination(size=size, offset=offset)
query_builder.add_sorting(sort_field=sort_field, sort_order=sort_order)
query_builder.add_filters(filters)
query_builder.add_post_filter(post_filter)
query_builder.add_aggregations()
query_builder.add_source_filter(include, exclude)
pub_result = es.search(index=index,
body=query_builder.query,
doc_type=CFG_PUB_TYPE)
parent_filter = {
"filtered": {
"filter": {
"terms": {
"_id": [hit["_id"] for hit in pub_result['hits']['hits']]
}
}
}
}
query_builder = QueryBuilder()
query_builder.add_child_parent_relation(CFG_PUB_TYPE,
relation="parent",
related_query=parent_filter,
must=True,
other_queries=[data_query])
query_builder.add_pagination(size=size * 50)
data_result = es.search(index=index,
body=query_builder.query,
doc_type=CFG_DATA_TYPE)
merged_results = merge_results(pub_result, data_result)
return map_result(merged_results)
def main():
args = get_arguments()
password = args.password
query_source = []
if "file" in args:
with open(args.file, encoding='utf-8') as json_file:
query_source = json.load(json_file)
else:
query_source.append({
"namespace": args.metric_name,
"query": args.query
})
try:
sql = sql_client.SqlClient.SqlClient(args.host, args.username,
password, args.database)
except (pypyodbc.DatabaseError, pypyodbc.DataError) as e:
print("error connecting")
print(type(e))
print(e)
sys.exit(errno.EACCES)
query_builder = QueryBuilder.QueryBuilder()
queries = query_builder.check(query_source)
result = []
columns = None
for query in queries:
try:
result_query, columns = sql.run_query(query["query"])
except Exception as e:
print(e)
print("Error executing namespace {} - query: {} ".format(
query["namespace"], query["query"]))
result_query = []
if result_query is None:
result_query = 0
single_value = isinstance(result_query, int)
if single_value:
result.append({
"namespace": query["namespace"].lower(),
"value": result_query
})
print("{}: {}".format(query["namespace"].lower(), result_query))
elif not single_value and ('format' not in query
or query['format'] == 'namespace'):
# original query format does not contain "format" field.
global_namespace = query["namespace"]
for row in result_query:
local_namespace = ""
v = None
cols = []
for col in row:
if isinstance(col, str):
cols.append(col)
else:
v = col
local_namespace = ".".join(cols)
current_namespace = ".".join(
[global_namespace, local_namespace]).lower()
result.append({
"namespace": current_namespace,
"value": v,
"tags": None
})
print("{}: {}".format(current_namespace, v))
elif 'format' in query and query['format'] == 'tags':
# tagged format has specific field "format" in query document to select it
for row in result_query: # row contain the data row. not a dict.
data = dict(zip(columns, row))
tags = [] # string list of tags
v = data['value']
for tag_key, tag_value in data.items():
if tag_key != "value":
a = ""
a.replace(" ", "")
tags.append("{}:{}".format(tag_key, tag_value))
result.append({
"namespace": query["namespace"].lower(),
"value": v,
"tags": tags
})
if args.datadog_apikey:
try:
for metric in result:
datadog_metrics.submit_custom_metric(
metric["namespace"],
metric["value"],
args.datadog_apikey,
args.datadog_appkey,
timestamp=int(time.time()),
tags=metric["tags"] if "tags" in metric else None)
except Exception as e:
print("ERROR sending to datadog: %s" % e)
sys.exit(errno.EACCES)
def setUp(self):
self.builder = QueryBuilder()
def parse_args():
#arg_name:db_col_name
#usage: -power ">5"
#usage: -toughness 5
int_keys = {"power":"POWER","toughness":"TOUGHNESS"
,"p":"POWER","t":"TOUGHNESS","cmc":"CMC"}
#usage: -name REGEXPATTERN
re_keys = {"name":"NAME","text":"CARD_TEXT"}
#usage:
#-type -i type_a type_b -x type_c
#include types a, types b, exclude type c
list_keys = {"type":"ALL_TYPES","legal":"LEGALITIES"
,"colorid":"COLOR_IDENTITY","printings":"PRINTINGS","format":"LEGALITIES"}
misc_keys = ["-print","-help",",","-bool","-debug","-sort","-random"]
valid_print_options = ['count_bare','custom']
valid_cols = ['CMC', 'COLOR_IDENTITY', 'COLORS', 'LEGALITIES', 'LOYALTY',
'MANA_COST', 'NAME', 'POWER', 'PRINTINGS', 'RULINGS', 'CARD_TEXT',
'TOUGHNESS', 'TYPE', 'ALL_TYPES']
i = 1
arr_len = len(sys.argv)
while i < arr_len:
if sys.argv[i][0] != "-" and sys.argv[i] != ",":
#SET THIS CRITERIA NAME
qb.add_regex("NAME",sys.argv[i])
i += 1
continue
if sys.argv[i][1:] in re_keys.keys():
col = re_keys[sys.argv[i][1:]]
i += 1
qb.add_regex(col,sys.argv[i])
i += 1
continue
if sys.argv[i][1:] in int_keys.keys():
col = int_keys[sys.argv[i][1:]]
i += 1
try:
int(sys.argv[i])
val = sys.argv[i]
qb.add_equality_operation(col,val,"==")
i += 1
continue
except ValueError:
if sys.argv[i][1] != "=" or sys.argv[i][1] != ">" or sys.argv[i][1] != "<":
qb.add_equality_operation(col,str(int(sys.argv[i][1:])),sys.argv[i][0])
i += 1
continue
qb.add_equality_operation(col,str(int(sys.argv[i][1:])),sys.argv[i][0:2])
i += 1
continue
#TODO: look into the sqlite3's adapter features for Python <-> sqlite data
if sys.argv[i][1:] in list_keys.keys():
arg = list_keys[sys.argv[i][1:]]
i += 1
inclusions = []
exclusions = []
if sys.argv[i] != '-x':
inclusions.append(sys.argv[i])
else:
i += 1
exclusions.append(sys.argv[i])
qb.add_list(arg,inclusions,exclusions)
i += 1
continue
#IF WE HIT HERE, WE'VE HIT MISCELANNY OPTIONS
if sys.argv[i] in misc_keys:
#PROCESS...
if sys.argv[i] == "-print":
i += 1
if sys.argv[i] not in valid_print_options:
print("BAD ARGUMENT FOR -print:")
print(sys.argv[i])
sys.exit(1)
qb.print_setting = sys.argv[i]
if sys.argv[i] == 'custom':
i += 1
qb.custom_print_str = open(sys.argv[i],'r').read()
if sys.argv[i] == '-random':
try:
qb.randoms_to_get = int(sys.argv[i + 1])
i += 1
except ValueError:
qb.randoms_to_get = 1
except IndexError:
qb.randoms_to_get = 1
if sys.argv[i] == '-debug':
i += 1
qb.debug_options[sys.argv[i]] = True
if sys.argv[i] == '-bool':
i += 1
qb.push_bool_operation(sys.argv[i])
if sys.argv[i] == "-sort":
i += 1
if sys.argv[i][0] != 'a' and sys.argv[i][0] != 'd':
print("-sort arg must begin with a or d")
print(sys.argv[i])
sys.exit(3)
s = sys.argv[i][1:]
if s not in valid_cols:
print("Invalid column name:")
print(s)
sys.exit(3)
if sys.argv[i][0] == 'a':
qb.sort_cols.append(s + ' ASC')
else:
qb.sort_cols.append(s + ' DESC')
elif sys.argv[i] == ',':
qb.combine_this_criteria()
i += 1
continue
print("Argument not recognized:")
print(sys.argv[i])
sys.exit(1)
first_request = False
user_input = input("See more results? (y/n): ").lower()
# If the user input is 'y' or 'yes', then we continue requesting data from the API.
if user_input == "y":
pass
else:
print("Not printing more results. Breaking.")
break
else:
# Handling unsuccessful request.
print("Request unsuccessful with status =", response.text)
break
if __name__ == "__main__":
base_url = "http://data.sfgov.org/resource/bbb8-hzi6.json"
fields = ["applicant", "location"]
n_results = 10
order_by = "applicant"
ascending = True
query_builder = QueryBuilder(base_url, fields, n_results, order_by,
ascending)
handle_requests(base_url, query_builder, fields, n_results)
def search(
query,
index=None,
filters=list(),
size=10,
include="*",
exclude="",
offset=0,
sort_field=None,
sort_order="",
post_filter=None,
):
""" Perform a search query.
:param query: [string] query string e.g. 'higgs boson'
:param index: [string] name of the index. If None a default is used
:param filters: [list of tuples] list of filters for the query.
Currently supported: ('author', author_fullname),
('collaboration', collaboration_name), ('date', date)
:param size: [int] max number of hits that should be returned
:param offset: [int] offset for the results (used for pagination)
:param sort_by: [string] sorting field. Currently supported fields:
"title", "collaboration", "date", "relevance"
:param sort_order: [string] order of the sorting either original
(for a particular field) or reversed. Supported:
'' or 'rev'
:return: [dict] dictionary with processed results and facets
"""
# If empty query then sort by date
if query == "" and not sort_field:
sort_field = "date"
query = HEPDataQueryParser.parse_query(query)
# Build core query
data_query = get_query_by_type(CFG_DATA_TYPE, query)
pub_query = get_query_by_type(CFG_PUB_TYPE, query)
authors_query = get_authors_query(query)
query_builder = QueryBuilder()
query_builder.add_child_parent_relation(
CFG_DATA_TYPE, relation="child", related_query=data_query, other_queries=[pub_query, authors_query]
)
# Add additional options
query_builder.add_pagination(size=size, offset=offset)
query_builder.add_sorting(sort_field=sort_field, sort_order=sort_order)
query_builder.add_filters(filters)
query_builder.add_post_filter(post_filter)
query_builder.add_aggregations()
query_builder.add_source_filter(include, exclude)
pub_result = es.search(index=index, body=query_builder.query, doc_type=CFG_PUB_TYPE)
parent_filter = {"filtered": {"filter": {"terms": {"_id": [hit["_id"] for hit in pub_result["hits"]["hits"]]}}}}
query_builder = QueryBuilder()
query_builder.add_child_parent_relation(
CFG_PUB_TYPE, relation="parent", related_query=parent_filter, must=True, other_queries=[data_query]
)
query_builder.add_pagination(size=size * 50)
data_result = es.search(index=index, body=query_builder.query, doc_type=CFG_DATA_TYPE)
merged_results = merge_results(pub_result, data_result)
return map_result(merged_results)
from flask import Flask, request, jsonify
from query_builder import QueryBuilder
app = Flask(__name__)
builder = QueryBuilder()
@app.route('/ask/storage/', methods=['POST'])
def ask_elastic_storage():
data = request.get_json()
if 'action' not in data.keys():
return 'action not in request body', 400
else:
elastic_results = builder.command(action=data['action'], payload=data)
json_results = builder.get_source(elastic_results)
return jsonify(json_results)
class TestQueryBuilder(TestQueryBuilderBase):
def setUp(self):
# table relationships
self.builder = QueryBuilder()
self.builder.add_table('table_a', 'a')
self.builder.add_field('a.f', 'fa')
self.builder.add_table('table_b', 'b')
self.builder.link_tables('a.id = b.id')
self.builder.add_field('b.f', 'fb')
self.builder.add_table('table_c', 'c')
self.builder.link_tables('a.id = c.id')
self.builder.add_field('c.f', 'fc')
self.builder.add_table('table_d', 'd')
self.builder.link_tables('c.id = d.id')
self.builder.add_field('d.f', 'fd')
def testSelectA(self):
query = self.builder.select(['fa'])
self.assertQuery(query, 'select a.f fa from table_a a')
def testSelectB(self):
query = self.builder.select(['fb'])
self.assertQuery(query, 'select b.f fb from table_b b')
def testSelectC(self):
query = self.builder.select(['fc'])
self.assertQuery(query, 'select c.f fc from table_c c')
def testSelectD(self):
query = self.builder.select(['fd'])
self.assertQuery(query, 'select d.f fd from table_d d')
def testSelectAB(self):
query = self.builder.select(['fa', 'fb'])
self.assertQuery(query, '''
select a.f fa, b.f fb
from table_a a,
table_b b
where a.id = b.id''')
def testSelectAC(self):
query = self.builder.select(['fa', 'fc'])
self.assertQuery(query, '''
select a.f fa, c.f fc
from table_a a,
table_c c
where a.id = c.id''')
def testSelectAD(self):
query = self.builder.select(['fa', 'fd'])
self.assertQuery(query, '''
select a.f fa, d.f fd
from table_a a,
table_c c,
table_d d
where a.id = c.id and c.id = d.id''')
def testSelectBC(self):
query = self.builder.select(['fb', 'fc'])
self.assertQuery(query, '''
select b.f fb, c.f fc
from table_a a,
table_b b,
table_c c
where a.id = b.id and a.id = c.id''')
def testSelectBD(self):
query = self.builder.select(['fb', 'fd'])
self.assertQuery(query, '''
select b.f fb, d.f fd
from table_a a,
table_b b,
table_c c,
table_d d
where a.id = b.id and a.id = c.id and c.id = d.id''')
def testSelectCD(self):
query = self.builder.select(['fc', 'fd'])
self.assertQuery(query, '''
select c.f fc, d.f fd
from table_c c,
table_d d
where c.id = d.id''')
def testSelectABC(self):
query = self.builder.select(['fa', 'fb', 'fc'])
self.assertQuery(query, '''
select a.f fa, b.f fb, c.f fc
from table_a a,
table_b b,
table_c c
where a.id = b.id and a.id = c.id''')
def testSelectABCD(self):
query = self.builder.select(['fa', 'fb', 'fc', 'fd'])
self.assertQuery(query, '''
select a.f fa, b.f fb, c.f fc, d.f fd
from table_a a,
table_b b,
table_c c,
table_d d
where a.id = b.id and a.id = c.id and c.id = d.id''')
def testSelectAWhereA(self):
query = self.builder.where('fa = 1').select(['fa'])
self.assertQuery(query, '''
select a.f fa
from table_a a
where a.f = 1''')
query = self.builder.where('fa = "a"').select(['fa'])
self.assertQuery(query, '''
select a.f fa
from table_a a
where a.f = "a"''')
query = self.builder.where('fa = \'a\'').select(['fa'])
self.assertQuery(query, '''
select a.f fa
from table_a a
where a.f = \'a\'''')
def testSelectAWhereB(self):
query = self.builder.where('fb = 1').select(['fa'])
self.assertQuery(query, '''
select a.f fa
from table_a a,
table_b b
where a.id = b.id and b.f = 1''')
def testWhereChainable(self):
query = self.builder.where('fa = 1').where('fb = 1').select(['fa'])
self.assertQuery(query, '''
select a.f fa
from table_a a,
table_b b
where a.id = b.id and a.f = 1 and b.f = 1''')
def testWhereReturnNewInstance(self):
self.builder.where('fb = 1').select(['fa'])
query = self.builder.where('fa = 1').select(['fa'])
self.assertQuery(query, '''
select a.f fa
from table_a a
where a.f = 1''')
from query_builder import QueryBuilder
except ImportError:
from modules.query_builder import QueryBuilder
print("Testing QueryBuilder...")
print(" -def push_bool_operation()")
################################################################################
print(" Testing push_bool_operation's error-finding...")
tests = {"DROP TABLES":False, "a":False, "DROP CARDS":False, "SELECT":False,
"1|6":False,"0|1":True,"(0|1)":True,"0":True,"{":False, "0&1":True,
"0|1 DROP CARDS":False,"0&(1|2)":True}
num_tests = len(tests)
failures = []
for test in tests.keys():
result = QueryBuilder.push_bool_operation(test) == tests[test]
if not result:
failures.append(test)
print(str(len(failures)) + '/' + str(len(tests)) + ' tests FAILED:')
for failure in failures:
print('FAILURE: ', failure)
print('EXPECTED:', tests[failure])
print('ACTUAL: ', not(tests[failure]))
################################################################################
#Resetting
QueryBuilder.bool_operation = None
print(" Testing push_bool_operation's converting correctly")
tests = {"0|1":"{0} OR {1}","0&1":"{0} AND {1}","0&(1|2)":"{0} AND ({1} OR {2})"}
class TestQueryBuilderAggregation(TestQueryBuilderBase):
def setUp(self):
self.builder = QueryBuilder()
self.builder.add_table('table_a', 'a')
self.builder.add_field('sum(a.f1)', 'fa')
self.builder.add_field('a.f2', 'ga')
self.builder.add_table('table_b', 'b')
self.builder.add_field('count(b.f)', 'fb')
self.builder.link_tables('a.id = b.id')
self.builder.add_field('sum(a.f1) / count(b.f)', 'fab')
self.builder.add_field('concat(a.f2, b.f)', 'cab')
self.builder.add_field('0', 'c')
def testSelectSumA(self):
query = self.builder.select(['fa'])
self.assertQuery(query, '''
select sum(a.f1) fa
from table_a a''')
def testSelectCountB(self):
query = self.builder.select(['fb'])
self.assertQuery(query, '''
select count(b.f) fb
from table_b b''')
def testSelectAvgAB(self):
query = self.builder.select(['fab'])
self.assertQuery(query, '''
select sum(a.f1) / count(b.f) fab
from table_a a,
table_b b
where a.id = b.id''')
def testGroupBy(self):
query = self.builder.group_by(['ga']).select(['ga', 'fa'])
self.assertQuery(query, '''
select a.f2 ga, sum(a.f1) fa
from table_a a
group by a.f2''')
query = self.builder.group_by(['ga']).select(['fa'])
self.assertQuery(query, '''
select sum(a.f1) fa
from table_a a
group by a.f2''')
def testGroupByFormula(self):
query = self.builder.group_by(['cab']).select(['fa'])
self.assertQuery(query, '''
select sum(a.f1) fa
from table_a a,
table_b b
where a.id = b.id
group by concat(a.f2, b.f)''')
query = self.builder.group_by(['ga', 'cab']).select(['fa'])
self.assertQuery(query, '''
select sum(a.f1) fa
from table_a a,
table_b b
where a.id = b.id
group by a.f2, concat(a.f2, b.f)''')
def testHaving(self):
query = self.builder.having('fa > 1').select(['fa'])
self.assertQuery(query, '''
select sum(a.f1) fa
from table_a a
having fa > 1''')
query = self.builder.group_by(['ga']).having('fa > 1').select(['fa'])
self.assertQuery(query, '''
select sum(a.f1) fa
from table_a a
group by a.f2
having fa > 1''')
def testHavingContainUnselectedAlias(self):
with self.assertRaisesRegexp(Exception, "alias 'cab' needs to be selected"):
self.builder.having('cab = "abc"').select(['fa'])
def testSelectConstant(self):
query = self.builder.select(['c'])
self.assertQuery(query, 'select 0 c')
query = self.builder.select(['fa', 'c'])
self.assertQuery(query, '''
select sum(a.f1) fa, 0 c
from table_a a''')
