mysql - MySQL 更新的 Python 格式字符串

Question

由于某些 NLP 需求，我是一名长期的 Perl 程序员，试图学习 Python。所以这可能是一个愚蠢的菜鸟问题。

我正在尝试从 MySQL 获取文本，使用 spaCy 将实体类型计数到字典中，然后使用字典的 JSON 版本更新数据库记录。一切都很好，直到我到达最后一步。代码是：

import spacy
nlp = spacy.load("en_core_web_sm")
import json
import mysql.connector
mydb = mysql.connector.connect(
  [redacted]
)
mycursor = mydb.cursor()
nrows = mycursor.execute("select id,message from post where entities is null")
while True:
    row = mycursor.fetchone()
    ne_types = {}
    if row == None:
        break
    doc = nlp(row[1])
    for ent in doc.ents:
        if ent.label_ not in ne_types:
            ne_types[ent.label_] = 0
        ne_types[ent.label_] += 1

    sql = "update post set entities = '",json.dumps(ne_types),"' where id = ",row[0]
    mycursor.execute(sql)
    mydb.commit()  

当我在 mycursor.execute 语句处中断时，我的调试器说 sql 是一个元组。嗯，这听起来不对。当我尝试执行时出现错误

Traceback (most recent call last):
  File "C:\named-ent.py", line 29, in <module>
    mycursor.execute(sql)
  File "c:\python36\lib\site-packages\mysql\connector\cursor_cext.py", line 234, in execute
    self._cnx.handle_unread_result()
  File "c:\python36\lib\site-packages\mysql\connector\connection_cext.py", line 712, in handle_unread_result
    raise errors.InternalError("Unread result found")
mysql.connector.errors.InternalError: Unread result found

所以这确实是不对的（虽然我不能理解那个错误信息）。如何正确构造 sql 字符串？

更新我尝试了尼克的建议来更改代码，如下所示：

mycursor.execute("update post set entities = %s where id = %s", (json.dumps(ne_types), row[0]))

并且仍然出现错误：

Traceback (most recent call last):
  File "C:\named-ent.py", line 28, in <module>
    mycursor.execute("update post set entities = %s where id = %s", (json.dumps(ne_types), row[0]))
  File "c:\python36\lib\site-packages\mysql\connector\cursor_cext.py", line 234, in execute
    self._cnx.handle_unread_result()
  File "c:\python36\lib\site-packages\mysql\connector\connection_cext.py", line 712, in handle_unread_result
    raise errors.InternalError("Unread result found")
mysql.connector.errors.InternalError: Unread result found

标签： mysqlpython-3.x