def delete_project(self, project, row):
     """delete a project from the database & file system if it's empty
     """
     self.log.debug("Attempting to delete project '{}' from database...".format(project))
     q = QSqlQuery()
     count_query = "select count(*) from alleles where project_name = '{}'".format(project)
     q.exec_(count_query)
     check_error(q, self.mydb, self.log)
     alleles = 0
     while q.next():
         alleles = q.value(0)
     if alleles == 0:
         delete_query = "delete from projects where project_name = '{}'".format(project)
         q.exec_(delete_query)
         self.log.debug("\t=> Successfully deleted empty project")
         
         self.log.debug("Attempting to delete project '{}' from file system...".format(project))
         project_dir = os.path.join(self.settings["projects_dir"], project)
         try:
             shutil.rmtree(project_dir)
         except Exception as E:
             self.log.debug("=> File deletion did not work:")
             self.log.error(E)
             self.log.exception(E)
         self.log.debug("=> Project '{}' successfully deleted from database and file system".format(project))
         self.deleted_project.emit()
     else:
         self.log.debug("\t=> Project contains {} alleles => cannot delete!".format(alleles))
Esempio n. 2
0
    def _load(self):
        self.clear()
        count = 0

        query = QSqlQuery(self.db)
        query.prepare("SELECT COUNT(DISTINCT position) "
                      "FROM treeparam WHERE pageid=?")
        query.addBindValue(self.pageId)
        query.exec_()
        if query.first():
            count = query.record().value(0)

        if count:
            for _ in range(count):
                self._params.append([])

            query = QSqlQuery(self.db)
            query.prepare("SELECT * FROM treeparam WHERE pageid=?")
            query.addBindValue(self.pageId)
            query.exec_()

            while query.next():
                record = query.record()
                position = record.value('position')
                fieldId = record.value('fieldid')
                self._params[position].append(self.fields.field(fieldId))
Esempio n. 3
0
    def update_game_properties(self):
        attraction = self.ui.comboBox_Game_Attraction.currentText().strip()
        if len(attraction) > 0:
            attraction_id, _ = attraction.split(" - ", 1)

            query = QSqlQuery()
            query.prepare('SELECT SUM(point), name '
                          'FROM (SELECT t_point.point, t_competitor.name '
                          'FROM t_point INNER JOIN t_competitor ON '
                          't_point.id_competitor = t_competitor.id '
                          'WHERE t_point.id_competitor_take = 1 '
                          'AND t_point.id_attraction = ' + attraction_id + ' '
                          'UNION ALL '
                          'SELECT t_point.point, t_competitor.name '
                          'FROM t_point INNER JOIN t_competitor '
                          'ON t_point.id_competitor_take = t_competitor.id '
                          'WHERE t_point.id_competitor_take != 1 '
                          'AND t_point.id_attraction = ' + attraction_id + ') '
                          'GROUP BY name ORDER BY SUM(point) DESC')
            query.setForwardOnly(1)
            query.exec_()

            points = QTreeWidgetItem()
            points.setText(0, "Points:")

            while query.next():
                point = QTreeWidgetItem()
                point.setText(0, str(query.value(0)) + " - "
                              + query.value(1))
                points.addChild(point)

            self.ui.treeWidget_Game_Properties.clear()
            self.ui.treeWidget_Game_Properties.addTopLevelItem(points)
            self.ui.treeWidget_Game_Properties.expandAll()
 def newJob(self, price, number, text, bId):
     query = QSqlQuery()
     sql = "insert into jobs values((select max(id) from jobs) + 1,'{0}','{1}','{2}','{3}')".format(bId, number,
                                                                                                    price, text)
     query.exec_(sql)
     query.exec_('select hours, price,job,id from jobs order by id desc limit 1')
     return self.extractJobs(query)[0]
Esempio n. 5
0
def execute_query(query, num_columns, log, task, err_type = "Database Error", parent = None):
    """executes a query;
    returns data of a SELECT statement as list of lists;
    reports errors to log and QMessageBox 
        (using task and err_type as message building blocks)
        
    usage example: 
    success, data = db_internal.execute_query(query, 1, self.log, 
                                              "generating the IPD submission number", 
                                              "IPD submission nr error", self)
    """
    log.debug("\tExecuting query {}[...]...".format(query.split("\n")[0][:100]))
    data = []
    success = False
    q = QSqlQuery()
    q.exec_(query)
    
    err_msg = error_in_query(q, task, log)
    if err_msg:
        if parent:
            QMessageBox.warning(parent, err_type, err_msg)
        else:
            data = err_msg
        return success, data
    
    success = True
    while q.next(): # if query has return-data, return it
        row = []
        for i in range(num_columns):
            row.append(q.value(i))
        data.append(row)
    q.finish()
    if data:
        log.debug("\t=> {} records found!".format(len(data)))
    return success, data
Esempio n. 6
0
 def create_model(self):
     """creates the table model
     """
     self.log.debug("Creating the table model...")
     self.model = QSqlQueryModel()
     q = QSqlQuery()
     query = """SELECT project_name, project_nr, 
       (sample_id_int || ' #' || allele_nr || ' (' || gene || ')'),
       local_name,
       allele_status, lab_status,
       sample_id_int, allele_nr
     FROM alleles
     order by project_nr 
      """
     q.exec_(query)
     self.check_error(q)
     self.model.setQuery(q)
     
     self.model.setHeaderData(1, Qt.Horizontal, "Nr")
     self.model.setHeaderData(2, Qt.Horizontal, "Target Allele")
     self.model.setHeaderData(3, Qt.Horizontal, "Allele Name")
     self.model.setHeaderData(4, Qt.Horizontal, "Allele Status")
     self.model.setHeaderData(5, Qt.Horizontal, "Lab Status")
     
     self.log.debug("\t=> Done!")
