def replace_all_links_to_this_word(self): remote_query = QSqlQuery(self.remote_cn) local_query = QSqlQuery(self.local_cn) if self.onlyClassified: remote_query.prepare(replace_uuid_in_links_query_OC(self.uuid, self.new_uuid)) else: remote_query.prepare(replace_uuid_in_links_query(self.uuid, self.new_uuid)) local_query.prepare(full_delete_local_termin_by_uuid) local_query.bindValue(":uuid", self.uuid) s1, s2 = False, False QtSql.QSqlDatabase.database('SQLiteConnection').transaction() QtSql.QSqlDatabase.database('PGconnection').transaction() if remote_query.exec_(): s1 = True else: print(remote_query.lastError().text()) print(remote_query.lastQuery()) if local_query.exec_(): s2 = True else: print(local_query.lastError().text()) print(local_query.lastQuery()) if s1 and s2: QtSql.QSqlDatabase.database('SQLiteConnection').commit() QtSql.QSqlDatabase.database('PGconnection').commit() else: QtSql.QSqlDatabase.database('SQLiteConnection').rollback() QtSql.QSqlDatabase.database('PGconnection').rollback() self.finishTrigger.emit(True)
def list_data(self, name="", type="", limit="", filter=""): data_list = [] if name == "": return "" query = QSqlQuery(self.db) if limit != "": limit = " LIMIT " + limit if filter != "": try: filter_split = filter.split('=') filter = 'AND data LIKE "%{0}: {1}%"'.format(filter_split[0], filter_split[1]) except: data_list.append("filter: BAD FILTER") filter = "" if type != "": query.exec_("SELECT * FROM DDS WHERE name = \"{0}\"" "AND type LIKE \"{1}%\"".format(name, type) + filter + limit) else: query.exec_("SELECT * FROM DDS WHERE name = \"{0}\"".format(name) + filter + limit) print query.lastQuery() while query.next(): type = str(query.value(2).toString()) data = str(query.value(3).toString()) data_list.append("type: " + type + '\n' + data) return data_list
def updateTeacher(self, teacherId, teacher): """Updates a Teacher record""" try: query = QSqlQuery(self.conn) query.prepare("UPDATE teachers \ SET first_name=:first, last_name=:last, address=:address, city=:city, postal_code=:postal,\ daytime_phone=:daytimePhone, evening_phone=:eveningPhone, email=:email \ WHERE id=:id") query.bindValue(":first", teacher.first) query.bindValue(":last", teacher.last) query.bindValue(":address", teacher.address) query.bindValue(":city", teacher.city) query.bindValue(":postal", teacher.postal) query.bindValue(":daytimePhone", teacher.daytimePhone) query.bindValue(":eveningPhone", teacher.eveningPhone) query.bindValue(":email", teacher.email) query.bindValue(":id", teacherId) query.exec_() if query.isActive() == False: print query.lastError().text() return query.lastError().text() self.teacherModel.select() return "" except Exception, e: # TODO: log this instead of printing to console print "updateTeacher FAILED\n\tquery: {0}\n\terror: {1}".format(query.lastQuery(), e) return e
def getTeacherFromId(self, teacherId): """Retrieve the appropriate Teacher from the given id""" try: query = QSqlQuery(self.conn) query.prepare("SELECT first_name, last_name, address, city, postal_code, daytime_phone, evening_phone, email \ FROM teachers WHERE id=:id") numericId = teacherId query.bindValue(":id", numericId) query.exec_() if query.isActive() == False: print query.lastError().text() return query.lastError().text() # Now turn it into the appropriate object query.next() first = str(query.value(0).toString()) last = str(query.value(1).toString()) address = str(query.value(2).toString()) city = str(query.value(3).toString()) postal = str(query.value(4).toString()) daytimePhone = str(query.value(5).toString()) eveningPhone = str(query.value(6).toString()) email = str(query.value(7).toString()) retrievedTeacher = Teacher(first, last, address, city, postal, daytimePhone, eveningPhone, email) return retrievedTeacher except Exception, e: # TODO: log this instead of printing to console print "getTeacherFromId FAILED\n\tquery: {0}\ \n\tvalues: {1}\n\terror: {2}".format(query.lastQuery(), numericId, e)
def updateGroupParticipant(self, participantId, participant): """Updates GroupParticipant record""" try: query = QSqlQuery(self.conn) query.prepare("UPDATE groupparticipants \ SET group_name=:groupName, group_size=:groupSize, school_grade=:schoolGrade,\ average_age=:averageAge, participants=:participants, contact=:contact, earliest_performance_time=:earliestPerformanceTime, latest_performance_time=:latestPerformanceTime \ WHERE id=:id") query.bindValue(":groupName", participant.groupName) query.bindValue(":groupSize", participant.groupSize) query.bindValue(":schoolGrade", participant.schoolGrade) query.bindValue(":averageAge", participant.averageAge) query.bindValue(":participants", participant.participants) query.bindValue(":contact", participant.contact) query.bindValue(":earliestPerformanceTime", participant.earliestPerformanceTime) query.bindValue(":latestPerformanceTime", participant.latestPerformanceTime) query.bindValue(":id", participantId) query.exec_() if query.isActive() == False: print query.lastError().text() return query.lastError().text() self.groupParticipantModel.select() return "" except Exception, e: # TODO: log this instead of printing to console print "updateGroupParticipant FAILED\n\tquery: {0}\n\terror: {1}".format(query.lastQuery(), e) return e
def getParticipantsWithName(self, first, last): """Looks for participants with the given name""" pList = [] try: query = QSqlQuery(self.conn) query.prepare("SELECT first_name, last_name, address, town, postal_code, home_phone, \ cell_phone, email, date_of_birth, school_attending, parent \ FROM soloparticipants WHERE first_name=:first AND last_name=:last") query.bindValue(":first", first) query.bindValue(":last", last) query.exec_() if query.isActive() == False: print query.lastError().text() return query.lastError().text() while query.next() == True: first = str(query.value(0).toString()) last = str(query.value(1).toString()) address = str(query.value(2).toString()) town = str(query.value(3).toString()) postal = str(query.value(4).toString()) home = str(query.value(5).toString()) cell = str(query.value(6).toString()) email = str(query.value(7).toString()) dob = str(query.value(8).toString()) schoolAttending = str(query.value(9).toString()) parent = str(query.value(10).toString()) pList.append(Participant(first, last, address, town, postal, home, cell, email, dob, schoolAttending, parent)) return pList except Exception, e: # TODO: log this instead of printing to console print "getParticipantsWithName FAILED\n\tquery: {0}\ \n\terror: {1}".format(query.lastQuery(), e)
def addTeacher(self, t): """Adds a new Teacher record to the db""" try: query = QSqlQuery(self.conn) query.prepare("INSERT INTO teachers \ (first_name, last_name, address, city, postal_code, daytime_phone, evening_phone, email) \ VALUES (:first, :last, :address, :city, :postal, :daytimePhone, :eveningPhone, :email)") query.bindValue(":first", t.first) query.bindValue(":last", t.last) query.bindValue(":address", t.address) query.bindValue(":city", t.city) query.bindValue(":postal", t.postal) query.bindValue(":daytimePhone", t.daytimePhone) query.bindValue(":eveningPhone", t.eveningPhone) query.bindValue(":email", t.email) query.exec_() if query.isActive() == False: print query.lastError().text() return query.lastError().text() self.teacherModel.select() return "" except Exception, e: # TODO: log this instead of printing to console print "addTeacher FAILED\n\tquery: {0}\n\terror: {1}".format(query.lastQuery(), e) return e
def getAllEntries(self): """Retrieve all the Entries and return them in a list""" entryList = [] try: query = QSqlQuery(self.conn) query.prepare("SELECT participant_id, teacher_id, discipline, level, class_number, \ class_name, instrument, years_of_instruction, scheduling_requirements, id FROM entries") query.exec_() if query.isActive() == False: print query.lastError().text() return query.lastError().text() while query.next() == True: participantID = str(query.value(0).toString()) teacherID = str(query.value(1).toString()) discipline = str(query.value(2).toString()) level = str(query.value(3).toString()) classNumber = str(query.value(4).toString()) className = str(query.value(5).toString()) instrument = str(query.value(6).toString()) yearsOfInstruction = str(query.value(7).toString()) schedulingRequirements = str(query.value(8).toString()) entryId = str(query.value(9).toString()) # get associated selections selections = self.getSelectionsFromEntryId(entryId) ee = Entry(participantID, teacherID, discipline, level, yearsOfInstruction, classNumber, className, instrument, selections, schedulingRequirements) entryList.append(ee) return entryList except Exception, e: # TODO: log this instead of printing to console print "getAllEntries FAILED\n\tquery: {0}\n\terror: {1}".format(query.lastQuery(), e) return e
def getEntryFromId(self, entryId): """Retrieve Entry from specified id.""" try: query = QSqlQuery(self.conn) query.prepare("SELECT participant_id, teacher_id, discipline, level, class_number, \ class_name, instrument, years_of_instruction, scheduling_requirements FROM entries \ WHERE id=:id") query.bindValue(":id", entryId) query.exec_() if query.isActive() == False: print query.lastError().text() return query.lastError().text() query.next() participantID = str(query.value(0).toString()) teacherID = str(query.value(1).toString()) discipline = str(query.value(2).toString()) level = str(query.value(3).toString()) classNumber = str(query.value(4).toString()) className = str(query.value(5).toString()) instrument = str(query.value(6).toString()) yearsOfInstruction = str(query.value(7).toString()) schedulingRequirements = str(query.value(8).toString()) # get associated selections selections = self.getSelectionsFromEntryId(entryId) ee = Entry(participantID, teacherID, discipline, level, yearsOfInstruction, classNumber, className, instrument, selections, schedulingRequirements) return ee except Exception, e: # TODO: log this instead of printing to console print "getEntryFromId FAILED\n\tquery: {0}\n\terror: {1}".format(query.lastQuery(), e) return e
def addGroupParticipant(self, gp): """Adds a new GroupParticipant record to the db""" try: query = QSqlQuery(self.conn) query.prepare("INSERT INTO groupparticipants \ (group_name, group_size, school_grade, average_age, participants, contact, earliest_performance_time, latest_performance_time) \ VALUES (:groupName, :groupSize, :schoolGrade, :averageAge, :participants, :contact, :earliestPerformanceTime, :latestPerformanceTime)") query.bindValue(":groupName", gp.groupName) query.bindValue(":groupSize", gp.groupSize) query.bindValue(":schoolGrade", gp.schoolGrade) query.bindValue(":averageAge", gp.averageAge) query.bindValue(":participants", gp.participants) query.bindValue(":contact", gp.contact) query.bindValue(":earliestPerformanceTime", gp.earliestPerformanceTime) query.bindValue(":latestPerformanceTime", gp.latestPerformanceTime) query.exec_() if query.isActive() == False: print query.lastError().text() return query.lastError().text() self.groupParticipantModel.select() return "" except Exception, e: # TODO: log this instead of printing to console print "addGroupParticipant FAILED\n\tquery: {0}\n\terror: {1}".format(query.lastQuery(), e) return e
def getTeachersWithName(self, first, last): """Looks for teachers with the given name""" tList = [] try: query = QSqlQuery(self.conn) query.prepare("SELECT first_name, last_name, address, city, postal_code, daytime_phone, \ evening_phone, email \ FROM teachers WHERE first_name=:first AND last_name=:last") query.bindValue(":first", first) query.bindValue(":last", last) query.exec_() if query.isActive() == False: print query.lastError().text() return query.lastError().text() while query.next() == True: first = str(query.value(0).toString()) last = str(query.value(1).toString()) address = str(query.value(2).toString()) city = str(query.value(3).toString()) postal = str(query.value(4).toString()) daytimePhone = str(query.value(5).toString()) eveningPhone = str(query.value(6).toString()) email = str(query.value(7).toString()) tList.append(Teacher(first, last, address, city, postal, daytimePhone, eveningPhone, email)) return tList except Exception, e: # TODO: log this instead of printing to console print "getTeachersWithName FAILED\n\tquery: {0}\ \n\terror: {1}".format(query.lastQuery(), e)
def updateEntry(self, entryId, entry): """Updates an Entry record""" try: query = QSqlQuery(self.conn) query.prepare("UPDATE entries \ SET participant_id=:participantID, teacher_id=:teacherID, discipline=:discipline,\ level=:level, class_number=:classNumber, class_name=:className, instrument=:instrument, \ years_of_instruction=:yearsOfInstruction, scheduling_requirements=:schedulingRequirements \ WHERE id=:id") query.bindValue(":participantID", entry.participantID) query.bindValue(":teacherID", entry.teacherID) query.bindValue(":discipline", entry.discipline) query.bindValue(":level", entry.level) query.bindValue(":classNumber", entry.classNumber) query.bindValue(":className", entry.className) query.bindValue(":instrument", entry.instrument) query.bindValue(":yearsOfInstruction", entry.yearsOfInstruction) query.bindValue(":schedulingRequirements", entry.schedulingRequirements) query.bindValue(":id", entryId) query.exec_() if query.isActive() == False: print query.lastError().text() print query.lastQuery() return query.lastError().text() self.entryModel.select() # delete all selections associated with this entry (handles deleting selections during update) result = self.deleteSelectionsFromEntryId(entryId) # re-add selections to db (handles updates and new selections) for selection in entry.selections: result = self.addSelection(selection, entryId) if result != "": if entryId != None: self.deleteEntryFromId(entryId) return result return "" except Exception, e: # If something went wrong adding the selections, we want to delete the whole entry if entryId != None: self.deleteEntryFromId(entryId) # TODO: log this instead of printing to console print "addEntry FAILED\n\tquery: {0}\n\terror: {1}".format(query.lastQuery(), e) return e
def getParticipantFromId(self, participantId): """Retrieve the appropriate Participant from the given id""" try: query = QSqlQuery(self.conn) # if participantId[0] == 's': # query.prepare("SELECT first_name, last_name, address, town, postal_code, home_phone, cell_phone, email, date_of_birth, school_attending, parent \ # FROM soloparticipants WHERE id=:id") # else: # query.prepare("SELECT group_name, group_size, school_grade, average_age, participants, contact \ # FROM groupparticipants WHERE id=:id") query.prepare("SELECT first_name, last_name, address, city, postal_code, home_phone, cell_phone, email, date_of_birth, school_attending, parent, age, school_grade, group_name, number_participants, earliest_time, latest_time, group_participants, average_age, contact \ FROM participants WHERE id=:id") # numericId = participantId[1:] query.bindValue(":id", participantId) query.exec_() if query.isActive() == False: print query.lastError().text() return query.lastError().text() # Now turn it into the appropriate object query.next() retrievedParticipant = None # if participantId[0] == 's': first = str(query.value(0).toString()) last = str(query.value(1).toString()) address = str(query.value(2).toString()) city = str(query.value(3).toString()) postal = str(query.value(4).toString()) home = str(query.value(5).toString()) cell = str(query.value(6).toString()) email = str(query.value(7).toString()) dob = str(query.value(8).toString()) schoolAttending = str(query.value(9).toString()) parent = str(query.value(10).toString()) # retrievedParticipant = SoloParticipant(first, last, address, town, postal, home, cell, email, dob, schoolAttending, parent) # else: age = str(query.value(11).toString()) schoolGrade = str(query.value(12).toString()) groupName = str(query.value(13).toString()) groupSize = str(query.value(14).toString()) earliestTime = str(query.value(15).toString()) latestTime = str(query.value(16).toString()) participants = str(query.value(17).toString()) averageAge = str(query.value(18).toString()) contact = str(query.value(19).toString()) # retrievedParticipant = GroupParticipant(groupName, groupSize, schoolGrade, averageAge, participants, contact) retrievedParticipant = Participant(first=first, last=last, address=address, city=city, postal=postal, home=home, cell=cell, email=email, dob=dob, schoolAttending=schoolAttending, parent=parent, age=age, schoolGrade=schoolGrade, groupName=groupName, numberParticipants=groupSize, averageAge=averageAge, participants=participants, contact=contact, earliestPerformanceTime=earliestTime, latestPerformanceTime=latestTime) return retrievedParticipant except Exception, e: # TODO: log this instead of printing to console print "getParticipantFromId FAILED\n\tquery: {0}\ \n\terror: {1}".format(query.lastQuery(), e)
def delete_all_links_to_this_word(self, uuid): remote_query = QSqlQuery(self.remote_cn) remote_query2 = QSqlQuery(self.remote_cn) local_query = QSqlQuery(self.local_cn) if self.onlyClassified: remote_query.prepare(find_all_termins_contains_link_to_uuid_OC(uuid)) else: remote_query.prepare(find_all_termins_contains_link_to_uuid(uuid)) local_query.prepare(full_delete_local_termin_by_uuid) local_query.bindValue(":uuid", uuid) s1, s2 = False, False QtSql.QSqlDatabase.database('SQLiteConnection').transaction() QtSql.QSqlDatabase.database('PGconnection').transaction() if remote_query.exec_(): s1 = True while remote_query.next(): # update definition new_definition = delete_uuid_from_definition(uuid, remote_query.value(1)) if self.onlyClassified: remote_query2.prepare(update_definition_query_OC) else: remote_query2.prepare(update_definition_query) remote_query2.bindValue(":uuid", remote_query.value(0)) remote_query2.bindValue(":definition", new_definition) else: print(remote_query.lastError().text()) print(remote_query.lastQuery()) if local_query.exec_(): s2 = True else: print(local_query.lastError().text()) print(local_query.lastQuery()) if s1 and s2: QtSql.QSqlDatabase.database('SQLiteConnection').commit() QtSql.QSqlDatabase.database('PGconnection').commit() else: QtSql.QSqlDatabase.database('SQLiteConnection').rollback() QtSql.QSqlDatabase.database('PGconnection').rollback() self.finishTrigger.emit(True)
def getLastGroupParticipantId(self): """Get the id of the most recently added GroupParticipant""" try: query = QSqlQuery(self.conn) query.exec_("SELECT MAX(id) FROM groupparticipants") if query.isActive() == False: print query.lastError().text() return query.lastError().text() query.next() participantId = str(query.value(0).toString()) return "g" + participantId except Exception, e: # TODO: log this instead of printing to console print "getLastGroupParticipantId FAILED\n\tquery: {0}\ \n\terror: {1}".format(query.lastQuery(), e)
def deleteTeacherFromId(self, tId): try: query = QSqlQuery(self.conn) # Delete the teacher query.prepare("DELETE FROM teachers WHERE id=:id") query.bindValue(":id", tId) query.exec_() if query.isActive() == False: print query.lastError().text() return query.lastError().text() self.teacherModel.select() except Exception, e: # TODO: log this instead of printing to console print "deleteTeacherFromId FAILED\n\tquery: {0}\n\terror: {1}".format(query.lastQuery(), e) return e
def getLastEntryId(self): """Get the id of the most recently added Entry""" try: query = QSqlQuery(self.conn) query.prepare("SELECT MAX(id) FROM entries") query.exec_() if query.isActive() == False: print query.lastError().text() return query.lastError().text() query.next() entryId = str(query.value(0).toString()) return entryId except Exception, e: # TODO: log this instead of printing to console print "getLastEntryId FAILED\n\tquery: {0}\n\terror: {1}".format(query.lastQuery(), e)
def deleteEntryFromId(self, entryId): try: query = QSqlQuery(self.conn) # Delete the entry query.prepare("DELETE FROM entries WHERE id=:id") query.bindValue(":id", entryId) query.exec_() # Delete its selections query.prepare("DELETE FROM selections WHERE entry_id=:id") query.bindValue(":id", entryId) query.exec_() if query.isActive() == False: print query.lastError().text() return query.lastError().text() self.entryModel.select() self.pieceModel.select() except Exception, e: # TODO: log this instead of printing to console print "deleteEntryFromId FAILED\n\tquery: {0}\n\terror: {1}".format(query.lastQuery(), e) return e
def fill_fields(self): query = QSqlQuery(get_remote_connection()) if self.onlyClassified: query.prepare(get_name_and_short_def_query_OC) else: query.prepare(get_name_and_short_def_query) query.bindValue(':uuid', self.uuid) if query.exec_(): if query.next(): self.mainWordLabel.setText('Ссылка на термин: <b>' + query.value(0) + "</b>") short_def = remove_all_tags(query.value(1)) if len(query.value(1)) < 299: self.definitionLabel.setText("Определение: " + short_def) else: self.definitionLabel.setText("Определение: " + short_def + '...') else: self.mainWordLabel.setText('Термин не найден') self.definitionLabel.setText("Возможно термин, на который ведет данная ссылка был удален или" " Вы подключились к другой БД. Пожалуйста, обновите список терминов.") else: print(query.lastError().text()) print(query.lastQuery())
def save(self, save_not_accepted=False): text = self.textEdit.toInitialHtmlFormat() links = re.findall(r'<[Aa][^>]*>.*?</[Aa]>', text) for l in links: print("LINK - " + l) if "termin##" in l: href = re.findall(r'href=([^>]*?)>', l)[0] if "status##1##" in l: if save_not_accepted: print("HREF - " + href) new_href = '"termin;' + href.split( "##")[1].__str__() + '"' print("NEW HREF - " + new_href) new_l = l.replace(href, new_href) print("NEW LINK - " + new_l) # заменить и сохранить ссылку text = text.replace(l, new_l) else: new_l = re.findall(r'<[Aa][^>]*>(.*?)</[Aa]>', l)[0] print("RES TEXT - " + new_l) text = text.replace(l, new_l) elif "status##2##" in l: new_l = re.findall(r'<[Aa][^>]*>(.*?)</[Aa]>', l)[0] print("RES TEXT - " + new_l) # удалить ссылку, потому что не однозначная text = text.replace(l, new_l) elif "status##" not in l: print("HREF - " + href) new_href = '"termin;' + href.split("##")[1].__str__() + '"' print("NEW HREF - " + new_href) new_l = l.replace(href, new_href) print("NEW LINK - " + new_l) text = text.replace(l, new_l) # заменить и сохранить ссылку, т.к. она принята print('\n') print(text) local_update = QSqlQuery(self.local_cn) local_update.prepare(update_local_termin_query) local_update.bindValue(":linked", 1) local_update.bindValue(":uuid", self.current_uuid) remote_update = QSqlQuery(self.remote_cn) if self.onlyClassified: remote_update.prepare(update_remote_termin_query_OC) else: remote_update.prepare(update_remote_termin_query) remote_update.bindValue(":definition", text) remote_update.bindValue(":uuid", self.current_uuid) QtSql.QSqlDatabase.database('SQLiteConnection').transaction() QtSql.QSqlDatabase.database('PGconnection').transaction() local_status = False remote_status = False if remote_update.exec_(): remote_status = True else: print(remote_update.lastQuery()) print(remote_update.lastError().text()) if local_update.exec_(): local_status = True else: print(local_update.lastQuery()) print(local_update.lastError().text()) if local_status and remote_status: QtSql.QSqlDatabase.database('SQLiteConnection').commit() QtSql.QSqlDatabase.database('PGconnection').commit() return True else: QtSql.QSqlDatabase.database('SQLiteConnection').rollback() QtSql.QSqlDatabase.database('PGconnection').rollback() return False
def getSelectionsFromEntryId(self, entryId): """Retrieves all the Selections associated with the entryId""" selectionList = [] try: query = QSqlQuery(self.conn) query.prepare("SELECT title, performance_time, composer_arranger, title_of_musical\ FROM selections WHERE entry_id=:id") query.bindValue(":id", entryId) query.exec_() if query.isActive() == False: print "DB ERROR: {0}".format(query.lastError().text()) return query.lastError().text() while query.next() == True: fields = {} fields['title'] = str(query.value(0).toString()) fields['performanceTime'] = str(query.value(1).toString()) fields['composerArranger'] = str(query.value(2).toString()) fields['titleOfMusical'] = str(query.value(3).toString()) selectionList.append(fields) return selectionList except Exception, e: # TODO: log this instead of printing to console print "getSelectionsFromEntryId FAILED\n\tquery: {0}\n\terror: {1}".format(query.lastQuery(), e) return e
def deleteSelectionsFromEntryId(self, entryId): """Deletes all selections that reference entryId""" try: query = QSqlQuery(self.conn) query.prepare("DELETE FROM selections \ WHERE entry_id=:id") query.bindValue(":id", entryId) query.exec_() if query.isActive() == False: print query.lastError().text() return query.lastError().text() return "" except Exception, e: # TODO: log this instead of printing to console print "deleteSelectionsFromEntryId FAILED\n\tquery: {0}\n\terror: {1}".format(query.lastQuery(), e) return e