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