Esempio n. 7
0
def execute_transaction(queries, mydb, log, task, err_type = "Database Error", parent = None):
    """executes a list of queries in a transaction;
    reports errors to log and QMessageBox 
        (using task and err_type as message building blocks)
    """
    log.debug("\tStarting transaction...")
    success = False
    
    mydb.transaction()
    q = QSqlQuery()
    i = 0
    for query in queries:
        i += 1
        log.debug("\t\tQuery #{}: '{}[...]...'".format(i, query.split("\n")[0][:50]))
        q.exec_(query)
        err_msg = error_in_query(q, task, log)
        if err_msg:
            if parent:
                QMessageBox.warning(parent, err_type, err_msg)
            mydb.rollback()
            return success
    
    success = True
    mydb.commit()
    log.debug("\t=> transaction successful")
    return success
Esempio n. 8
0
    def show_points(self):
        attraction = self.ui.comboBox_Game_Attraction.currentText().strip()
        if len(attraction) > 0:
            attraction_id, _ = attraction.split(" - ", 1)

            query = QSqlQuery()
            query.prepare('SELECT SUM(point), name '
                          'FROM (SELECT t_point.point, t_competitor.name '
                          'FROM t_point INNER JOIN t_competitor ON '
                          't_point.id_competitor = t_competitor.id '
                          'WHERE t_point.id_competitor_take = 1 '
                          'AND t_point.id_attraction = ' + attraction_id + ' '
                          'UNION ALL '
                          'SELECT t_point.point, t_competitor.name '
                          'FROM t_point INNER JOIN t_competitor '
                          'ON t_point.id_competitor_take = t_competitor.id '
                          'WHERE t_point.id_competitor_take != 1 '
                          'AND t_point.id_attraction = ' + attraction_id + ') '
                          'GROUP BY name ORDER BY SUM(point) DESC')
            query.setForwardOnly(1)
            query.exec_()

            points_string = "<h1>Points:<br>"
            while query.next():
                points_string += str(query.value(0)) + " - " + query.value(1) + "<br>"
            points_string += "</h1>"
            self.game_window.show_string(points_string)
    def save(self):
        if self.cleared or self.isNull():
            self.remove()
        else:
            if not self.file:
                self.file = str(uuid.uuid1()) + '.jpg'

                if self.id_:
                    query = QSqlQuery(self.db)
                    query.prepare("UPDATE photos SET file=?, url=? WHERE id=?")
                    query.addBindValue(self.file)
                    query.addBindValue(self.url)
                    query.addBindValue(self.id_)
                    query.exec_()
                else:
                    query = QSqlQuery(self.db)
                    query.prepare("INSERT INTO photos (file, url) VALUES (?, ?)")
                    query.addBindValue(self.file)
                    query.addBindValue(self.url)
                    query.exec_()

                    self.id_ = query.lastInsertId()

            if not self.image.isNull():
                file_name = self._generateFileName(self.file, True)
                self.image.save(file_name)
    def insertRecord(self, row, record):
        self._updateRecord(record)
        record.setNull('id')  # remove ID value from record
        record.setValue('createdat', record.value('updatedat'))

        record.remove(record.indexOf('image_id'))
        image = record.value('image')
        if image:
            query = QSqlQuery(self.database())
            query.prepare("INSERT INTO images (image) VALUES (?)")
            query.addBindValue(image)
            query.exec_()

            img_id = query.lastInsertId()
            record.setValue('image', img_id)
        else:
            record.setNull('image')

        for i in range(4):
            field = "photo%d" % (i + 1)
            photo = record.value(field)
            if not photo.isNull():
                if photo.changed:
                    photo.save()
                photo.image = QtGui.QImage()  # free image
                record.setValue(field, photo.id_)
            else:
                record.setNull(field)

        return super(CollectionModel, self).insertRecord(row, record)
Esempio n. 11
0
    def anime_selection(self, text):
        assert isinstance(text, str)

        self.ui.comboBox_Songs_Anime.clear()
        self.ui.label_Songs_Anime_Count.setText("(0)")

        if len(text) != 0:
            text_split = text.split(" ")
            anime_words = []
            for split in text_split:
                if len(split) != 0:
                    anime_words.append(split)

            query_string = 'SELECT t_anime.id, t_anime.name FROM t_anime WHERE'
            for i in range(len(anime_words)):
                if i == 0:
                    query_string += " t_anime.name LIKE '%'||?||'%'"
                else:
                    query_string += " AND t_anime.name LIKE '%'||?||'%'"

            query = QSqlQuery()
            query.prepare(query_string)

            for i in range(len(anime_words)):
                query.bindValue(i, anime_words[i])

            query.setForwardOnly(1)
            query.exec_()

            query_count = 0
            while query.next():
                query_count += 1
                self.ui.comboBox_Songs_Anime.addItem(str(query.value(0)) + " - " + query.value(1))
            self.ui.label_Songs_Anime_Count.setText("(" + str(query_count) + ")")
Esempio n. 12
0
    def update_songs_properties(self):
        index = self.ui.tableView_Songs.selectedIndexes()
        if len(index) > 0:
            assert isinstance(index[0], QModelIndex)
            row_id = index[0].sibling(index[0].row(), 0).data()

            query = QSqlQuery()
            query.prepare('SELECT t_artist.id, t_artist.name '
                          'FROM t_song '
                          'INNER JOIN t_song_artist ON t_song.id = t_song_artist.id_song '
                          'INNER JOIN t_artist ON t_artist.id = t_song_artist.id_artist '
                          'WHERE t_song.id = ' + str(row_id))
            query.setForwardOnly(1)
            query.exec_()

            songs = QTreeWidgetItem()
            songs.setText(0, "Artists:")

            while query.next():
                song = QTreeWidgetItem()
                song.setText(0, str(query.value(0)) + " - "
                             + query.value(1))
                songs.addChild(song)

            self.ui.treeWidget_Songs_Properties.clear()
            self.ui.treeWidget_Songs_Properties.addTopLevelItem(songs)
            self.ui.treeWidget_Songs_Properties.expandAll()
