Пример #1
0
 def validarCierreAnual(self):
     try:
         query=QSqlQuery()
         q=u"""
         SELECT d.iddocumento 
         FROM documentos d 
         WHERE d.idtipodoc=%d  
         AND YEAR(d.fechacreacion)=%s""" %(constantes.IDCIERREMENSUAL,self.fecha.toString( "yyyy" ))
         query.prepare(q)
         
         if not query.exec_():
             raise Exception( "No se pudo ejecutar la consulta para determinar si se cerraron todos los meses del año" )
         
         if query.size()<12 and query.size()>0:
             raise UserWarning( "No se han cerrado todos los meses del Ejercicio" )
         return True
         
     except UserWarning as inst:
         logging.error( unicode( inst ) )
         QMessageBox.critical( self, qApp.applicationName(), unicode( inst ) )
         self.toolBar.removeAction( self.actionSave )
         return False
         
     except Exception as inst:
         logging.critical( unicode( inst ) )
         self.toolBar.removeAction( self.actionSave )
         QMessageBox.warning( self,
          qApp.organizationName(),unicode(inst))
         return False   
Пример #2
0
 def delCliRecord(self):
     if not self.db.isOpen():
         self.statusbar.showMessage(
             "Database non aperto...",
             5000)
         return
     selrows = self.cItmSelModel.selectedRows()
     if not selrows:
         self.statusbar.showMessage(
             "No selected customers to delete...",
             5000)
         return
     if(QMessageBox.question(self, "Delete Customers",
             "Do you want to delete: {0} customer(s)?".format(len(selrows)),
             QMessageBox.Yes|QMessageBox.No) ==
             QMessageBox.No):
         return
     QSqlDatabase.database().transaction()
     query = QSqlQuery()
     query.prepare("DELETE FROM clienti WHERE id = :val")
     for i in selrows:
         if i.isValid():
             query.bindValue(":val", QVariant(i.data().toInt()[0]))
             query.exec_()
     QSqlDatabase.database().commit()
     self.cModel.select()
Пример #3
0
def movArqueo(iddoc, difference):
    """
    MOVIMIENTO CONTABLE PARA UN ARQUEO
    @param iddoc: El id del documento arqueo que genera estos movimientos
    @type iddoc: int
    @param difference: La diferencia que existe entre el arqueo y el total de la sesión
    @type difference: Decimal
    """

    query = QSqlQuery()
    iddoc = str(iddoc)
    if difference == 0:
        raise Exception("Se trato de hacer un movimiento contable de arqueo de monto 0")
    elif difference > 0:
        query.prepare("INSERT INTO cuentasxdocumento ( idcuenta, iddocumento, monto) VALUES " +
                      " ( " + CAJAGENERAL + " , " + iddoc + " , " + difference.to_eng_string() + " ) , " +
                      " ( " + OTROSINGRESOS + " , " + iddoc + " , -" + difference.to_eng_string() + " )  "
                      )
    else:
        query.prepare("INSERT INTO cuentasxdocumento ( idcuenta, iddocumento, monto) VALUES " +
                      " ( " + CAJAGENERAL + " , " + iddoc + " , " + difference.to_eng_string() + " ) , " +
                      " ( " + PERDIDAS + " , " + iddoc + " , -" + difference.to_eng_string() + " )  "
                      )
    if not query.exec_():
        logging.critical(query.lastError().text())
        raise Exception("No se pudo ejecutar la consulta para el movimiento contable de arqueo")
Пример #4
0
 def init_db(self):
     print "here"
     sql_query = QSqlQuery('''SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA 
                              WHERE SCHEMA_NAME = 'helferlein';''', self.db)
     size = sql_query.size()
     print size
     if size == 0:
         print "db anlegen"
         sql_query = QSqlQuery("CREATE DATABASE helferlein;", self.db)
         print "tabelle schueler anlegen"
         sql_query = QSqlQuery('''CREATE TABLE SCHUELER (
            id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
            Name CHAR(100),
            Vorname CHAR(100),
            Stufe CHAR(2));''', self.db)
         print "tabelle fehlzeit anlegen"
         sql_query.prepare('''CREATE TABLE FEHLZEIT (
            id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
            schueler_id INT,
            Grund CHAR(100),
            Beginn CHAR(10),
            Ende CHAR(10),
            Stunden INT,
            ist_Beurlaubung INT,
            Attest INT,
            klausur_verpasst INT,
            Schuljahr CHAR(10));''')
         sql_query.exec_()            
