Python/MySQL查询错误:“未知列" [英] Python/MySQL query error: `Unknown column`

问题描述

此脚本旨在充当命令行前端，以将记录添加到本地托管的MySQL数据库中.

This script is meant to act as a command-line front-end to add records to a locally hosted MySQL database.

我收到此错误:

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

但是，西瓜是我要输入的值，而不是列名！

这是脚本:

#! /usr/bin/python

#use command line as front end to enter new rows into locally hosted mysql database

import mysql.connector

#create inputs
new_fruit = raw_input('What fruit do you want to add? ')
new_fruit_type = raw_input('Which type of ' + new_fruit + '? ')

#connect to dbase
conn = mysql.connector.connect(user='root', password='xxxx', database='play')

#instansiate cursor
cursor = conn.cursor()

#define sql statement
add_record = "INSERT INTO fruit (name, variety) VALUES (%s, %s)" % (new_fruit, new_fruit_type)

#execute sql
cursor.execute(add_record)

#close out
conn.commit()
cursor.close()
conn.close()

表结构:

mysql> describe fruit;
+---------+----------+------+-----+---------+----------------+
| Field   | Type     | Null | Key | Default | Extra          |
+---------+----------+------+-----+---------+----------------+
| id      | int(11)  | NO   | PRI | NULL    | auto_increment |
| name    | char(30) | YES  |     | NULL    |                |
| variety | char(30) | YES  |     | NULL    |                |
+---------+----------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

推荐答案

"INSERT INTO fruit (name, variety) VALUES (%s, %s)" % ("watermelon", "melon")

从字面上变得

INSERT INTO fruit (name, variety) VALUES (watermelon, melon)

watermelon和melon是列，而不是字符串.要解决此问题，请在您的%s周围加上引号.

Instead of strings, watermelon and melon are columns. To fix this, put quotes around your %s.

"INSERT INTO fruit (name, variety) VALUES ('%s', '%s')" % (new_fruit, new_fruit_type)

但是，您应该以以下方式运行它:

However, you should run it as:

cursor.execute("INSERT INTO fruit (name, variety) VALUES (%s, %s)", (new_fruit, new_fruit_type));

注意，我们删除了%s周围的引号，并将变量作为第二个参数传递给execute方法. Execute防止从变量中进行sql注入，并将字符串用引号引起来.

Notice we took away the quotations around the %s and are passing the variables as the second argument to the execute method. Execute prevents sql injection from the variables as well as wraps strings in quotation marks.

有关更多信息，请参见 http://mysql-python.sourceforge.net /MySQLdb.html#some-examples

For more information, see http://mysql-python.sourceforge.net/MySQLdb.html#some-examples

这篇关于Python/MySQL查询错误:“未知列"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！