def register(userid, sp): print userid print "sp: " + sp sp_id = int(sp) # decrypt user ID with the key for this SP sp_userid = get_encrypt_obj(sp_id).decryptAES(userid) error = None if request.method == 'POST': # get username & password hashes from html form username = request.form['username'] password = hashlib.sha1(request.form['password']).hexdigest() confirm_pass = hashlib.sha1(request.form['confirmPassword']).hexdigest() if confirm_pass == password: # add this user to database data_base_manager = DataBaseManager(DB_NAME) user_id = data_base_manager.last_id("Users") + 1 data_base_manager.close_connection() add_user(user_id, username, password, sp_id, sp_userid) # redirect to SP to continue registration sp_userid = get_encrypt_obj(sp_id).encryptAES(sp_userid) addr = get_sp_url(sp_id) path = addr + '/registeredas/' + sp_userid server = "PhoneBook Server" return render_template('RedirectPage.html', path=path, server=server, addr=addr) #return redirect() else: # if password confirmation was incorrect error = 'Invalid Credentials. Please try again.' return render_template('Register.html', error=error)
def login(sp): error = None if request.method == 'POST': try: # get username & password hash from html form username = request.form['username'] hashed_pass = hashlib.sha1(request.form['password']).hexdigest() sp_id = int(sp) # find user in database query = "SELECT spUserID FROM Users WHERE username='******' AND passw='%s' AND spID=%d" %\ (username, hashed_pass, sp_id) data_base_manager = DataBaseManager(DB_NAME) sp_user_id = data_base_manager.exec_query(query)[0] data_base_manager.close_connection() # redirect to user's profile page is SP sp_user_id = get_encrypt_obj(sp_id).encryptAES(str(sp_user_id)) addr=get_sp_url(sp_id) sp_url = '%s/user/%s' % (addr, str(sp_user_id)) server = "PhoneBook Server" return render_template('RedirectPage.html', path=sp_url, server=server, addr=addr) #return redirect(sp_url) except Exception, exc: # if user wasn't found error = 'Invalid Credentials. Please try again.' print exc
def __init__(self, hoca_id, hoca_KullaniciAdi, hoca_sifre, GenelKod, model_name, distance_metric, parent=None): super(MainWindow, self).__init__(parent=parent) self.ui = Ui_MainWindow() self.ui.setupUi(self) self.Teacher = DataBaseManager.Teacher() self.Student = DataBaseManager.Student() self.Lesson = DataBaseManager.Lessons() self.SinifListesi = [] self.model_name = model_name self.distance_metric = distance_metric self.hoca_id = hoca_id self.sifre = hoca_sifre self.kadi = hoca_KullaniciAdi self.dersGenelKod = GenelKod self.functionsSettings() self.UI_Ayar() self.initSlots() self.show()
def __init__(self, ogrenci_no): super(OgrenciDuzenleWidget, self).__init__() self.ui = Ui_OgrenciDuzenleForm() self.ui.setupUi(self) self.Student = DataBaseManager.Student() self.Teacher = DataBaseManager.Teacher() self.Lesson = DataBaseManager.Lessons() self.ogrenci_no = ogrenci_no self.result = None self.UI_Settings() self.initSlots() self.show()
def GetProductCarrierList(targetID=0, getTitle=None, targetProduct=None): if getTitle == None: getTitle = False if targetProduct == None: targetProduct = '' resultList = {} if targetID == 0: if targetProduct == '': sqlResult = DataBaseManager.DoSQL(""" SELECT Carrier_ID FROM product_carrier """) else: sqlResult = DataBaseManager.DoSQL(""" SELECT Carrier_ID FROM product_carrier WHERE Carrier_Name = '""" + targetProduct + """' """) carrierList = [] for result in sqlResult: carrierList.extend(result) if getTitle: for carrier in carrierList: sqlResult = DataBaseManager.DoSQL(""" SELECT Carrier_Name FROM prodcut_carrier WHERE Carrier_ID = """ + str(carrier) + """ """) newDic = {carrier: sqlResult[0][0]} resultList.update(newDic) else: for carrier in carrierList: sqlResult = DataBaseManager.DoSQL(""" SELECT Carrier_Word_ID, Carrier_Alias FROM carrier_dic WHERE Carrier_ID = """ + str(carrier) + """ """) aliasList = dict(sqlResult) newDic = {carrier: aliasList} resultList.update(newDic) else: sqlResult = DataBaseManager.DoSQL(""" SELECT Carrier_ID, Carrier_Alias FROM carrier_dic WHERE Carrier_Word_ID = """ + str(targetID) + """ """) newDic = {sqlResult[0][0]: {targetID: sqlResult[0][1]}} resultList.update(newDic) return resultList
def __init__(self, ogretmen_kadi, ogretmen_sifre, parent=None): super(HocaEkraniWidget, self).__init__(parent=parent) self.id = None self.ui = Ui_HocaEkraniMainWindow() self.ui.setupUi(self) self.Teacher = DataBaseManager.Teacher() self.Lesson = DataBaseManager.Lessons() self._translate = QtCore.QCoreApplication.translate self.kadi = ogretmen_kadi self.sifre = ogretmen_sifre self.initSlots() self.UI() self.show()
def get_encrypt_obj(sid): """ Gets encryption key from SPs table in db. Creates an instance of Encryption using that key. :param sid: SP server ID :return: AES encryption object """ query = "SELECT key FROM SPs WHERE SPID=%d" % sid data_base_manager = DataBaseManager(DB_NAME) key = data_base_manager.exec_query(query)[0] data_base_manager.close_connection() key = Encryption(MASTER).decryptAES(key) return Encryption(key)
def __init__(self, dersGenelKod): super(OgrenciListesiWidget, self).__init__() self.ui = Ui_OgrenciListesiForm() self.ui.setupUi(self) self.Lesson = DataBaseManager.Lessons() self.Student = DataBaseManager.Student() self.dersGenelKod = dersGenelKod self.ui.pushButton.clicked.connect(self.select) self.Table() self.ui.sinif_listesi.setSelectionBehavior(QTableView.SelectRows) self.liste = [] self.show()
def __init__(self, ogretmen_kadi, ogretmen_sifre): super().__init__() print(ogretmen_kadi) print(ogretmen_sifre) self.ui = Ui_DersEkleForm() self.ui.setupUi(self) self.kadi = ogretmen_kadi self.sifre = ogretmen_sifre self.Teacher = DataBaseManager.Teacher() self.Lesson = DataBaseManager.Lessons() self.UI_Settings() self.getIformation() self.ui.btn_DersEkle.clicked.connect(self.HocayaDersEkle) self.show()
def GetProductDic(refresh=False): global productDic if refresh == True or len(productDic) <= 0: carrierAliasList = [] productList = DataBaseManager.DoSQL(""" SELECT product_dic.Product_ID, product_dic.Carrier_ID, product_dic.Product_Name, product_dic.Category_ID, product_discription.Discription, product_discription.Type, product_dic.Count, product_dic.Relation_Table_Name FROM product_dic LEFT JOIN product_discription ON product_dic.Product_ID = product_discription.Product_ID """) currentProductID = -1 currentCarrierID = -1 for data in productList: if data[1] != currentCarrierID: currentCarrierID = data[1] carrierAliasList = [] sqlResult = DataBaseManager.DoSQL(""" SELECT Carrier_Alias FROM carrier_dic WHERE Carrier_ID = """ + str(currentCarrierID) + """ """) for result in sqlResult: carrierAliasList.append(result[0]) if data[0] != currentProductID: currentProductID = data[0] newProduct = { 'productID': data[0], 'categoryID': data[3], 'carrier': carrierAliasList, 'mainDiscription': [], 'subDiscription': [], 'count': data[6], 'relationTable': data[7] } newItem = {data[2]: newProduct} productDic.update(newItem) if data[5] == 1: productDic.get(data[2]).get('mainDiscription').append(data[4]) elif data[5] == 2: productDic.get(data[2]).get('subDiscription').append(data[4])
def insert_init_values(): data_base_manager = DataBaseManager(DB_NAME) fields = [(1, 'elle', hashlib.sha1('EL').hexdigest(), 1, 101), (2, 'David', hashlib.sha1('2511').hexdigest(), 1, 102), (3, 'dana123', hashlib.sha1('12345').hexdigest(), 1, 103), (4, 'dana123', hashlib.sha1('12345').hexdigest(), 2, 101), (5, 'elle', hashlib.sha1('EL').hexdigest(), 2, 102)] data_base_manager.insert("Users", fields) # print db tables data_base_manager.print_table("Users") data_base_manager.print_table("SPs") data_base_manager.close_connection()
def get_sp_url(sp_id=1): """ Gets RedirectPath data from SPs table in db, for a specific SP :param sp_id: SP to find url of :return: path of this SP """ query = "SELECT redirectPath FROM SPs WHERE SPID=%d" % sp_id try: data_base_manager = DataBaseManager(DB_NAME) path = data_base_manager.exec_query(query)[0] data_base_manager.close_connection() return path except Exception, e: print 'Unable to execute query: ' + query print e
def CreateMenu(): try: DataBaseManager.CreateDataBase() messagebox.showinfo(title="Data base created", message="The data base has been created") except: messagebox.showwarning(title="Create Database", message="We have a data base")
def __init__(self, hoca_KullaniciAdi, sifre): super(OgretmenDuzenleWidget, self).__init__() self.ui = Ui_OgretmenDuzenleForm() self.ui.setupUi(self) self.Teacher = DataBaseManager.Teacher() self.hoca_kadi = hoca_KullaniciAdi self.hoca_sifre = sifre self.getTeacherInformations() self.ui.btn_update.clicked.connect(self.UpdateInformations) self.ui.btn_resimCek.clicked.connect(self.YeniResimCek) self.show()
def GetSimilarWordDic(): global similarWordDic sqlResult = DataBaseManager.DoSQL(""" SELECT Sub_Word, Super_Word FROM similar_word_dic """) if len(sqlResult) > 0: similarWordDic = dict(sqlResult) else: similarWordDic = {}
def CreateButton(): global nameTextVar global passwordTextVar global lastNameTextVar global directionTextVar global commentText DataBaseManager.InsertTuple(nameTextVar.get(), passwordTextVar.get(), lastNameTextVar.get(), directionTextVar.get(), commentText.get(1.0, "end")) messagebox.showinfo(title="Tuple Inserted", message="The tuple has been inserted")
def GetProductCategoryList(): resultList = {} sqlResult = DataBaseManager.DoSQL(""" SELECT Category_ID, Category FROM category_dic """) resultList = dict(sqlResult) return resultList
def AppendWordDicQuery(wordDic): global insertQuery global updateQuery stackUnit = DataBaseManager.maximumQueryStactUnit WordLastID = DataBaseManager.DoSQL(""" SELECT `AUTO_INCREMENT` FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'db_capstone' AND TABLE_NAME = 'similar_word_relation'""")[0][0] WordList = [] index = 0 while True: WordList.extend( DataBaseManager.DoSQL(""" SELECT Normal_Word, Similar_Relation_ID FROM similar_word_relation WHERE Similar_Relation_ID > """ + str(index) + """ AND Similar_Relation_ID <= """ + str(index + stackUnit))) index += stackUnit if index > WordLastID: break existWord = dict(WordList) for word, count in wordDic.items(): try: RelationID = existWord[word] except: insertQuery.append(""" INSERT INTO similar_word_relation (Normal_Word, Target_Word ,Word_Count) VALUES ('""" + word + """', '""" + word + """', """ + str(count) + """) """) else: updateQuery.append(""" UPDATE similar_word_relation SET Word_Count = Word_Count + """ + str(count) + """ WHERE Similar_Relation_ID = """ + str(RelationID) + """ """)
def __init__(self): super(OgrenciAraWidget, self).__init__() self.ui = Ui_OgrenciAraWindow() self.ui.setupUi(self) self.ui.txt_ogrNo.setValidator(QIntValidator()) self.Student = DataBaseManager.Student() self.ui.txt_ogrNo.setText("185542008") self.ui.text_Bolum.setEnabled(False) self.ui.btn_ara.clicked.connect(self.getStudentInformations) self.kontrol = False self.ui.btn_duzenle.clicked.connect(self.OgrenciDuzenleWidget) self.show()
def create_db(): # create Users table data_base_manager = DataBaseManager(DB_NAME) fields = ["ID integer primary key autoincrement", "username text not null", "passw text not null", "spID integer not null", "spUserID integer not null"] data_base_manager.create_table("Users", fields) # create SPs table fields = ["SPID integer primary key autoincrement", "details text not null", "redirectPath text not null", "key text not null"] data_base_manager.create_table("SPs", fields) data_base_manager.close_connection()
def Inserter(self, TaskQueue): self.LimitQueue.put(1) if not self.InsertLock.acquire(): self.InsertLock.wait() DBHandler = DataBaseManager.DBHandler("UNIDATABASE") while not TaskQueue.empty(): Task = TaskQueue.get() DBHandler.Insert(Task[0], Task[1], Task[2]) DBHandler.Commit() self.Did += 1 del DBHandler self.InsertThreadNum -= 1 self.InsertLock.notify() self.InsertLock.release()
def UpdateButton(): global idTextVar global nameTextVar global passwordTextVar global lastNameTextVar global directionTextVar global commentText DataBaseManager.UpdateTuple(idTextVar.get(), nameTextVar.get(), passwordTextVar.get(), lastNameTextVar.get(), directionTextVar.get(), commentText.get(1.0, "end")) messagebox.showinfo( title="Tuple Update", message="The tuple with id: {}, has been updated".format( idTextVar.get()))
def __init__(self): super(OgrenciEkleWindow, self).__init__() self.ui = Ui_OgrenciEkleWindow() self.ui.setupUi(self) self.setFixedSize(542, 873) self.Student = DataBaseManager.Student() self.ui.btn_kayit.clicked.connect(self.getUserInformation) self.UI_Settings() self.ui.actionStart_Camera.triggered.connect(self.startCam) self.ui.actionStop_Camera.triggered.connect(self.stopCam) self.ui.btn_resimCek.clicked.connect(self.ResimKaydet) self.sayac = 0 self.capture = None self.ResimKontrol = False self.sayacDosya = False self.show()
def add_user(id, username, passw, sp_id, sp_user_id): """ Adds A row in UserProfiles table for a new user with this info: :param id: user ID :param username: chosen username :param passw: chosen password :param sp_id: ID of SP server to which the user is registrating :param sp_user_id: ID o this user in SP server :return: None """ fields = [(id, username, passw, sp_id, sp_user_id)] data_base_manager = DataBaseManager(DB_NAME) data_base_manager.insert("Users", fields) data_base_manager.print_table("Users") data_base_manager.close_connection()
def sps_comm(): """ Server side of socket comm. Inserts newly connected SPs to DB. :return: None """ public_key, new_key = generate_RSA_key() while True: sp_sock, sp_addr = server_sock.accept() sp_thread = ThreadWithReturnValue(target=register_sp, args=(sp_sock, public_key, new_key)) sp_thread.start() fields = sp_thread.join() data_base_manager = DataBaseManager("MainDB.db") data_base_manager.insert("SPs", fields) data_base_manager.print_table("SPs") data_base_manager.close_connection()
def ReadButton(): global idTextVar global nameTextVar global passwordTextVar global lastNameTextVar global directionTextVar global commentText try: ret = DataBaseManager.ReadTuple(int(idTextVar.get())) nameTextVar.set(ret[0][1]) passwordTextVar.set(ret[0][2]) lastNameTextVar.set(ret[0][3]) directionTextVar.set(ret[0][4]) commentText.delete(1.0, "end") commentText.insert(1.0, ret[0][5]) except: messagebox.showerror(title="Index_Error", message="Id its not found") DeleteMenu()
def UpdateSimilarityDatabase(target=None, title=None, outPut=None): global embedding_model if title == None: title = 'Append Similar word relation' else: title += '\nAppend Similar word relation' if outPut == None: outPut = '' else: outPut = 'Process data of ' + outPut + '\n' main.ShowTitle(title, outPut + 'Getting exist data') if target == None: relationDict = {} relationList = DataBaseManager.DoSQL(""" SELECT Normal_Word, Target_Word, Similar_Relation_ID FROM similar_word_relation """) for relation in relationList: if relation[0] == relation[1]: newWordDict = {relation[0]: {}} relationDict.update(newWordDict) else: newRelation = {relation[1]: relation[2]} relationDict.get(relation[0]).update(newRelation) else: sqlResult = DataBaseManager.DoSQL(""" SELECT Category_ID, Relation_Table_Name FROM product_dic WHERE Product_ID = """ + str(target) + """ """) productInfo = sqlResult[0] featureList = [productInfo[1]] sqlResult = DataBaseManager.DoSQL(""" SELECT Feature_Name FROM feature_dic WHERE Category_ID = """ + str(productInfo[0]) + """ """) for result in sqlResult: featureList.append(result[0]) if target == None: main.ShowTitle(title, outPut + 'Getting latest calculated similar data') wordList = [] for word in embedding_model.wv.index2word: wordList.append(word) wordDict = {} removeList = [] index = 0 updateTime = 0 while True: currentTime = int( str(datetime.datetime.now().strftime('%Y%m%d%H%M%S'))) if updateTime < currentTime: updateTime = currentTime main.ShowTitle( title, outPut + 'Removing not verb and adjective (' + str(index) + '/' + str(len(wordList)) + ' removed: ' + str(len(removeList)) + ')') word = wordList[index] targetTag = ['VA', 'VV'] if len(NLP.DoNLP(word, targetTag)) <= 0: removeList.append(index) index += 1 if index >= len(wordList): break removeList.sort(reverse=True) for index in removeList: wordList.pop(index) wordDict = dict.fromkeys(wordList) insertQuery = [] updateQuery = [] index = 0 if target == None: updateTime = 0 for word in wordDict.keys(): result = embedding_model.most_similar( positive=[word], topn=len(embedding_model.wv.index2word) - 1) currentTime = int( str(datetime.datetime.now().strftime('%Y%m%d%H%M%S'))) if updateTime < currentTime: updateTime = currentTime main.ShowTitle( title, outPut + 'Append query (' + str(index) + '/' + str(len(wordList)) + ')') for similar in result: try: wordDict[similar[0]] except: continue else: existData = relationDict.get(word) try: relationID = existData[similar[0]] except: newQuery = """ INSERT INTO similar_word_relation (Normal_Word, Target_Word, Similar_Value) VALUES ('""" + word + """', '""" + similar[ 0] + """', """ + str(similar[1]) + """)""" insertQuery.append(newQuery) else: newQuery = """ UPDATE similar_word_relation SET Similar_Value = """ + str(similar[1]) + """ WHERE Similar_Relation_ID = """ + str(relationID) updateQuery.append(newQuery) index += 1 else: SentiWordBinder = BindSentiWords.BindSentiWords() updateTime = 0 for feature in featureList: result = embedding_model.most_similar( positive=[feature], topn=len(embedding_model.wv.index2word) - 1) currentTime = int( str(datetime.datetime.now().strftime('%Y%m%d%H%M%S'))) if updateTime < currentTime: updateTime = currentTime main.ShowTitle( title, outPut + 'Append query (' + str(index) + '/' + str(len(featureList)) + ')') for similar in result: if feature != similar[0] and productInfo[1] != similar[0]: # if feature != productInfo[1]: # try: # wordDict[similar[0]] # except: # updateQuery.append(""" # UPDATE `""" + productInfo[1] + """` # SET `""" + feature + """` = null # WHERE Word = '""" + similar[0] + """' # """) # else: # updateQuery.append(""" # UPDATE `""" + productInfo[1] + """` # SET `""" + feature + """` = """ + str(similar[1]) + """ # WHERE Word = '""" + similar[0] + """' # """) # else: updateQuery.append(""" UPDATE `""" + productInfo[1] + """` SET `""" + feature + """` = """ + str(similar[1]) + """ WHERE Word = '""" + similar[0] + """' """) sentiValueDict = SentiWordBinder.BindSentiWords( [similar[0]]) if sentiValueDict[similar[0]] != 'None': updateQuery.append(""" UPDATE `""" + productInfo[1] + """` SET Sentiment_Value = """ + sentiValueDict[similar[0]] + """ WHERE Word = '""" + similar[0] + """' """) updateQuery.append(""" UPDATE `""" + productInfo[1] + """` SET `""" + feature + """` = null WHERE Word_Count <= """ + str(5) + """ """) index += 1 if target == None: db = 'db_capstone' else: db = 'db_capstone_similarity' DataBaseManager.DoManyQuery(insertQuery, db=db, title=title, outPut=outPut, queryType='INSERT') DataBaseManager.DoManyQuery(updateQuery, db=db, title=title, outPut=outPut, queryType='UPDATE')
def UpdateSimilarWordDictionary(title=None, outPut=None): if title == None: title = 'Update Similar word Dictionary' else: title += '\nUpdate Similar word Dictionary' if outPut == None: outPut = '' else: outPut = 'Process data of ' + outPut + '\n' main.ShowTitle(title, 'Getting similarity data') sqlResult = DataBaseManager.DoSQL(""" SELECT Normal_Word, Target_Word, Word_Count FROM similar_word_relation WHERE Similar_Value > 0.95 ORDER BY Similar_Value DESC """) index = 0 wordDict = {} updateTime = 0 for result in sqlResult: currentTime = int(str( datetime.datetime.now().strftime('%Y%m%d%H%M%S'))) if updateTime < currentTime: updateTime = currentTime main.ShowTitle( title, outPut + 'Building relation dictionary (' + str(index) + '/' + str(len(sqlResult)) + ')') newRelation = {result[1]: result[2]} try: existRelation = wordDict[result[0]] except: wordRelation = {result[0]: newRelation} else: existRelation.update(newRelation) wordRelation = {result[0]: existRelation} wordDict.update(wordRelation) index += 1 index = 0 removeIndex = 0 initialLength = len(wordDict) removeList = [] updateTime = 0 for key, relation in wordDict.items(): currentTime = int(str( datetime.datetime.now().strftime('%Y%m%d%H%M%S'))) if updateTime < currentTime: updateTime = currentTime main.ShowTitle( title, 'Removing unnecessary word (' + str(index) + '/' + str(initialLength) + ' removed: ' + str(removeIndex) + ')') if len(relation) <= 1: removeList.append(key) removeIndex += 1 index += 1 index = 0 updateTime = 0 for key in removeList: currentTime = int(str( datetime.datetime.now().strftime('%Y%m%d%H%M%S'))) if updateTime < currentTime: updateTime = currentTime main.ShowTitle( title, outPut + 'Removing unnecessary word (' + str(index) + '/' + str(initialLength) + ' removed: ' + str(removeIndex) + ')') wordDict.pop(key) relatedWordDict = {} SentiWordBinder = BindSentiWords.BindSentiWords() index = 0 updateTime = 0 for key, value in wordDict.items(): currentTime = int(str( datetime.datetime.now().strftime('%Y%m%d%H%M%S'))) if updateTime < currentTime: updateTime = currentTime main.ShowTitle( title, outPut + 'Calculating sentimental value (' + str(index) + '/' + str(len(wordDict)) + ')') compareList = [] compareList.extend(value.keys()) sentiValueDict = SentiWordBinder.BindSentiWords(compareList) keySentiValue = sentiValueDict[key] if keySentiValue != 'None': sentiValueDict.pop(key) for word, targetSentiValue in sentiValueDict.items(): if targetSentiValue != 'None': if int(keySentiValue) == int(targetSentiValue): if value[key] > wordDict[word][word]: newRelation = {word: key} elif value[key] < wordDict[word][word]: newRelation = {key: word} relatedWordDict.update(newRelation) index = 0 updateTime = 0 for subWord, superWord in relatedWordDict.items(): currentTime = int(str( datetime.datetime.now().strftime('%Y%m%d%H%M%S'))) if updateTime < currentTime: updateTime = currentTime main.ShowTitle( title, outPut + 'Building similar word dictionary (' + str(index) + '/' + str(len(relatedWordDict)) + ')') try: upperWord = relatedWordDict[superWord] except: continue else: relatedWordDict[subWord] = upperWord for key, value in relatedWordDict.items(): if value == superWord: relatedWordDict[key] = upperWord main.ShowTitle(title, outPut + 'Getting exist similar word dictionary') sqlResult = DataBaseManager.DoSQL(""" SELECT Sub_Word, Similar_ID FROM similar_word_dic """) existRelatedWordDict = dict(sqlResult) insertQuery = [] updateQuery = [] index = 0 updateTime = 0 for subWord, superWord in relatedWordDict.items(): currentTime = int(str( datetime.datetime.now().strftime('%Y%m%d%H%M%S'))) if updateTime < currentTime: updateTime = currentTime main.ShowTitle( title, outPut + 'Appending Query (' + str(index) + '/' + str(len(relatedWordDict)) + ')') try: dictionaryID = existRelatedWordDict[subWord] except: insertQuery.append(""" INSERT INTO similar_word_dic (Sub_Word, Super_Word) VALUES ('""" + subWord + """', '""" + superWord + """') """) else: updateQuery.append(""" UPDATE similar_word_dic SET Super_Word WHERE Similar_ID = """ + str(dictionaryID) + """ """) DataBaseManager.DoManyQuery(insertQuery, title=title, outPut=outPut, queryType='INSERT') DataBaseManager.DoManyQuery(updateQuery, title=title, outPut=outPut, queryType='UPDATE')
def GetContent(target=None, title=None, outPut=None): global contents global embedding_model global baseDir embedding_model = None if title == None: title = 'Get data for Analyze similarity' else: title += '\nGet data for Analyze similarity' if outPut == None: outPut = '' else: outPut = 'Process data of ' + outPut + '\n' targetDataList = [] main.ShowTitle(title, outPut + "Find exist data") if target == None: sqlResult = DataBaseManager.DoSQL(""" SELECT Date FROM article_dic ORDER BY Article_ID DESC LIMIT 1 """) else: sqlResult = DataBaseManager.DoSQL(""" SELECT Date FROM review_dic WHERE Product_ID = """ + str(target) + """ ORDER BY Review_ID DESC LIMIT 1 """) lastUpdateDate = sqlResult[0][0] targetFasttextList = [] if target == None: targetDir = baseDir + '\\WordVectorData\\Normal' else: targetDir = baseDir + '\\WordVectorData\\' + str(target) if os.path.isdir(targetDir): fileList = os.listdir(targetDir) for file in fileList: extension = file.split('.')[-1] fileName = '.'.join(file.split('.')[:-1]) if extension == 'fasttext': targetFasttextList.append(fileName) else: os.makedirs(targetDir) lastProcessDate = '0000-00-00 00:00:00' if len(targetFasttextList) > 0: targetFileName = max(targetFasttextList) lastProcessDate = '-'.join( targetFileName.split('&')[0].split('#')) + ' ' + ':'.join( targetFileName.split('&')[1].split('#')) if targetFileName > lastUpdateDate.strftime('%Y#%m#%d&%H#%M#%S'): embedding_model = FastText.load(targetDir + '\\' + targetFileName + '.fasttext') outPut = 'There is existing similarity data' else: embedding_model = None targetDataList = [] main.ShowTitle(title, outPut + "Reading data") if target == None: sqlResult = DataBaseManager.DoSQL(""" SELECT Article FROM article_dic WHERE Date > '""" + lastProcessDate + """' AND Article != '' """) else: sqlResult = DataBaseManager.DoSQL(""" SELECT Review FROM review_dic WHERE Date > '""" + lastProcessDate + """' AND Review != '' """) for result in sqlResult: if result != []: targetDataList.extend(result) for data in targetDataList: contents.append(data.split('#')) return ''
def __init__(self, recipes_database_file, profiles_database_file): self.DBManager = DataBaseManager(recipes_database_file, profiles_database_file)
import DataBaseManager DBHandler = DataBaseManager.DBHandler("UNIDATABASE") TableList = DBHandler.Tables() Count = 0 for Table in TableList: Count += DBHandler.Count(Table) print(Count)
def ReviewDictionaryMenu(outPut=''): sqlResult = DataBaseManager.DoSQL(""" SELECT Product_Name, Count FROM product_dic """) productDict = dict(sqlResult) sqlResult = DataBaseManager.DoSQL(""" SELECT COUNT(*) FROM product_dic WHERE Count > 0 """) existProductCount = sqlResult[0][0] sqlResult = DataBaseManager.DoSQL(""" SELECT SUM(Count) FROM product_dic WHERE Count > 0 """) totalProductCount = sqlResult[0][0] sqlResult = DataBaseManager.DoSQL(""" SELECT COUNT(*) FROM review_dic """) reviewCount = sqlResult[0][0] while True: number = 1 if len(productDict) <= 0: ShowTitle('No exist Review Dictionary\n' + outPut, 'Review Dictionary Menu\n') else: ShowTitle(outPut) print('products: ' + str(existProductCount) + ' / total products: ' + str(totalProductCount)) print('total reviews: ' + str(reviewCount) + ' / total product infomation: ' + str(len(productDict))) print(str(number) + '. Show 20 Most frequent products') number += 1 print(str(number) + '. Search product') number += 1 print(str(number) + '. Manage product dictionary') number += 1 print(str(number) + '. Input new data') print('') print('b. Back') inputValue = input('=> ') if len(productDict) <= 0: if inputValue == '1': outPut = ReviewDivider.Proceed() return ReviewDictionaryMenu(outPut) else: if inputValue == '1': ShowFrequentWord(20, productDict, title='20 Most frequent products') continue elif inputValue == '2': ShowSearchResult(productDict, targetKey='Count', isProduct=True, title='Search Product Dictionary') continue elif inputValue == '3': ReviewDivider.ManageProduct('Manage product dictionary') continue elif inputValue == '4': outPut = ReviewDivider.Proceed() return ReviewDictionaryMenu(outPut) if inputValue == 'b': return '' else: outPut = 'Please enter correct number or charactor\n' continue
def Dividing(reviewData, fileName, title=None, outPut=None): global similarInsertQuery global similarUpdateQuery global insertQuery global updateQuery if title == None: title = '' if outPut == None: outPut = '' similarInsertQuery = [] similarUpdateQuery = [] insertQuery = [] updateQuery = [] completeIndex = 0 noProductIndex = 0 skippedIndex = 0 sqlResult = DataBaseManager.DoSQL(""" SELECT Review_ID, Review_Number FROM review_dic """) completedReview = dict(sqlResult) updateTime = 0 for data in reviewData: currentTime = int(str( datetime.datetime.now().strftime('%Y%m%d%H%M%S'))) if updateTime < currentTime: updateTime = currentTime additionString = '' if noProductIndex > 0 or skippedIndex > 0: additionString += ' (' if skippedIndex > 0: additionString += 'skipped: ' + str(skippedIndex) if noProductIndex > 0 and skippedIndex > 0: additionString += ' / ' if noProductIndex > 0: additionString += 'not product: ' + str(noProductIndex) additionString += ')' main.ShowTitle( title, outPut + 'Building dictionary for ' + fileName + ' (' + str(completeIndex + skippedIndex + noProductIndex) + '/' + str(len(reviewData)) + ')' + additionString) splitData = data.split(',') if len(splitData) < 2: return 'No data in ' + fileName reviewNumber = fileName + '-' + splitData[0] splitData.remove(splitData[0]) reviewTitleString = splitData[0] reviewTitleString = reviewTitleString.replace('\n', '') reviewTitleString = reviewTitleString.replace(';', ',') splitData.remove(splitData[0]) reviewString = ''.join(splitData) reviewString = reviewString.replace('\n', '') reviewString = reviewString.replace(';', ',') if reviewNumber in completedReview.values(): skippedIndex += 1 continue if reviewTitleString == '!e': continue reviewTitleStringList = NLP.DoNLP(reviewTitleString, None, 'Review') reviewStringList = NLP.DoNLP(reviewString, None, 'Review') resultList = GetProductName( ' '.join(reviewTitleStringList), ' '.join(reviewStringList)).get('product_Name') if len(resultList) > 0: resultStringList = DictionaryBuilder.ConvertNormalWord( mainStringList=reviewTitleStringList, subStringList=reviewStringList, mode='Review') resultString = '#'.join(resultStringList) for name in resultList: updateQuery.append(""" UPDATE product_dic SET Count = Count + 1 WHERE Product_Name = '""" + name + """' """) insertQuery.append(""" INSERT INTO review_dic (Review_Number, Review, Product_ID) VALUES ('""" + reviewNumber + """', '""" + resultString + """', """ + str(productDic[name].get('productID')) + """) """) for word in resultStringList: try: wordDict = productSimilarDic[name] except: wordDict = {} try: currentCount = wordDict[word] except: currentCount = 0 wordInfo = {word: currentCount + 1} wordDict.update(wordInfo) newItem = {name: wordDict} productSimilarDic.update(newItem) completeIndex += 1 else: noProductIndex += 1 returnString = "Complete building dictionary for " + fileName if skippedIndex > 0 or noProductIndex > 0: returnString += ' (' if skippedIndex > 0: returnString += 'skipped: ' + str(skippedIndex) if skippedIndex > 0 and noProductIndex > 0: returnString += ' / ' if noProductIndex > 0: returnString += 'not product: ' + str(noProductIndex) returnString += ')' returnString += '\n' AppendWordDicQuery(title=title, outPut=outPut + returnString) DataBaseManager.DoManyQuery(insertQuery, title=title, outPut=outPut + returnString, queryType='INSERT') DataBaseManager.DoManyQuery(updateQuery, title=title, outPut=outPut + returnString, queryType='UPDATE') DataBaseManager.DoManyQuery(similarInsertQuery, 'db_capstone_similarity', title=title, outPut=outPut + returnString, queryType='INSERT') DataBaseManager.DoManyQuery(similarUpdateQuery, 'db_capstone_similarity', title=title, outPut=outPut + returnString, queryType='UPDATE') return returnString
def AppendWordDicQuery(title=None, outPut=None): global productDic global productSimilarDic global similarInsertQuery global similarUpdateQuery global insertQuery global updateQuery if title == None: title = '' if outPut == None: outPut = '' featureDic = {} sqlResult = DataBaseManager.DoSQL(""" SELECT Feature_Name, Category_ID FROM feature_dic """) for result in sqlResult: try: existFeatureList = featureDic[result[1]] except: existFeatureList = [] existFeatureList.append(result[0]) newDict = {result[1]: existFeatureList} featureDic.update(newDict) createQuery = [] updateRelateTableQuery = [] for name, wordDict in productSimilarDic.items(): if productDic[name]['relationTable'] == None: try: featureList = [name] featureList[1:] = featureDic[productDic[name]['categoryID']] except: featureList = [name] createQuery.append(""" CREATE TABLE `db_capstone_similarity`.`""" + name + """` ( `Word_ID` INT(11) NOT NULL AUTO_INCREMENT, `Word` TEXT NOT NULL, `Word_Count` INT(11) NOT NULL DEFAULT '0', `Sentiment_Value` INT(11) NULL DEFAULT NULL,`""" + '`FLOAT NULL DEFAULT NULL,`'.join(featureList) + """` FLOAT NULL DEFAULT NULL, PRIMARY KEY (`Word_ID`) ) ENGINE = InnoDB """) updateRelateTableQuery.append(""" UPDATE product_dic SET Relation_Table_Name = '""" + name + """' WHERE Product_ID = """ + str(productDic[name]['productID']) + """ """) productDic[name]['relationTable'] = name existWord = {} else: WordList = [] WordList.extend( DataBaseManager.DoSQL( """ SELECT `Word`, `Word_ID` FROM `""" + name + """`""", 'db_capstone_similarity')) existWord = dict(WordList) for word, count in wordDict.items(): try: wordID = existWord[word] except: similarInsertQuery.append(""" INSERT INTO `""" + name + """` (`Word` ,`Word_Count`) VALUES ('""" + word + """', """ + str(count) + """) """) else: similarUpdateQuery.append(""" UPDATE `""" + name + """` SET `Word_Count` = `Word_Count` + """ + str(count) + """ WHERE `Word_ID` = """ + str(wordID) + """ """) DataBaseManager.DoManyQuery(createQuery, title=title, outPut=outPut, queryType='CREATE') DataBaseManager.DoManyQuery(updateRelateTableQuery, title=title, outPut=outPut, queryType='UPDATE')
def __init__(self): super().__init__() self.ui = Ui_OgretmenKayitForm() self.Teacher = DataBaseManager.Teacher() self.initUI() self.show()
PORT = int(config.get('General', 'port')) MAIN_SERVER_IP = config.get('General', 'MainServerIP') MAIN_SERVER_PORT = int(config.get('General', 'MainServerPort')) DB_NAME = config.get('General', 'db_name') except (ConfigParser.NoOptionError, ConfigParser.NoSectionError), e: print e sys.exit(1) HOST = '0.0.0.0' MAIN_SERVER_PATH = 'http://' + MAIN_SERVER_IP + ':' + str(MAIN_SERVER_PORT) ME = 'PhoneBook' + '@' + 'http://' + MY_IP + ':' + str(PORT) # endregion # region ----------------------- GLOBAL ------------------------- app = Flask(__name__) data_base_manager = DataBaseManager(DB_NAME) # endregion # region ------------------- URL_FUNCTIONS ---------------------- # route for SP index page @app.route('/') def index(): return render_template('PhoneBookIndex.html') # route for SP login page @app.route('/login', methods=['GET', 'POST']) def login(): error = None if request.method == 'POST':
def SelectProduct(Mode, outPut=None, title=None): if title == None: title = '' if outPut == None: outPut = '' if Mode == 'Product': while True: main.ShowTitle(title, outPut) inputValue = input( 'Enter product Name (%q to back %a to process all): ') if inputValue == '%q': return '' if inputValue == '%a': return ProcessAllProduct() productList = ReviewDivider.GetProductName(inputValue).get( 'product_Name') main.ShowTitle(title, 'Data for ' + inputValue) number = 1 if len(productList) > 0: for product in productList: print(str(number) + '. ' + product) number += 1 else: print('No result') print('') print('r. Re-enter name') print('b. Back') inputValue = input("=> ") if inputValue == 'r': outPut = '' continue elif inputValue == 'b': return '' try: sqlResult = DataBaseManager.DoSQL(""" SELECT Product_ID FROM product_dic WHERE Product_Name = '""" + productList[int(inputValue) - 1] + """' """)[0] if sqlResult != []: targetID = sqlResult[0] else: outPut = 'Please enter correct number or charactor' continue except: outPut = 'Please enter correct number or charactor' continue tableName = DataBaseManager.DoSQL(""" SELECT Relation_Table_Name FROM product_dic WHERE Product_ID = """ + str(targetID) + """ LIMIT 1 """)[0][0] if tableName == None: outPut = 'No review for ' + tableName continue GetContent(targetID, title=title, outPut=productList[int(inputValue) - 1]) GetSimilarity(targetID, title=title, outPut=productList[int(inputValue) - 1]) if len(contents) > 0: UpdateSimilarityDatabase(targetID, title=title, outPut=productList[int(inputValue) - 1]) outPut = '' while True: main.ShowTitle('', outPut) inputValue = input('Enter target word (%q to back): ') if inputValue == '%q': outPut = '' break outPut = GetRelatedWord(tableName, inputValue) else: articleCount = DataBaseManager.DoSQL(""" SELECT COUNT(*) FROM article_dic """)[0][0] if articleCount <= 0: return 'There is no data' ProcessArticle() outPut = '' while True: main.ShowTitle('', outPut) inputValue = input('Enter target word (%q to back): ') if inputValue == '%q': outPut = '' break outPut = GetRelatedWord('Normal', inputValue)
def DeleteButton(): global idTextVar DataBaseManager.DeleteTuple(idTextVar.get()) DeleteMenu()
import pandas as pd import numpy as np import matplotlib.pyplot as plt import codecs import DataBaseManager import GetData from pandas.tools.plotting import andrews_curves import seaborn as sns Head = ['AQI', '范围', '质量等级', 'PM2.5', 'PM10', 'SO2', 'CO', 'NO2', 'O3'] CityListO = codecs.open("CityList.info", "r", "utf-8") CityList = [item.strip('\r') for item in CityListO.read().split('\n')] CityListO.close() DataBase = DataBaseManager.DBHandler("DATABASE") Connection = DataBase.GetConn() OriginalTime = OT = "20131202" def queryByDate(startDate, endingDate, city, freq="D"): QueryResult = [ list(item) for item in DataBase.Query(city, int(startDate), int(endingDate)) ] index = pd.date_range(startDate, endingDate) df = pd.DataFrame(QueryResult) Date = df[[0]] Data = df[[1, 2, 3, 4, 5, 6, 7, 8, 9]] Data.columns = Head ChangeList = ['AQI', 'PM2.5', 'PM10', 'SO2', 'CO', 'NO2', 'O3'] Data = Data.apply(lambda x: pd.to_numeric(x, errors='ignore')) Date = pd.to_datetime(Date[0], format='%Y%m%d', errors='ignore')