Esempio n. 13
0
    def artist_selection(self, text):
        assert isinstance(text, str)

        self.ui.listWidget_Songs_Artist_Main.clear()

        if len(text) != 0:
            text_split = text.split(" ")
            artist_words = []
            for split in text_split:
                if len(split) != 0:
                    artist_words.append(split)

            query_string = 'SELECT t_artist.id, t_artist.name FROM t_artist WHERE'
            for i in range(len(artist_words)):
                if i == 0:
                    query_string += " t_artist.name LIKE '%'||?||'%'"
                else:
                    query_string += " AND t_artist.name LIKE '%'||?||'%'"

            query = QSqlQuery()
            query.prepare(query_string)

            for i in range(len(artist_words)):
                query.bindValue(i, artist_words[i])

            query.setForwardOnly(1)
            query.exec_()

            while query.next():
                self.ui.listWidget_Songs_Artist_Main.addItem(str(query.value(0)) + " - " + query.value(1))
Esempio n. 14
0
 def removeRow(self, row):
     record = super().record(row)
     
     ids = []
     for field in ['obverseimg', 'reverseimg', 'edgeimg',
                   'photo1', 'photo2', 'photo3', 'photo4']:
         value = record.value(field)
         if value:
             ids.append(value)
             
     if ids:
         ids_sql = '(' + ','.join('?' * len(ids)) + ')'
         
         query = QSqlQuery(self.database())
         query.prepare("DELETE FROM photos WHERE id IN " + ids_sql)
         for id_ in ids:
             query.addBindValue(id_)
         query.exec_()
         
     value = record.value('image')
     if value:
         query = QSqlQuery(self.database())
         query.prepare("DELETE FROM images WHERE id=?")
         query.addBindValue(value)
         query.exec_()
         
     return super().removeRow(row)
Esempio n. 15
0
 def savePositions(self, pages):
     for position, page in enumerate(pages):
         query = QSqlQuery(self.db)
         query.prepare("UPDATE pages SET position=? WHERE id=?")
         query.addBindValue(position)
         query.addBindValue(page.id)
         query.exec_()
Esempio n. 16
0
    def update(self):
        self._begin()

        self.db.transaction()

        fields = ('emitent', 'signaturetype', 'signature', 'signatureimg',
                  'address', 'latitude', 'longitude')
        for field in fields:
            self._updateRecord()

            fieldDesc = getattr(self.collection.fields, field)
            fieldDesc.enabled = False
            query = QSqlQuery(self.db)
            query.prepare("INSERT INTO fields (id, title, enabled)"
                          " VALUES (?, ?, ?)")
            query.addBindValue(fieldDesc.id)
            query.addBindValue(fieldDesc.title)
            query.addBindValue(int(False))
            query.exec_()

            sql = "ALTER TABLE coins ADD COLUMN %s %s" % (field, Type.toSql(fieldDesc.type))
            QSqlQuery(sql, self.db)

            self.collection.fields.userFields.append(fieldDesc)

        self._updateRecord()

        self.collection.settings['Version'] = 7
        self.collection.settings.save()

        self.db.commit()

        self._finish()
Esempio n. 17
0
    def updateEquipment(self, equipmentLayout):
        if not self.db.open():
            return -1

        self.devicesInfo = []
        for serie in equipmentLayout.getSelectedSeries():
            query = QSqlQuery()
            query.prepare("SELECT d.description, " +
                                 "d.brand, " +
                                 "d.model, " +
                                 "i.serie, " +
                                 "i.id " +
                           "FROM Devices AS d " +
                           "INNER JOIN Items AS i " +
                           "ON d.id = i.device_id " +
                           "WHERE i.serie = ?")
            query.addBindValue(serie)
            query.exec_()
            while query.next():
                device = {}
                self.devicesInfo.append(device)
                device["name"] = str(query.value(0))
                device["brand"] = str(query.value(1))
                device["model"] = str(query.value(2))
                device["serie"] = str(query.value(3))
                device["id"] = str(query.value(4))

        self.db.close()
Esempio n. 18
0
    def update(self):
        self._begin()

        self.db.transaction()

        fields = ['ruler', 'region']
        for field in fields:
            self._updateRecord()

            fieldDesc = getattr(self.collection.fields, field)
            fieldDesc.enabled = False
            query = QSqlQuery(self.db)
            query.prepare("INSERT INTO fields (id, title, enabled)"
                          " VALUES (?, ?, ?)")
            query.addBindValue(fieldDesc.id)
            query.addBindValue(fieldDesc.title)
            query.addBindValue(int(fieldDesc.enabled))
            query.exec_()

            sql = "ALTER TABLE coins ADD COLUMN %s TEXT" % field
            QSqlQuery(sql, self.db)

            self.collection.fields.userFields.append(fieldDesc)

        self.progressDlg.setLabelText(self.tr("Saving..."))

        sql = "UPDATE photos SET title=NULL"
        QSqlQuery(sql, self.db)

        self.collection.settings['Version'] = 4
        self.collection.settings.save()

        self.db.commit()

        self._finish()
Esempio n. 19
0
 def populateCustomers(self):
     query = QSqlQuery()
     self.ui.customerChooser.clear()
     query.exec_('select id, name,address, zipcode from customer')
     while query.next():
         c = self.extractCustomer(query)
         self.ui.customerChooser.addItem(c.name)
    def createReference(self):
        sections = self.reference.allSections()
        progressDlg = Gui.ProgressDialog(self.tr("Updating reference"),
                            self.tr("Cancel"), len(sections), self.parent())

        for columnName in sections:
            progressDlg.step()
            if progressDlg.wasCanceled():
                break

            refSection = self.reference.section(columnName)
            if isinstance(refSection, CrossReferenceSection):
                rel = refSection.model.relationModel(1)
                for i in range(rel.rowCount()):
                    data = rel.data(rel.index(i, rel.fieldIndex('value')))
                    parentId = rel.data(rel.index(i, rel.fieldIndex('id')))
                    query = QSqlQuery(self.db)
                    sql = "SELECT DISTINCT %s FROM coins WHERE %s<>'' AND %s IS NOT NULL AND %s=?" % (columnName, columnName, columnName, refSection.parentName)
                    query.prepare(sql)
                    query.addBindValue(data)
                    query.exec_()
                    refSection.fillFromQuery(parentId, query)
            else:
                sql = "SELECT DISTINCT %s FROM coins WHERE %s<>'' AND %s IS NOT NULL" % (columnName, columnName, columnName)
                query = QSqlQuery(sql, self.db)
                refSection.fillFromQuery(query)

        progressDlg.reset()