Пример #5
0
    def removeRelationship(self):
        actlayer = qgis.utils.iface.activeLayer()
        index = self.modelt.index(0,0)
        columnindex=0
        for i in range(0,self.modelt.columnCount(index)):
            qresult = self.modelt.headerData(i, Qt.Horizontal, 0)
            if self.keycolumn == qresult:
                columnindex=i
                break
        idlist = []

        for row in range(self.modelt.rowCount()):
            index = self.modelt.index(row,columnindex)
            id = self.modelt.data(index,columnindex)
            idlist.append(id)

        queryinsert = QSqlQuery()
        querystr3 = "SELECT " + self.schema + ".del_row(:schema, :ref_id);" # "DELETE FROM " + self.schema+ " prodser.user_cim WHERE " self.keycolumn + " IN (" + stringlist + ");"

        for id in idlist:
            # create an item with a caption
            #print "id="+str(id)+", curruid="+str(curruid)+", fullname="+fullname
            queryinsert.prepare(querystr3)
            queryinsert.bindValue(":schema", self.schema)
            queryinsert.bindValue(":ref_id", id)
            testquery = queryinsert.exec_()
            if testquery:
                print "deleted: ", id
            else:
                print "not deleted: " + id + queryinsert.lastError().text()
                print querystr3

        actlayer.setSubsetString("")
        self.dlg.accept()
Пример #6
0
def SitesHaveFindSpots(db, siteNumbers):
    sites = u", ".join(u"'{0}'".format(siteNumber) for siteNumber in siteNumbers)
    query = QSqlQuery(db)
    query.prepare(u"SELECT COUNT(*) FROM fundstelle WHERE fundortnummer IN ({0})".format(sites))
    query.exec_()
    query.first()
    return query.value(0)
Пример #7
0
 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)
Пример #8
0
 def delDettRecord(self):
     if not self.db.isOpen():
         self.statusbar.showMessage(
             "Database non aperto...",
             5000)
         return
     selrows = self.sItmSelModel.selectedRows()
     if not selrows:
         self.statusbar.showMessage(
             "No articles selected to delete...",
             5000)
         return
     if(QMessageBox.question(self, "Cancellazione righe",
             "Vuoi cancellare: {0} righe?".format(len(selrows)),
             QMessageBox.Yes|QMessageBox.No) ==
             QMessageBox.No):
         return
     QSqlDatabase.database().transaction()
     query = QSqlQuery()
     query.prepare("DELETE FROM fattslave WHERE id = :val")
     for i in selrows:
         if i.isValid():
             query.bindValue(":val", QVariant(i.data().toInt()[0]))
             query.exec_()
     QSqlDatabase.database().commit()
     self.sModel.revertAll()
     self.mmUpdate()
Пример #9
0
 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)
Пример #10
0
def movKardex(iddoc, total):
    '''
    MOVIMIENTO CONTABLE PARA UNA ENTRADA DE BODEGA POR LIQUIDACION O ENTRADA LOCAL
    
    @param iddoc: El id del documento que genera estos movimientos
    @type iddoc: int
    @param total: El total del movimiento
    @type total: Decimal 
    '''
    query = QSqlQuery()
    iddoc = str(iddoc)
    if total == 0:
        raise Exception("Se trato de hacer un movimiento de monto 0")
    elif total > 0:
        query.prepare("INSERT INTO cuentasxdocumento ( idcuenta, iddocumento, monto) VALUES " +
                      " ( " + INVENTARIO + " , " + iddoc + " , " + total.to_eng_string() + " ) , " +
                      " ( " + OTROSINGRESOS + " , " + iddoc + " , -" + total.to_eng_string() + " )  ")
    
    else:
        query.prepare("INSERT INTO cuentasxdocumento ( idcuenta, iddocumento, monto) VALUES " +
                      " ( " + INVENTARIO + " , " + iddoc + " , " + total.to_eng_string() + " ) , " +
                      " ( " + PERDIDAS + " , " + iddoc + " , -" + total.to_eng_string() + " )  ")
        
    if not query.exec_():
        logging.critical(query.lastError().text())
        raise Exception("No se pudo ejecutar la consulta para el movimiento contable de kardex")
Пример #11
0
 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)
Пример #12
0
def prepareLinkFromWordInDB(word, html, exclude_uuid):
    try:
        html = html.split("<!--StartFragment-->")[1].split(
            "<!--EndFragment-->")[0]
    except:
        html = word
    str = stem_str(word)
    remote_sql = QSqlQuery(get_local_connection())
    remote_sql.prepare(count_word_in_db_query)
    # remote_sql.prepare(search_word_in_db_query)
    remote_sql.bindValue(":word", str)
    remote_sql.bindValue(":exclude_uuid", exclude_uuid)
    if remote_sql.exec_():
        if remote_sql.next():
            num = remote_sql.value(0)
            if num == 0:
                return None
            elif num == 1:
                return {
                    'link':
                    "<a href='termin##" + remote_sql.value(1) +
                    "##status##1##word##" + word + "##inithtml##" + html +
                    "' style='color:green'>" + html + "</a>"
                }
            elif num > 1:
                return {
                    'link':
                    "<a href='termin##" + str + "##status##2##word##" + word +
                    "##inithtml##" + html + "' style='color:red'>" + html +
                    "</a>"
                }
        else:
            return None
    else:
        return None
