python - 我无法获得INSERT INTO表名等在Python 3.8 pyodbc MySQL中工作

我相当确定已经建立了到数据库的连接。
这是我的代码...

#
# *************************************************************************************************************************************
# * ADDKEYWORD processing
# *************************************************************************************************************************************
#
def addkeyword(ktyp, kword, kweight):
    tbkeywordtype = str(ktyp)
    tbkeyword = str(kword)
    tbweighting = float(kweight)
#
    tbkeywordkey = 1   # temporary work around
#
# Prepare string for inserting new tbkeyword record
#
    insertString = str
    insertString = "\"INSERT INTO tbkeyword (KeywordKey, KeywordType, Keyword, Weighting, Added, Updated) VALUES (%s,'%s','%s',%s,'%s','%s');\"" % (tbkeywordkey,tbkeywordtype,tbkeyword,tbweighting,formatted_date,formatted_date)
    print(insertString)
#
# Insert new tbkeyword record
#
    mycursor.execute(insertString)
#
# Check status code to go here
#
    mydb.commit

这是结果。请注意，第一行是打印字符串的结果，该字符串将位于mycursor.execute（）括号之间。

“将tbkeyword（KeywordKey，KeywordType，Keyword，权重，添加，更新）插入VALUES（1，'K'，'free'，0.6，'2019-11-19 09:12:08'，'2019-11-19 09:12:08'）;“

Exception in Tkinter callback
Traceback (most recent call last):
  File "C:\Users\Tony\AppData\Local\Programs\Python\Python38\lib\tkinter\__init__.py", line 1883, in __call__
    return self.func(*args)
  File "C:\Users\Tony\AppData\Local\Programs\Python\Python38\MainMenuGUI.py", line 185, in <lambda>
    command=lambda: addkeyword(ektyp.get(),ekword.get(),eweight.get()))
  File "C:\Users\Tony\AppData\Local\Programs\Python\Python38\MainMenuGUI.py", line 103, in addkeyword
    mycursor.execute(insertString)
pyodbc.ProgrammingError: ('42000', '[42000] [MySQL][ODBC 8.0(a) Driver][mysqld-8.0.18]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near \'"INSERT INTO tbkeyword (KeywordKey, KeywordType, Keyword, Weighting, Added, Upda\' at line 1 (1064) (SQLExecDirectW)')

我根本看不到我做错了什么。救命！！！！

最佳答案

问题是您要在查询字符串中插入“。只需删除它们（在开头和结尾）：

   insertString = str
   insertString = "INSERT INTO tbkeyword (KeywordKey, KeywordType, Keyword, Weighting, Added, Updated) VALUES (%s,'%s','%s',%s,'%s','%s');" % (tbkeywordkey,tbkeywordtype,tbkeyword,tbweighting,formatted_date,formatted_date)
   print(insertString)
   #
   # Insert new tbkeyword record
   #
   mycursor.execute(insertString)

但是如果您还使用准备好的语句并删除不需要的行会更好，例如：

insertString = "INSERT INTO tbkeyword (KeywordKey, KeywordType, Keyword, Weighting, Added, Updated)
                VALUES (?,?,?,?,?,?);"
# print(insertString)
#
# Insert new tbkeyword record
#
mycursor.execute(insertString,(tbkeywordkey,tbkeywordtype,tbkeyword,tbweighting,formatted_date,formatted_date))

关于python - 我无法获得INSERT INTO表名等在Python 3.8 pyodbc MySQL中工作，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/58930708/