Python QSqlTableModel.setSort方法代码示例

# 需要导入模块: from PyQt4.QtSql import QSqlTableModel [as 别名]
# 或者: from PyQt4.QtSql.QSqlTableModel import setSort [as 别名]
class ReferenceDataDlg(QDialog):

    def __init__(self, table, title, parent=None):
        super(ReferenceDataDlg, self).__init__(parent)
        self.create_widgets(table)
        self.layout_widgets()
        self.create_connections()
        self.setWindowTitle(
                "Asset Manager - Edit {0} Reference Data".format(title))


    def create_widgets(self, table):
        self.model = QSqlTableModel(self)
        self.model.setTable(table)
        self.model.setSort(NAME, Qt.AscendingOrder)
        self.model.setHeaderData(ID, Qt.Horizontal, QVariant("ID"))
        self.model.setHeaderData(NAME, Qt.Horizontal, QVariant("Name"))
        self.model.setHeaderData(DESCRIPTION, Qt.Horizontal,
                                 QVariant("Description"))
        self.model.select()

        self.view = QTableView()
        self.view.setModel(self.model)
        self.view.setSelectionMode(QTableView.SingleSelection)
        self.view.setSelectionBehavior(QTableView.SelectRows)
        self.view.setColumnHidden(ID, True)
        self.view.resizeColumnsToContents()

        self.addButton = QPushButton("&Add")
        self.deleteButton = QPushButton("&Delete")
        self.okButton = QPushButton("&OK")


    def layout_widgets(self):
        buttonLayout = QHBoxLayout()
        buttonLayout.addWidget(self.addButton)
        buttonLayout.addWidget(self.deleteButton)
        buttonLayout.addStretch()
        buttonLayout.addWidget(self.okButton)
        layout = QVBoxLayout()
        layout.addWidget(self.view)
        layout.addLayout(buttonLayout)
        self.setLayout(layout)


    def create_connections(self):
        self.addButton.clicked.connect(self.addRecord)
        self.deleteButton.clicked.connect(self.deleteRecord)
        self.okButton.clicked.connect(self.accept)


    def addRecord(self):
        row = self.model.rowCount()
        self.model.insertRow(row)
        index = self.model.index(row, NAME)
        self.view.setCurrentIndex(index)
        self.view.edit(index)


    def deleteRecord(self):
        index = self.view.currentIndex()
        if not index.isValid():
            return
        
        record = self.model.record(index.row())
        id = record.value(ID).toInt()[0]
        table = self.model.tableName()
        query = QSqlQuery()
        if table == "deps":
            query.exec_(QString("SELECT COUNT(*) FROM employee "
                                "WHERE deo_id = %1").arg(id))
        elif table == "cities":
            query.exec_(QString("SELECT COUNT(*) FROM employee "
                                "WHERE city_id = %1").arg(id))
        count = 0
        if query.next():
            count = query.value(0).toInt()[0]
        if count:
            QMessageBox.information(self,
                    QString("Delete %1").arg(table),
                    (QString("Cannot delete %1<br>"
                             "from the %2 table because it is used by "
                             "%3 records")
                    .arg(record.value(NAME).toString())
                    .arg(table).arg(count)))
            
            return
        self.model.removeRow(index.row())
        self.model.submitAll()

