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)

• BarnardMikey: The logic is very nice and I managed to get it working! Thank you very much! Now I have a minor problem: It updates all same values in column. Let me show you an example: If I try to edit the third row in the column named Number and the value there is the same with the value in the 10th row then both values are changing (I assume because the WHERE in the UPDATE statement change all same values). Anyway this is not a big problem now I will track the index number and change accordingly to the database. Your answer is my solution! Thank you!
• Achayan: Glad to hear it helped, can you up vote and choose as answer please ?. So I will get some points :)
• paddyg: in python3 I seem to need to take out the QtCore.QVariant() out of the setData.