I have a PySide2 GUI that can add, update and delete records in my SQLite3 database. I use QTableView to view the data and QDataWidgetMapper to edit data.
My problem is that when I add a record to the database that has a blank value in any field it does not register as a NULL value in the database, only a blank string (""). I don't think I am implementing the delegate in the correct way. Should I be setting the delegate on the column or on the QDataWidgetMapper?
import sys
import os
from PySide2 import QtCore, QtGui, QtWidgets, QtSql
import PySide2.QtUiTools as QtUiTools
import sqlite3
class NullDelegate(QtWidgets.QStyledItemDelegate):
def __init__(self, parent=None):
super(NullDelegate, self).__init__(parent)
def createEditor(self, parent, options, index):
editor = QtWidgets.QLineEdit(parent)
return editor
def setEditorData(self, editor, index):
if index.siblingAtColumn(1).data() == "": # This is for testing purposes
print(index.siblingAtColumn(2).data())
if index.data():
editor.setText(str(index.data()))
editor.selectAll() # This is for testing purposes
def setModelData(self, editor, model, index):
value = editor.text()
if value == "":
print("null value")
model.setData(index, None, QtCore.Qt.EditRole)
else:
model.setData(index, value, QtCore.Qt.EditRole)
class Main(QtWidgets.QMainWindow):
def __init__(self, parent=None):
super(Main, self).__init__(parent)
# load ui file
loader = QtUiTools.QUiLoader()
# The UI file must be a widget, NOT MainWindow, to load into a QMainWindow object
# otherwise you will have to use .show() to open in separate window
path = os.path.join(os.path.dirname(__file__), "main.ui")
self.main = loader.load(path, self)
# create SQLite Database and Table
db = QtSql.QSqlDatabase.addDatabase("QSQLITE")
db.setDatabaseName("customers_demo.db")
if db.open():
print('connection open')
query = QtSql.QSqlQuery(db=db)
query.prepare("CREATE TABLE IF NOT EXISTS Customers_demo (ID INT PRIMARY KEY, FEIN varchar(255), CustomerName varchar(255), Address varchar(255), City varchar(255), State varchar(255), Zip INT) ")
query.exec_()
# Set up QSqlTableModel
self.model = QtSql.QSqlTableModel(self)
self.model.setTable("customers_demo")
self.model.select()
# Set up QDataWidgetMapper
self.mapper = QtWidgets.QDataWidgetMapper()
self.mapper.setModel(self.model)
# Set up the delegate for null values
null_delegate = NullDelegate(self.main.tableView)
self.mapper.setItemDelegate(null_delegate)
# Configure QDataWidgetMapper
self.mapper.addMapping(self.main.id_lineEdit, 0)
self.mapper.addMapping(self.main.fein_lineEdit, 1)
self.mapper.addMapping(self.main.customer_lineEdit, 2)
self.mapper.addMapping(self.main.address_lineEdit, 3)
self.mapper.addMapping(self.main.city_lineEdit, 4)
self.mapper.addMapping(self.main.state_lineEdit, 5)
self.mapper.addMapping(self.main.zip_lineEdit, 6)
self.mapper.setSubmitPolicy(self.mapper.ManualSubmit)
# Set up QTableView
self.main.tableView.setModel(self.model)
# Set up the delegate for null values
# null_delegate = NullDelegate(self.main.tableView)
# self.main.tableView.setItemDelegateForColumn(1, null_delegate)
self.main.tableView.resizeColumnsToContents()
# QTableView properties
self.main.tableView.setSortingEnabled(True) # sort by clicking title
self.main.tableView.resizeColumnToContents(2) # "Customer Name" column
self.main.tableView.setSelectionBehavior(
QtWidgets.QAbstractItemView.SelectRows)
# add background color to every other row
self.main.tableView.setAlternatingRowColors(True)
# make table uneditable
self.main.tableView.setEditTriggers(
QtWidgets.QTableView.NoEditTriggers)
self.mapper.setCurrentIndex(0)
print(self.mapper.currentIndex())
self.RECORD_NUMBER()
self.handle_buttons()
def handle_buttons(self):
self.main.update_btn.clicked.connect(self.UPDATE)
self.main.delete_btn.clicked.connect(self.DELETE)
self.main.new_btn.clicked.connect(self.NEW)
self.main.first_btn.clicked.connect(self.mapper.toFirst)
self.main.first_btn.clicked.connect(self.RECORD_NUMBER)
self.main.previous_btn.clicked.connect(self.mapper.toPrevious)
self.main.previous_btn.clicked.connect(self.RECORD_NUMBER)
self.main.next_btn.clicked.connect(self.mapper.toNext)
self.main.next_btn.clicked.connect(self.RECORD_NUMBER)
self.main.last_btn.clicked.connect(self.mapper.toLast)
self.main.last_btn.clicked.connect(self.RECORD_NUMBER)
self.main.tableView.clicked.connect(self.SELECT_ROW)
def SELECT_ROW(self, item):
# Set text to the selected customer info
self.main.id_lineEdit.setText(
str(item.siblingAtColumn(0).data())) # convert int to str
self.main.customer_lineEdit.setText(item.siblingAtColumn(2).data())
self.main.fein_lineEdit.setText(item.siblingAtColumn(1).data())
self.main.address_lineEdit.setText(item.siblingAtColumn(3).data())
self.main.city_lineEdit.setText(item.siblingAtColumn(4).data())
self.main.state_lineEdit.setText(item.siblingAtColumn(5).data())
self.main.zip_lineEdit.setText(
str(item.siblingAtColumn(6).data())) # convert int to str
self.mapper.setCurrentIndex(item.row())
self.RECORD_NUMBER()
def UPDATE(self):
loader = QtUiTools.QUiLoader()
path = os.path.join(os.path.dirname(__file__), "dialog.ui")
dialog = loader.load(path)
currentIndex = self.mapper.currentIndex()
print("Mapper", currentIndex)
self.main.tableView.selectRow(currentIndex)
customer = self.main.tableView.currentIndex().siblingAtColumn(2).data()
print(customer)
text = f"Are you sure you want to UPDATE {customer} info?"
dialog.label.setText(text)
dialog.buttonBox.accepted.connect(dialog.accept)
dialog.buttonBox.rejected.connect(dialog.reject)
dialog.show()
if dialog.exec_():
print("update")
te = self.mapper.submit()
print(te)
print(self.mapper.model())
self.model.select()
else:
pass
def DELETE(self):
loader = QtUiTools.QUiLoader()
path = os.path.join(os.path.dirname(__file__), "dialog.ui")
dialog = loader.load(path)
currentIndex = self.mapper.currentIndex()
print("Mapper", currentIndex)
self.main.tableView.selectRow(currentIndex)
customer = self.main.tableView.currentIndex().siblingAtColumn(2).data()
print(customer)
text = f"Are you sure you want to DELETE {customer} info?"
dialog.label.setText(text)
dialog.buttonBox.accepted.connect(dialog.accept)
dialog.buttonBox.rejected.connect(dialog.reject)
dialog.show()
if dialog.exec_():
print("yes")
self.model.removeRow(currentIndex)
self.model.select()
else:
pass
self.mapper.setCurrentIndex(0)
print(self.mapper.currentIndex())
self.RECORD_NUMBER()
def NEW(self):
loader = QtUiTools.QUiLoader()
path = os.path.join(os.path.dirname(__file__), "dialog.ui")
dialog = loader.load(path)
customer = self.main.customer_lineEdit.text()
text = f"Are you sure you want to ADD {customer} info?"
dialog.label.setText(text)
dialog.buttonBox.accepted.connect(dialog.accept)
dialog.buttonBox.rejected.connect(dialog.reject)
dialog.show()
if dialog.exec_():
row = self.model.rowCount()
record = self.model.record()
# record.setGenerated('id', False)
record.setValue('ID', self.main.id_lineEdit.text())
record.setValue('FEIN', str(self.main.fein_lineEdit.text()))
record.setValue("CustomerName",
self.main.customer_lineEdit.text())
record.setValue('Address', self.main.address_lineEdit.text())
record.setValue('City', self.main.city_lineEdit.text())
record.setValue('State', self.main.state_lineEdit.text())
record.setValue('Zip', self.main.zip_lineEdit.text())
self.model.insertRecord(row, record)
self.model.select()
else:
pass
self.mapper.setCurrentIndex(0)
print(self.mapper.currentIndex())
self.RECORD_NUMBER()
def RECORD_NUMBER(self):
currentIndex = self.mapper.currentIndex()
print(currentIndex)
rows = self.model.rowCount()
self.main.record_label.setText(
f"Customer {currentIndex+1} of {rows} customers")
if __name__ == "__main__":
app = QtWidgets.QApplication(sys.argv)
widget = Main()
widget.show()
app.exec_()
You can download the ui files and a sample database here.
Also I'm using Python 3.7.4
The problem is not the delegate but in the code that adds the row since you are passing the text of the QLineEdits directly without implementing the logic of the nulls.
def NEW(self):
loader = QtUiTools.QUiLoader()
path = os.path.join(os.path.dirname(__file__), "dialog.ui")
dialog = loader.load(path)
customer = self.main.customer_lineEdit.text()
text = f"Are you sure you want to ADD {customer} info?"
dialog.label.setText(text)
dialog.buttonBox.accepted.connect(dialog.accept)
dialog.buttonBox.rejected.connect(dialog.reject)
if dialog.exec_():
mapping = {
"ID": self.main.id_lineEdit,
"FEIN": self.main.fein_lineEdit,
"CustomerName": self.main.customer_lineEdit,
"Address": self.main.address_lineEdit,
"City": self.main.city_lineEdit,
"State": self.main.state_lineEdit,
"Zip": self.main.zip_lineEdit,
}
record = self.model.record()
for fieldname, lineedit in mapping.items():
text = lineedit.text()
record.setValue(fieldname, text if text else None)
row = self.model.rowCount()
self.model.insertRecord(row, record)
self.model.select()
self.mapper.setCurrentIndex(0)
print(self.mapper.currentIndex())
self.RECORD_NUMBER()