pymysql模块:pymysql.err.ProgrammingError: (1064, “You have an error in your SQL syntax; check
想用pymysql操作mysql,使用了以下代码运行出现了错误import pymysql.cursors# 连接到数据库connection = pymysql.connect(host='localhost',user='root',password='111',database='myemployees',
·
想用pymysql操作mysql,使用了以下代码运行出现了错误
import pymysql.cursors
# 连接到数据库
connection = pymysql.connect(host='localhost',
user='root',
password='111',
database='myemployees',
cursorclass=pymysql.cursors.DictCursor)
with connection:
with connection.cursor() as cursor:
# Create a new record
sql = "INSERT INTO `my_employees`('Id', 'First_name', 'Last_name', 'Userid', 'Salary')" \
" VALUES (%s, %s, %s, %s, %s)"
cursor.execute(sql, (6, 'hangjie', 'zheng', 'zhj', 0))
# connection is not autocommit by default. So you must commit to save
# your changes.
connection.commit()
with connection.cursor() as cursor:
# Read a single record
sql = "SELECT * FROM `my_employees` WHERE `id`=%s"
cursor.execute(sql, (5,))
result = cursor.fetchone()
print(result)
错误异常为
pymysql.err.ProgrammingError: (1064, "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 ''Id', 'First_name', 'Last_name', 'Userid', 'Salary') VALUES (6, 'hangjie', 'zhen' at line 1")
我的尝试:
- 先开始以为格式化% 的原因,把value(%d,%s,%s,%s,%d)改为了value(%s,%s,%s,%s,%s),运行一次发现还是不对
- 修改execute的对应的元组参数,还是不对
- 把sql语句的单引号’'改为``,就好了
改为:
import pymysql.cursors
# 连接到数据库
connection = pymysql.connect(host='localhost',
user='root',
password='111',
database='myemployees',
cursorclass=pymysql.cursors.DictCursor)
with connection:
with connection.cursor() as cursor:
# Create a new record
sql = "INSERT INTO `my_employees`(`Id`, `First_name`, `Last_name`, `Userid`, `Salary`)" \
" VALUES (%s, %s, %s, %s, %s)"
cursor.execute(sql, (6,'hangjie','zheng','zhj',0))
# connection is not autocommit by default. So you must commit to save
# your changes.
connection.commit()
with connection.cursor() as cursor:
# Read a single record
sql = "SELECT * FROM `my_employees` WHERE `id`=%s"
cursor.execute(sql, (6,))
result = cursor.fetchone()
print(result)
总结:
- sql语句的单引号’'改为``
- execute的元组参数还是用单引号’’
更多推荐
已为社区贡献1条内容
所有评论(0)