Updating the entered data in a selected table row according to the ID

BeardyBear

Hi,

What I want to achieve is to select a row in a given table, then to open another dialog by pressing the edit button, and inside that dialog the corresponding editable data have to be displayed from the row I have selected, i.e. fetched from the database's table row. After editing it and confirming, update the data in the database table according to the id of the selected row.

What I have done so far is the ability to open a separate dialog and according to the id number entered the data will be stored in the corresponding row, but all the data in the editing dialog have to be written all over again, otherwise it will update with empty fields if nothing entered.

Here is an example of what I have done so far:

from PySide6.QtCore import *
from PySide6.QtWidgets import *
from PySide6.QtGui import *
from PySide6.QtPrintSupport import *
import sys,sqlite3,time
import os

class UpdateDialog(QDialog):
	def __init__(self, *args, **kwargs):
		super(UpdateDialog, self).__init__(*args, **kwargs)
		
		self.QBtn = QPushButton()
		self.QBtn.setText("Update")

		self.setWindowTitle("Update Query")
		self.setFixedWidth(300)
		self.setFixedHeight(250)

		self.QBtn.clicked.connect(self.updateQuery)

		layout = QVBoxLayout()

		self.id_ = QLineEdit()
		self.id_.setPlaceholderText("Id")
		layout.addWidget(self.idinput)

		self.fnameinput = QLineEdit()
		self.fnameinput.setPlaceholderText("First name")
		layout.addWidget(self.fnameinput)

		self.lnameinput = QLineEdit()
		self.lnameinput.setPlaceholderText("Last name")
		layout.addWidget(self.lnameinput)

		layout.addWidget(self.QBtn)
		self.setLayout(layout)

	def updateQuery(self):

		fname = self.fnameinput.text()
		lname = self.lnameinput.text()
		id_ = self.idinput.text()
		try:
			self.conn = sqlite3.connect("database.db")
			self.c = self.conn.cursor()
			self.c.execute("UPDATE people SET fname=?,lname=? WHERE id=?",(fname,lname,id_))
			self.conn.commit()
			self.c.close()
			self.conn.close()
			QMessageBox.information(QMessageBox(),'Successful','Database updated successfully.')
			self.close()
		except Exception:
			QMessageBox.warning(QMessageBox(), 'Error', 'Could not update the database.')

app = QApplication(sys.argv)
window = MainWindow()
window.show()
window.loaddata()
sys.exit(app.exec_())

Thank you.

jsulm

@BeardyBear said in Updating the entered data in a selected table row according to the ID:

otherwise it will update with empty fields if nothing entered

You can check whether the fields are empty before executing the update query.
Or what exactly is your question?

BeardyBear

@jsulm Excuses for my bad expression. I will try to be more specific and as clear as possible with my request.

Simply, after selecting a row in the table and after opening the "Update Query" dialog, what I want to achieve is to fetch the data from the selected row in the corresponding field in the "Update Query" dialog and after confirming the changes to update those changes in the selected row and in database table also.

For now, the "Update Query" dialog opens no matter if a-or what row is selected, and with that there is no data fetched from the row I want to edit and I am only able to update the wanted row data according to the entered Id that is corresponding to the row I want to edit.

To sum up:

Select a row and fetch that data in the "Update Query" dialog
After modifying and confirming, update the fetched data from the desired row in the row itself and in the database's table of course.

Here is the full code with the UpdateDialog class segment:

from PySide6.QtCore import *
from PySide6.QtWidgets import *
from PySide6.QtGui import *
from PySide6.QtPrintSupport import *
import sys,sqlite3,time
import os

class UpdateDialog(QDialog):
	def __init__(self, *args, **kwargs):
		super(UpdateDialog, self).__init__(*args, **kwargs)
		
		self.QBtn = QPushButton()
		self.QBtn.setText("Update")

		self.setWindowTitle("Update Query")
		self.setFixedWidth(300)
		self.setFixedHeight(250)

		self.QBtn.clicked.connect(self.updateQuery)

		layout = QVBoxLayout()

		self.idinput = QLineEdit()
		self.idinput.setPlaceholderText("Id")
		layout.addWidget(self.idinput)

		self.fnameinput = QLineEdit()
		self.fnameinput.setPlaceholderText("First name")
		layout.addWidget(self.fnameinput)

		self.lnameinput = QLineEdit()
		self.lnameinput.setPlaceholderText("Last name")
		layout.addWidget(self.lnameinput)

		layout.addWidget(self.QBtn)
		self.setLayout(layout)

	def updateQuery(self):

		fname = self.fnameinput.text()
		lname = self.lnameinput.text()
		id_ = self.idinput.text()
		try:
			self.conn = sqlite3.connect("people.db")
			self.c = self.conn.cursor()
			self.c.execute("UPDATE people SET fname=?,lname=? WHERE id=?",(fname,lname,id_))
			self.conn.commit()
			self.c.close()
			self.conn.close()
			QMessageBox.information(QMessageBox(),'Successful','Database updated successfully.')
			self.close()
		except Exception:
			QMessageBox.warning(QMessageBox(), 'Error', 'Could not update the database.')