Esempio n. 21
0
 def add_project_to_db(self):
     """adds all info about a project to the projects table
     """
     self.log.debug("Adding new project to database...")
     mydate = general.timestamp("%d.%m.%Y")
     query = """INSERT INTO projects VALUES
     ('{}', 'Open', '{}', '{}', '{}', '{}', '{}', '{}', '{}', '{}');
     """.format(self.project_name, mydate, self.user, self.gene, self.pool, 
                self.title, self.description, self.accession_ID, self.submission_ID)
     q = QSqlQuery()
     q.exec_(query)
     
     lasterr = q.lastError()
     if lasterr.isValid():
         self.log.error(lasterr.text())
         if lasterr.text().startswith("UNIQUE constraint failed:"):
             self.project_btn.setText("Such a project exists already!")
             self.project_btn.setStyleSheet(general.btn_style_clickme)
             self.submit_btn.setEnabled(False)
             self.accession_ID = ""
             self.acc_entry.setText(self.accession_ID)
         success = False
     else:
         self.log.debug("=> Added to database successfully")
         success = True
     return success
Esempio n. 22
0
    def save_lists(self, only_if_changed=False):
        if not only_if_changed or self.__lists_changed:
            self.db.transaction()

            # Remove old values
            self.__remove_lists()

            for position, param in enumerate(self.columns):
                query = QSqlQuery(self.db)
                query.prepare("INSERT INTO lists (pageid, fieldid, position,"
                              " enabled, width, sortorder)"
                              " VALUES (?, ?, ?, ?, ?, ?)")
                query.addBindValue(self.page.id)
                query.addBindValue(param.fieldid)
                query.addBindValue(position)
                query.addBindValue(int(param.enabled))
                if not param.enabled:
                    param.width = None
                query.addBindValue(param.width)
                query.addBindValue(param.sortorder)
                query.exec_()

            self.db.commit()

            self.__lists_changed = False
Esempio n. 23
0
    def save_filters(self):
        self.db.transaction()

        # Remove old values
        self.__remove_filters()

        for fieldId, columnFilters in self.filters.items():
            for filter_ in columnFilters.filters():
                query = QSqlQuery(self.db)
                query.prepare("INSERT INTO filters (pageid, fieldid, value,"
                              " blank, data, revert) VALUES (?, ?, ?, ?, ?, ?)")
                query.addBindValue(self.page.id)
                query.addBindValue(fieldId)
                query.addBindValue(filter_.value)
                if filter_.isBlank():
                    blank = int(True)
                else:
                    blank = None
                query.addBindValue(blank)
                if filter_.isData():
                    data = int(True)
                else:
                    data = None
                query.addBindValue(data)
                if filter_.isRevert():
                    revert = int(True)
                else:
                    revert = None
                query.addBindValue(revert)
                query.exec_()

        self.db.commit()
 def getImage(self, img_id):
     query = QSqlQuery(self.database())
     query.prepare("SELECT image FROM images WHERE id=?")
     query.addBindValue(img_id)
     query.exec_()
     if query.first():
         return query.record().value(0)
Esempio n. 25
0
    def update(self):
        self._begin()

        self.db.transaction()

        fields = ('category', 'sort_id')
        for field in fields:
            self._updateRecord()

            fieldDesc = getattr(self.collection.fields, field)
            fieldDesc.enabled = False
            query = QSqlQuery(self.db)
            query.prepare("INSERT INTO fields (id, title, enabled)"
                          " VALUES (?, ?, ?)")
            query.addBindValue(fieldDesc.id)
            query.addBindValue(fieldDesc.title)
            query.addBindValue(int(False))
            query.exec_()

            sql = "ALTER TABLE coins ADD COLUMN %s %s" % (field, Type.toSql(fieldDesc.type))
            QSqlQuery(sql, self.db)

            self.collection.fields.userFields.append(fieldDesc)

        self._updateRecord()

        sql = "UPDATE coins SET sort_id = id"
        QSqlQuery(sql, self.db)

        self.collection.settings['Version'] = 6
        self.collection.settings.save()

        self.db.commit()

        self._finish()
Esempio n. 26
0
 def create_model(self):
     """creates the table model
     """
     q = QSqlQuery(self.query + " " + self.filter)
     self.model = QSqlQueryModel()
     q.exec_(self.query)
     self.model.setQuery(q)
Esempio n. 27
0
    def removePage(self, page):
        page.listParam.remove()
        page.treeParam.remove()

        query = QSqlQuery(self.db)
        query.prepare("DELETE FROM pages WHERE id=?")
        query.addBindValue(page.id)
        query.exec_()
Esempio n. 28
0
 def create_model(self):
     """creates the model as QSqlQueryModel,
     using the given query
     """
     self.model = QSqlQueryModel()
     q = QSqlQuery()
     q.exec_(self.query)
     self.model.setQuery(q)
Esempio n. 29
0
 def reloadEvent(self):
     query = QSqlQuery()
     query.prepare("SELECT id, name, place FROM t_event")
     query.setForwardOnly(1)
     query.exec_()
     while query.next():
         self.comboBox_Event.addItem(str(query.value(0)) + " - " + query.value(1) + " - " + query.value(2))
     pass
Esempio n. 30
0
 def newCustomer(self, name, address, zip):
     query = QSqlQuery()
     sql = "insert into customer values((select max(id) from customer) + 1,'{0}','{1}','{2}')".format(name, address,
                                                                                                      zip)
     query.exec_(sql)
     query.exec_('select id, name,address, zipcode from customer order by id desc limit 1')
     query.next()
     return self.extractCustomer(query)
