def showMembsPayments(): cmd = 'SELECT M.FirstName, M.LastName, Amount, Date, Type, Comment FROM FinancialTrans F, Member M WHERE M.ID={0} AND F.MID=M.ID ORDER BY DATE ASC'.format( self.parent.currentMember.ID) query = self.db.execQuery( cmd, 'TabTransMan.py -> showMembsPayments -> showPaymentWindow') model = QtSql.QSqlQueryModel() model.setQuery(query) model.setHeaderData(0, QtCore.Qt.Horizontal, 'First Name') model.setHeaderData(1, QtCore.Qt.Horizontal, 'Last Name') model.setHeaderData(2, QtCore.Qt.Horizontal, 'Ammount') model.setHeaderData(3, QtCore.Qt.Horizontal, 'Date') model.setHeaderData(4, QtCore.Qt.Horizontal, 'Type') model.setHeaderData(5, QtCore.Qt.Horizontal, 'Comment') self.viewMembers = QtGui.QTableView(None) self.viewMembers.setWindowTitle('Member Transactions') self.viewMembers.setWindowIcon(self.parent.icon) self.viewMembers.resize(600, 500) self.viewMembers.setModel(model) self.viewMembers.setEditTriggers( QtGui.QAbstractItemView.NoEditTriggers) self.viewMembers.setSelectionMode( QtGui.QAbstractItemView.SingleSelection) self.viewMembers.setSelectionBehavior( QtGui.QAbstractItemView.SelectRows) self.viewMembers.resizeColumnsToContents() self.viewMembers.show()
def show_shit_list(self): query = self.db.getShitListQuery() model = QtSql.QSqlQueryModel() model.setQuery(query) model.setHeaderData(0, QtCore.Qt.Horizontal, 'Member First Name') model.setHeaderData(1, QtCore.Qt.Horizontal, 'Member Last Name') model.setHeaderData(2, QtCore.Qt.Horizontal, 'Gear Name') model.setHeaderData(3, QtCore.Qt.Horizontal, 'Gear ID') model.setHeaderData(4, QtCore.Qt.Horizontal, 'Check Out Date/Time') model.setHeaderData(5, QtCore.Qt.Horizontal, 'Due Date') self.viewShitList.setWindowTitle('Shit List') self.viewShitList.setWindowIcon(self.parent.icon) self.viewShitList.resize(600, 500) self.viewShitList.setModel(model) self.viewShitList.setEditTriggers( QtGui.QAbstractItemView.NoEditTriggers) self.viewShitList.setSelectionMode( QtGui.QAbstractItemView.SingleSelection) self.viewShitList.setSelectionBehavior( QtGui.QAbstractItemView.SelectRows) self.viewShitList.resizeColumnsToContents() self.viewShitList.show()
def openTable(self): selectedItem = self.ui.treeWidgetDB.currentItem() table = str(selectedItem.text(0)) cmd = "SELECT * FROM %s" % table model = QtSql.QSqlQueryModel() model.setQuery(cmd) self.ui.tableViewTable.setModel(model)
def createModel(self): self.model = QtSql.QSqlQueryModel() query = QtSql.QSqlQuery() query.exec_("Select * from Cars") self.model.setQuery(query) self.model.removeColumn(0) self.model.setHeaderData(0, QtCore.Qt.Horizontal, "Name") self.model.setHeaderData(1, QtCore.Qt.Horizontal, "Price")
def button_show_gear_history_table(self): # Clear the message window self.parent.set_stat_message() if self.parent.currentGear: cmd = ( 'SELECT (M1.FirstName||' '||M1.LastName), (M2.FirstName||' '||M2.LastName), G.Name, ' 'strftime("%m/%d/%Y %H:%M:%S", AT.CheckOutDateTime), strftime("%m/%d/%Y", AT.DueDate), ' 'strftime("%m/%d/%Y %H:%M:%S", AT.CheckInDateTime) ' 'FROM ArchiveTrans AT, Member M1, Member M2, Gear G WHERE AT.GID="{GID}" ' 'AND AT.MID_OUT=M1.ID AND AT.GID=G.ID AND AT.MID_IN=M2.ID ' 'ORDER BY AT.CheckInDateTime ASC').format( GID=self.parent.currentGear.ID) query = self.db.execQuery(cmd, 'TabGearMan.py -> showGearHist') model = QtSql.QSqlQueryModel() model.setQuery(query) model.setHeaderData(0, QtCore.Qt.Horizontal, 'Rented By') model.setHeaderData(1, QtCore.Qt.Horizontal, 'Returned By') model.setHeaderData(2, QtCore.Qt.Horizontal, 'Gear Name') model.setHeaderData(3, QtCore.Qt.Horizontal, 'Checkout Date/Time') model.setHeaderData(4, QtCore.Qt.Horizontal, 'DueDate') model.setHeaderData(5, QtCore.Qt.Horizontal, 'Checkin Date/Time') self.view_gear_hist.setWindowTitle('Gear Transaction History') self.view_gear_hist.setWindowIcon(self.parent.icon) self.view_gear_hist.resize(600, 500) self.view_gear_hist.setModel(model) self.view_gear_hist.setEditTriggers( QtGui.QAbstractItemView.NoEditTriggers) self.view_gear_hist.setSelectionMode( QtGui.QAbstractItemView.SingleSelection) self.view_gear_hist.setSelectionBehavior( QtGui.QAbstractItemView.SelectRows) self.view_gear_hist.clicked.connect( self.get_gear_id_from_gear_history_table) self.view_gear_hist.resizeColumnsToContents() self.view_gear_hist.show() else: self.parent.set_stat_message(Util.noActiveGear, c='red') if self.gDissAmbSearch.count( ) > 1 and self.gDissAmbSearch.currentIndex() == 1: self.gDissAmbSearch.showPopup()
def Button_showTrans(self): # Clear the message window self.parent.set_stat_message() cmd = 'SELECT M.FirstName, M.LastName, strftime("%m/%d/%Y", M.Birthday), G.Name, T.GID, ' \ 'strftime("%m/%d/%Y %H:%M:%S", T.CheckOutDateTime), strftime("%m/%d/%Y", T.DueDate) ' \ 'FROM Transactions T ' \ 'JOIN Member M ON T.MID=M.ID ' \ 'JOIN Gear G ON T.GID=G.ID'# ORDER BY T.DueDate ASC') query = self.db.execQuery(cmd, 'database.py -> showTrans') model = QtSql.QSqlQueryModel() model.setQuery(query) model.setHeaderData(0, QtCore.Qt.Horizontal, 'First Name') model.setHeaderData(1, QtCore.Qt.Horizontal, 'Last Name') model.setHeaderData(2, QtCore.Qt.Horizontal, 'Birthday') model.setHeaderData(3, QtCore.Qt.Horizontal, 'Gear Name') model.setHeaderData(4, QtCore.Qt.Horizontal, 'Gear ID') model.setHeaderData(5, QtCore.Qt.Horizontal, 'Check Out Date/Time') model.setHeaderData(6, QtCore.Qt.Horizontal, 'Due Date') self.viewTransactions = QtGui.QTableView() self.viewTransactions.setWindowTitle('Active Transactions List') self.viewTransactions.setWindowIcon(self.parent.icon) self.viewTransactions.resize(600, 500) self.viewTransactions.setModel(model) self.viewTransactions.setEditTriggers( QtGui.QAbstractItemView.NoEditTriggers) self.viewTransactions.setSelectionMode( QtGui.QAbstractItemView.SingleSelection) self.viewTransactions.setSelectionBehavior( QtGui.QAbstractItemView.SelectRows) self.viewTransactions.clicked.connect( self.get_member_id_and_gear_id_from_transaction_table) self.viewTransactions.resizeColumnsToContents() self.viewTransactions.setSortingEnabled(True) # self.viewTransactions.sortByColumn(7, QtCore.Qt.AscendingOrder) self.viewTransactions.show()
def executeSQL(self): cmd = self.ui.txtSqlEdit.text() model = QtSql.QSqlQueryModel() model.setQuery(cmd) self.ui.tableViewTable.setModel(model)
def __init__(self): super(MainForm, self).__init__() #Database: self.db = QtSql.QSqlDatabase.addDatabase("QMYSQL") self.db.setHostName("localhost") self.db.setDatabaseName("voc-app-db-test") self.db.setUserName("root") self.db.setPassword("passwd") ok = self.db.open() if not ok: QtGui.QMessageBox.warning(self, "Error", "Invalid database!") return # GUI: hbox = QtGui.QHBoxLayout(self) self.setLayout(hbox) left_vbox = QtGui.QVBoxLayout() right_vbox = QtGui.QVBoxLayout() grid = QtGui.QGridLayout() grid.setSpacing(10) self.db_table = QtGui.QTableView(self) hbox.addLayout(left_vbox) hbox.addLayout(right_vbox) left_vbox.addWidget(self.db_table) left_vbox.addLayout(grid) label_fn = QtGui.QLabel('First Name:') label_ln = QtGui.QLabel('Last Name:') self.e_fn = QtGui.QLineEdit() self.e_ln = QtGui.QLineEdit() grid.addWidget(label_fn, 1, 0) grid.addWidget(self.e_fn, 1, 1) grid.addWidget(label_ln, 2, 0) grid.addWidget(self.e_ln, 2, 1) self.clear_btn = QtGui.QPushButton("Clear") self.delete_btn = QtGui.QPushButton("Delete") self.refresh_btn = QtGui.QPushButton("Refresh") self.insert_btn = QtGui.QPushButton("Insert") self.update_btn = QtGui.QPushButton("Update") self.exit_btn = QtGui.QPushButton("Exit") right_vbox.addWidget(self.clear_btn) right_vbox.addWidget(self.delete_btn) right_vbox.addWidget(self.refresh_btn) right_vbox.addStretch() right_vbox.addWidget(self.insert_btn) right_vbox.addWidget(self.update_btn) right_vbox.addWidget(self.exit_btn) self.setGeometry(300, 100, 800, 600) self.setWindowTitle('Simpsons') #Event Listeners: self.refresh_btn.clicked.connect(self.refresh_btn_clicked) self.insert_btn.clicked.connect(self.insert_btn_clicked) self.delete_btn.clicked.connect(self.delete_btn_clicked) self.clear_btn.clicked.connect(self.clear_btn_clicked) self.update_btn.clicked.connect(self.update_btn_clicked) self.exit_btn.clicked.connect(self.exit_btn_clicked) #Table: self.model = QtSql.QSqlQueryModel() self.model.setQuery("SELECT * FROM simpsons") print '0 rowcount = {}'.format(self.model.rowCount()) print '0 columncount = {}'.format(self.model.columnCount()) self.model.setHeaderData(1, QtCore.Qt.Horizontal, self.tr("First Name")) self.model.setHeaderData(2, QtCore.Qt.Horizontal, self.tr("Last Name")) self.db_table.setModel(self.model) self.db_table.hideColumn(0) #hide column 'id' self.db_table.setSelectionBehavior( QtGui.QAbstractItemView.SelectRows) #select Row self.db_table.setSelectionMode( QtGui.QAbstractItemView.SingleSelection) #disable multiselect self.show()
view = QtGui.QTableView() views.append(view) view.setModel(model) view.setWindowTitle(title) view.move(100 + offset, 100 + offset) offset += 20 view.show() if __name__ == '__main__': import sys app = QtGui.QApplication(sys.argv) if not connection.createConnection(): sys.exit(1) plainModel = QtSql.QSqlQueryModel() editableModel = EditableSqlModel() customModel = CustomSqlModel() initializeModel(plainModel) initializeModel(editableModel) initializeModel(customModel) createView("Plain Query Model", plainModel) createView("Editable Query Model", editableModel) createView("Custom Query Model", customModel) sys.exit(app.exec_())