Пример #13
0
 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
Пример #14
0
    def CadastrarAluguel(aluguel):
        conn = ConexaoSQL
        db = conn.getConexao()
        db.open()

        query = QSqlQuery()


        query.prepare("UPDATE Veiculo SET Alugado = 'Sim' WHERE CodigoVeic = "+aluguel.CodigoVeic)
        query.exec_()
        db.commit()

        query.prepare("INSERT INTO Aluguel(DataAluguel, DataPrazo, DataDevolucao, ValorAluguel, "
                      "ValorMulta, KmEntrada, KmSaida, CodigoCli, CodigoVeic) "
                      "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)")
        query.addBindValue(aluguel.DataAluguel)
        query.addBindValue(aluguel.DataPrazo)
        query.addBindValue(aluguel.DataDevolucao)
        query.addBindValue(aluguel.ValorAluguel)
        query.addBindValue(aluguel.ValorMulta)
        query.addBindValue(aluguel.KmEntrada)
        query.addBindValue(aluguel.KmSaida)
        query.addBindValue(aluguel.CodigoCli)
        query.addBindValue(aluguel.CodigoVeic)
        query.exec_()
        db.commit()
Пример #15
0
 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
Пример #16
0
 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
Пример #17
0
 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
Пример #18
0
    def saveComboValues(self, combobox, text):
        """
        Save the value of the combo box into the form settings values.
        Only saves new values.
        """
        comboitems = [combobox.itemText(i) for i in range(combobox.count())]
        name = combobox.objectName()
        query = QSqlQuery()
        query.prepare("SELECT value FROM ComboBoxItems WHERE control = :contorl")
        query.bindValue(":control", name)
        query.exec_()
        log("LAST ERROR")
        log(query.lastError().text())
        items = []
        while query.next():
            value = query.value(0).toString()
            if not value.isEmpty():
                items.append(str(value))

        if not text in comboitems and not text in items:
            query = QSqlQuery()
            query.prepare("INSERT INTO ComboBoxItems (control, value)" "VALUES (:control,:value)")
            query.bindValue(":control", name)
            query.bindValue(":value", text)
            query.exec_()
            log("LAST ERROR FOR INSERT")
            log(query.lastError().text())
Пример #19
0
    def DevolverVeiculo(codigoAlug, aluguel):
        conn = ConexaoSQL
        db = conn.getConexao()
        db.open()



        select = "SELECT Veiculo.CodigoVeic FROM Aluguel"\
                      " INNER JOIN Veiculo ON Aluguel.CodigoVeic = Veiculo.CodigoVeic"\
                      " WHERE Aluguel.CodigoAlug = "+codigoAlug

        query = QSqlQuery(select)

        while query.next():
            codigoVeic = str(query.value(0))

        sql = "UPDATE Veiculo SET Alugado = 'Não' WHERE CodigoVeic = "+codigoVeic
        query.prepare(sql)
        query.exec_()
        db.commit()


        sql = "UPDATE Aluguel SET DataDevolucao = '"+aluguel.DataDevolucao+"', ValorMulta = '"+aluguel.ValorMulta\
                      +"', KmSaida = '"+aluguel.KmSaida\
                      +"' WHERE CodigoAlug = "+codigoAlug
        print(sql)
        query.prepare(sql)
        print
        query.exec_()
        db.commit()
Пример #20
0
 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
Пример #21
0
 def get_song_listmodel(self, key = None, parent = None):
     """Create and return a QStringListModel of songs in database
     By default all songs are included
     @param key: Include only songs in given key
     @param key: String
     @return: List model to be passed to QListView
     @type return: QStringListModel"""
     if key == None:
         query = QSqlQuery()
         query.prepare("SELECT song FROM Patterns")
         success = query.exec_()
         if not success:
             pass  # TODO
         songs = [], 
         while query.next():
             songs.append(query.value(0))
     else:
         query = QSqlQuery()
         query.prepare("SELECT song FROM Patterns WHERE key == :key")
         query.bindValue(":key", key)
         success = query.exec_()
         if not success:
             pass  # TODO
         songs = [], 
         while query.next():
             songs.append(query.value(0))  # Ajatusvirhe
     return QStringListModel(songs, parent)
Пример #22
0
def movDeposito( iddoc, deposito, ctabanco ):
    '''
    MOVIMIENTOS CONTABLE PARA UNA ENTRADA COMPRA
    
    (-)deposito        > sale de Caja genera:id=5, cod=110 001 001 000 
    
    (+)deposito                            > entra a cuenta del banco ctabanco
    @param iddoc: El id del documento que genera estos movimientos 
    @type iddoc: int
    @param deposito: TODO
    @type deposito: Decimal
    @param ctabanco: TODO
    @type ctabanco: Decimal
    
    
    '''
    iddoc = str( iddoc )
    total = deposito.to_eng_string()
    ctabanco = str( ctabanco )

    query = QSqlQuery()
    query.prepare( "INSERT INTO cuentasxdocumento (idcuenta,iddocumento,monto) values " +
    "(" + CAJAGENERAL + "," + iddoc + ",-" + total + ")," +
    "(" + ctabanco + "," + iddoc + "," + total + ")" )
    if not query.exec_():
        print( iddoc )
        print( query.lastError().text() )
        raise Exception( "NO SE PUDIERON INSERTAR LAS CUENTAS CONTABLES" )
