def client_handler(conn, addr, db_file): sql = DBhandler.open_db(db_file) print(pt(), 'Новый поток создан') # Вход/регистрация try: sign(conn, sql, db_file) except: print(pt(), 'Клиент принудительно разорвал соединение') sql.close() print(pt(), 'Поток уничтожен') return while True: req = conn.recv(2048) if not req: print(pt(), 'Клиент отключился') break req = pickle.loads(req) if req[0] == 'send': DBhandler.add_message(sql, req[1]) elif req[0] == 'view': res = sqlreq(sql, req[1], req[2]) if not res: resp = False resp = pickle.dumps(resp) conn.send(resp) resp = pickle.dumps(res) conn.send(resp) elif req[0] == 'del': print(pt(), 'Поступил запрос на удаление последнего сообщения от', req[1]) DBhandler.del_last(sql, req[1]) print(pt(), 'Поток уничтожен') sql.close()
def continueImport(self): self.filename = filedialog.askopenfilename( title="Select file", filetypes=(("html files", "*.html"), ("all files", "*.*"))) if not self.filename: return name = str(self.filename).split("/")[-1] if os.path.exists( os.path.join(os.sys.path[0], "data", "dataBase", name + ".db")): db = os.path.join(os.sys.path[0], "data", "dataBase", name + ".db") DBhandler.loadDB(db) else: location = "offline" print("value is", name, location) DBhandler.createDB(name, location) self.updatDBLable() chatParser.resetGlobal() d = importCancel(self) d.top.grab_set() d.top.widgetName = "importCancel" t1 = Thread(target=self.theadImport) t1.start()
def get_my_promotion(shopid): '''得到店铺已有的关键字推广,返回的格式:当一个商品的不同关键字的出价不一样时,在不同行中显示 [{"productid":"id","productname":"name", "price":"price", "keywords":["keyword1","keyword2"]}, {"productid":"id","productname":"name", "keywords":["keyword1","keyword2"], "price":"price"} ''' #首先得到该店铺一条推广的记录:[(productid,productname,keyword,price)] shop_promotions = DBhandler.shop_promotion(shopid) if len(shop_promotions) < 1: return "0" #将数据转换为:{ productid:{price:[keyword1,keyword2]}}和{productid:productname}的格式 shop_promotion_format = dict() for id, name, word, price in shop_promotions: shop_promotion_format.setdefault(id, {}) shop_promotion_format[id].setdefault(price, []) shop_promotion_format[id][price].append(word) #将上述的格式转换为json的格式 js = '[' for id, item1 in shop_promotion_format.items(): for price, keywords in item1.items(): js += '{"productid":"' + id + '","productname":"' + DBhandler.get_productname( id) + '","price":"' + str(price) + '","keywords":[' for keyword in keywords: js += '"' + keyword + '",' js = js[:-1] + ']},' js = js[:-1] + ']' return js
def recommend_on_itemsimilarity(customer_id, T0, N=20, beta=1): '''基于物品相似度的推荐''' #首先从数据库中得到物品的相似度列表 {物品:{相似物品:相似度}} similarity = DBhandler.get_item_similarity() #从数据库中得到该顾客的购买记录{购买的物品:时间,购买的物品:时间} related_commodity = DBhandler.orders_based_on_customerId(customer_id) #related_commodity = {'Snakes on a Plane': 4.5,'Superman Returns': 4.0,'You, Me and Dupree': 1.0} #找到和购买的物品相似度较高的前N个产品 rec_commodities = {} rec_reasons = {} #表示推荐的解释{推荐的物品:{曾经购买过的物品:相似度}} for item, time in related_commodity.items(): for sim_item, sim in similarity[item].items(): if sim_item in related_commodity: continue #如果相似的物品是用户曾经购买过的话,直接跳过 #tmp = sim/(1+beta*abs(T0-time)) tmp = sim / (1 + beta * getIntervalDays(time, T0)) rec_commodities[sim_item] = rec_commodities.get(sim_item, 0) + tmp #提供推荐的解释 rec_reasons.setdefault(sim_item, {}) rec_reasons[sim_item][item] = tmp rec = sorted(rec_commodities.items(), key=lambda x: x[1], reverse=True)[:N] rec_reason = [(item, sorted(reasons.items(), key=lambda x: x[1], reverse=True)[0][0]) for item, reasons in rec_reasons.items()] return rec, rec_reason
def run_sql_string(): data = None print request.json if not request.json: return jsonify({'successful': False, 'message': '请求的数据格式不正确'}) if request.method == 'POST': json_sqlString = request.json['sql_string'] json_flg = request.json['flg'] if json_flg == 'select_one': datas = dbs.get_one_sql(json_sqlString) elif json_flg == 'select_all': data = dbs.get_all_sql(json_sqlString) print 'data====', data datas = json.dumps(data, cls=DateEncoder) elif json_flg == 'update': dbs.update_sql(json_sqlString) return Response(response=datas, mimetype="application/json", status=200) # return jsonify({'success': True, 'message': u'成功响应', 'data': datas}) else: return jsonify({ 'success': False, 'message': u'请使用POST请求', 'data': None })
def to_be_checked_headlines(): '''管理员界面显示所有的头条申请,返回的格式,当没有头条的时候返回“0”: [{"shopname":"name","productid":"productid","productname":"productname", "price":"99.9","date":"2018/05/06"}, {"shopname":"name","productid":"productid","productname":"productname", "price":"99.9","date":"2018/05/06"}]''' #得到头条的基本信息:[(id,price,date)] headlines = DBhandler.get_to_be_checked_headlines() if len(headlines) < 1: return "0" #将以上的信息组装成js的格式 js = '[' for id, price, date in headlines: js += '{"shopname":"' + DBhandler.get_shopname( id ) + '","productid":"' + id + '","productname":"' + DBhandler.get_productname( id) + '",' js += '"price":"' + str(price) + '","date":"' + date + '"},' js = js[:-1] + ']' return js
def sign(conn, db_conn, db_file): while True: sign = conn.recv(1024) sign = pickle.loads(sign) if sign[0] == 'up': # Попытка добавления нового пользователя print(pt(), 'Запрос на регистрацию') r_id = DBhandler.add_user(db_conn, sign[1], sign[2]) if not r_id: # Сообщение об ошибке resp = (False, 'Такой пользователь уже существует!') resp = pickle.dumps(resp) conn.send(resp) print(pt(), 'Запрос на регистрацию был отменён') continue resp = (True, 'Пользователь зарегистрирован') resp = pickle.dumps(resp) conn.send(resp) break elif sign[0] == 'in': # Попытка залогиниться print(pt(), 'Запрос на авторизацию') cur_id = DBhandler.check_user(db_conn, sign[1], sign[2]) if not cur_id: resp = (False, 'Неправильный логин и/или пароль!') resp = pickle.dumps(resp) conn.send(resp) print(pt(), ' Запрос на авторизацию был отменён') continue resp = (True, 'Вход в систему завершён успешно') resp = pickle.dumps(resp) conn.send(resp) print(pt(), 'Пользователь авторизирован') break
def getRecommendation(customer_id, N=10, beta=1): '''从订单表中得到该顾客的购买记录,如果购买的记录为0的话,根据他的性别、年龄来推荐和他相同的人喜爱的商品''' customer_orders = DBhandler.orders_based_on_customerId( customer_id) #{item:date} #如果订单的数量大于0的话,根据基于时间的ItemCF和基于内容的推荐得到初始的推荐列表 rec_reason = None if len(customer_orders) < 1: personal_recommendation = recommend_on_features(customer_id, T0=getCurrentDay(), N=2 * N, beta=beta) else: personal_recommendation, rec_reason = recommend_on_itemsimilarity( customer_id, T0=getCurrentDay(), N=2 * N, beta=beta) ''' personal_recommendation = [('Superman Returns', 1.0107142857142857), ('Snakes on a Plane', 0.9645604395604396), ('The Night Listener', 0.7967032967032968), ('You, Me and Dupree', 0.7130718954248366), ('Lady in the Water', 0.6102941176470589), ('Just My Luck', 0.46637426900584794)]''' #对初始的推荐列表进行过滤:过滤掉不属于商品推广列表中的的商品和用户不喜欢的商品 #排序:根据相似度和推荐的出价来对推荐的结果加权,并据此进行排序,得到最终推荐的商品的ID rec_commodities = DBhandler.get_recommend_commodities() dislike_commodities = DBhandler.get_dislike_commodities( customer_id) #用户不喜欢的商品 ''' rec_commodities = [('Superman Returns', 2), ('The Night Listener', 1), ('You, Me and Dupree', 1.5), ('Just My Luck',0.5)]''' rec_commodities = {item: price for item, price in rec_commodities} #将推荐的商品转换为字符串的形式 recommendation = dict() for item, sim in personal_recommendation: if item not in rec_commodities: continue if item in dislike_commodities: continue recommendation[item] = sim * rec_commodities[item] rec_commodities_id = [ item for item, rank in sorted( recommendation.items(), key=lambda x: x[1], reverse=True) ] #得到商品的推荐理由 recommend_reason = {} if rec_reason is not None: for rec, reason in rec_reason: recommend_reason[rec] = DBhandler.get_productname(reason) #还需要一个方法将商品的ID转换为商品属性信息,并以json的格式返回 return getCommodityAttributes(rec_commodities_id, recommend_reason)
def loadDB(self): dateBase = os.path.join(os.sys.path[0], "data", "dataBase") self.filename = filedialog.askopenfilename( initialdir=dateBase, title="Select file", filetypes=(("db files", "*.db"), ("all files", "*.*"))) if not self.filename: return DBhandler.loadDB(self.filename) self.updatDBLable()
def updatDBLable(self): message = "Game Name: " + DBhandler.getGameName( ) + "| URL: " + DBhandler.getURL() if DBhandler.getURL() == "offline": self.frame.offline.set(1) self.frame.offline_checkBox['state'] = "disabled" else: self.frame.offline.set(0) self.frame.offline_checkBox['state'] = "normal" self.currentDB_string.set(message)
def event_processor(event_queue): """事件处理进程,消费者""" while 1: # 读队列会阻塞进程 event = event_queue.get() time_at = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()) if event['call_back'] != None: if event['event_name'] == 'CHANNEL_CREATE': sql = cc_sql.format(time_at, event['channal_uuid']) logger.info('[sql]:........CHANNEL_CREATE........ %s' % sql) db.update_sql(sql) #更新fs_host 线路数+1 host_sql = chc_update_line.format(event['host_id']) logger.info( '[sql]:........CHANNEL_CREATE.. table...host line+1...... %s' % host_sql) db.update_sql(host_sql) elif event['event_name'] == 'CHANNEL_ANSWER': sql = ca_sql.format(time_at, event['channal_uuid']) logger.info('[sql]:.........CHANNEL_ANSWER...... %s' % sql) db.run_sql(sql) elif event['event_name'] == 'CHANNEL_HANGUP_COMPLETE': call_back = event['call_back'] host_id = event['host_id'] user_id = event['user_id'] call_id = event['call_id'] logger.info( '[ call_back----> %s ,user_id---->%s, call_id---->%s]' % (call_back, user_id, call_id)) logger.info('[ is_test----> %s ]' % event['is_test']) if call_back != None and call_back == 'true': try: sql2 = chc_host_sql.format(int(host_id)) logger.info('[ sql2 :----> fs_host line_use - 1 ]%s ' % sql2) db.update_sql(sql2) sql3 = chc_sql.format('finish', time_at, event['Channel-Call-State'], event['Hangup-Cause'], event['channal_uuid']) logger.info( '[sql3 :..........CHANNEL_HANGUP_COMPLETE....... ]%s' % sql3) db.update_sql(sql3) # 回调函数 callback_aliyun(event['channal_uuid'], user_id, call_id) except Exception as e: logger.info('hangup_complete execute sql error %s ' % e) else: print '[ ***** curent event is not platform ***** ]'
def trainTimeBasedItemCF(alpha=1, sim_counts=0.8): '''训练基于时间的ItemCF模型:主要是得到物品的相似度表,然后更新到数据库中''' #计算物品的相似度 ##得到用户和物品的数据:{用户:{物品:日期}} prefs = DBhandler.all_customer_orders() ##计算相似度 similarity = TimeBasedItemSimilarity(prefs, alpha=alpha, sim_counts=sim_counts) #将物品的相似度更新到数据库中 DBhandler.update_item_similarity(similarity)
def sqlreq(conn, flag, args): print(pt(), 'запрос на просмотр сообщений от пользователя ', args[0]) msgs = DBhandler.pull_messages(conn, flag, args[0], args[1], args[2]) if not msgs: return False print(pt(), 'Список сообщений был отправлен пользователю ', args[0]) return msgs
def analyzeByTagAndNameByDateRange(name, tagNameList, dateA, dateB, offline): if not offline: chatParser.addScrapParseToDB() analyzeDB( DBhandler.getMessagesByTagAndNameByDateRange(tagNameList, name, dateA, dateB)) return returnStats()
def getCommodityAttributes(commodity_ids, recommend_reason): '''根据给定的商品列表得到商品的属性信息,并以json的格式返回 commodity_ids : 列表,表示推荐的商品的ID ''' #得到商品的属性:[ (商品id,名称,图片,店铺,价格) ] attributes = DBhandler.get_recommend_commodities_attributes(commodity_ids) ''' attributes = [(1,"苹果","/web/image/apple.png","涛哥的小店",1.5), (2,"梨","/web/image/peach.png","涛哥的小店",0.5), (3,"香蕉","/web/image/banana.png","涛哥的小店",1)]''' #将商品信息转换为json格式 ''' [{"id":commodity_id, "name":name, "pic":picture_url, "price":price,"reason":"reason"}, {"id":commodity_id, "name":name, "pic":picture_url, "price":price,"reason":"reason"} ] ''' has_reason = 0 if len(recommend_reason) > 0: has_reason = 1 js = "[" for i in range(len(attributes)): item = attributes[i] js += '{"id":"' + str( item[0]) + '","name":"' + item[1] + '","pic":"' + item[ 2] + '","shop":"' + item[3] + '","price":"' + str( item[4]) + '"' if (has_reason): js += ',"reason":"' + recommend_reason[item[0]] + '"' js += '},' js = js[:-1] + ']' return js
def recommend_on_features(customer_id, T0, N=20, beta=1): '''根据他的性别、年龄来推荐和他相同的人喜爱的商品''' #从数据库中找到和该用户特征相似的用户,得到相似用户的:购买商品、购买时间 { 用户:{购买物品:购买日期}} prefs = DBhandler.orders_based_on_customer_features(customer_id) #计算相似用户的商品的:流行度(基于时间的流行度) return popularItem(prefs, T0=T0, N=N, beta=beta)
def can_use(self): if not self.conn.connected: return 'disconnect' num = db.get_one_sql(get_free_line_sql.format(self.host_id)) if num <= 0: return 'full' return 'free'
def call(self, item): task_id = item[3] call_id = item[0] uuid = str(item[2]) number = str(item[1]) is_success = self.fs_api(uuid=uuid, number=number, task_id=task_id, call_id=call_id, host_id=self.host_id, gateway=self.gateway) logger.info('[ call is_success = %s ]' % is_success) if is_success: db.update_sql(chc_host_sql.format(self.host_id)) else: logger.info('self.bgapi.....retrun %s' % is_success)
def analyzeByTagToday(tagName, offline): if not offline: chatParser.addScrapParseToDB() startToday = datetime(datetime.today().year, datetime.today().month, datetime.today().day) analyzeDB(DBhandler.getMessagesWithTagsBYDate(tagName, startToday)) return returnStats()
def getPopularItem(N=10, beta=1): '''返回在所有顾客中最热门的商品''' prefs = DBhandler.all_customer_orders() #计算相似用户的商品的:流行度(基于时间的流行度) popular_item = popularItem(prefs, T0=getCurrentDay(), N=N, beta=beta) items = [item for item, popularity in popular_item] return getCommodityAttributes(items, {})
def count(self,options): query = DBhandler.getQuery(options) condition = query["condition"] where = Encoder.enQuery(self,condition) return self.db(where).count()
def analyzeByTagAndNameToday(name, tagNameList, offline): if not offline: chatParser.addScrapParseToDB() startToday = datetime(datetime.today().year, datetime.today().month, datetime.today().day) analyzeDB( DBhandler.getMessagesByTagAndNameByDate(tagNameList, name, startToday)) return returnStats()
def analyzeToday(offline): if not offline: chatParser.addScrapParseToDB() startToday = datetime(datetime.today().year, datetime.today().month, datetime.today().day) analyzeDB(DBhandler.getRollresultDateTime(startToday)) return returnStats()
def get_current_headlines(): '''得到当前的头条,格式和上面的完全一样''' headlines = DBhandler.current_headlines() if len(headlines) < 1: return "0" #将以上的信息组装成js的格式 js = '[' for id, price, date in headlines: js += '{"shopname":"' + DBhandler.get_shopname( id ) + '","productid":"' + id + '","productname":"' + DBhandler.get_productname( id) + '",' js += '"price":"' + str(price) + '","date":"' + date + '"},' js = js[:-1] + ']' return js
def fs_api(self, uuid, number, task_id, flow_id, call_id, host_id, gateway): if not gateway: gateway = 'sofia/gateway/gw1' if self.conn.connected: channel_vars = 'ignore_early_media=true,absolute_codec_string=g729,' \ 'origination_uuid=%s,task_id=%s,flow_id=%s,call_id=%s,host_id=%s,is_test=%s' % \ (uuid, task_id, flow_id,call_id, host_id, '1') command = "originate {%s}%s/%s &python(callappv2.Bot)" % ( channel_vars, gateway, number) logger.error('Invoke fs api:\n%s' % command) self.conn.bgapi(command) try: time_at = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()) db.update_sql(update_call_sql.format(time_at, uuid)) except Exception as e: print 'eror', e.message return True else: return False
def find(self,options): query = DBhandler.getQuery(options) condition = query['condition'] order = query['order'] limit = query['limit'] where = Encoder.enQuery(self,condition) rows = self.db(where).select(orderby=eval(order),limitby=limit) return rows
def delete_keywords(js): '''删除关键字推广,js的格式: {"productid":"p201804281620","keywords":["新款"]}''' #将上述格式转换为[(productid,keyword)]的格式 promotion = json.loads(js) promotion_info_format = list() productid = promotion["productid"] for keyword in promotion["keywords"]: promotion_info_format.append((productid, keyword)) #返回删除的结果 return DBhandler.delete_keywords(promotion_info_format)
def run_sql_string(): data = None if not request.json: return jsonify({'successful': False, 'message': '请求的数据格式不正确'}) if request.method == 'POST': json_sqlString = request.json['sql_string'] json_flg = request.json['flg'] if json_flg == 'select_one': data = dbs.get_one_sql(json_sqlString) elif json_flg == 'select_all': data = dbs.get_all_sql(json_sqlString) # print 'data====',data elif json_flg == 'update': dbs.update_sql(json_sqlString) return jsonify({'success': True, 'message': u'成功响应', 'data': data}) else: return jsonify({ 'success': False, 'message': u'请使用POST请求', 'data': None })
def addToDb(chatContent): global current, size, cancel, status static.timeStamp = "" x = 1 status = "Parsing data" activeTags.clear() activeTags.extend(DBhandler.getActiveTags()) for c in chatContent: if (not cancel): current = x #test = c.attrs.get("data-messageid") #print(test) #print(activeTags) s = c["class"] if "rollresult" in s: addRollresult(c) elif "general" in s: addGeneral(c) pass elif "emote" in s: addEmote(c) pass else: print("unknown message type: ", c) x += 1 else: print("chatPar has been canceled") return addActive() status = "Adding messages data to DB" print(status) DBhandler.addManyToMessageTable(allMessage) status = "Adding user data to DB" print(status) DBhandler.addManyToUserIDTable(allUserID) status = "Adding Formula and Dice data to DB" print(status) DBhandler.addManyFormulaAndDice(allFormulaDice) status = "Adding tags data to DB" print(status) DBhandler.addManyToTag(allTags) status = "DONE"
def add_promotion_keywords(js): '''为商品添加关键字,js的格式: {"productid":"p201804281620", "keywords":["新款","潮流"], "price":"0.45"}''' #首先要将数据转换为[(productid,keyword,price)]的格式 promotions = json.loads(js) promotions_info_format = list() productid = promotions["productid"] price = promotions["price"] for keyword in promotions["keywords"]: promotions_info_format.append((productid, keyword, price)) #修改或插入推荐的信息: state = DBhandler.add_alter_promotion(promotions_info_format) while state == "0": #当删除不成功时,连续删除 #如果插入失败的话需要取消之前插入的索引 state = DBhandler.cancel_add_alter_keywords(promotions_info_format) return state
def event_processor(event_queue): """事件处理进程,消费者""" while 1: # 读队列会阻塞进程 event = event_queue.get() time_at = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()) if event['event_name'] == 'CHANNEL_CREATE': sql = cc_sql.format(time_at, event['channal_uuid']) logger.info('[sql]:........CHANNEL_CREATE........ %s' % sql) db.update_sql(sql) #更新fs_host 线路数+1 host_sql = chc_update_line.format(event['host_id']) logger.info( '[sql]:........CHANNEL_CREATE.. table...host line+1...... %s' % sql) db.update_sql(host_sql) elif event['event_name'] == 'CHANNEL_ANSWER': sql = ca_sql.format(time_at, event['channal_uuid']) logger.info('[sql]:.........CHANNEL_ANSWER...... %s' % sql) db.run_sql(sql) elif event['event_name'] == 'CHANNEL_HANGUP_COMPLETE': sql = chc_sql.format('finish', time_at, event['Channel-Call-State'], event['Hangup-Cause'], event['channal_uuid']) logger.info('[sql]:..........CHANNEL_HANGUP_COMPLETE....... %s' % sql) db.run_sql(sql) task_id = event['task_id'] host_id = event['host_id'] logger.info('task_id....%s' % task_id) if task_id != None: logger.info('-------is_test------%s ' % event['is_test']) # if event['is_test'] and event['is_test'] != '0': sql = chc_host_sql.format(int(host_id)) logger.info('[execute sql]...%s' % sql) db.run_sql(sql) HttpClientPost(event['channal_uuid'])
def get_keywords(): '''得到推广的关键字,返回的格式: [{"keywordid":"id","keyword":"keyword"}, {"keywordid":"id","keyword":"keyword"} ]''' keywords = DBhandler.get_keywords() js = '[' for id, keyword in keywords: js += '{"keywordid":"' + id + '","keyword":"' + keyword + '"},' js = js[:-1] + ']' return js
def find(self,options): simplequery = DBhandler.simple("materiales","4","id",">") complexquery = DBhandler.complex("materiales","4","nombre",">",{'id':"2",'nombre':"'Cl'"}) #Default values print "\n\n" print "*****************************************************************************************************" print ' Printing queries for testing purposes' print "*****************************************************************************************************" print '\n\nSimple query: \n' print 'query: ' print simplequery print 'response: ' print self.db.executesql(simplequery) print '\nComplex query: \n' print 'query: ' print complexquery print 'response: ' print self.db.executesql(complexquery) print "\n" return self.db().select(self.table.ALL)