Esempio n. 31
0
def remove_book_from_collection(book_id, collection):
    err = ''
    q = QSqlQuery(config.db)
    q.prepare(queries.DELETE_BOOK_FROM_COLLECTION)
    q.bindValue(0, collection.id)
    q.bindValue(1, book_id)
    if not q.exec_():
        err = q.lastError().text()
        config.db.rollback()
    else:
        config.db.commit()
    return err
Esempio n. 32
0
    def empty_rule(self, name=""):
        if name == "":
            return
        qstr = "DELETE FROM connections WHERE rule = ?"

        with self._lock:
            q = QSqlQuery(qstr, self.db)
            q.prepare(qstr)
            q.addBindValue(name)
            if not q.exec_():
                print("db, empty_rule() ERROR: ", qstr)
                print(q.lastError().driverText())
Esempio n. 33
0
    def initPlanTable(self):
        index = 0
        query = QSqlQuery()
        query.exec_("""select * from(select *,
(select (capacity - (select count(*) as ReservedSeats from Reservation where planId = tbt.planId)) as Remained from Bus as b where b.id = tbt.busId) as RemainedSeats
from (select id as planId,busId,(select c.name from City as c where c.id == p.originId) as Origin , (select c.name from City as c where c.id == p.destId) as Destination , dptDate , arvDate from Plan as p)
as tbt)as tbt2 where tbt2.RemainedSeats > 0""")

        self.planReserveTable.clear()
        self.planReserveTable.setHorizontalHeaderLabels([
            'Plan Id', 'Bus Id', 'Origin', 'Destination', 'Departure',
            'Arrival', 'Remained Seats'
        ])

        while query.next():
            self.planReserveTable.setRowCount(index + 1)
            for i in range(7):
                self.planReserveTable.setItem(
                    index, i, QTableWidgetItem(str(query.value(i))))

            index += 1
Esempio n. 34
0
    def remove(self, qstr):
        try:
            q = QSqlQuery(qstr, self.db)
            if q.exec_():
                return True
            else:
                print("db, remove() ERROR: ", qstr)
                print(q.lastError().driverText())
        except Exception as e:
            print("db, remove exception: ", e)

        return False
Esempio n. 35
0
 def update_myrequest(self, id, **kwargs):
     query = QSqlQuery(self.db)
     keys = list(kwargs.keys())
     keysstr = ["%s = ?" % (x, ) for x in keys]
     querystr = 'update myrequests set %s where id = ?' % (
         ", ".join(keysstr), )
     query.prepare(querystr)
     for k in keys:
         query.addBindValue(kwargs[k])
     query.addBindValue(id)
     if not query.exec_():
         logger.error(query.lastError().text())
Esempio n. 36
0
    def modelChanged(self):
        filter_ = self.model.filter()
        if filter_:
            sql_filter = "WHERE %s" % filter_
        else:
            sql_filter = ""

        self.points = []
        sql = "SELECT latitude, longitude, id, status FROM coins %s" % sql_filter
        query = QSqlQuery(self.model.database())
        query.exec_(sql)
        while query.next():
            record = query.record()
            lat = record.value(0)
            lng = record.value(1)
            if lat and lng:
                coin_id = record.value(2)
                status = record.value(3)
                self.addMarker(lat, lng, coin_id, status)

        self.showMarkers()
Esempio n. 37
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
Esempio n. 38
0
    def queryDB(self):
        db = QSqlDatabase.addDatabase('QODBC')
        # TODO: Add support for trusted connections.
        # ("Driver={SQLServer};Server=Your_Server_Name;Database=Your_Database_Name;Trusted_Connection=yes;")
        db.setDatabaseName(
            'DRIVER={SQL Server};SERVER=%s;DATABASE=%s;UID=%s;PWD=%s;' %
            ('.', 'Test', 'sa', 'sql'))

        if not db.open():
            print('db.open failed')
            QMessageBox.critical(None, ("无法打开数据库"),
                                 ("无法建立到数据库的连接,这个例子需要SQLite 支持,请检查数据库配置。\n\n"
                                  "点击取消按钮退出应用。"), QMessageBox.Cancel)
            return False

        query = QSqlQuery()
        # query.exec_("create table people(id int primary key, name varchar(20), address varchar(30))")
        query.exec_("select * from people")
        print(str(query.result()))
        db.close()
        return True
Esempio n. 39
0
def add_book_in_collection(book_id, collection):
    err = ''
    q = QSqlQuery(config.db)
    q.prepare(queries.ADD_BOOK_IN_COLLECTION)
    q.bindValue(0, collection.id)
    q.bindValue(1, book_id)
    if not q.exec_():
        err = q.lastError().text()
        config.db.rollback()
    else:
        config.db.commit()
    return err
Esempio n. 40
0
    def getServingAndNeighbors(self):
        self.openConnection()
        MAX_NEIGHBORS = 32
        dataList = []
        condition = ""

        # Set query condition for serving cell
        if self.timeFilter:
            condition = "WHERE time <= '%s'" % (self.timeFilter)

        dataList.append([self.timeFilter, "", "", ""])

        for neighbor in range(1, MAX_NEIGHBORS):
            row = []
            queryString = """SELECT cdma_cell_pn_%d, cdma_ecio_%d, cdma_cell_type_%d
                            FROM cdma
                            %s
                            ORDER BY time DESC
                            LIMIT 1""" % (
                neighbor,
                neighbor,
                neighbor,
                condition,
            )
            query = QSqlQuery()
            query.exec_(queryString)
            if query.first():
                for i in range(3):
                    row.append("" if str(query.value(i)) ==
                               "NULL" else query.value(i))
            else:
                row = ["", "", ""]

            if not all(v == "" for v in row):
                row.insert(0, "")
                dataList.append(row)
        if len(dataList) == 0:
            dataList.append([self.timeFilter, "", "", ""])
        self.closeConnection()
        return dataList