Пример #23
0
    def btns_click(self, btnid):
        # curclassname = self.tabWidget.tabText(0)
        query = QSqlQuery(self.db)
        # cur = conn.cursor()
        today = datetime.date.today()
        self.g_curbtn = str(btnid).zfill(2)
        if self.g_curbtn not in self.dict_choices:
            self.btngroup.button(int(self.g_curbtn)).setStyleSheet(stylesheetstr_new)
            query.exec_("select count(*) from tmprecord where stusn='" + str(self.g_curbtn) + "'")
            query.next()            
            if query.value(0) == 0:
                query.prepare("insert into tmprecord (classname, stusn, datequestion) values (:classname, :stusn, :datequestion)")
                query.bindValue(":classname", self.g_curClassName)
                query.bindValue(":stusn", self.g_curbtn)
                query.bindValue(":datequestion", today)
                query.exec_() 
                
            self.dict_choices[self.g_curbtn] = "111"
        else:
            self.btngroup.button(int(self.g_curbtn)).setStyleSheet(stylesheetstr_old)
            self.btngroup.button(int(self.g_curbtn)).setIcon(QIcon())            
            query.exec_("delete from tmprecord where stusn='"+ str(self.g_curbtn) + "'")            
            self.dict_choices.pop(self.g_curbtn)

        self.btnSysMenu.setFocus()
Пример #24
0
    def fill_fields(self):

        search_word = self.filterEdit.text()
        if search_word.strip() != '':
            search_str = stem_str(search_word)
        else:
            search_str = ''

        query = QSqlQuery(self.local_cn)
        LIMIT = 100
        OFFSET = 0
        query.prepare(show_termins_in_link_selector_query)
        query.bindValue(':search_str', search_str + '%')
        query.bindValue(':linked', 1)
        query.bindValue(':limit', LIMIT.__str__())
        query.bindValue(':offset', OFFSET.__str__())
        if query.exec_():
            self.root.takeChildren()
            while query.next():
                c = QTreeWidgetItem()
                c.setText(0, query.value(0))  # Заглавное слово
                c.setData(1, 0, query.value(1))  # uuid
                self.root.addChild(c)
        else:
            print(query.lastError().text())
            print("not exec")
        self.treeWidget.scrollToTop()
Пример #25
0
 def get_song_listmodel(self, key=None, parent=None):
     """Create and return a QStringListModel of songs in database
     By default all songs are included
     @param key: Include only songs in given key
     @param key: String
     @return: List model to be passed to QListView
     @type return: QStringListModel"""
     if key == None:
         query = QSqlQuery()
         query.prepare("SELECT song FROM Patterns")
         success = query.exec_()
         if not success:
             pass  # TODO
         songs = [],
         while query.next():
             songs.append(query.value(0))
     else:
         query = QSqlQuery()
         query.prepare("SELECT song FROM Patterns WHERE key == :key")
         query.bindValue(":key", key)
         success = query.exec_()
         if not success:
             pass  # TODO
         songs = [],
         while query.next():
             songs.append(query.value(0))  # Ajatusvirhe
     return QStringListModel(songs, parent)
Пример #26
0
 def getAllEntriesInDiscipline(self, discipline):
     """Returns all the Entries for the given discipline"""
     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 \
             WHERE discipline=:discipline")
         query.bindValue(":discipline", discipline)
         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 "getAllEntriesInDiscipline FAILED\n\tquery: {0}\n\terror: {1}".format(query.lastQuery(), e)
         return e