class MainWindow(QMainWindow):
	def __init__(self, *args, **kwargs):
		super(MainWindow, self).__init__(*args, **kwargs)

		self.conn = sqlite3.connect("people.db")
		self.c = self.conn.cursor()
		self.c.execute("CREATE TABLE IF NOT EXISTS people(id INTEGER PRIMARY KEY AUTOINCREMENT ,fname TEXT,lname TEXT)")
		self.c.close()

		self.setWindowTitle("Table update example")

		self.setMinimumSize(800, 600)

		self.tableWidget = QTableWidget()
		self.setCentralWidget(self.tableWidget)
		self.tableWidget.setAlternatingRowColors(True)
		self.tableWidget.setColumnCount(3)
		self.tableWidget.horizontalHeader().setCascadingSectionResizes(False)
		self.tableWidget.horizontalHeader().setSortIndicatorShown(False)
		self.tableWidget.horizontalHeader().setStretchLastSection(True)
		self.tableWidget.verticalHeader().setVisible(False)
		self.tableWidget.verticalHeader().setCascadingSectionResizes(False)
		self.tableWidget.verticalHeader().setStretchLastSection(False)
		self.tableWidget.setHorizontalHeaderLabels(("Id", "First name", "Last name"))

		toolbar = QToolBar()
		toolbar.setMovable(False)
		self.addToolBar(toolbar)

		statusbar = QStatusBar()
		self.setStatusBar(statusbar)

		btn_ac_updateuser = QAction(QIcon("icon/refresh.png"), "Update Query", self)
		btn_ac_updateuser.triggered.connect(self.update)
		btn_ac_updateuser.triggered.connect(self.loaddata)
		btn_ac_updateuser.setStatusTip("Update Query")
		toolbar.addAction(btn_ac_updateuser)

	def loaddata(self):
		self.connection = sqlite3.connect("people.db")
		query = "SELECT * FROM people"
		result = self.connection.execute(query)
		self.tableWidget.setRowCount(0)
		for row_number, row_data in enumerate(result):
			self.tableWidget.insertRow(row_number)
			for column_number, data in enumerate(row_data):
				self.tableWidget.setItem(row_number, column_number,QTableWidgetItem(str(data)))
		self.connection.close()

	def handlePaintRequest(self, printer):
		document = QTextDocument()
		cursor = QTextCursor(document)
		model = self.table.model()
		table = cursor.insertTable(
			model.rowCount(), model.columnCount())
		for row in range(table.rows()):
			for column in range(table.columns()):
				cursor.insertText(model.item(row, column).text())
				cursor.movePosition(QTextCursor.NextCell)
		document.print_(printer)

	def update(self):
		dlg = UpdateDialog()
		dlg.exec_()

app = QApplication(sys.argv)
window = MainWindow()
window.show()
window.loaddata()
sys.exit(app.exec_())

SGaist

Hi,

From the looks of it, you might want to check QDataWidgetMapper.

BeardyBear

@SGaist Thank you. Would you mind giving me instructions on which functions should I use related to QDataWidgetMapper class?

I have to admit that Qt Documentation is lacking a detailed explanation with examples.

eyllanesc

@BeardyBear I do not agree with you, Qt offers detailed examples of many of its classes, for example a simple search through google: https://www.google.com/search?q=qdatawidgetmapper+example you get as the first option https://doc.qt.io/archives/qt-5.7/qtwidgets-itemviews-simplewidgetmapper-example.html , In addition, the community offers many more examples.

BeardyBear

@eyllanesc Thank you for pointing me an example, I will be more patient in searching through the documentation of Qt although I must admit that it's not that easy to find what you need at a first glance.

According to the community helpfulness, that is why I have raised my question here, expecting a help which I already got it thanks to you guys.