# 需要导入模块: from PyQt4.QtSql import QSqlTableModel [as 别名]
# 或者: from PyQt4.QtSql.QSqlTableModel import setSort [as 别名]
class StaffDataDlg(QDialog):
    def __init__(self, parent=None):
        super(StaffDataDlg, self).__init__(parent)
        self.create_widgets()
        self.layout_widgets()
        self.create_connections()
        self.setMinimumWidth(850)
        self.setWindowTitle(u"Список сотрудников компании")

    def create_widgets(self):
        self.model = QSqlTableModel(self)
        self.model.setTable("employee")
        self.model.setSort(ID, Qt.AscendingOrder)
        self.model.setHeaderData(ID, Qt.Horizontal, QVariant("ID"))
        self.model.setHeaderData(CITY, Qt.Horizontal, QVariant("City"))
        self.model.setHeaderData(DEPARTMENT, Qt.Horizontal, QVariant("Department"))
        self.model.setHeaderData(FIRSTNAME, Qt.Horizontal, QVariant("First Name"))
        self.model.setHeaderData(LASTNAME, Qt.Horizontal, QVariant("Last Name"))
        self.model.setHeaderData(SEX, Qt.Horizontal, QVariant("sex"))
        self.model.setHeaderData(ADDRESS, Qt.Horizontal, QVariant("Address"))
        self.model.setHeaderData(PID, Qt.Horizontal, QVariant("PID"))

        self.model.setHeaderData(TITLE, Qt.Horizontal, QVariant("Title"))

        self.model.setHeaderData(SOLARY, Qt.Horizontal, QVariant("SOLARY"))
        self.model.setHeaderData(SHIFT, Qt.Horizontal, QVariant("SHIFT"))
        self.model.setHeaderData(HOURS, Qt.Horizontal, QVariant("HOURS"))

        self.model.select()

        self.view = QTableView()
        self.view.setModel(self.model)
        self.view.setSelectionMode(QTableView.SingleSelection)
        self.view.setSelectionBehavior(QTableView.SelectRows)
        self.view.setColumnHidden(ID, True)
        self.view.resizeColumnsToContents()

        self.buttonBox = QDialogButtonBox()
        self.addButton = self.buttonBox.addButton(u"&Добавить", QDialogButtonBox.ActionRole)
        self.deleteButton = self.buttonBox.addButton(u"&Удалить", QDialogButtonBox.ActionRole)
        self.sortButton = self.buttonBox.addButton(u"&Сортировать", QDialogButtonBox.ActionRole)

        menu = QMenu(self)
        self.sortByTitleAction = menu.addAction(u"Сортировка по &Title")
        self.sortBySolaryAction = menu.addAction(u"Сортировка по &SOLARY")
        self.sortByIDAction = menu.addAction(u"Сортировка по &ID")
        self.sortButton.setMenu(menu)
        self.closeButton = self.buttonBox.addButton(QDialogButtonBox.Close)

    def layout_widgets(self):
        layout = QVBoxLayout()
        layout.addWidget(self.view)
        layout.addWidget(self.buttonBox)
        self.setLayout(layout)

    def create_connections(self):
        self.addButton.clicked.connect(self.addRecord)
        self.deleteButton.clicked.connect(self.deleteRecord)

        self.closeButton.clicked.connect(self.accept)

    def addRecord(self):
        row = self.model.rowCount()
        self.model.insertRow(row)
        index = self.model.index(row, TITLE)
        self.view.setCurrentIndex(index)
        self.view.edit(index)

    def deleteRecord(self):
        index = self.view.currentIndex()
        if not index.isValid():
            return
        record = self.model.record(index.row())
        title = record.value(TITLE).toString()
        desc = record.value(SOLARY).toString()
        if (
            QMessageBox.question(
                self,
                "Reference Data",
                QString("Delete %1 from title %2?").arg(desc).arg(title),
                QMessageBox.Yes | QMessageBox.No,
            )
            == QMessageBox.No
        ):
            return
        self.model.removeRow(index.row())
        self.model.submitAll()