Пример #27
0
def check_credentials(params, password):
    """
    Check credentials against database.  If correct, return 'role', else
    return False.

    :param params: Dictionary of parameters, including UserName and db path.
    :param password: String of password
    :return role / False: role (string), or false if denied.
    """
    # Connect to database
    if config.DEBUG_MODE:
        print('DEBUG_MODE: checking credentials')
    db_path = os.path.join(params['RNDataStorePath'], params['DbName'])
    username = params['UserName']
    db = database.connect_and_open(db_path, 'roadnet_db')

    # Query database
    login_query = QSqlQuery(db)
    login_query.prepare("""SELECT usertype FROM tblUsers
           WHERE username =:usr AND userpwd =:pwd""")
    login_query.bindValue(":usr", username, QSql.Out)
    login_query.bindValue(":pwd", password, QSql.Out)
    executed = login_query.exec_()
    if not executed:
        raise Exception('Database query failed.')
    if login_query.first() is True:  # i.e. matching record returned
        # Correct username or password: get role
        role = login_query.value(0)
        if role == 'admin':
            role = 'editor'  # Admin role is no longer used
    else:
        # Set debug mode settings to thinkwhere and editor and remove lock
        if config.DEBUG_MODE:
            params['UserName'] = '******'
            params['role'] = 'editor'
            role = 'editor'
            lock_file_path = os.path.join(params['RNDataStorePath'], 'RNLock')
            if os.path.isfile(lock_file_path):
                os.remove(lock_file_path)
        else:
            # Wrong username or password: warning message
            wrong_login_msg = QMessageBox(QMessageBox.Warning, " ",
                                          "Incorrect username or password",
                                          QMessageBox.Ok, None)
            wrong_login_msg.setWindowFlags(Qt.CustomizeWindowHint
                                           | Qt.WindowTitleHint)
            wrong_login_msg.exec_()
            role = 'init'

    # Close database
    del (login_query)
    connection_name = db.connectionName()
    db.close()
    del (db)
    QSqlDatabase.removeDatabase(connection_name)
    if config.DEBUG_MODE:
        print('DEBUG_MODE: closing QSqlDatabase {}'.format(connection_name))
    # Return role or None
    return role
Пример #28
0
 def updateParticipant(self, participantId, participant):
     """Updates a Participant record"""
     try:
         query = QSqlQuery(self.conn)
         query.prepare("""UPDATE participants
             SET 
                 first_name=:first,
                 last_name=:last,
                 address=:address,
                 city=:city,
                 postal_code=:postal,
                 home_phone=:home,
                 cell_phone=:cell,
                 email=:email,
                 date_of_birth=:dob,
                 school_attending=:schoolAttending,
                 parent=:parent,
                 age=:age,
                 school_grade=:schoolGrade,
                 group_name=:groupName,
                 number_participants=:numberParticipants,
                 earliest_time=:earliestTime,
                 latest_time=:latestTime,
                 group_participants=:groupParticipants,
                 average_age=:averageAge,
                 contact=:contact
             WHERE 
                 id=:id""")
         query.bindValue(":first", participant.first)
         query.bindValue(":last", participant.last)
         query.bindValue(":address", participant.address)
         query.bindValue(":city", participant.city)
         query.bindValue(":postal", participant.postal)
         query.bindValue(":home", participant.home)
         query.bindValue(":cell", participant.cell)
         query.bindValue(":email", participant.email)
         query.bindValue(":dob", participant.dob)
         query.bindValue(":schoolAttending", participant.schoolAttending)
         query.bindValue(":parent", participant.parent)
         query.bindValue(":age", participant.age)
         query.bindValue(":schoolGrade", participant.schoolGrade)
         query.bindValue(":groupName", participant.groupName)
         query.bindValue(":numberParticipants", participant.numberParticipants)
         query.bindValue(":earliestTime", participant.earliestPerformanceTime)
         query.bindValue(":latestTime", participant.latestPerformanceTime)
         query.bindValue(":groupParticipants", participant.participants)
         query.bindValue(":averageAge", participant.averageAge)
         query.bindValue(":contact", participant.contact)
         query.bindValue(":id", participantId)
         query.exec_()
         if query.isActive() == False:
             print query.lastError().text()
             return query.lastError().text()
         self.participantModel.select()
         return ""
     except Exception, e:
         # TODO: log this instead of printing to console
         print "updateParticipant FAILED\n\tquery: {0}\n\terror: {1}".format(query, e)
         return e
Пример #29
0
 def saveDefault(self, control, value):
     self.removeDefault(control)
     name = control.objectName()
     query = QSqlQuery()
     query.prepare("INSERT INTO DefaultValues (control, value)" "VALUES (:control,:value)")
     query.bindValue(":control", name)
     query.bindValue(":value", value)
     query.exec_()
Пример #30
0
 def create_table(self):
     """Create table Progression(song, pattern, key) if not exist already"""
     # Tablen nimen saa vaihtaa
     query = QSqlQuery()
     query.prepare("CREATE TABLE IF NOT EXISTS Progressions(song TEXT PRIMARY KEY, pattern TEXT, key TEXT)")
     success = query.exec_()
     if not success:
         pass  # TODO
Пример #31
0
 def _query(self, querystring, **mappings):
     query = QSqlQuery(self.db)
     query.prepare(querystring)
     for key, value in mappings.iteritems():
         bindvalue = ":{}".format(key)
         if bindvalue in querystring:
             query.bindValue(bindvalue, value)
     return query
Пример #32
0
 def set_info(self, image_date, info):
     query = QSqlQuery(self.db)
     query.prepare('INSERT INTO copyright (image_date, copyright_info) VALUES (?, ?);')
     query.addBindValue(image_date)
     query.addBindValue(info)
     if not query.exec_():
         self.error.emit('Error adding copyright info', query.lastError().text())
         return False
     return True
