python通过mysql.connector向mysql中插入数据时出现1064 (42000)语法错误解决方案_insert error 1064 (42000)

python通过mysql.connector向mysql中插入数据时出现1064 (42000)语法错误解决方案

首先在这里声明一下我用的是python3.8.0版本，mysql版本为5.5.47，mysql.connector库版本为2.2.9。

1、要连接的mysql数据库为woaini，执行insert插入语句
代码如下：

import mysql.connector

mydb = mysql.connector.connect(
    host="localhost",
    user="root",
    passwd="root",
    database="woaini"
)
mycursor = mydb.cursor()
mycursor.execute("INSERT INTO teacher (name) VALUES (%s)", ('一花一世界'))

mydb.commit()  # 数据表内容有更新，必须使用到该语句

print(mycursor.rowcount, "记录插入成功。")
1
2
3
4
5
6
7
8
9
10
11
12
13
14

报出的错误如下：

Traceback (most recent call last):
  File "D:/MyProgramming/python3/hello.py", line 808, in <module>
    mycursor.execute("INSERT INTO teacher (name) VALUES (%s)", ('一花一世界'))
  File "D:\Application\python3\lib\site-packages\mysql\connector\cursor.py", line 551, in execute
    self._handle_result(self._connection.cmd_query(stmt))
  File "D:\Application\python3\lib\site-packages\mysql\connector\connection.py", line 490, in cmd_query
    result = self._handle_result(self._send_cmd(ServerCmd.QUERY, query))
  File "D:\Application\python3\lib\site-packages\mysql\connector\connection.py", line 395, in _handle_result
    raise errors.get_exception(packet)
mysql.connector.errors.ProgrammingError: 1064 (42000): 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 '%s)' at line 1

1
2
3
4
5
6
7
8
9
10
11

从错误提示中我们可以看出，出在了语法上的错误，在%s前后出现了语法错误。
这时我们尝试使用新的输出格式化语句.format()
代码如下：

mycursor1.execute("insert into teacher (name) values('{}')".format(val))
1

执行成功

感谢你的阅读，希望对你问题的解决有所帮助。如有疑问或不清楚的地方，欢迎评论。共同进步，加油。