Esempio n. 41
0
    def insertRecord(self, row, record):
        self._updateRecord(record)
        record.setNull('id')  # remove ID value from record
        record.setValue('createdat', record.value('updatedat'))

        for field in [
                'obverseimg', 'reverseimg', 'edgeimg', 'photo1', 'photo2',
                'photo3', 'photo4'
        ]:
            value = record.value(field)
            if value:
                query = QSqlQuery(self.database())
                query.prepare(
                    "INSERT INTO photos (title, image) VALUES (?, ?)")
                query.addBindValue(record.value(field + '_title'))
                query.addBindValue(value)
                query.exec_()

                img_id = query.lastInsertId()
            else:
                img_id = None

            record.setValue(field, img_id)
            record.remove(record.indexOf(field + '_id'))
            record.remove(record.indexOf(field + '_title'))

        value = record.value('image')
        if value:
            query = QSqlQuery(self.database())
            query.prepare("INSERT INTO images (image) VALUES (?)")
            query.addBindValue(value)
            query.exec_()

            img_id = query.lastInsertId()
        else:
            img_id = None
        record.setValue('image', img_id)
        record.remove(record.indexOf('image_id'))

        return super(CollectionModel, self).insertRecord(row, record)
Esempio n. 42
0
def initda():
    print(111)
    db = QSqlDatabase.addDatabase("QSQLITE")
    db.setDatabaseName("./db/database.db")
    query = QSqlQuery()
    query.exec_("INSERT INTO 深渊统计 (日期, 图, 角色, 深渊次数, 爆货次数, 灵魂爆数,加百利次数, 晶石, SS1,SS2,SS3,SS4,SS5,SS6,SS7,SS8) VALUES "
                "('2018-05-07', '裂缝','hehe',1,2,3,4,5,'SS1','SS2','SS3','SS4','SS5','','','')")
    print(222)
    query.exec_("INSERT INTO 深渊统计 (日期, 图, 角色, 深渊次数, 爆货次数, 灵魂爆数,加百利次数, 晶石, SS1,SS2,SS3,SS4,SS5,SS6,SS7,SS8) VALUES "
                "('2018-05-07', '裂缝','123',1,2,3,4,5,'SS1','SS2','SS3','SS4','SS5','','','')")
    query.exec_("INSERT INTO 深渊统计 (日期, 图, 角色, 深渊次数, 爆货次数, 灵魂爆数,加百利次数, 晶石, SS1,SS2,SS3,SS4,SS5,SS6,SS7,SS8) VALUES "
                "('2018-05-07', '裂缝','124',1,2,3,4,5,'SS1','SS2','SS3','SS4','SS5','','','')")
    query.exec_("INSERT INTO 深渊统计 (日期, 图, 角色, 深渊次数, 爆货次数, 灵魂爆数,加百利次数, 晶石, SS1,SS2,SS3,SS4,SS5,SS6,SS7,SS8) VALUES "
                "('2018-05-07', '裂缝','453',1,2,3,4,5,'SS1','SS2','SS3','SS4','SS5','','','')")
Esempio n. 43
0
    def btnShow(self):
        print('btnShow')
        db = QSqlDatabase.addDatabase('QODBC')
        # TODO: Add support for trusted connections.
        # ("Driver={SQLServer};Server=Your_Server_Name;Database=Your_Database_Name;Trusted_Connection=yes;")
        db.setDatabaseName(
            'DRIVER={SQL Server};SERVER=%s;DATABASE=%s;UID=%s;PWD=%s;' %
            ('.', 'Test', 'sa', 'sql'))
        # 判断是否打开
        if not db.open():
            QMessageBox.critical(
                None, ("Cannot open database"),
                ("Unable to establish a database connection. \n"
                 "This example needs SQLite support. Please read "
                 "the Qt SQL driver documentation for information "
                 "how to build it.\n\n"
                 "Click Cancel to exit."), QMessageBox.Cancel)
            return False
        print('btnShow2')
        # 声明数据库查询对象
        query = QSqlQuery()
        # 创建表
        query.exec_(
            "create table student(id int primary key, name vchar, sex vchar, age int, deparment vchar)"
        )
        # 添加记录
        query.exec_("insert into student values(1,'张三1','男',20,'计算机')")
        query.exec_("insert into student values(2,'李四1','男',19,'经管')")
        query.exec_("insert into student values(3,'王五1','男',22,'机械')")

        # 关闭数据库
        db.close()

        return True
Esempio n. 44
0
 def fill_db(self):
     query = QSqlQuery(db=self.db)
     with open('/home/fenrir/tmp/sha.txt') as book:
         text = book.read()
         text = text[:int(len(text) / 20)]
         num_s = 0
         num_w = 0
         while text:
             ends = '.!?'
             endn = [text.find(e) for e in ends if e in text]
             if endn:
                 sentence = text[:min(endn) + 1]
                 text = text[min(endn) + 1:]
             else:
                 sentence = text
                 text = ''
             q4 = 'insert into sentences values (%s, "%s", %s)' % (
                 num_s, sentence, 0)
             query.exec_(q4)
             words = sentence.split(' ')
             for word in words:
                 q5 = 'insert into words values (%s, "%s", %s)' % (
                     num_w, word, num_s)
                 query.exec_(q5)
                 q6 = 'insert into domains values (%s, "%s", %s)' % (
                     num_w, word, num_w)
                 query.exec_(q6)
                 num_w += 1
             num_s += 1
