def data(self, index, rol):
if rol < Qt.UserRole:
return QSqlTableModel.data(self, index, rol)
sql_kayit = QSqlRecord()
sql_kayit = self.record(index.row())
return sql_kayit.value(rol - Qt.UserRole)
def __insert_reminder_events(self, record: QSqlRecord):
index = record.value(0)
start_date = record.value(
Resources.EventManager_Column_StartDate_Index)
count = record.value(Resources.EventManager_Column_Count_Index)
Tools.write_verbose_class_method_name(
self, EventManager.__insert_reminder_events, "event_id",
str(index))
self.__insert_missing_reminder_events(index, start_date, count)
def _setDefaultColumn(self, model, db, record: QtSql.QSqlRecord):
'''根据一个record对象设置默认ViewColumns'''
self.__model = model
for i in range(record.count()):
fld = record.field(i)
fn = fld.name()
vc = ViewColumn(model, fn, fn)
vc.parent = self
self._list.append(vc)
self._setViewColumnAlighAndFormatString(vc)
def __set_next_id(self, record: QSqlRecord):
if self.__added_items:
new_id = self.__added_items[-1] + 1
else: # list is empty
new_id = self.__last_id + 1
record.setValue(0, QVariant(new_id))
self.__added_items.append(new_id)
self.__last_id = new_id
Tools.write_verbose_class_method_name(self, EventManager.__set_next_id,
"new_id", str(new_id))
def submittodb(self):
#print ('Inside label: ', self.oq_input.text())
if self.oq_input.text().isdigit():
self.new_record = QSqlRecord(self.record_template)
self.new_record.setValue(0, self.combo.currentText())
self.new_record.setValue(1, self.oq_input.text())
self.new_record.setValue(2, str(datetime.datetime.now()))
self.close()
else:
self.error_message.setText('Please enter an integer only')
self.error_message.adjustSize()
self.error_message.setStyleSheet("color:red")
def initdiagUI(self):
exitShortcut = QShortcut(QKeySequence('Ctrl+W'), self, qApp.quit)
#=====# #=====# #=====# #=====# #=====# #=====#
# Create local seperate connection to databse
#=====# #=====# #=====# #=====# #=====# #=====#
self.db = QSqlDatabase.database("dialog")
#=====# #=====# #=====# #=====# #=====# #=====#
# Create SqlRecord template with predefined fields
#=====# #=====# #=====# #=====# #=====# #=====#
self.record_template = QSqlRecord()
self.record_template.insert(0, QSqlField('model'))
self.record_template.insert(1, QSqlField('order_quantity'))
self.record_template.insert(2, QSqlField('order_time'))
self.record_template.insert(3, QSqlField('expected_production'))
#=====# #=====# #=====# #=====# #=====# #=====#
# Main widgets
#=====# #=====# #=====# #=====# #=====# #=====#
label_model = QLabel('Model', self)
label_model.move(40, 50)
self.combo = QComboBox(self)
self.fillcombo()
self.combo.move(180, 50)
label_orderquantity = QLabel('Order Quantity', self)
label_orderquantity.move(40, 100)
self.oq_input = QLineEdit('', self)
self.oq_input.move(180, 100)
self.error_message = QLabel('', self)
self.error_message.move(180, 130)
btn_submit = QPushButton('Submit Order', self)
btn_submit.move(180, 150)
btn_submit.clicked.connect(self.submittodb)
self.setFixedSize(350, 200)
self.setWindowTitle('Input Dialog')
self.show()
self.new_record = -1
def __update_active(self, old_record: QSqlRecord, new_record: QSqlRecord):
new_value = bool(
new_record.value(Resources.EventManager_Column_IsActive_Index))
if bool(old_record.value(
Resources.EventManager_Column_IsActive_Index)) == new_value:
return
index = old_record.value(0)
Tools.write_verbose_class_method_name(self,
EventManager.__update_active,
"event_id", str(index))
if new_value is False: # if change to inactive
sql = Resources.Manager_DELETE_ReminderEvent % index
self.__db.exec(sql)
else: # if change to active
self.__insert_reminder_events(new_record)
def callback(record: QSqlRecord, pattern: typing.Any):
if custom_match:
return custom_match(record, pattern)
value = record.value(field)
regex = '.*?{0}.*'.format(pattern)
match = re.search(pattern, value or '', flags=re.IGNORECASE)
return match is not None
def __before_update(self, row: int, record: QSqlRecord):
del row
index = record.value(0)
query = self.__db.exec(Resources.EventManager_SELECT_Event % index)
query.next()
old_record = query.record()
self.__update_active(old_record, record)
self.__update_count(old_record, record)
Tools.write_verbose_class_method_name(self,
EventManager.__before_update,
"updated", str(index))
def __before_insert(self, record: QSqlRecord):
index = record.value(0)
self.__insert_reminder_events(record)
self.__added_items.remove(index)
Tools.write_verbose_class_method_name(self,
EventManager.__before_insert,
"added_items.remove", str(index))
Tools.write_verbose_class_method_name(self,
EventManager.__before_insert,
"added_items.remained",
str(self.__added_items))
def __update_count(self, old_record: QSqlRecord, new_record: QSqlRecord):
new_value = new_record.value(Resources.EventManager_Column_Count_Index)
old_value = old_record.value(Resources.EventManager_Column_Count_Index)
if new_value == old_value:
return
index = new_record.value(0)
Tools.write_verbose_class_method_name(self,
EventManager.__update_count,
"event_id", str(index))
if new_value > old_value: # we need to add missing records
start_date = new_record.value(
Resources.EventManager_Column_StartDate_Index)
count = new_value - old_value
self.__insert_missing_reminder_events(index, start_date, count)
else: # we need to delete redundant records
count = old_value - new_value
sql = Resources.EventManager_DELETE_Newest_ReminderEvents % (index,
count)
self.__db.exec(sql)
def _handleProperties(self):
"""
Iterates over the class dict looking for QSqlDBProperties.
Any properties found have their fields generated and added to the schema
"""
cls = self._class
schema = QSqlRecord()
dbProperties = [
getattr(cls, method) for method in cls.__dict__
if type(getattr(cls, method)) is _QSqlDBProperty
]
for dbProperty in dbProperties:
index = dbProperty.FieldIndex
fieldName = dbProperty.PropertyName
fieldArgs = dbProperty.FieldArguments
field = QSqlDBObject._createField(fieldName, fieldArgs)
schema.insert(index, field)
QSqlUpgradeManager.RegisterDBObject(cls.__name__, schema)
def saveDataOld(self):
if self.db.isOpen() != True:
if self.opendb() != True:
# todo 打开数据库失败错误处理退出
return
rowNum = 0
model = QSqlTableModel()
model.setEditStrategy(QSqlTableModel.OnManualSubmit)
while rowNum < self.tableWidget.rowCount():
year = self.tableWidget.item(rowNum,0)
profilo = self.tableWidget.item(rowNum,1)
model.setTable("history")
strFilter = "unit = '%s' and year = '%s'" %(self.unit,year.text())
model.setFilter(strFilter)
model.select()
if model.rowCount() != 0:
curRecord = model.record(0)
curRecord.setValue(self.tableWidget.item(rowNum,1))
model.setRecord(0,curRecord)
model.submitAll()
else:
curRecord = QSqlRecord()
curRecord.setValue("year",year.text())
curRecord.setValue("profilo",profilo.text())
curRecord.setValue("unit",self.unit)
ret = model.insertRecord(-1,curRecord)
if ret == True:
print("数据库插入成功")
else:
print("数据插入失败ret = %d" %ret)
ret = model.submitAll()
if ret == True:
print("提交成功")
else:
error = self.db.lastError()
print("提交失败,ret = %d" %ret)
# print(error.text())
qDebug(error.text())
rowNum += 1
self.db.close()
return True
def __add_value_to_row(self, record: QSqlRecord, row: int, column: int, value_name: str, is_editable: bool = False, is_selectable: bool = True, is_enable: bool = True):
item = QTableWidgetItem(str(record.value(value_name)))
flags = item.flags()
if is_editable:
flags = flags | Qt.ItemIsEditable
else:
flags = flags & ~Qt.ItemIsEditable
if is_selectable:
flags = flags | Qt.ItemIsSelectable
else:
flags = flags & ~Qt.ItemIsSelectable
if is_enable:
flags = flags | Qt.ItemIsEnabled
else:
flags = flags & ~Qt.ItemIsEnabled
item.setFlags(flags)
self.__view.setItem(row, column, item)
class Dialog(QDialog):
def __init__(self):
super().__init__()
self.initdiagUI()
def initdiagUI(self):
exitShortcut = QShortcut(QKeySequence('Ctrl+W'), self, qApp.quit)
#=====# #=====# #=====# #=====# #=====# #=====#
# Create local seperate connection to databse
#=====# #=====# #=====# #=====# #=====# #=====#
self.db = QSqlDatabase.database("dialog")
#=====# #=====# #=====# #=====# #=====# #=====#
# Create SqlRecord template with predefined fields
#=====# #=====# #=====# #=====# #=====# #=====#
self.record_template = QSqlRecord()
self.record_template.insert(0, QSqlField('model'))
self.record_template.insert(1, QSqlField('order_quantity'))
self.record_template.insert(2, QSqlField('order_time'))
self.record_template.insert(3, QSqlField('expected_production'))
#=====# #=====# #=====# #=====# #=====# #=====#
# Main widgets
#=====# #=====# #=====# #=====# #=====# #=====#
label_model = QLabel('Model', self)
label_model.move(40, 50)
self.combo = QComboBox(self)
self.fillcombo()
self.combo.move(180, 50)
label_orderquantity = QLabel('Order Quantity', self)
label_orderquantity.move(40, 100)
self.oq_input = QLineEdit('', self)
self.oq_input.move(180, 100)
self.error_message = QLabel('', self)
self.error_message.move(180, 130)
btn_submit = QPushButton('Submit Order', self)
btn_submit.move(180, 150)
btn_submit.clicked.connect(self.submittodb)
self.setFixedSize(350, 200)
self.setWindowTitle('Input Dialog')
self.show()
self.new_record = -1
#=====# #=====# #=====# #=====# #=====# #=====#
#=====# #=====# #=====# #=====# #=====# #=====#
# METHODS #
#=====# #=====# #=====# #=====# #=====# #=====#
#=====# #=====# #=====# #=====# #=====# #=====#
#=====# #=====# #=====# #=====# #=====# #=====#
#Populate combo box with model names using QSqlQuery and QSqlRecord objects
#=====# #=====# #=====# #=====# #=====# #=====#
def fillcombo(self):
q = QSqlQuery("select name from models", self.db)
self.rec = q.record()
nameCol = self.rec.indexOf("name")
print(nameCol)
while q.next():
self.combo.addItem(q.value(nameCol))
#=====# #=====# #=====# #=====# #=====# #=====#
#Submit input data to the model in database
#=====# #=====# #=====# #=====# #=====# #=====#
def submittodb(self):
#print ('Inside label: ', self.oq_input.text())
if self.oq_input.text().isdigit():
self.new_record = QSqlRecord(self.record_template)
self.new_record.setValue(0, self.combo.currentText())
self.new_record.setValue(1, self.oq_input.text())
self.new_record.setValue(2, str(datetime.datetime.now()))
self.close()
else:
self.error_message.setText('Please enter an integer only')
self.error_message.adjustSize()
self.error_message.setStyleSheet("color:red")
def __init__(self,parent=None):
super().__init__(parent) #调用父类构造函数，创建窗体
self.ui=Ui_QWDialogData() #创建UI对象
self.ui.setupUi(self) #构造UI界面
self.__record=QSqlRecord() #用于存储一条记录的数据
def _prev_challenge_insert(self, row, record: QSqlRecord):
record.setValue(record.indexOf('competition_id'), self.idn)
def _beforeInsert(self, record: QtSql.QSqlRecord):
'''增加一行之前给主表键值列赋值'''
if self.filter():
fn, fv = self.filter().split('=')
record.setValue(fn, int(fv))
record.setGenerated(fn, True)
def __init__(self, parent=None):
super().__init__(parent)
self.ui = Ui_QWDialogData()
self.ui.setupUi(self)
self.__record = QSqlRecord()
class QmyDialogData(QDialog):
def __init__(self, parent=None):
super().__init__(parent) #调用父类构造函数，创建窗体
self.ui = Ui_QWDialogData() #创建UI对象
self.ui.setupUi(self) #构造UI界面
self.__record = QSqlRecord() #用于存储一条记录的数据
## ==============自定义功能函数============
def setInsertRecord(self, recData): ##设置插入记录的数据
self.__record = recData
self.ui.spinEmpNo.setEnabled(True) #员工编号允许编辑
self.setWindowTitle("插入新记录")
self.ui.spinEmpNo.setValue(recData.value("empNo"))
def setUpdateRecord(self, recData): ##设置更新记录的数据
self.__record = recData
self.ui.spinEmpNo.setEnabled(False) #员工编号不允许编辑
self.setWindowTitle("更新记录")
##根据recData的数据更新界面显示
self.ui.spinEmpNo.setValue(recData.value("empNo"))
self.ui.editName.setText(recData.value("Name"))
self.ui.comboSex.setCurrentText(recData.value("Gender"))
birth = recData.value("Birthday") #str类型, 注意！！！
birth_date = QDate.fromString(birth, "yyyy-MM-dd")
self.ui.editBirth.setDate(birth_date)
self.ui.comboProvince.setCurrentText(recData.value("Province"))
self.ui.comboDep.setCurrentText(recData.value("Department"))
self.ui.spinSalary.setValue(recData.value("Salary"))
self.ui.editMemo.setPlainText(recData.value("Memo"))
picData = recData.value("Photo") #bytearray类型
if (picData == ''): #图片字段内容为空
self.ui.LabPhoto.clear()
else:
pic = QPixmap()
pic.loadFromData(picData)
W = self.ui.LabPhoto.size().width()
self.ui.LabPhoto.setPixmap(pic.scaledToWidth(W))
def getRecordData(self): ##返回界面编辑的数据
self.__record.setValue("empNo", self.ui.spinEmpNo.value())
self.__record.setValue("Name", self.ui.editName.text())
self.__record.setValue("Gender", self.ui.comboSex.currentText())
self.__record.setValue("Birthday", self.ui.editBirth.date())
self.__record.setValue("Province", self.ui.comboProvince.currentText())
self.__record.setValue("Department", self.ui.comboDep.currentText())
self.__record.setValue("Salary", self.ui.spinSalary.value())
self.__record.setValue("Memo", self.ui.editMemo.toPlainText())
##照片编辑时已经修改了self.__record的photo字段的值
return self.__record #以记录作为返回值
## ==========由connectSlotsByName() 自动连接的槽函数==================
@pyqtSlot() ##“导入照片”按钮
def on_btnSetPhoto_clicked(self):
fileName, filt = QFileDialog.getOpenFileName(self, "选择图片", "",
"照片(*.jpg)")
if (fileName == ''):
return
file = QFile(fileName)
file.open(QIODevice.ReadOnly)
data = file.readAll()
file.close()
self.__record.setValue("Photo", data) #图片保存到Photo字段
pic = QPixmap()
pic.loadFromData(data) #在界面上显示
W = self.ui.LabPhoto.width()
self.ui.LabPhoto.setPixmap(pic.scaledToWidth(W))
@pyqtSlot() ##“清除照片按钮”
def on_btnClearPhoto_clicked(self):
self.ui.LabPhoto.clear()
self.__record.setNull("Photo") #Photo字段清空
class QmyDialogData(QDialog):
def __init__(self, parent=None):
super().__init__(parent)
self.ui = Ui_QWDialogData()
self.ui.setupUi(self)
self.__record = QSqlRecord()
def setInsertRecord(self, recData):
self.__record = recData
self.ui.spinEmpNo.setEnabled(True)
self.setWindowTitle("插入新记录")
self.ui.spinEmpNo.setValue(recData.value("empNo"))
def setUpdateRecord(self, recData):
self.__record = recData
self.ui.spinEmpNo.setEnabled(False)
self.setWindowTitle("更新记录")
self.ui.spinEmpNo.setValue(recData.value("empNo"))
self.ui.editName.setText(recData.value("Name"))
self.ui.comboSex.setCurrentText(recData.value("Gender"))
birth = recData.value("Birthday")
birth_date = QDate.fromString(birth, "yyyy-MM-dd")
self.ui.editBirth.setDate(birth_date)
self.ui.comboProvince.setCurrentText(recData.value("Province"))
self.ui.comboDep.setCurrentText(recData.value("Department"))
self.ui.spinSalary.setValue(recData.value("Salary"))
self.ui.editMemo.setPlainText(recData.value("Memo"))
picData = recData.value("Photo")
if (picData == ''):
self.ui.LabPhoto.clear()
else:
pic = QPixmap()
pic.loadFromData(picData)
W = self.ui.LabPhoto.size().width()
self.ui.LabPhoto.setPixmap(pic.scaledToWidth(W))
def getRecordData(self):
self.__record.setValue("empNo", self.ui.spinEmpNo.value())
self.__record.setValue("Name", self.ui.editName.text())
self.__record.setValue("Gender", self.ui.comboSex.currentText())
self.__record.setValue("Birthday", self.ui.editBirth.date())
self.__record.setValue("Province", self.ui.comboProvince.currentText())
self.__record.setValue("Department", self.ui.comboDep.currentText())
self.__record.setValue("Salary", self.ui.spinSalary.value())
self.__record.setValue("Memo", self.ui.editMemo.toPlainText())
return self.__record
@pyqtSlot()
def on_btnSetPhoto_clicked(self):
fileName, filt = QFileDialog.getOpenFileName(self, "选择图片", "",
"照片(*.jpg)")
if (fileName == ''):
return
file = QFile(fileName)
file.open(QIODevice.ReadOnly)
data = file.readAll()
file.close()
self.__record.setValue("Photo", data)
pic = QPixmap()
pic.loadFromData(data)
W = self.ui.LabPhoto.width()
self.ui.LabPhoto.setPixmap(pic.scaledToWidth(W))
@pyqtSlot()
def on_btnClearPhoto_clicked(self):
self.ui.LabPhoto.clear()
self.__record.setNull("Photo")
def _handleNewColumns(self, tableName:str, existingSchema: QSqlRecord,
newSchema: QSqlRecord):
"""
Compares the existing schema with the new schema to work out what
changes need to be made.
Note: To modify a table column or insert a new column between existing
columns (as opposed to just appending at the end), a schema upgrade
routine will need to be written.
"""
i = 0
while i < min(existingSchema.count(), newSchema.count()):
if existingSchema.field(i) != newSchema.field(i):
raise UpgradeException("New schema cannot modify field " +
"properties or ordering without a schema" +
" upgrade routine.")
i += 1
if newSchema.count() > existingSchema.count():
while i < newSchema.count():
self._query.AlterTableAddColumn(tableName, newSchema.field(i))
i += 1
elif newSchema.count() < existingSchema.count() and self._dangerousMode:
while i < existingSchema.count():
self._query.AlterTableDropColumn(tableName, existingSchema.field(i))
i += 1
def __add_button_to_row(self, record: QSqlRecord, row: int, column: int):
button = QPushButton(Resources.ReminderEventTab_BUTTON_NAME_Done)
button.setProperty(Resources.ReminderEventTab_Property_Id, QVariant(record.value(0)))
button.setProperty(Resources.ReminderEventTab_Property_EventId, QVariant(record.value(Resources.ReminderEventTab_Column_EventId)))
button.clicked.connect(self.__button_clicked)
self.__view.setCellWidget(row, column, button)