使用python在mysql中插入字符串：未知列

Question

首先，我是初学者，我提前感谢您的帮助:)

当我插入一个字符串时，它给了我一个错误双引号或单引号。这只发生在我调用我创建的函数 insert() 时。但是当我只是用一个数字代替那些没有引号的字符串时，它就可以工作了。

import mysql.connector

conn = mysql.connector.connect(user="root",password='password',

       host='localhost',database='library',port='3306')

cur = conn.cursor()

def create_table():

    cur.execute("CREATE TABLE IF NOT EXISTS store 

               (id INT PRIMARY KEY, item VARCHAR(25), quantity INT, price REAL);")

    conn.commit()

    conn.close()

def insert(id, item, quantity, price):

    cur.execute("INSERT INTO store (id, quantity, price, item) 

                VALUES (%s, %s, %s, %s)" % (id, item, quantity, price))

    conn.commit()

    conn.close()

insert(50, 'test', 20, 10)

然后它给出了一个错误：

Traceback (most recent call last):

      File "C:\Program Files\Python36\lib\site- 

packages\mysql\connector\connection_cext.py", line 392, in cmd_query

        raw_as_string=raw_as_string)

    _mysql_connector.MySQLInterfaceError: Unknown column 'test' in 'field list'

    During handling of the above exception, another exception occurred:

    Traceback (most recent call last):

      File "C:\Users\Acer\Desktop\Remastered.py", line 128, in <module>

        insert(50, 'test', 20, 10)

      File "C:\Users\Acer\Desktop\Remastered.py", line 109, in insert

        cur.execute("INSERT INTO store (id, quantity, price, item) VALUES (%s, %s, %s, %s)" % (id, item, quantity, price))

      File "C:\Program Files\Python36\lib\site-packages\mysql\connector\cursor_cext.py", line 266, in execute

        raw_as_string=self._raw_as_string)

      File "C:\Program Files\Python36\lib\site-packages\mysql\connector\connection_cext.py", line 395, in cmd_query

        sqlstate=exc.sqlstate)

    mysql.connector.errors.ProgrammingError: 1054 (42S22): Unknown column 'test' in 'field list'

    [Finished in 0.8s with exit code 1]

Answer 1

您没有正确使用占位符功能。正确的行是：cur.execute("INSERT&nbsp;INTO&nbsp;store&nbsp;(id,&nbsp;quantity,&nbsp;price,&nbsp;item)&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;VALUES&nbsp;(%s,&nbsp;%s,&nbsp;%s,&nbsp;%s)",&nbsp;(id,&nbsp;item,&nbsp;quantity,&nbsp;price))注意,而不是&nbsp;%会造成严重的SQL 注入错误。您的查询已扩展为以下内容：INSERT&nbsp;INTO&nbsp;store&nbsp;(...)&nbsp;VALUES&nbsp;(1,&nbsp;test,&nbsp;1,&nbsp;1.0);该test部分根本没有被引用并被视为可能的列名。