Esempio n. 45
0
def registrar_cliente(**kwargs):
    data_formatada = datetime.strptime(kwargs["data_nascimento"], "%d/%m/%Y")

    try:
        sql_cliente = f"""INSERT INTO cliente 
        (nome, email, senha, genero, numero_conta, data_nascimento, data_registro) 
        VALUES
        ('{kwargs["nome"]}', '{kwargs["email"]}', '{kwargs["senha"]}', '{kwargs["genero"]}', 
        {kwargs["numero_da_conta"]}, '{data_formatada}', '{datetime.now()}')"""

        q = QSqlQuery()
        q.exec_(sql_cliente)
        print(sql_cliente)

        mensagem = q.result().lastError().text()
        if mensagem:
            print(q.result().lastError().text())
            return False, mensagem
        else:
            sql_conta = f"""INSERT INTO conta
            (numero_conta, agencia, tipo_conta, saldo, data_registro)
            VALUES 
            ({kwargs["numero_da_conta"]}, '{kwargs["agencia"]}', '{kwargs["tipo_da_conta"]}',
            {kwargs["saldo"]}, '{datetime.now()}');"""

            qc = QSqlQuery()
            qc.exec_(sql_conta)
            print(sql_conta)

            mensagem = qc.result().lastError().text()
            if not mensagem:
                return True, mensagem
            else:
                print(qc.result().lastError().text())
                print(2)
                return False, ''

    except QSqlError as qc:
        print(qc.result().lastError().text())
        print('erro ao registrar o cliente')
    def CadastrarVeiculo(veiculo):
        conn = ConexaoSQL
        db = conn.getConexao()
        db.open()

        query = QSqlQuery()
        query.prepare("INSERT INTO Veiculo(Modelo, Marca, AnoModelo, Placa, "
                      "Alugado, Batido, KmAtual, ValorDiaria, Descricao, TipoVeiculo) "
                      "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)")
        query.addBindValue(veiculo.Modelo)
        query.addBindValue(veiculo.Marca)
        query.addBindValue(veiculo.AnoModelo)
        query.addBindValue(veiculo.Placa)
        query.addBindValue(veiculo.Alugado)
        query.addBindValue(veiculo.Batido)
        query.addBindValue(veiculo.KmAtual)
        query.addBindValue(veiculo.ValorDiaria)
        query.addBindValue(veiculo.Descricao)
        query.addBindValue(veiculo.TipoVeiculo)

        query.exec_()
        db.commit()
Esempio n. 47
0
def del_DB(ui, window, db, table, identifier, idvalue, widget_list):
    db.open()
    #Prepare a query and add all values then execute and commit to DB
    query = QSqlQuery(db)
    db.transaction()
    query.prepare(f"DELETE FROM {table} WHERE {identifier}")
    for i in idvalue:
        query.addBindValue(i)
    try:
        query.exec_()
        db.commit()
        db.close()
        print(query.lastQuery())
        QtWidgets.QMessageBox.information(
            window, f"{table} deleted",
            f"{table} has been successfully deleted", QtWidgets.QMessageBox.Ok)
    except Exception:
        QtWidgets.QMessageBox.critical(window, 'Error', 'Something went wrong',
                                       QtWidgets.QMessageBox.Ok)
    finally:
        for i in widget_list:
            i.clear()
Esempio n. 48
0
def add_DB(ui, window, db, table, vlist, placeholderstr, widget_list):
    db.open()
    #Prepare a query and add all values then execute and commit to DB
    query = QSqlQuery(db)
    db.transaction()
    query.prepare(f"INSERT INTO {table} VALUES({placeholderstr})")
    for i in vlist:
        query.addBindValue(i)
    try:
        query.exec_()
        db.commit()
        db.close()
        QtWidgets.QMessageBox.information(
            window, f"New {table} created",
            f"New {table} has been successfully created",
            QtWidgets.QMessageBox.Ok)
    except Exception:
        QtWidgets.QMessageBox.critical(window, 'Error', 'Something went wrong',
                                       QtWidgets.QMessageBox.Ok)
    finally:
        for i in widget_list:
            i.clear()
Esempio n. 49
0
 def find(self, **args):
     condition = []
     for key, value in args.items():
         if key not in ['id', 'classid', 'courseid', 'exam_weight']:
             condition.append("{0}='{1}'".format(key, value))
         else:
             condition.append("{0}={1}".format(key, value))
     And = " and ".join(condition)
     model = QSqlQuery()
     model.exec_('PRAGMA foreign_keys = ON;')
     if condition != []:
         sql = 'select * from exam where {};'.format(And)
     else:
         sql = 'select * from exam'
     model.exec_(sql)
     res = []
     while model.next():
         #print((model.value(0),model.value(1),model.value(2)))
         res.append((model.value(0), model.value(1), model.value(2),
                     model.value(3), model.value(4), model.value(5),
                     model.value(6), model.value(7)))
     return res
Esempio n. 50
0
def db_dac_register_data_to_dictionary(db, regname, dac_num):
    tablename = 'tbldacRegBits'
    query = QSqlQuery(db)

    qrytxt = "select {name}, {mask}, {shift}, {value} ,{parent} from {tn} inner join tbldacRegisters on " \
             "{parent} = tbldacRegisters.ADDRESS where tbldacRegisters.NAME = '{rn}' and dac_num = {dac_num}" \
        .format(name=tablename + ".NAME", mask=tablename + ".MASK", shift=tablename + ".SHIFT",
                value=tablename + ".VALUE", parent=tablename + ".FK_PARENT_ID", tn=tablename, rn=regname, dac_num=dac_num)
    data_dict = {}
    data = []
    query.exec_(qrytxt)

    while query.next():
        data_dict["NAME"] = query.value(0)
        data_dict["MASK"] = query.value(1)
        data_dict["SHIFT"] = query.value(2)
        data_dict["VALUE"] = query.value(3)
        data_dict["PK_PAPRENT_ID"] = query.value(4)

        data.append(data_dict)
        data_dict = {}
    return data
Esempio n. 51
0
 def insertCity(self,name):
     if name != "" and name not in self.cities.keys():
          query = QSqlQuery()
          query.prepare("insert into City(name) values(:cityName)")
          query.bindValue(":cityName" , name)
          if query.exec_() == False:
              criticalMessage("Could not insert into City table")
          else:
              self.initCityTable()
              self.cityNameLineEdit.clear()
              self.initReservationForm()
     else:
         criticalMessage("Could not insert into City table")
Esempio n. 52
0
def log_transferir(dados):
    numero_conta = dados['numero_conta_s']
    conta_destino = dados['numero_conta_d']
    valor = dados['valor']
    data = str(datetime.now())

    saldo_atual = get_saldo(numero_conta)
    saldo = saldo_atual + valor

    sql_t = f"""INSERT INTO historico (numero_conta, valor, saldo_anterior, saldo_atual, 
    operacao, conta_destino, data)
    VALUES ({numero_conta},{valor}, {saldo}, {saldo_atual}, 
    'transferir' ,{conta_destino} , '{data}')"""

    try:
        q = QSqlQuery()
        q.exec_(sql_t)
        print(sql_t)

    except QSqlError as qc:
        print(qc.result().lastError().text())
        print(err_deposito)