# 需要导入模块: from PyQt4.QtSql import QSqlTableModel [as 别名]
# 或者: from PyQt4.QtSql.QSqlTableModel import setSort [as 别名]
class PlayerList(QDialog):
    """QtSQL Model view of the players"""
    def __init__(self, parent):
        QDialog.__init__(self)
        self.parent = parent
        self.model = QSqlTableModel(self, DBHandle.default)
        self.model.setEditStrategy(QSqlTableModel.OnManualSubmit)
        self.model.setTable("player")
        self.model.setSort(1, 0)
        self.model.setHeaderData(1, Qt.Horizontal, QVariant(m18nc("Player", "Name")))
        self.model.setFilter('name not like "ROBOT %" and name not like "Robot %"')
        self.view = MJTableView(self)
        self.view.verticalHeader().show()
        self.view.setModel(self.model)
        self.view.hideColumn(0)
        self.buttonBox = QDialogButtonBox()
        self.buttonBox.setStandardButtons(QDialogButtonBox.Ok | QDialogButtonBox.Cancel)
        self.buttonBox.accepted.connect(self.accept)
        self.buttonBox.rejected.connect(self.reject)
        self.newButton = self.buttonBox.addButton(m18nc('define a new player', "&New"), QDialogButtonBox.ActionRole)
        self.newButton.setIcon(KIcon("document-new"))
        self.newButton.clicked.connect(self.slotInsert)
        self.deleteButton = self.buttonBox.addButton(m18n("&Delete"), QDialogButtonBox.ActionRole)
        self.deleteButton.setIcon(KIcon("edit-delete"))
        self.deleteButton.clicked.connect(self.delete)

        cmdLayout = QHBoxLayout()
        cmdLayout.addWidget(self.buttonBox)
        layout = QVBoxLayout()
        layout.addWidget(self.view)
        layout.addLayout(cmdLayout)
        self.setLayout(layout)

        self.setWindowTitle(m18n("Players") + ' - Kajongg')
        self.setObjectName('Players')

    def showEvent(self, dummyEvent):
        """adapt view to content"""
        if not self.model.select():
            logError("PlayerList: select failed")
            sys.exit(1)
        self.view.initView()
        StateSaver(self, self.view.horizontalHeader())
        if not self.view.isColumnHidden(2):
            # we loaded a kajonggrc written by an older kajongg version where this table
            # still had more columns. This should happen only once.
            self.view.hideColumn(2)
            self.view.hideColumn(3)

    def accept(self):
        """commit all modifications"""
        self.view.selectRow(0) # if ALT-O is entered while editing a new row, this is one way
        # to end editing and to pass the new value to the model
        if not self.model.submitAll():
            Sorry(m18n('Cannot save this. Possibly the name already exists. <br><br>' \
                    'Message from database:<br><br><message>%1</message>',
                    self.model.lastError().text()))
            return
        QDialog.accept(self)

    def slotInsert(self):
        """insert a record"""
        self.model.insertRow(self.model.rowCount())
        self.view.selectRow(self.model.rowCount()-1)

    def delete(self):
        """delete selected entries"""
        sel = self.view.selectionModel()
        maxDel = self.view.currentIndex().row() - 1
        for idx in sel.selectedIndexes():
            if idx.column() != 1:
                continue
            # sqlite3 does not enforce referential integrity.
            # we could add a trigger to sqlite3 but if it raises an exception
            # it will be thrown away silently.
            # if anybody knows how to propagate sqlite3 exceptions via QtSql
            # into python please tell me (wrohdewald)
            player = self.model.createIndex(idx.row(), 0).data().toInt()[0]
            # no query preparation, we don't expect lots of data
            if Query("select 1 from game where p0==%d or p1==%d or p2==%d or p3==%d" % \
                (player, player, player, player)).records:
                Sorry(m18n('This player cannot be deleted. There are games associated with %1.',
                        idx.data().toString()))
            else:
                self.model.removeRow(idx.row())
                maxDel = max(maxDel, idx.row())
        self.view.selectRow(maxDel+1)

    def keyPressEvent(self, event):
        """use insert/delete keys for insert/delete"""
        key = event.key()
        if key == Qt.Key_Insert:
            self.slotInsert()
            return
        QDialog.keyPressEvent(self, event)