Пример #33
0
 def _query(self, querystring, **mappings):
     querystring = querystring.replace(r"\r\n", " ")
     query = QSqlQuery(self.db)
     query.prepare(querystring)
     for key, value in mappings.iteritems():
         bindvalue = ":{}".format(key)
         if re.search(r"{}\b".format(bindvalue), querystring):
             query.bindValue(bindvalue, value)
     return query
Пример #34
0
 def _query(self, querystring, **mappings):
     querystring = querystring.replace(r"\r\n", " ")
     query = QSqlQuery(self.db)
     query.prepare(querystring)
     for key, value in mappings.iteritems():
         bindvalue = ":{}".format(key)
         if bindvalue in querystring:
             query.bindValue(bindvalue, value)
     return query
Пример #35
0
 def create_table(self):
     """Create table Progression(song, pattern, key) if not exist already"""
     # Tablen nimen saa vaihtaa
     query = QSqlQuery()
     query.prepare(
         "CREATE TABLE IF NOT EXISTS Progressions(song TEXT PRIMARY KEY, pattern TEXT, key TEXT)"
     )
     success = query.exec_()
     if not success:
         pass  # TODO
Пример #36
0
    def ExcluirVeiculo(codigoVeic):
        conn = ConexaoSQL
        db = conn.getConexao()
        db.open()

        query = QSqlQuery()
        query.prepare("DELETE FROM Veiculo WHERE CodigoVeic=:codigoVeic")
        query.bindValue(":codigoVeic", codigoVeic)
        query.exec_()
        db.commit()
Пример #37
0
    def ExcluirCliente(codigoCli):
        conn = ConexaoSQL
        db = conn.getConexao()
        db.open()

        query = QSqlQuery()
        query.prepare("DELETE FROM Cliente WHERE CodigoCli=:codigoCli")
        query.bindValue(":codigoCli", codigoCli)
        query.exec_()
        db.commit()
Пример #38
0
 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)
Пример #39
0
 def remove_song(self, song_name):
     """Removes given song from database
     @param song_name: name of the song to be removed
     @type song_name: String"""
     query = QSqlQuery()
     query.prepare("DELETE FROM Patterns WHERE song == :song")
     query.bindValue(":song", song_name)
     success = query.exec_()
     if not success:
         return  False # TODO
     return True
Пример #40
0
def GetFindSpotNumbers(db, siteNumbers):
    query = QSqlQuery(db)
    sites = u", ".join(u"'{0}'".format(siteNumber) for siteNumber in siteNumbers)
    query.prepare(u"SELECT fundortnummer || '.' || fundstellenummer FROM fundstelle WHERE fundortnummer  IN ({0})".format(sites))
    res = query.exec_()
    query.seek(-1)
    findSpots = []
    while query.next():
        findSpots.append(query.value(0))

    return findSpots
Пример #41
0
    def login(self, username, password):
        sql = 'SELECT username, password FROM users WHERE username=:username AND password=:password'
        q = QSqlQuery()
        q.prepare(sql)
        q.bindValue(':username', username)
        q.bindValue(':password', password)

        q.exec_()
        if q.next():
            print "Sesion correcta para %s" % username
            return True
Пример #42
0
    def save(self):
        resultado = False
        try:
            if not self.database.isOpen():
                if not self.database.open():
                    raise UserWarning( u"No se pudo abrir la conexión "\
                                       + "con la base de datos" )
            
            if not self.database.transaction():
                raise Exception( u"No se pudo comenzar la transacción" )
            
                    
                    
            
            query = QSqlQuery()
            

            if not query.prepare( """
            UPDATE preciosproducto 
            SET activo = 0
            WHERE idproducto = :id;
            """ ):
                raise Exception( "No se pudo preparar la consulta para actualizar" )
            query.bindValue( ":id", self.articuloId)
            
            if not query.exec_():
                raise Exception( "No se pudo desactivar el precio actual" )
            
            
            if not query.prepare( """
            INSERT INTO preciosproducto(idproducto,precio,unidadesxcaja)
            VALUES (:id,:precio,:cantidad);
            """ ):
                raise Exception( "No se pudo preparar la consulta para insertar los nuevos precios" )
            query.bindValue( ":id", self.articuloId)
            query.bindValue( ":precio", self.precio)
            query.bindValue( ":unidades", self.unidades )

            if not query.exec_():
                raise Exception( "No se pudo insertar el nuevo precio" )
     
            if not self.database.commit():
                raise Exception( "No se pudo hacer commit" )

            resultado = True
        except UserWarning as inst:
            self.database.rollback()
            print  unicode( inst ) 
            QMessageBox.critical( self, qApp.organizationName(), unicode( inst ) )
            resultado = False
        finally:
            if self.database.isOpen():
                self.database.close()
        return resultado