Esempio n. 53
0
def registrar_funcionario(dados):
    data_formatada = datetime.strptime(dados["data_nascimento"], "%d/%m/%Y")
    data_atual = datetime.now()

    try:
        sql = f"""INSERT INTO funcionario (nome, email, senha, cargo, salario, data_nascimento, data_registro) 
        VALUES('{dados["nome"]}', '{dados["email"]}', '{dados["senha"]}', '{dados["cargo"]}', {dados["salario"]}, 
        '{data_formatada}', '{data_atual}')"""

        q = QSqlQuery()
        q.exec_(sql)
        print(sql)

        if q.result().lastError().text():
            mensagem = q.result().lastError().text()
            print(mensagem)
            return False, mensagem
        else:
            return True, ''

    except QSqlError as qc:
        print(qc.result().lastError().text())
Esempio n. 54
0
    def _generateWeatherCode(self, weatherCode):
        categories = ["Low Cloud Amount", "Visibility Kilometres", "Low Cloud Height", "Weather", "Remarks Mission", "Remarks Weather"]
        query = QSqlQuery(self.dbm.db)
        pos = 0
        help = 0
        weatherDescription = ""
        for c in weatherCode:
            qryStr = "select description from wetter where category = '{0}' and code = '{1}' limit 1".format(categories[pos - help], c)
            query.exec_(qryStr)
            query.first()
            fn = query.value(0)
            if pos <= 5:
                weatherDescription += categories[pos] + ': ' + fn
                if pos < 5:
                    weatherDescription += '\n'
            else:
                weatherDescription += '; ' + fn

            if pos >= 5:
                help += 1
            pos += 1
        return weatherDescription
Esempio n. 55
0
    def init(self) -> bool:
        """
        Initialise Albums table.
        It creates the table if it does not exists yet
        :return: bool - initialization was successful, else false if initialization were unsuccessful
        """
        if self.TABLE_NAME not in self._conn.tables():
            query = QSqlQuery(self._conn)
            return query.exec_(
                "create table %s (id integer primary key autoincrement, name text)"
                % self.TABLE_NAME)

        return True
Esempio n. 56
0
def load_spatialite():
    queries = (
        "SELECT load_extension('mod_spatialite')",
        "SELECT InitSpatialMetadata(1)",
    )
    q = QSqlQuery()
    for query in queries:
        if not q.exec_(query):
            print(
                f"Error: cannot load the Spatialite extension ({q.lastError().text()})"
            )
            return False
    return True
    def __init__(self, db, parent=None):
        super().__init__(parent)
        self.db = db

        if 'fields' not in self.db.tables():
            self.create(self.db)

        query = QSqlQuery(self.db)
        query.prepare("SELECT * FROM fields")
        query.exec_()
        self.userFields = []
        self.disabledFields = []
        while query.next():
            record = query.record()
            fieldId = record.value('id')
            field = self.field(fieldId)
            field.title = record.value('title')
            field.enabled = bool(record.value('enabled'))
            if field.enabled:
                self.userFields.append(field)
            else:
                self.disabledFields.append(field)
Esempio n. 58
0
    def dumpCache(self):
        self.db.database(self.connectionName, False)
        # just to close the connection I use False as the second parameter
        query = QSqlQuery(self.db)
        isDeleteSuccessful = query.exec_("drop table cache")

        if isDeleteSuccessful:
            print("the table has been deleted successfully")
        else:
            print("the table could not be deleted")
            print("error:")
            print(query.lastError().text())
        return True
    def create(db=QSqlDatabase()):
        db.transaction()

        sql = """CREATE TABLE fields (
            id INTEGER NOT NULL PRIMARY KEY,
            title TEXT,
            enabled INTEGER)"""
        QSqlQuery(sql, db)

        fields = CollectionFieldsBase()

        for field in fields:
            query = QSqlQuery(db)
            query.prepare("""INSERT INTO fields (id, title, enabled)
                VALUES (?, ?, ?)""")
            query.addBindValue(field.id)
            query.addBindValue(field.title)
            enabled = field in fields.userFields
            query.addBindValue(int(enabled))
            query.exec_()

        db.commit()
Esempio n. 60
0
 def submit(self):
     self.text = self.textedit.toPlainText()
     self.feedbackid = 'f' + str(time.strftime("%g%m%d")) + str(
         random.randint(0, 9999)).zfill(4)
     now = int(time.time())
     timeStruct = time.localtime(now)
     self.strTime = time.strftime("%Y/%m/%d %H:%M", timeStruct)
     if (len(self.text) < 15):
         print(
             QMessageBox.warning(self, "警告", "无法提交少于15字的反馈。",
                                 QMessageBox.Yes, QMessageBox.Yes))
         return
     if (len(self.text) > 200):
         print(
             QMessageBox.warning(self, "警告", "无法提交大于200字的反馈",
                                 QMessageBox.Yes, QMessageBox.Yes))
         return
     db = QSqlDatabase.addDatabase("QSQLITE")
     db.setDatabaseName('./db/myPet.db')
     db.open()
     query = QSqlQuery()
     sql = "select * from feedback where FeedbackId = '%s'" % (
         self.feedbackid)
     query.exec_(sql)
     if (query.next()):  # 反馈编号已存在
         print(
             QMessageBox.warning(self, "警告", "系统错误,请重新提交", QMessageBox.Yes,
                                 QMessageBox.Yes))
         return
     else:
         sql = "insert into feedback values('%s','%s','%s','未读','%s')" % (
             self.feedbackid, self.UserId, self.strTime, self.text)
         query.exec_(sql)
         db.commit()
         db.close()
         print(
             QMessageBox.information(self, "提醒", "您已成功提交反馈信息!",
                                     QMessageBox.Yes, QMessageBox.Yes))
         self.textedit.setText("")