使用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 FilesPython36libsite- 
packagesmysqlconnectorconnection_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:UsersAcerDesktopRemastered.py", line 128, in <module>
insert(50, 'test', 20, 10)
File "C:UsersAcerDesktopRemastered.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 FilesPython36libsite-packagesmysqlconnectorcursor_cext.py", line 266, in execute
raw_as_string=self._raw_as_string)
File "C:Program FilesPython36libsite-packagesmysqlconnectorconnection_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 INTO store (id, quantity, price, item) 
VALUES (%s, %s, %s, %s)", (id, item, quantity, price))

请注意,和NOT%会产生严重的SQL注入错误。

您的查询被扩展到以下内容：

INSERT INTO store (...) VALUES (1, test, 1, 1.0);

其中test部分根本没有被引用，并且被视为可能的列名。

Answer 2

您混淆了insert语句中的列顺序，将"insert INTO store(id，quantity，price，item("更改为"insert INTO store(id，item，quantities，price("，请注意列名item在哪里！导入mysql.connector

def create_table():
cur.execute("CREATE TABLE IF NOT EXISTS store " 
"(id INT PRIMARY KEY, item VARCHAR(25), quantity INT, price REAL);")
conn.commit()
return
def insert(id, item, quantity, price):
cur.execute("INSERT INTO store (id, item, quantity, price)" 
"VALUES (%s, '%s', %s, %s)" % (id, item, quantity, price))
conn.commit()
return
#
conn = mysql.connector.connect(user="root",
host='localhost',database='DO',port='3306')
cur = conn.cursor()
create_table
insert(50, 'test', 20, 10)
insert(51, 'test', 20, 10)
insert(52, 'test', 20, 10)
conn.close()

来自MySQL

mysql> select * from store;
+----+------+----------+-------+
| id | item | quantity | price |
+----+------+----------+-------+
| 50 | test |       20 |    10 |
| 51 | test |       20 |    10 |
| 52 | test |       20 |    10 |
+----+------+----------+-------+
3 rows in set (0.00 sec)
mysql>  INSERT INTO store (id, item, quantity, price) VALUES (53, 'test', 20, 10);
Query OK, 1 row affected (0.00 sec)
mysql> select * from store;
+----+------+----------+-------+
| id | item | quantity | price |
+----+------+----------+-------+
| 50 | test |       20 |    10 |
| 51 | test |       20 |    10 |
| 52 | test |       20 |    10 |
| 53 | test |       20 |    10 |
+----+------+----------+-------+
4 rows in set (0.00 sec)