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 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 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''')