Python 和 mySQLdb 错误:OperationalError:(1054,“'where 子句'中的未知列") [英] Python and mySQLdb error: OperationalError: (1054, "Unknown column in 'where clause'")

查看:62
本文介绍了Python 和 mySQLdb 错误:OperationalError:(1054,“'where 子句'中的未知列")的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

大家好,我遇到了一个错误

Hey all, I'm getting an error

OperationalError: (1054, "'where 子句'中的未知列'XX'")

OperationalError: (1054, "Unknown column 'XX' in 'where clause'")

以下代码中XX为CLASS的值

Where XX is the value of CLASS in the following code

conn = MySQLdb.connect(host = "localhost",user = "user", passwd = "pass",db = "dbase")
cursor = conn.cursor()
cursor.execute("""SELECT * FROM %s WHERE course =%s AND sec = %s""" % (str(DEPT),str(CLASS),str(SEC),))

问题是,我只会在某些值时收到此错误,即当 CLASS 包含一个字母时.如果有帮助,我将表设置为 varchar

The thing is, I only get this error with certain values, namely, when CLASS contains a letter. I have the table set up as varchar if that helps

谢谢!

推荐答案

不要在 SQL 中使用字符串注入",除非它确实是必不可少的情况,例如 str(DEPT)在这里选择您要选择的表.对于其他所有情况,请改用 Python DB API 的参数传递功能——它会为您正确引用内容并自动保护您免受SQL 注入"攻击等.(有时也可以更快).

Don't use "string injection" into your SQL except for cases where it's truly indispensable, such as the str(DEPT) here to choose what table you're selecting from. For every other case, use the parameter passing feature of the Python DB API instead -- it will quote things properly for you and automatically defend you against "SQL injection" attacks, among other things. (It can also be faster, sometimes).

由于 MySQLdb 使用不幸的符号 %s 作为参数,这是您应该做的(还将样式修复为符合 PEP8,不是必需的,但不会受到伤害;-):

Since MySQLdb uses the unfortunate notation %s for parameters, here's what you should do (also fixing the style to be PEP8-compliant, not required but can't hurt;-):

conn = MySQLdb.connect(host="localhost", user="user", passwd="pass", db="dbase")
cursor = conn.cursor()
q = 'SELECT * FROM %s WHERE course=%%s AND sec = %%s""" % (DEPT,)
cursor.execute(q, (CLASS, SEC))

产生q的字符串格式化中的%%s在格式化时变成一个单独的%,所以q 留下了两次 %s -- execute 用正确格式的 CLASSSEC 填充它们.所有的 str 调用都是多余的,等等.

The %%s in the string formatting which produces q become a single % each upon formatting, so q is left with two occurrences of %s -- which the execute fills in neatly with correctly formatted versions of CLASS and SEC. All the str calls are redundant, etc.

顺便说一句,如果您使用的是 Python 2.6 或更高版本,对于字符串格式,您应该使用新的 format 方法而不是旧的 % 运算符 -- 即使您无需那些双倍百分比符号",以及其他优势.我没有在上面的代码片段中应用该更改,以防万一您坚持使用 2.5 或更早版本(因此上面的代码适用于任何版本的 Python,而不仅仅是最近的版本).

As an aside, if you're on Python 2.6 or later, for string formatting you should use the new format method instead of the old % operator -- that saves you from the need for those "doubled up % signs", among other advantages. I haven't applied that change in the above snippet just in case you're stuck with 2.5 or earlier (so the code above works in any version of Python, instead of just in reasonably recent ones).

这篇关于Python 和 mySQLdb 错误:OperationalError:(1054,“'where 子句'中的未知列")的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