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!

Achayan · Accepted Answer · 2015-08-13 17:47:29Z

2

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)

answered Aug 13, 2015 at 17:47

Achayan

5,8953 gold badges43 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

BarnardMikey Over a year ago

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 Over a year ago

Glad to hear it helped, can you up vote and choose as answer please ?. So I will get some points :)

paddyg Over a year ago

in python3 I seem to need to take out the QtCore.QVariant() out of the setData.

Collectives™ on Stack Overflow

Python PyQT4 Simple database GUI

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related