# 需要导入模块: from PyQt4.QtSql import QSqlTableModel [as 别名]
# 或者: from PyQt4.QtSql.QSqlTableModel import setSort [as 别名]
class DatabaseInteraction(object):
    """Handles all interactions with the database"""
    def __init__(self, test=False):
        if test:
            filename = os.path.join(CONFIG_TEST_DATABASE_PATH, CONFIG_DATABASE_NAME)
        else:
            filename = os.path.join(CONFIG_DATABASE_PATH, CONFIG_DATABASE_NAME)
        self.conn = QSqlDatabase.addDatabase("QSQLITE")
        self.conn.setDatabaseName(filename)
        result = self.conn.open()
        # Check that the db opened successfully, close program if it didn't
        # TODO: handle this better?
        if result == False:
            print "Failed to open database " + filename + \
                " with error: " + self.conn.lastError().text()
            sys.exit(1)

        self.setupModels()

    def setupModels(self):
        # Participant
        self.participantModel = QSqlTableModel(db=self.conn)
        self.participantModel.setTable("participants")
        self.participantModel.setSort(9, Qt.DescendingOrder)
        self.participantModel.select()
        # set headers
        self.participantModel.setHeaderData(0, Qt.Horizontal, "First")
        self.participantModel.setHeaderData(1, Qt.Horizontal, "Last")
        self.participantModel.setHeaderData(2, Qt.Horizontal, "Address")
        self.participantModel.setHeaderData(3, Qt.Horizontal, "Town")
        self.participantModel.setHeaderData(4, Qt.Horizontal, "Postal Code")
        self.participantModel.setHeaderData(5, Qt.Horizontal, "Home Phone")
        self.participantModel.setHeaderData(6, Qt.Horizontal, "Cell Phone")
        self.participantModel.setHeaderData(7, Qt.Horizontal, "Email")
        self.participantModel.setHeaderData(9, Qt.Horizontal, "Date of Birth")
        self.participantModel.setHeaderData(10, Qt.Horizontal, "School Attending")
        self.participantModel.setHeaderData(11, Qt.Horizontal, "Parent")
        self.participantModel.setHeaderData(12, Qt.Horizontal, "School Grade")
        self.participantModel.setHeaderData(13, Qt.Horizontal, "Group Name")
        self.participantModel.setHeaderData(14, Qt.Horizontal, "Group Size")
        self.participantModel.setHeaderData(15, Qt.Horizontal, "Earliest Performance Time")
        self.participantModel.setHeaderData(16, Qt.Horizontal, "Latest Performance Time")
        self.participantModel.setHeaderData(17, Qt.Horizontal, "Participants")  # TODO display names
        self.participantModel.setHeaderData(18, Qt.Horizontal, "Average Age")
        self.participantModel.setHeaderData(19, Qt.Horizontal, "Contact")  # TODO display name
        # TODO display first piece title for groups

        # Teacher
        self.teacherModel = QSqlTableModel(db=self.conn)
        self.teacherModel.setTable("teachers")
        self.teacherModel.setSort(0, Qt.DescendingOrder)
        self.teacherModel.select()
        # set headers
        self.teacherModel.setHeaderData(1, Qt.Horizontal, "First")
        self.teacherModel.setHeaderData(2, Qt.Horizontal, "Last")
        self.teacherModel.setHeaderData(3, Qt.Horizontal, "Address")
        self.teacherModel.setHeaderData(4, Qt.Horizontal, "Town")
        self.teacherModel.setHeaderData(5, Qt.Horizontal, "Postal Code")
        self.teacherModel.setHeaderData(6, Qt.Horizontal, "Daytime Phone")
        self.teacherModel.setHeaderData(7, Qt.Horizontal, "Evening Phone")
        self.teacherModel.setHeaderData(8, Qt.Horizontal, "Email")

        # Entry
        self.entryModel = QSqlTableModel(db=self.conn)
        self.entryModel.setTable("entries")
        self.entryModel.setSort(0, Qt.DescendingOrder)
        self.entryModel.select()
        # set headers
        self.entryModel.setHeaderData(1, Qt.Horizontal, "Participant")  # TODO display name
        self.entryModel.setHeaderData(2, Qt.Horizontal, "Teacher")  # TODO display name
        self.entryModel.setHeaderData(3, Qt.Horizontal, "Discipline")
        self.entryModel.setHeaderData(4, Qt.Horizontal, "Level")
        self.entryModel.setHeaderData(5, Qt.Horizontal, "Class Number")
        self.entryModel.setHeaderData(6, Qt.Horizontal, "Class Name")
        self.entryModel.setHeaderData(7, Qt.Horizontal, "Instrument")
        self.entryModel.setHeaderData(8, Qt.Horizontal, "Years of Instruction")
        self.entryModel.setHeaderData(9, Qt.Horizontal, "Scheduling Requirements")

        # Piece
        self.pieceModel = QSqlTableModel(db=self.conn)
        self.pieceModel.setTable("selections")
        self.pieceModel.setSort(0, Qt.DescendingOrder)
        self.pieceModel.select()
        # set headers
        self.pieceModel.setHeaderData(1, Qt.Horizontal, "Title")
        self.pieceModel.setHeaderData(2, Qt.Horizontal, "Performance Time")
        self.pieceModel.setHeaderData(3, Qt.Horizontal, "Composer/Arranger")
        self.pieceModel.setHeaderData(5, Qt.Horizontal, "Title of Musical")

    def close(self):
        """Clean everything up and close the connection"""
        connName = self.conn.connectionName()
        self.conn.close()
        self.conn = QSqlDatabase()
        self.conn.removeDatabase(connName)

        global dbInteractionInstance 
        dbInteractionInstance = None

    def backupDb(self):
