没有正确插入简单的Varchar?正在生成字符串

Question

遇到一个奇怪的错误，我就是不知道出了什么问题。所以我有一个MySQL服务器，我正在运行一个使用以下脚本设置的表：

CREATE TABLE Cases (
systemSN VARCHAR(15),
caseID int PRIMARY KEY AUTO_INCREMENT);

在Python 3.8.6中，我目前正试图在数据库的一个表中实现一个包含大约19个字段的大型表单，但遇到了问题，所以我逐字段缩小了它的范围，这是我目前无法实现的基本INSERT。

为了生成我试图插入MySQL数据库的变量，我让用户选择行编辑或组合框来选择文本字符串，然后我尝试，并让他们将这些值上传到表中。

组合框显示字符串列表，如：

systemSNs = ['----------------------', 'S129', '2342', 'a;sld']
for x in systemSNs:
self.ncw_SystemSN_CB.addItem(x)

我用来将组合框值传递到MySQL函数的函数是：

def submitNewCase_call(self):
print(self.ncw_SystemSN_CB.currentText())
print(type(self.ncw_SystemSN_CB.currentText()))
inputNewCaseintoDB(
self.ncw_SystemSN_CB.currentText()
)

然后我使用这个函数尝试将信息加载到数据库中：

def inputNewCaseintoDB(systemSN):
db = dbConnect()  #connects to my database
mycursor = db.cursor()
addCase = ("INSERT INTO Cases "
"(systemSN) "
"VALUES (%s)")
mycursor.execute(addCase, systemSN)

print(systemSN)
db.commit()
db.close()

我在pycharm终端中得到的Python错误代码是：进程已完成，退出代码为1073740791(0xC0000409(

如果我用变量systemSN代替int，我可以很好地插入，但由于某种原因，我似乎无法输入字符串，我不明白为什么不能。

第1版：

例如：

testFormMain.py

from testForm import Ui_MainWindow
from PyQt5 import QtWidgets
import sys
import mysql.connector
def dbConnect():
db = mysql.connector.connect(
host="localhost",
user="root",
passwd="password!",
database="mysqldb"
)
return db
def inputNewCaseintoDB(systemSN):
print(systemSN)
db = dbConnect()
mycursor = db.cursor()
mycursor.execute("INSERT INTO Cases "
"(systemSN)"
"VALUES (%s)",
(systemSN))
db.commit()
db.close()
class testFormMain(QtWidgets.QMainWindow,
Ui_MainWindow):
def __init__(self, *args, **kwargs):
super().__init__(*args, **kwargs)
self.setupUi(self)
self.ncw_Submit_PB.clicked.connect(self.submitNewCase_call)
systemSNs = ['----------------------', 'S129', '2342', 'a;sld']
for x in systemSNs:
self.ncw_SystemSN_CB.addItem(x)

def submitNewCase_call(self):
inputNewCaseintoDB(self.ncw_SystemSN_CB.currentText())
if __name__ == '__main__':
app = QtWidgets.QApplication(sys.argv)
widget = testFormMain()
widget.show()
app.exec_()

testForm.py

# -*- coding: utf-8 -*-
# Form implementation generated from reading ui file 'testForm.ui'
#
# Created by: PyQt5 UI code generator 5.15.1
#
# WARNING: Any manual changes made to this file will be lost when pyuic5 is
# run again.  Do not edit this file unless you know what you are doing.

from PyQt5 import QtCore, QtGui, QtWidgets

class Ui_MainWindow(object):
def setupUi(self, MainWindow):
MainWindow.setObjectName("MainWindow")
MainWindow.resize(393, 307)
self.centralwidget = QtWidgets.QWidget(MainWindow)
self.centralwidget.setObjectName("centralwidget")
self.ncw_Submit_PB = QtWidgets.QPushButton(self.centralwidget)
self.ncw_Submit_PB.setGeometry(QtCore.QRect(150, 210, 75, 23))
self.ncw_Submit_PB.setObjectName("ncw_Submit_PB")
self.ncw_SystemSN_CB = QtWidgets.QComboBox(self.centralwidget)
self.ncw_SystemSN_CB.setGeometry(QtCore.QRect(90, 70, 231, 81))
self.ncw_SystemSN_CB.setObjectName("ncw_SystemSN_CB")
MainWindow.setCentralWidget(self.centralwidget)
self.menubar = QtWidgets.QMenuBar(MainWindow)
self.menubar.setGeometry(QtCore.QRect(0, 0, 393, 21))
self.menubar.setObjectName("menubar")
MainWindow.setMenuBar(self.menubar)
self.statusbar = QtWidgets.QStatusBar(MainWindow)
self.statusbar.setObjectName("statusbar")
MainWindow.setStatusBar(self.statusbar)
self.retranslateUi(MainWindow)
QtCore.QMetaObject.connectSlotsByName(MainWindow)
def retranslateUi(self, MainWindow):
_translate = QtCore.QCoreApplication.translate
MainWindow.setWindowTitle(_translate("MainWindow", "MainWindow"))
self.ncw_Submit_PB.setText(_translate("MainWindow", "Submit"))

if __name__ == "__main__":
import sys
app = QtWidgets.QApplication(sys.argv)
MainWindow = QtWidgets.QMainWindow()
ui = Ui_MainWindow()
ui.setupUi(MainWindow)
MainWindow.show()
sys.exit(app.exec_())

第2版：我代码中其他地方的一个函数的例子，它试图为James执行类似的任务(将行编辑字段放入数据库表中(。不管我试图用什么字符填充VARCHAR，这个都能正常工作。

def addCompany(companyName, businessPhone, faxPhone, streetAddress, city, state, zipCode, country):
print(companyName, businessPhone, faxPhone, streetAddress, city, state, zipCode, country)
db = dbConnect()
mycursor = db.cursor()
mycursor.execute("INSERT INTO Company "
"(companyName,"
"businessPhone,"
"faxPhone,"
"streetAddress,"
"city,"
"state,"
"zipCode,"
"country)"
"VALUES (%s, %s, %s, %s, %s, %s, %s, %s)",
(companyName, businessPhone, faxPhone, streetAddress, city, state, zipCode, country))
db.commit()
db.close()

Answer 1

我遇到这么多问题的原因似乎是在使用python将单个变量插入mysql数据库时出现了一些不直观的格式化。

当我写声明时：

mycursor.execute("INSERT INTO Cases (systemSN) VALUES (%s)", (systemSN))

我收到了1064个不喜欢我的格式的错误。问题是需要在元组中传递变量名。声明：

mycursor.execute("INSERT INTO Cases (systemSN) VALUES (%s)", (systemSN,))

工作良好。

我以为我很聪明，试图一次把插入的内容分解成一个变量，但实际上我只是发现了一个新问题！

感谢您提供有关从控制台执行的提示。更有用的错误消息！感谢所有的帮助。