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

bnlyeluc 于 2021-06-18 发布在 Mysql

关注(0)|答案(2)|浏览(423)

首先我是一个初学者，我很感激你的帮助：）
当我插入一个字符串时，它会给我一个双引号或单引号的错误。这只发生在我调用我创建的函数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]

mysql python mysql-python

来源：https://stackoverflow.com/questions/53108865/inserting-string-in-mysql-using-python-unknown-column

2条答案

按热度按时间

j0pj023g1#

您混淆了insert语句中列的顺序，将“insert into store（id，quantity，price，item）”更改为“insert into store（id，item，quantity，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)

赞(0）回复(0）举报 2021-06-18

o4hqfura2#

您没有正确使用占位符功能。正确的行是：

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

注意 , 而不是 % 这就产生了严重的sql注入错误。
您的查询正在扩展为以下内容：

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

在哪里 test part完全不被引用，并被视为可能的列名。

赞(0）回复(0）举报 2021-06-18

我来回答

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

2条答案

相关问题

热门标签

最新问答