#.........这里部分代码省略.........

# 需要导入模块: from PyQt4.QtSql import QSqlTableModel [as 别名]
# 或者: from PyQt4.QtSql.QSqlTableModel import setSort [as 别名]
class DataDialog(QDialog):
    def __init__(self, name, table, parent=None):
        super(DataDialog, self).__init__(parent)

        self.name = name

        self.resize(600, 300)

        self.model = QSqlTableModel(self)
        self.model.setTable(table)
        self.model.setSort(ID, Qt.AscendingOrder)
        self.model.setHeaderData(ID, Qt.Horizontal, QVariant(_("Id")))
        self.model.setHeaderData(EMAIL, Qt.Horizontal, QVariant(_("Email")))
        self.model.setHeaderData(WWW, Qt.Horizontal, QVariant(_("WWW")))
        self.model.setHeaderData(PACKAGE, Qt.Horizontal, QVariant(_("Package")))
        self.model.select()

        self.view = QTableView()
        self.view.setModel(self.model)
        self.view.setSelectionMode(QTableView.SingleSelection)
        self.view.setSelectionBehavior(QTableView.SelectRows)
        # self.view.setColumnHidden(ID, True)
        # self.view.resizeColumnsToContents()

        addButton = QPushButton(_("&Add"))
        addButton.setIcon(QIcon(":/add.png"))
        deleteButton = QPushButton(_("&Delete"))
        deleteButton.setIcon(QIcon(":/delete.png"))
        okButton = QPushButton(_("&OK"))
        okButton.setIcon(QIcon(":/quit.png"))

        addButton.setFocusPolicy(Qt.NoFocus)
        deleteButton.setFocusPolicy(Qt.NoFocus)

        buttonLayout = QHBoxLayout()
        buttonLayout.addWidget(addButton)
        buttonLayout.addWidget(deleteButton)
        buttonLayout.addStretch()
        buttonLayout.addWidget(okButton)

        layout = QVBoxLayout()
        layout.addWidget(self.view)
        layout.addLayout(buttonLayout)
        self.setLayout(layout)

        self.connect(addButton, SIGNAL("clicked()"), self.addRecord)
        self.connect(deleteButton, SIGNAL("clicked()"), self.deleteRecord)
        self.connect(okButton, SIGNAL("clicked()"), self.accept)

        self.setWindowTitle(name)

    def addRecord(self):
        row = self.model.rowCount()
        self.model.insertRow(row)
        index = self.model.index(row, EMAIL)
        self.view.setCurrentIndex(index)
        self.view.edit(index)

    def deleteRecord(self):
        index = self.view.currentIndex()
        if not index.isValid():
            return
        self.model.removeRow(index.row())
        self.model.submitAll()

    def accept(self):
        QDialog.accept(self)

# 需要导入模块: from PyQt4.QtSql import QSqlTableModel [as 别名]
# 或者: from PyQt4.QtSql.QSqlTableModel import setSort [as 别名]