Пример #43
0
 def remove_song(self, song_name):
     """Removes given song from database
     @param song_name: name of the song to be removed
     @type song_name: String"""
     query = QSqlQuery()
     query.prepare("DELETE FROM Patterns WHERE song == :song")
     query.bindValue(":song", song_name)
     success = query.exec_()
     if not success:
         return False  # TODO
     return True
Пример #44
0
def updateDefinition(uuid, definition, oc):
    remote_sql = QSqlQuery(get_remote_connection())
    if oc:
        remote_sql.prepare(update_definition_query_OC)
    else:
        remote_sql.prepare(update_definition_query)
    remote_sql.bindValue(":uuid", uuid.__str__())
    remote_sql.bindValue(":definition", definition.__str__())
    if remote_sql.exec_():
        return True
    else:
        return False
Пример #45
0
    def show_termins_in_list(self, page):
        self.currentPage = page
        # фильтрация списка терминов
        search_word = self.searchLineEdit.text()
        if search_word.strip() != '':
            search_str = search_word
        else:
            search_str = ''

        # показ уже обработанных терминов
        if self.showLinkedCheck.isChecked():
            show_linked = 1
        else:
            show_linked = 0

        query = QSqlQuery(self.local_cn)
        LIMIT = int(self.terminsPerPageComboBos.currentText())
        OFFSET = (page - 1) * LIMIT
        query.prepare(show_termins_in_list_query)
        query.bindValue(':search_str', search_str + '%')
        query.bindValue(':linked', show_linked)
        query.bindValue(':limit', LIMIT.__str__())
        query.bindValue(':offset', OFFSET.__str__())
        if query.exec_():
            self.root.takeChildren()
            i = 1
            f = QFont()
            f.setBold(True)
            while query.next():
                c = QTreeWidgetItem()
                c.setText(0, query.value(0))  # Заглавное слово
                c.setData(1, 0, query.value(1))  # uuid
                c.setData(2, 0, i)  # номерок
                if query.value(2) == 1:
                    c.setFont(0, f)
                self.root.addChild(c)
                i += 1

            pages = 1
            query.prepare(show_termins_in_list_count_query)
            query.bindValue(':search_str', search_str + '%')
            query.bindValue(':linked', show_linked)
            if query.exec_() and query.next():
                try:
                    pages = math.ceil(query.value(0) / LIMIT)
                except:
                    pages = 1

            self.draw_paginator(pages, page)
        else:
            print(query.lastError().text())
            print("not exec")
        self.terminsTreeWidget.scrollToTop()
Пример #46
0
    def AtualizarCliente(codigoCli, cliente):
        conn = ConexaoSQL
        db = conn.getConexao()
        db.open()

        query = QSqlQuery()
        query.prepare("UPDATE Cliente SET Nome = '" + cliente.Nome +
                      "', CPF = '" + cliente.CPF + "', Endereco = '" +
                      cliente.Endereco + "', Email = '" + cliente.Email +
                      "', Telefone = '" + cliente.Telefone +
                      "' WHERE CodigoCli = " + codigoCli)
        query.exec_()
        db.commit()
Пример #47
0
def checkTerminExists(uuid, oc):
    remote_sql = QSqlQuery(get_remote_connection())
    if oc:
        remote_sql.prepare(get_definition_query_OC)
    else:
        remote_sql.prepare(get_definition_query)
    remote_sql.bindValue(":uuid", uuid.__str__())
    if remote_sql.exec_():
        if remote_sql.next():
            return True
        else:
            return False
    else:
        return False
Пример #48
0
 def get_song_pattern(self, song_name):
     """Get chord pattern of given song
     @param song_name: name of the song in database
     @type song_name: String
     @return: pattern
     @type return: String"""
     query = QSqlQuery()
     query.prepare("SELECT pattern FROM Progressions WHERE song == :song")
     query.bindValue(":song", song_name)
     success = query.exec_()
     if not success:
         pass  # TODO
     query.next()  # Get only the first result as song name is primary key.
     return query.value(0)  # pattern is the only column in result set.
Пример #49
0
    def CadastrarCliente(cliente):
        conn = ConexaoSQL
        db = conn.getConexao()
        db.open()

        query = QSqlQuery()
        query.prepare(
            "INSERT INTO Cliente(Nome, CPF, Endereco, Email, Telefone) "
            "VALUES (?, ?, ?, ?, ?)")
        query.addBindValue(cliente.Nome)
        query.addBindValue(cliente.CPF)
        query.addBindValue(cliente.Endereco)
        query.addBindValue(cliente.Email)
        query.addBindValue(cliente.Telefone)
        query.exec_()
        db.commit()
Пример #50
0
 def compare_pwd(self):
     """
     function that compares the current password to the one the user is trying to change
     :return: bool True if password matches False if not
     """
     pwd_query = QSqlQuery()
     pwd_str = "SELECT userpwd FROM tblUsers WHERE username =:user"
     pwd_query.prepare(pwd_str)
     pwd_query.bindValue(":user", self.user, QSql.Out)
     pwd_query.exec_()
     pwd_query.first()
     old_pwd = pwd_query.value(0)
     if self.old_input_pwd != old_pwd:
         return False
     else:
         return True
