Python 和 mySQLdb 错误：操作错误：(1054，“‘where 子句’中的未知列”)

发布于 2024-09-14 10:12:32 字数 434 浏览 3 评论 0原文

大家好，我收到一个错误

操作错误：（1054，“‘where 子句’中未知列‘XX’”）

其中 XX 是以下代码中 CLASS 的值

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

谢谢！

原文

Hey all, I'm getting an error

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

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),))

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

Thanks!

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

清秋悲枫 2024-09-21 10:12:32

不要在 SQL 中使用“字符串注入”，除非确实必不可少，例如此处的 str(DEPT) 来选择要从中选择的表。对于所有其他情况，请使用 Python DB API 的参数传递功能 - 它会为您正确引用内容并自动保护您免受“SQL 注入”攻击等。（有时它也可以更快）。

由于 MySQLdb 使用不幸的符号 %s 作为参数，因此您应该执行以下操作（同时将样式修复为符合 PEP8 标准，不是必需的，但不会造成伤害；-）：

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 用格式正确的 CLASS 和 SEC 版本整齐地填充。所有 str 调用都是多余的，等等。

顺便说一句，如果您使用的是 Python 2.6 或更高版本，对于字符串格式化，您应该使用新的 format 方法而不是旧的 % 运算符——除了其他优点之外，它还可以让您不再需要那些“双倍的 % 符号”。我没有在上面的代码片段中应用该更改，以防万一您坚持使用 2.5 或更早版本（因此上面的代码适用于任何版本的 Python，而不仅仅是最近的版本）。

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).

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))

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.

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).

回复收藏 0 原文