#.........这里部分代码省略.........
        self.sTableView.setAlternatingRowColors(True)
        self.sItmSelModel = QItemSelectionModel(self.sModel)
        self.sTableView.setSelectionModel(self.sItmSelModel)
        self.sTableView.setSelectionBehavior(QTableView.SelectRows)
        #self.sTableView.setTabKeyNavigation(True)


        self.fTableView.setModel(self.fModel)
        self.fTableView.setColumnHidden(ID, True)
        self.fTableView.setWordWrap(True)
        self.fTableView.resizeRowsToContents()
        self.fTableView.setAlternatingRowColors(True)
        self.fItmSelModel = QItemSelectionModel(self.fModel)
        self.fTableView.setSelectionModel(self.fItmSelModel)

    def setupModels(self):
        """
            Initialize all the application models
        """
        # setup slaveModel
        self.sModel = ssModel(self)
        self.sModel.setTable(QString("magaslave"))
        self.sModel.setHeaderData(ID, Qt.Horizontal, QVariant("ID"))
        self.sModel.setHeaderData(DATAINS, Qt.Horizontal, QVariant("DataIns"))
        self.sModel.setHeaderData(ABBI, Qt.Horizontal, QVariant("Abbi"))
        self.sModel.setHeaderData(ANGRO, Qt.Horizontal, QVariant("Angro"))
        self.sModel.setHeaderData(DESC, Qt.Horizontal, QVariant("Desc"))
        self.sModel.setHeaderData(QT, Qt.Horizontal, QVariant("Qt"))
        self.sModel.setHeaderData(IMP, Qt.Horizontal, QVariant("Imp"))
        self.sModel.setHeaderData(EQUIV, Qt.Horizontal, QVariant("Equiv"))
        self.sModel.setHeaderData(MMID, Qt.Horizontal, QVariant("ScaffId"))
        self.sModel.setHeaderData(FATT, Qt.Horizontal, QVariant("Fatt"))
        self.sModel.setHeaderData(NOTE, Qt.Horizontal, QVariant("Note"))
        self.sModel.setSort(DATAINS, Qt.AscendingOrder)
        self.sModel.setEditStrategy(QSqlTableModel.OnRowChange)
        self.sModel.select()

        # setup masterModel
        self.mModel = QSqlTableModel(self)
        self.mModel.setTable(QString("magamaster"))
        self.mModel.setSort(SCAFF, Qt.AscendingOrder)
        self.mModel.setHeaderData(ID, Qt.Horizontal, QVariant("ID"))
        self.mModel.setHeaderData(SCAFF, Qt.Horizontal, QVariant("Scaff"))
        self.mModel.select()

        # setup findModel
        self.fModel = QSqlRelationalTableModel(self)
        self.fModel.setTable(QString("magaslave"))
        self.fModel.setHeaderData(ID, Qt.Horizontal, QVariant("ID"))
        self.fModel.setHeaderData(DATAINS, Qt.Horizontal, QVariant("DataIns"))
        self.fModel.setHeaderData(ABBI, Qt.Horizontal, QVariant("Abbi"))
        self.fModel.setHeaderData(ANGRO, Qt.Horizontal, QVariant("Angro"))
        self.fModel.setHeaderData(DESC, Qt.Horizontal, QVariant("Desc"))
        self.fModel.setHeaderData(QT, Qt.Horizontal, QVariant("Qt"))
        self.fModel.setHeaderData(IMP, Qt.Horizontal, QVariant("Imp"))
        self.fModel.setHeaderData(EQUIV, Qt.Horizontal, QVariant("Equiv"))
        self.fModel.setHeaderData(MMID, Qt.Horizontal, QVariant("ScaffId"))
        self.fModel.setHeaderData(FATT, Qt.Horizontal, QVariant("Fatt"))
        self.fModel.setHeaderData(NOTE, Qt.Horizontal, QVariant("Note"))
        self.fModel.setSort(MMID, Qt.AscendingOrder)
        self.fModel.setRelation(MMID, QSqlRelation("magamaster",
                                            "id", "scaff"))
        self.fModel.select()

    def clipCopy(self):
        self.Clipboard = self.sTableView.selectedIndexes()