Пример #51
0
 def get_by_key(self, key):
     """Get all chord patterns in given key
     @param key: Song key e.g. C major
     @type key: String
     @return: all patterns in key
     @type return: list"""
     query = QSqlQuery()
     query.prepare("SELECT pattern FROM Progressions WHERE key == :key")
     query.bindValue(":key", key)
     success = query.exec_()
     if not success:
         pass  # TODO
     patterns = []
     while query.next():
         patterns.append(query.value(0))
     return patterns
Пример #52
0
 def insertNewRecord(self, data_dict):
     query = QSqlQuery(self.handle)
     if query.prepare(self.insert_query % data_dict):
         query.bindValue(":photo", data_dict['photo'])
         return query.exec_()
     else:
         return False
Пример #53
0
    def AtualizarVeiculo(codigoVeic, veiculo):
        conn = ConexaoSQL
        db = conn.getConexao()
        db.open()

        query = QSqlQuery()
        query.prepare("UPDATE Veiculo SET Modelo = '" + veiculo.Modelo +
                      "', Marca = '" + veiculo.Marca + "', AnoModelo = " +
                      veiculo.AnoModelo + ", Placa = '" + veiculo.Placa +
                      "', Alugado = '" + veiculo.Alugado + "', Batido = '" +
                      veiculo.Batido + "', KmAtual = '" + veiculo.KmAtual +
                      "', ValorDiaria = '" + veiculo.ValorDiaria +
                      "', Descricao = '" + veiculo.Descricao +
                      "', TipoVeiculo = '" + veiculo.TipoVeiculo +
                      "' WHERE CodigoVeic = " + codigoVeic)
        query.exec_()
        db.commit()
Пример #54
0
 def prepare(self, query_string):
     query = QSqlQuery(self.db)
     query.setForwardOnly(True)
     # weird; QSqlQuery instances don't appear to be GC'ed by the time the DB is closed - so I capture them in a strong-ref'd
     # set and finish them up myself manually.
     self.__refQuery(query)
     if query.prepare(query_string):
         return query
     raise SyntaxError("failed to prepare query, sql was: '{}', db error: {}".format(query_string, query.lastError().text()))
Пример #55
0
    def get_dds_data_list(self, id_objet, topic_name):
        query = QSqlQuery(self.db)

        # "DATA LIKE" to match things like AlarmeV2 even when the topic_name passed is Alarme
        query.prepare(
            'SELECT * FROM DDS WHERE idObjet = :idObjet AND topicName LIKE "{0}%"'
            .format(topic_name))
        query.bindValue(":idObjet", id_objet)
        # query.bindValue(":topicName", topic_name)

        if not query.exec_():
            logging.error("%s", query.lastError().text())

        dds_data_list = []
        while query.next():
            dds_data = unicode(query.record().value(3).toString())
            dds_data_list.append(dds_data)

        return dds_data_list
Пример #56
0
 def add_song(self, song_name, pattern, key):
     """Add a new song to database
     @param song_name: name of the song to be shown in QListView
     @type song_name: String
     @param pattern: chord pattern of the song
     @type pattern: String
     @param key: key of the song. Used as filter parameter
     @type key: String"""
     query = QSqlQuery()
     query.prepare(
         "INSERT INTO Patterns(song, pattern, key) VALUES (:song, :pattern, :key)"
     )
     query.bindValue(":song", song_name)
     query.bindValue(":pattern", pattern)
     query.bindValue(":key", key)
     success = query.exec_()
     if not success:
         return False  # TODO
     return True
Пример #57
0
def getDefinition(uuid, oc):
    remote_sql = QSqlQuery(get_remote_connection())
    if oc:
        remote_sql.prepare(get_definition_query_OC)
    else:
        remote_sql.prepare(get_definition_query)
    remote_sql.bindValue(":uuid", uuid.__str__())
    if remote_sql.exec_():
        if remote_sql.next():
            definition = remote_sql.value(0).__str__()
            definition = definition.replace('&nbsp;', ' ')
            definition = definition.replace(' </A>', '</A> ')
            fs = re.findall(r'<[Aa][^>]*> ', definition)
            for f in fs:
                new_f = f.strip()
                definition = definition.replace(f, new_f)
            return definition
        else:
            return None
    else:
        return None
Пример #58
0
    def updateRecord(self, id_, data_dict):
        # Register id too
        data_dict['id_'] = id_

        # Prepare query
        query = QSqlQuery(self.handle)

        if query.prepare(self.update_query % data_dict):
            query.bindValue(":photo", data_dict['photo'])
            return query.exec_()
        else:
            return False
Пример #59
0
 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)
Пример #60
0
 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)