基本上,我正在尝试使用pymysql事务设置PyQt5表单的save方法。保存意味着对新记录进行一次插入查询,以及两次更新,这是插入的结果。该方法运行平稳,但要更新的两个表保持不变。没有引发错误,插入操作正常
最初,我试图使用SqlDatabase来建立事务,但我无法设置它。似乎需要为MySQL开发一个驱动程序,或者可能已经有了一个,但找不到。在此方面如有任何建议,将不胜感激
def saveAndClose(self):
"""Requires to:
- Save (Insert) new record in mortality table.
- Update agreementhorses table - (active = False)
- Update horses table - (active = False).
- Refresh the dockMortality query in the main form.
- Refresh this form horse query.
"""
try:
cnn = pymysql.connect(**self.con_string)
cnn.begin()
with cnn.cursor() as cur:
sql_mortality = """ INSERT INTO mortality
(dod, agreementhorseid, causeid, diagnose, veterinaryid,
notes)
VALUES (%s, %s, %s, %s, %s, %s)"""
self.comboVet.setModelColumn(0)
self.comboCause.setModelColumn(0)
parameters = (self.dateOfDeath.date.toString('yyyy-MM-dd'),
self.record.value(8),
self.comboCause.currentText(),
self.lineDiagnosis.text(),
self.comboVet.currentText(),
self.textNotes.toPlainText())
cur.execute(sql_mortality, parameters)
sql_horses = """ UPDATE horses
SET active = False
WHERE id = %s"""
cur.execute(sql_horses, (self.record.value(8)),)
sql_agreementhorses = """ UPDATE agreementhorses
SET active = False
WHERE id = %s"""
cur.execute(sql_agreementhorses, (self.record.value(9),))
cnn.commit()
except pymysql.Error as e:
QMessageBox.Warning("saveAndClose", (type(e).__name__, e.args))
cnn.rollback()
except AttributeError as err:
print(err.args)
cnn.rollback()
except Exception as err:
print(type(err).__name__, err.args)
cnn.rollback()
finally:
self.comboVet.setModelColumn(1)
self.comboCause.setModelColumn(1)
"""Resets the active horses list for this particular agreement"""
self.tableHorses.model().setQuery(self.getHorsesQuery())
"""Resets the mortality list for this particular agreement on
the main form subform"""
self.parent.tableMortality.model().\
setQuery(self.parent.queryMortality())
"""Clear the form for the next record to be entered"""
self.clearForm()
我希望事务能够正常执行,插入一个表并更新另外两个表。同时,我将尝试使用MySql.connector建立事务,作为新的尝试
交易进行得很顺利。我错误地放置了更新查询的参数。我将第一个查询作为参数放置在第二个查询中,反之亦然。我花了一些时间才弄明白。我向所有试图找出我不存在的问题的人道歉。简而言之,“self.record.value(8)”应该是“self.record.value(9)”,反之亦然
相关问题 更多 >
编程相关推荐