def __init__(self, user): self.query_manager_instance = QueryManager() # Method map corresponding to the input type. It can be scaled in this way. self.method_map = { "hi": "say_hi", "!google": "search_google", "!recent": "get_recent_searches" } self.user = user
def post(self, request): """ Summary: Returns the search according to parameters --- parameters: - name: doc_type required: false type: string - name: query_terms required: false type: json Array - name: filters required: false type: json Array - name: boosters required: false type: json Array - name: latlong type: json Array required: false - name: offset type: integer required: false - name: count type: integer required: false """ try: doc_type = request.data.get("doc_type", 'restaurant') query_terms = request.data.get("query_terms",[]) filters = request.data.get("filters",[]) boosters = request.data.get("boosters",[]) latlong = request.data.get("latlong",[28.3169,77.2090]) offset = request.data.get("offset", 0) count = request.data.get("count", 0) search = QueryManager(index_name='nomnom', doc_type=doc_type, query_terms=query_terms, filters=filters, latlong= latlong, boosters=boosters) result = json.loads(search.get_results(offset=offset, count=count)) if 'hits' in result.get("hits", {}): result_dict = result['hits']['hits'] result_dict = json.dumps(result_dict) return JSONResponse(data=result_dict, search=True, status=200) else: return JSONResponse(result, status=400) except Exception as e: beam(e) print traceback.format_exc(e) return JSONResponse({"Error": "Missing parameters in request"}, status=400)
def __init__(self): super(QueryManagerMachine, self).__init__() self.config = { 'db': 'test.db', 'fields': [], 'filters': [], 'table': 'Customers', 'max_export_rows': 500000, 'max_display_rows': 1000, 'order_by': '' } self.query_manager = QueryManager(self.config)
def __init__(self, table: Table): super(AbstractModel, self).__init__() self.query_manager = QueryManager(table=table) self.original_data = [] self.modified_data = [] self.visible_data = [] # variables needed for pagination self.rows_per_page = 50 self.rows_loaded = 50 # Connect Signals self.query_manager.query_results_signal.connect(self.update_view)
class QueryManagerMachine(RuleBasedStateMachine): """Stateful tests for the Query Manager class""" fields = Bundle('fields') criteria = Bundle('criteria') def __init__(self): super(QueryManagerMachine, self).__init__() self.config = { 'db': 'test.db' , 'fields': [] , 'filters': [] , 'table': 'Customers' , 'max_export_rows': 500000 , 'max_display_rows': 1000 , 'order_by': '' } self.query_manager = QueryManager(self.config) @rule(target=fields, field=FieldStrategy) def add_field(self, field): assume(field.name not in self.query_manager.headers) # must be unique assume(valid_sql_field_name(field.name)) self.config.get('fields').append([field.name, field.type]) @rule(field=fields, value=text()) def add_criteria(self, field, value): assume(field) assume(field.name) random_op = random_operator(field) self.query_manager.add_criteria( field=field , value=value , operator=random_op ) @rule() def query_is_valid(self): assume(self.query_manager.fields) fieldlist = [ fld.name for fld in self.query_manager.fields.values() ] note('field list: {}'.format(list(fieldlist))) note('sql display: {}'.format(self.query_manager.sql_display)) assert valid_sql(stmt=self.query_manager.sql_display)
def example_query_manager(): config = { 'db': 'test.db', 'fields': [], 'filters': [], 'table': 'Customers', 'max_export_rows': 500000, 'max_display_rows': 1000, 'order_by': '' } return QueryManager(config)
def datasheet_view(fields, filters): config = { 'db': 'test.db', 'fields': fields, 'filters': filters, 'table': 'Customers', 'max_export_rows': 500000, 'max_display_rows': 1000, 'order_by': '' } return QueryManager(config)
class QueryManagerMachine(RuleBasedStateMachine): """Stateful tests for the Query Manager class""" fields = Bundle('fields') criteria = Bundle('criteria') def __init__(self): super(QueryManagerMachine, self).__init__() self.config = { 'db': 'test.db', 'fields': [], 'filters': [], 'table': 'Customers', 'max_export_rows': 500000, 'max_display_rows': 1000, 'order_by': '' } self.query_manager = QueryManager(self.config) @rule(target=fields, field=FieldStrategy) def add_field(self, field): assume(field.name not in self.query_manager.headers) # must be unique assume(valid_sql_field_name(field.name)) self.config.get('fields').append([field.name, field.type]) @rule(field=fields, value=text()) def add_criteria(self, field, value): assume(field) assume(field.name) random_op = random_operator(field) self.query_manager.add_criteria(field=field, value=value, operator=random_op) @rule() def query_is_valid(self): assume(self.query_manager.fields) fieldlist = [fld.name for fld in self.query_manager.fields.values()] note('field list: {}'.format(list(fieldlist))) note('sql display: {}'.format(self.query_manager.sql_display)) assert valid_sql(stmt=self.query_manager.sql_display)
def __init__(self): super(QueryManagerMachine, self).__init__() self.config = { 'db': 'test.db' , 'fields': [] , 'filters': [] , 'table': 'Customers' , 'max_export_rows': 500000 , 'max_display_rows': 1000 , 'order_by': '' } self.query_manager = QueryManager(self.config)
def query_manager(): fields = [ Field('CustomerID', 'int'), Field('ProductID', 'int'), Field('CustomerName', 'str'), Field('OrderDate', 'date'), Field('ShippingDate', 'date'), Field('ShippingAddress', 'str'), Field('SalesAmount', 'float') ] config = { 'db': 'test.db', 'fields': fields, 'filters': [], 'table': 'Customers', 'max_export_rows': 500000, 'max_display_rows': 1000, 'order_by': '' } return QueryManager(config)
class AbstractModel(QtCore.QAbstractTableModel): filters_changed_signal = QtCore.pyqtSignal() error_signal = QtCore.pyqtSignal(str) def __init__(self, table: Table): super(AbstractModel, self).__init__() self.query_manager = QueryManager(table=table) self.original_data = [] self.modified_data = [] self.visible_data = [] # variables needed for pagination self.rows_per_page = 50 self.rows_loaded = 50 # Connect Signals self.query_manager.query_results_signal.connect(self.update_view) def add_row(self, ix: QtCore.QModelIndex) -> None: dummies = { FieldType.bool: True , FieldType.int: 0 , FieldType.float: 0.0 , FieldType.str: '' , FieldType.date: '1900-01-01' } dummy_row = [] # type: List for fld in self.query_manager.table.fields: dummy_row.append(dummies[fld.dtype]) for k, v in self.query_manager.table.foreign_keys.items(): dummy_row[k] = next(fk for fk in self.foreign_keys[k]) dummy_row[self.query_manager.table.primary_key_index] = uuid.uuid4().int self.visible_data.insert(ix.row(), dummy_row) self.modified_data.insert(0, dummy_row) self.dataChanged.emit(ix, ix) def canFetchMore(self, index=QtCore.QModelIndex()): if len(self.visible_data) > self.rows_loaded: return True return False @property def changes(self) -> Dict[str, set]: if not self.query_manager.table.editable: return # safe guard pk = self.query_manager.table.primary_key_index original = set(map(tuple, self.original_data)) modified = set(map(tuple, self.modified_data)) changed_ids = set(row[pk] for row in original ^ modified) updated = set( row for row in modified if row[pk] in changed_ids and row[pk] in {row[pk] for row in original} ) added = (modified - original) - updated deleted = set( row for row in (original - modified) if row[pk] not in {row[pk] for row in updated} ) return { 'added': added , 'deleted': deleted , 'updated': updated } def fetchMore(self, index=QtCore.QModelIndex()): remainder = len(self.visible_data) - self.rows_loaded rows_to_fetch = min(remainder, self.rows_per_page) self.beginInsertRows( QtCore.QModelIndex() , self.rows_loaded , self.rows_loaded + rows_to_fetch - 1 ) self.rows_loaded += rows_to_fetch self.endInsertRows() def field_totals(self, col_ix: ColumnIndex) -> list: totals = [] fld = self.query_manager.table.fields[col_ix] rows = self.rowCount() if fld.dtype == FieldType.float: total = sum(val[col_ix] for val in self.visible_data) avg = total / rows if rows > 0 else 0 totals.append('{} Sum \t = {:,.2f}'.format(fld.name, float(total))) totals.append('{} Avg \t = {:,.2f}'.format(fld.name, float(avg))) elif fld.dtype == FieldType.date: minimum = min(val[col_ix] for val in self.visible_data) maximum = max(val[col_ix] for val in self.visible_data) totals.append('{} Min \t = {}'.format(fld.name, minimum or 'Empty')) totals.append('{} Max \t = {}'.format(fld.name, maximum or 'Empty')) else: totals.append('{} Distinct Count \t = {}'.format(fld.name , len(set(val[col_ix] for val in self.visible_data)))) return totals def columnCount(self, parent: QtCore.QModelIndex=None) -> int: return len(self.query_manager.table.fields) def data(self, index: QtCore.QModelIndex, role: int=QtCore.Qt.DisplayRole): alignment = { FieldType.bool: QtCore.Qt.AlignHCenter | QtCore.Qt.AlignVCenter, FieldType.date: QtCore.Qt.AlignHCenter | QtCore.Qt.AlignVCenter, FieldType.int: QtCore.Qt.AlignLeft | QtCore.Qt.AlignVCenter, FieldType.float: QtCore.Qt.AlignRight | QtCore.Qt.AlignVCenter, FieldType.str: QtCore.Qt.AlignLeft | QtCore.Qt.AlignVCenter, } col = index.column() fld = self.query_manager.table.fields[col] val = self.visible_data[index.row()][col] try: if not index.isValid(): return elif role == QtCore.Qt.TextAlignmentRole: return alignment[fld.dtype] elif role == QtCore.Qt.DisplayRole: if col in self.foreign_keys.keys(): return self.foreign_keys[col][val] return fld.format_value(val) except Exception as e: self.error_signal.emit('Error modeling data: {}'.format(e)) def delete_row(self, ix: QtCore.QModelIndex) -> None: row = ix.row() pk = self.visible_data[row][self.query_manager.table.primary_key_index] mod_row = next( i for i, r in enumerate(self.modified_data) if r[self.query_manager.table.primary_key_index] == pk ) del self.visible_data[row] del self.modified_data[mod_row] self.dataChanged.emit(ix, ix) def distinct_values(self, col_ix: ColumnIndex) -> List[str]: return SortedSet( str(self.fk_lookup(col=col_ix, val=row[col_ix])) for row in self.visible_data ) def filter_equality(self, col_ix: ColumnIndex, val: SqlDataType) -> None: self.visible_data = [ row for row in self.visible_data if row[col_ix] == val ] self.filters_changed_signal.emit() def filter_greater_than(self, col_ix, val) -> None: lkp = partial(self.fk_lookup, col=col_ix) self.visible_data = [ row for row in self.visible_data if lkp(row[col_ix]) >= lkp(val) ] self.sort(col=col_ix, order=QtCore.Qt.AscendingOrder) self.filters_changed_signal.emit() def filter_less_than(self, col_ix, val) -> None: lkp = partial(self.fk_lookup, col=col_ix) self.visible_data = [ row for row in self.visible_data if lkp(row[col_ix]) <= lkp(val) ] self.sort(col=col_ix, order=QtCore.Qt.DescendingOrder) self.filters_changed_signal.emit() def filter_like(self, val: str, col_ix: Optional[ColumnIndex]=None) -> None: self.layoutAboutToBeChanged.emit() lkp = partial(self.fk_lookup, col=col_ix) def normalize(value: str) -> str: return str(value).lower() def is_like(input_val: str, row: Tuple[SqlDataType], col: int) -> bool: if col: if normalize(input_val) in normalize(lkp(row[col])): return True else: if normalize(input_val) in ' '.join([ normalize(self.fk_lookup(val=v, col=c)) for c, v in enumerate(row) ]): return True return False self.visible_data = [ row for row in self.modified_data if is_like(val, row, col_ix) ] self.layoutChanged.emit() self.filters_changed_signal.emit() def filter_set(self, col: int, values: Set[str]) -> None: self.visible_data = [ row for row in self.visible_data if self.fk_lookup(col=col, val=row[col]) in values ] self.filters_changed_signal.emit() def flags(self, ix: QtCore.QModelIndex) -> int: if ix.column() in self.query_manager.editable_fields_indices: return ( QtCore.Qt.ItemIsEditable | QtCore.Qt.ItemIsEnabled | QtCore.Qt.ItemIsSelectable ) return QtCore.Qt.ItemIsEnabled | QtCore.Qt.ItemIsSelectable def fk_lookup(self, val, col) -> SqlDataType: if col in self.query_manager.table.foreign_keys.keys(): return self.foreign_keys[col][val] return val @property def foreign_keys(self) -> Dict[ColumnIndex, Dict[int, str]]: return { ColumnIndex(k): cfg.foreign_keys(v.dimension) for k, v in self.query_manager.table.foreign_keys.items() } def full_reset(self) -> None: self.layoutAboutToBeChanged.emit() self.original_data = [] self.modified_data = [] self.visible_data = [] self.layoutChanged.emit() self.filters_changed_signal.emit() def headerData(self, col: ColumnIndex, orientation: int, role: QtCore.Qt.DisplayRole) -> List[str]: if orientation == QtCore.Qt.Horizontal and role == QtCore.Qt.DisplayRole: return self.query_manager.headers[col] def pull(self) -> None: self.rows_loaded = self.rows_per_page self.query_manager.pull() def primary_key(self, row: int) -> int: """Return the primary key value of the specified row""" return row[self.query_manager.table.primary_key_index] @QtCore.pyqtSlot(str) def query_errored(self, msg) -> None: self.error_signal.emit(msg) def reset(self) -> None: """reset filters - not pending changes""" self.layoutAboutToBeChanged.emit() self.visible_data = self.modified_data self.filters_changed_signal.emit() self.layoutChanged.emit() def rowCount(self, index: Optional[QtCore.QModelIndex]=None) -> int: if self.visible_data: if len(self.visible_data) <= self.rows_loaded: return len(self.visible_data) return self.rows_loaded return 0 def save(self) -> Optional[Dict[str, int]]: chg = self.changes if chg['added'] or chg['deleted'] or chg['updated']: try: # print('changes:', self.changes) results = self.query_manager.save_changes(chg) def update_id(old_id, new_id): row = next( i for i, row in enumerate(self.modified_data) if row[self.query_manager.table.primary_key_index] == old_id ) self.modified_data[row][self.query_manager.table.primary_key_index] = new_id for m in results['new_rows_id_map']: update_id(m[0], m[1]) self.original_data = deepcopy(self.modified_data) if self.query_manager.table in cfg.dimensions: cfg.pull_foreign_keys(self.query_manager.table.table_name) return results except: raise # else no changes to save, view displays 'no changes' when this function returns None def setData(self, ix: QtCore.QModelIndex, value: SqlDataType, role: int=QtCore.Qt.EditRole) -> bool: try: pk = self.visible_data[ix.row()][self.query_manager.table.primary_key_index] row = next( i for i, row in enumerate(self.modified_data) if row[self.query_manager.table.primary_key_index] == pk ) self.visible_data[ix.row()][ix.column()] = value self.modified_data[row][ix.column()] = value self.dataChanged.emit(ix, ix) return True except: return False def sort(self, col: ColumnIndex, order: int) -> None: """sort table by given column number col""" try: self.layoutAboutToBeChanged.emit() if col in self.foreign_keys.keys(): self.visible_data = sorted( self.visible_data , key=lambda row: self.fk_lookup(row[col], col) ) else: self.visible_data = sorted( self.visible_data , key=operator.itemgetter(col) ) if order == QtCore.Qt.DescendingOrder: self.visible_data.reverse() self.layoutChanged.emit() except Exception as e: err_msg = "Error sorting data: {}".format(e) self.error_signal.emit(err_msg) def undo(self) -> None: self.layoutAboutToBeChanged.emit() self.modified_data = deepcopy(self.original_data) self.visible_data = deepcopy(self.original_data) self.layoutChanged.emit() @QtCore.pyqtSlot(list) def update_view(self, results) -> None: self.layoutAboutToBeChanged.emit() self.original_data = results self.visible_data = deepcopy(results) self.modified_data = deepcopy(results) self.layoutChanged.emit()
def post(self, request): try: filters = request.data.get("filters",[]) search = QueryManager(index_name='nomnom', doc_type='dish', query_terms=[], filters=filters, latlong= [], boosters=[]) result = json.loads(search.get_results(offset=0, count=1000)) map_dict = {} result_dict = [] variants =[] i = 0 for c in CART_CATEGORY: result_dict.append({ "cart_category_name": c[0], "display_name": c[1], "items":[] }) map_dict[c[0]] = i i += 1 for r in result['hits']['hits']: i= 0 r = r['_source'] r["variants"] = [] j = 0 variant_position = {} # collect variations of a dish if len(r["dish_variation_names"]): for variant in r["dish_variation_names"]: if not variant in variant_position: r["variants"].append({"name": variant, "line_item": []}) variant_position[variant] = j j += 1 r["variants"][variant_position[variant]]["line_item"].append({"variation_id": r["dish_variation_ids"][i], "portion_size": r["portion_sizes"][i], "quantity": 0, "unit_price": r["unit_price"][i]}) i += 1 else: r["dish_name"] = "Pricing missing for this dish" for e in ["dish_variation_names", "dish_variations", "dish_variation_ids", "portion_sizes", "number_of_pieces", "unit_price", "serving_sizes"]: r.pop(e, None) # Put a dish in a cart category dict result_dict[map_dict[r['cart_category']]]['items'].append(r) #print result_dict response= [] for r in result_dict: if len(r["items"]) > 0: response.append(r) return JSONResponse(data=json.dumps(response), search=True, status=200) except Exception as e: beam(e) print traceback.format_exc(e) return JSONResponse({"Error": traceback.format_exc(e)}, status=400)
class DataManager: def __init__(self, config_path, train_set_size=0.7): if config_path is not None: self.qm = QueryManager(config_path, train_set_size) self.categories = None self.log = None def get_user_items(self, data_type='train', item_aggr_sum=False): """ Parameters -------------- data_type 'train', 'test' or 'all' item_aggr_sum if True, 'users_items' contains total number of connections from user to an item, otherwise 'users_items' is binary matrix Return -------------- {'items': [int,..], 'users': [int,..], 'users_items':[[int,..],..]} """ if self._is_data_available('_ui_data', data_type): return self._ui_data[data_type] items = self.qm.query_items(data_type)[0]['items'] users_items_raw = self.qm.query_users_items(data_type) template_items = [0.0] * len(items) self._ui_data[data_type] = { 'items': items, 'users': [], 'users_items': [] } user_i = -1 for user_items in users_items_raw: self._ui_data[data_type]['users'].append(user_items['user']) user_i += 1 self._ui_data[data_type]['users_items'].append( list(template_items)) for user_item in user_items['items']: if item_aggr_sum: self._ui_data[data_type]['users_items'][user_i][ items.index(user_item)] += 1 else: self._ui_data[data_type]['users_items'][user_i][ items.index(user_item)] = 1 return self._ui_data[data_type] def get_orders(self, data_type='train'): """ Parameters -------------- data_type 'train', 'test' or 'all' Return -------------- {'items': [int,..], 'orders': [int,..], 'orders_items':[[int,..],..]} """ if self._is_data_available('_o_data', data_type): return self._o_data[data_type] self._o_data[data_type] = self.qm.query_orders(data_type, 'order') return self._o_data[data_type] def get_order_items(self, data_type='train'): """ Parameters -------------- data_type 'train', 'test' or 'all' Return -------------- {'items': [int,..], 'orders': [int,..], 'orders_items':[[int,..],..]} """ if self._is_data_available('_oi_data', data_type): return self._oi_data[data_type] orders_raw = self.qm.query_orders(data_type) items = self.qm.query_items('all')[0]['items'] template_items = [0.0] * len(items) self._oi_data[data_type] = { 'items': items, 'orders': [], 'orders_items': [] } current_order_id = -1 current_order_i = -1 for order_item in orders_raw: try: if current_order_id != order_item['order']: current_order_id = order_item['order'] current_order_i += 1 self._oi_data[data_type]['orders'].append(current_order_id) self._oi_data[data_type]['orders_items'].append( list(template_items)) self._oi_data[data_type]['orders_items'][current_order_i][ items.index(order_item['item'])] += 1 except ValueError: print 'ORDER: Item not found: ' + str(order_item['item']) return self._oi_data[data_type] def get_items_users(self, data_type='train'): """ Parameters -------------- data_type 'train', 'test' or 'all' Return -------------- {item_id: {user_id: count,...},...} """ if self._is_data_available('_iu_data', data_type): return self._iu_data[data_type] items_users = self.qm.query_items_users(data_type) items = self.qm.query_items_categories('all') users_items = self.qm.query_users_items(data_type) users = [] for user_items in users_items: users.append(user_items['user']) template_users = [0.0] * len(users) self._iu_data[data_type] = { 'items': [], 'users': users, 'items_users': [] } for item in items: has_item_users = False item_users_matrix = list(template_users) self._iu_data[data_type]['items'].append(item['p.oid']) for item_users in items_users: if item['p.oid'] == item_users['product']: has_item_users = True for user in item_users['users']: item_users_matrix[users.index(user)] += 1 item_users_matrix[users.index(user)] = 1 self._iu_data[data_type]['items_users'].append( item_users_matrix) break if has_item_users is False: self._iu_data[data_type]['items_users'].append( item_users_matrix) return self._iu_data[data_type] def get_users_items_cats(self, data_type='train'): """ Parameters -------------- data_type 'train', 'test' or 'all' Return -------------- { 'items': [int,..], 'item_cats': [[int,...],..], 'users': [int,..], 'user_items': [[int,...],..], 'cats': [int,...] } where 'items' are ids of the items and 'item_cats' are weights for each category and item accordingly to 'items' id, they share same list index """ if self._is_data_available('_uic_data', data_type): return self._uic_data[data_type] # query data from db self.cats = self.qm.query_categories() # no connected TFs items = self.qm.query_items_categories('all') users_items = self.qm.query_users_items(data_type) # return dict self._uic_data[data_type] = { 'items': [], 'item_cats': [], 'users': [], 'user_items': [], 'cats': [] } # templates template_item_cats = [0.0] * len(self.cats) template_user_items = [0.0] * len(items) item_w = 1.0 item_w_parent_cost = item_w / 4 # TODO explain this magic num? # categories for cat_oid in self.cats: self._uic_data[data_type]['cats'].append(cat_oid) for item in items: # items without categories are useless if len(item['cats']) < 1: continue # items self._uic_data[data_type]['items'].append(int(item['p.oid'])) # define item categories item_cats_matrix = list(template_item_cats) for cat_defined_oid in item['cats']: cat_w = item_w item_cats_matrix[self._uic_data[data_type]['cats'].index( cat_defined_oid)] = round(cat_w, 2) # parent level is defined by list's index # e.g. cat's parent has index 0, it's parent 1,... # TODO test diff strategies for pondering for cat_parent in self.cats[cat_defined_oid]: # if written w is smaller write the new w, favorite close connections if item_cats_matrix[self._uic_data[data_type]['cats']. index(cat_defined_oid)] < cat_w: item_cats_matrix[self._uic_data[data_type]['cats']. index(cat_defined_oid)] = round( cat_w, 4) cat_w -= item_w_parent_cost self._uic_data[data_type]['item_cats'].append(item_cats_matrix) # create user items matrices for user_items in users_items: self._uic_data[data_type]['users'].append(int(user_items['user'])) user_item_matrix = list(template_user_items) for user_item in user_items['items']: try: item_index = self._uic_data[data_type]['items'].index( int(user_item)) except ValueError: continue user_item_matrix[item_index] += round(item_w, 2) self._uic_data[data_type]['user_items'].append(user_item_matrix) return self._uic_data[data_type] def get_items(self, data_type='train'): """ Parameters -------------- data_type 'train', 'test' or 'all' Return -------------- {[product: int, cats: [int,...],...],} """ if self._is_data_available('_i_data', data_type): return self._i_data[data_type] self._i_data[data_type] = self.qm.query_items_cats(data_type) return self._i_data[data_type] def get_items_total_num(self, data_type='train'): total_num = self.qm.query_items_total_num(data_type) return total_num[0]['items_total_num'] def set_logger(self, logger=None): if logger is not None: self.log = logger def _is_data_available(self, data_name, data_type, expected_py_type=dict): try: if type(getattr(self, data_name)) is not expected_py_type: setattr(self, data_name, expected_py_type()) return False elif getattr(self, data_name)[data_type]: return True except (AttributeError, TypeError, KeyError): setattr(self, data_name, expected_py_type()) except NameError: pass return False
def __init__(self, config_path, train_set_size=0.7): if config_path is not None: self.qm = QueryManager(config_path, train_set_size) self.categories = None self.log = None
class AbstractModel(QtCore.QAbstractTableModel): filters_changed_signal = QtCore.pyqtSignal() error_signal = QtCore.pyqtSignal(str) def __init__(self, table: Table): super(AbstractModel, self).__init__() self.query_manager = QueryManager(table=table) self.original_data = [] self.modified_data = [] self.visible_data = [] # variables needed for pagination self.rows_per_page = 50 self.rows_loaded = 50 # Connect Signals self.query_manager.query_results_signal.connect(self.update_view) def add_row(self, ix: QtCore.QModelIndex) -> None: dummies = { FieldType.bool: True, FieldType.int: 0, FieldType.float: 0.0, FieldType.str: '', FieldType.date: '1900-01-01' } dummy_row = [] # type: List for fld in self.query_manager.table.fields: dummy_row.append(dummies[fld.dtype]) for k, v in self.query_manager.table.foreign_keys.items(): dummy_row[k] = next(fk for fk in self.foreign_keys[k]) dummy_row[ self.query_manager.table.primary_key_index] = uuid.uuid4().int self.visible_data.insert(ix.row(), dummy_row) self.modified_data.insert(0, dummy_row) self.dataChanged.emit(ix, ix) def canFetchMore(self, index=QtCore.QModelIndex()): if len(self.visible_data) > self.rows_loaded: return True return False @property def changes(self) -> Dict[str, set]: if not self.query_manager.table.editable: return # safe guard pk = self.query_manager.table.primary_key_index original = set(map(tuple, self.original_data)) modified = set(map(tuple, self.modified_data)) changed_ids = set(row[pk] for row in original ^ modified) updated = set(row for row in modified if row[pk] in changed_ids and row[pk] in {row[pk] for row in original}) added = (modified - original) - updated deleted = set(row for row in (original - modified) if row[pk] not in {row[pk] for row in updated}) return {'added': added, 'deleted': deleted, 'updated': updated} def fetchMore(self, index=QtCore.QModelIndex()): remainder = len(self.visible_data) - self.rows_loaded rows_to_fetch = min(remainder, self.rows_per_page) self.beginInsertRows(QtCore.QModelIndex(), self.rows_loaded, self.rows_loaded + rows_to_fetch - 1) self.rows_loaded += rows_to_fetch self.endInsertRows() def field_totals(self, col_ix: ColumnIndex) -> list: totals = [] fld = self.query_manager.table.fields[col_ix] rows = self.rowCount() if fld.dtype == FieldType.float: total = sum(val[col_ix] for val in self.visible_data) avg = total / rows if rows > 0 else 0 totals.append('{} Sum \t = {:,.2f}'.format(fld.name, float(total))) totals.append('{} Avg \t = {:,.2f}'.format(fld.name, float(avg))) elif fld.dtype == FieldType.date: minimum = min(val[col_ix] for val in self.visible_data) maximum = max(val[col_ix] for val in self.visible_data) totals.append('{} Min \t = {}'.format(fld.name, minimum or 'Empty')) totals.append('{} Max \t = {}'.format(fld.name, maximum or 'Empty')) else: totals.append('{} Distinct Count \t = {}'.format( fld.name, len(set(val[col_ix] for val in self.visible_data)))) return totals def columnCount(self, parent: QtCore.QModelIndex = None) -> int: return len(self.query_manager.table.fields) def data(self, index: QtCore.QModelIndex, role: int = QtCore.Qt.DisplayRole): alignment = { FieldType.bool: QtCore.Qt.AlignHCenter | QtCore.Qt.AlignVCenter, FieldType.date: QtCore.Qt.AlignHCenter | QtCore.Qt.AlignVCenter, FieldType.int: QtCore.Qt.AlignLeft | QtCore.Qt.AlignVCenter, FieldType.float: QtCore.Qt.AlignRight | QtCore.Qt.AlignVCenter, FieldType.str: QtCore.Qt.AlignLeft | QtCore.Qt.AlignVCenter, } col = index.column() fld = self.query_manager.table.fields[col] val = self.visible_data[index.row()][col] try: if not index.isValid(): return elif role == QtCore.Qt.TextAlignmentRole: return alignment[fld.dtype] elif role == QtCore.Qt.DisplayRole: if col in self.foreign_keys.keys(): return self.foreign_keys[col][val] return fld.format_value(val) except Exception as e: self.error_signal.emit('Error modeling data: {}'.format(e)) def delete_row(self, ix: QtCore.QModelIndex) -> None: row = ix.row() pk = self.visible_data[row][self.query_manager.table.primary_key_index] mod_row = next(i for i, r in enumerate(self.modified_data) if r[self.query_manager.table.primary_key_index] == pk) del self.visible_data[row] del self.modified_data[mod_row] self.dataChanged.emit(ix, ix) def distinct_values(self, col_ix: ColumnIndex) -> List[str]: return SortedSet( str(self.fk_lookup(col=col_ix, val=row[col_ix])) for row in self.visible_data) def filter_equality(self, col_ix: ColumnIndex, val: SqlDataType) -> None: self.visible_data = [ row for row in self.visible_data if row[col_ix] == val ] self.filters_changed_signal.emit() def filter_greater_than(self, col_ix, val) -> None: lkp = partial(self.fk_lookup, col=col_ix) self.visible_data = [ row for row in self.visible_data if lkp(row[col_ix]) >= lkp(val) ] self.sort(col=col_ix, order=QtCore.Qt.AscendingOrder) self.filters_changed_signal.emit() def filter_less_than(self, col_ix, val) -> None: lkp = partial(self.fk_lookup, col=col_ix) self.visible_data = [ row for row in self.visible_data if lkp(row[col_ix]) <= lkp(val) ] self.sort(col=col_ix, order=QtCore.Qt.DescendingOrder) self.filters_changed_signal.emit() def filter_like(self, val: str, col_ix: Optional[ColumnIndex] = None) -> None: self.layoutAboutToBeChanged.emit() lkp = partial(self.fk_lookup, col=col_ix) def normalize(value: str) -> str: return str(value).lower() def is_like(input_val: str, row: Tuple[SqlDataType], col: int) -> bool: if col: if normalize(input_val) in normalize(lkp(row[col])): return True else: if normalize(input_val) in ' '.join([ normalize(self.fk_lookup(val=v, col=c)) for c, v in enumerate(row) ]): return True return False self.visible_data = [ row for row in self.modified_data if is_like(val, row, col_ix) ] self.layoutChanged.emit() self.filters_changed_signal.emit() def filter_set(self, col: int, values: Set[str]) -> None: self.visible_data = [ row for row in self.visible_data if self.fk_lookup(col=col, val=row[col]) in values ] self.filters_changed_signal.emit() def flags(self, ix: QtCore.QModelIndex) -> int: if ix.column() in self.query_manager.editable_fields_indices: return (QtCore.Qt.ItemIsEditable | QtCore.Qt.ItemIsEnabled | QtCore.Qt.ItemIsSelectable) return QtCore.Qt.ItemIsEnabled | QtCore.Qt.ItemIsSelectable def fk_lookup(self, val, col) -> SqlDataType: if col in self.query_manager.table.foreign_keys.keys(): return self.foreign_keys[col][val] return val @property def foreign_keys(self) -> Dict[ColumnIndex, Dict[int, str]]: return { ColumnIndex(k): cfg.foreign_keys(v.dimension) for k, v in self.query_manager.table.foreign_keys.items() } def full_reset(self) -> None: self.layoutAboutToBeChanged.emit() self.original_data = [] self.modified_data = [] self.visible_data = [] self.layoutChanged.emit() self.filters_changed_signal.emit() def headerData(self, col: ColumnIndex, orientation: int, role: QtCore.Qt.DisplayRole) -> List[str]: if orientation == QtCore.Qt.Horizontal and role == QtCore.Qt.DisplayRole: return self.query_manager.headers[col] def pull(self) -> None: self.rows_loaded = self.rows_per_page self.query_manager.pull() def primary_key(self, row: int) -> int: """Return the primary key value of the specified row""" return row[self.query_manager.table.primary_key_index] @QtCore.pyqtSlot(str) def query_errored(self, msg) -> None: self.error_signal.emit(msg) def reset(self) -> None: """reset filters - not pending changes""" self.layoutAboutToBeChanged.emit() self.visible_data = self.modified_data self.filters_changed_signal.emit() self.layoutChanged.emit() def rowCount(self, index: Optional[QtCore.QModelIndex] = None) -> int: if self.visible_data: if len(self.visible_data) <= self.rows_loaded: return len(self.visible_data) return self.rows_loaded return 0 def save(self) -> Optional[Dict[str, int]]: chg = self.changes if chg['added'] or chg['deleted'] or chg['updated']: try: # print('changes:', self.changes) results = self.query_manager.save_changes(chg) def update_id(old_id, new_id): row = next( i for i, row in enumerate(self.modified_data) if row[self.query_manager.table.primary_key_index] == old_id) self.modified_data[row][ self.query_manager.table.primary_key_index] = new_id for m in results['new_rows_id_map']: update_id(m[0], m[1]) self.original_data = deepcopy(self.modified_data) if self.query_manager.table in cfg.dimensions: cfg.pull_foreign_keys(self.query_manager.table.table_name) return results except: raise # else no changes to save, view displays 'no changes' when this function returns None def setData(self, ix: QtCore.QModelIndex, value: SqlDataType, role: int = QtCore.Qt.EditRole) -> bool: try: pk = self.visible_data[ix.row()][ self.query_manager.table.primary_key_index] row = next( i for i, row in enumerate(self.modified_data) if row[self.query_manager.table.primary_key_index] == pk) self.visible_data[ix.row()][ix.column()] = value self.modified_data[row][ix.column()] = value self.dataChanged.emit(ix, ix) return True except: return False def sort(self, col: ColumnIndex, order: int) -> None: """sort table by given column number col""" try: self.layoutAboutToBeChanged.emit() if col in self.foreign_keys.keys(): self.visible_data = sorted( self.visible_data, key=lambda row: self.fk_lookup(row[col], col)) else: self.visible_data = sorted(self.visible_data, key=operator.itemgetter(col)) if order == QtCore.Qt.DescendingOrder: self.visible_data.reverse() self.layoutChanged.emit() except Exception as e: err_msg = "Error sorting data: {}".format(e) self.error_signal.emit(err_msg) def undo(self) -> None: self.layoutAboutToBeChanged.emit() self.modified_data = deepcopy(self.original_data) self.visible_data = deepcopy(self.original_data) self.layoutChanged.emit() @QtCore.pyqtSlot(list) def update_view(self, results) -> None: self.layoutAboutToBeChanged.emit() self.original_data = results self.visible_data = deepcopy(results) self.modified_data = deepcopy(results) self.layoutChanged.emit()
def test_table_with_query_provided(tmpdir): sql = tmpdir.mkdir('test_config').join('test.sql') sql.write("SELECT * FROM test") qm = QueryManager(config={'table': str(sql)}) assert qm.table == "(SELECT * FROM test)"
def test_table_with_tablename_provided(): qm = QueryManager(config={'table': 'test'}) assert qm.table == 'test'
class MessageListener: """ Message Listner API - It will get the message and send the reply string. """ def __init__(self, user): self.query_manager_instance = QueryManager() # Method map corresponding to the input type. It can be scaled in this way. self.method_map = { "hi": "say_hi", "!google": "search_google", "!recent": "get_recent_searches" } self.user = user def send_message(self, message): self.command = message.split(" ", 1) # Get the corresponding method to process the message method = self.method_map.get(self.command[0].lower()) if method: method = getattr(self, method) reply_string = method() self.query_manager_instance.close() return reply_string def say_hi(self): """ :return: reply string """ return "hey" def search_google(self): """ search on google and create/update the search_keyword in db. :return: Top 5 links """ google_query = self.command[1].lower() # search on Google API results = google_search(google_query) if results: # Check if the data already exists and then create/update the data. self.create_update_db(google_query) results = ' \n'.join(results) return "The Top five links for your search are -\n{links}".format( links=results) else: return "Sorry, there are no matching Links found" def get_recent_searches(self): """ Check for recent searches and return in sorted order by last searched. :return: recent searches """ google_query = self.command[1].lower() result = self.query_manager_instance.search_recent_history( user_id=self.user.id, search_term=google_query) if result: search_term = [each['search_term'] for each in result] search_term = ' \n'.join(search_term) return "Your recent search history for this keyword is -\n{result}".format( result=search_term) else: return "Sorry, there are not recent search history corresponding to this keyword" def create_update_db(self, google_query): """ If user_id-search_term already exists update the updated_at else create an entry in the database :param google_query: search keyword """ result = self.query_manager_instance.search_history( user_id=self.user.id, search_term=google_query) if result: updated_at = datetime.now() self.query_manager_instance.update_user_history( user_id=self.user.id, search_term=google_query, updated_at=updated_at) else: self.query_manager_instance.create_user_history( user_id=self.user.id, username=self.user.name, search_term=google_query)