想用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")

我的尝试:

  1. 先开始以为格式化% 的原因,把value(%d,%s,%s,%s,%d)改为了value(%s,%s,%s,%s,%s),运行一次发现还是不对
  2. 修改execute的对应的元组参数,还是不对
  3. 把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)

总结:

  1. sql语句的单引号’'改为``
  2. execute的元组参数还是用单引号’’
Logo

为开发者提供学习成长、分享交流、生态实践、资源工具等服务,帮助开发者快速成长。

更多推荐