Python PyQT4 Simple database GUI

Question

I made a very simple python script to read a sqlite3 database into a QtTableWidget.

import sqlite3 as db
from PyQt4 import QtCore, QtGui
import sys

con = db.connect('results.db', isolation_level=None)
cur = con.cursor()
cur.execute("SELECT * FROM Results")
all_data = cur.fetchall()


class UiDialog(object):
    def setupUi(self, datadb):
        datadb.setObjectName("Dialog")
        datadb.resize(404, 304)
        datadb.setWindowTitle("Database results")
        sizePolicy = QtGui.QSizePolicy(QtGui.QSizePolicy.Fixed, QtGui.QSizePolicy.Fixed)
        sizePolicy.setHorizontalStretch(0)
        sizePolicy.setVerticalStretch(0)
        sizePolicy.setHeightForWidth(datadb.sizePolicy().hasHeightForWidth())
        datadb.setSizePolicy(sizePolicy)
        datadb.setMinimumSize(QtCore.QSize(404, 304))
        datadb.setMaximumSize(QtCore.QSize(404, 304))
        self.table = QtGui.QTableWidget(datadb)
        self.table.setGeometry(QtCore.QRect(2, 2, 400, 261))
        self.table.setObjectName("table")
        self.show = QtGui.QPushButton(datadb)
        self.show.setGeometry(QtCore.QRect(2, 270, 400, 31))
        self.show.setObjectName("show")
        self.show.setText("Show results")
        QtCore.QObject.connect(self.show, QtCore.SIGNAL("clicked()"), self.populate)
        QtCore.QMetaObject.connectSlotsByName(datadb)

    def populate(self):
        self.table.setRowCount(len(all_data))
        self.table.setColumnCount(4)
        self.table.setHorizontalHeaderLabels(['Number', 'Keys', 'Time', 'Tries'])
        for i, item in enumerate(all_data):
            number = QtGui.QTableWidgetItem(str(item[0]))
            keys = QtGui.QTableWidgetItem(item[1])
            time = QtGui.QTableWidgetItem(str(item[2]))
            tries = QtGui.QTableWidgetItem(str(item[3]))
            self.table.setItem(i, 0, number)
            self.table.setItem(i, 1, keys)
            self.table.setItem(i, 2, time)
            self.table.setItem(i, 3, tries)

if __name__ == "__main__":
    app = QtGui.QApplication(sys.argv)
    main_conf = QtGui.QDialog()
    ui = UiDialog()
    ui.setupUi(main_conf)
    main_conf.show()
    ret = app.exec_()
    sys.exit(ret)

It works fine. My question is now: Is there any way when I edit a field into the QtableWidget (and finish editing by pressing ENTER) to update automatically the database? Thank you very much for your precious time!

Answer 1

I didn't test the db part since I don't have the database server running. But this logic should work.

import sqlite3 as db
from PyQt4 import QtCore, QtGui
import sys

con = db.connect('results.db', isolation_level=None)
cur = con.cursor()
cur.execute("SELECT * FROM Results")
all_data = cur.fetchall()


class UiDialog(object):
    def setupUi(self, datadb):
        datadb.setObjectName("Dialog")
        datadb.resize(404, 304)
        datadb.setWindowTitle("Database results")
        sizePolicy = QtGui.QSizePolicy(QtGui.QSizePolicy.Fixed, QtGui.QSizePolicy.Fixed)
        sizePolicy.setHorizontalStretch(0)
        sizePolicy.setVerticalStretch(0)
        sizePolicy.setHeightForWidth(datadb.sizePolicy().hasHeightForWidth())
        datadb.setSizePolicy(sizePolicy)
        datadb.setMinimumSize(QtCore.QSize(404, 304))
        datadb.setMaximumSize(QtCore.QSize(404, 304))
        self.table = QtGui.QTableWidget(datadb)
        self.table.setGeometry(QtCore.QRect(2, 2, 400, 261))
        self.table.setObjectName("table")
        self.show = QtGui.QPushButton(datadb)
        self.show.setGeometry(QtCore.QRect(2, 270, 400, 31))
        self.show.setObjectName("show")
        self.show.setText("Show results")
        QtCore.QObject.connect(self.show, QtCore.SIGNAL("clicked()"), self.populate)
        QtCore.QMetaObject.connectSlotsByName(datadb)
        self.table.itemChanged.connect(self.updateDB)

    def populate(self):
        self.table.blockSignals(True)
        #all_data = [["1","2","3","4"],["1","2","3","4"],["1","2","3","4"],["1","2","3","4"]]
        self.table.setRowCount(len(all_data))
        self.table.setColumnCount(4)
        self.table.setHorizontalHeaderLabels(['Number', 'Keys', 'Time', 'Tries'])
        for i, item in enumerate(all_data):
            number = QtGui.QTableWidgetItem(str(item[0]))
            #if we need old data then
            number.setData(QtCore.Qt.UserRole,QtCore.QVariant(str(str(item[0]))))
            keys = QtGui.QTableWidgetItem(item[1])
            keys.setData(QtCore.Qt.UserRole,QtCore.QVariant(str(str(item[1]))))
            time = QtGui.QTableWidgetItem(str(item[2]))
            time.setData(QtCore.Qt.UserRole,QtCore.QVariant(str(str(item[2]))))
            tries = QtGui.QTableWidgetItem(str(item[3]))
            tries.setData(QtCore.Qt.UserRole,QtCore.QVariant(str(str(item[3]))))
            self.table.setItem(i, 0, number)
            self.table.setItem(i, 1, keys)
            self.table.setItem(i, 2, time)
            self.table.setItem(i, 3, tries)
        self.table.blockSignals(False)

    def updateDB(self, itmWid):
        updatedVal = str(itmWid.text())
        oldValue = itmWid.data(QtCore.Qt.UserRole).toString()
        with con:
            cur.execute("UPDATE Results SET Number='%s' WHERE Number='%s'" % (updatedVal, oldValue))

if __name__ == "__main__":
    app = QtGui.QApplication(sys.argv)
    main_conf = QtGui.QDialog()
    ui = UiDialog()
    ui.setupUi(main_conf)
    main_conf.show()
    ret = app.exec_()
    sys.exit(ret)