使用Python访问具有可变列名的SQL [英] Using Python to access SQL with a variable column name

问题描述

我对Python(和一般的编码)很陌生，并尝试了一个小项目，该项目使用pymysql将我的代码链接到MySQL数据库.总的来说，一切都进行得很顺利，但是我很难使用以下函数来查找变量列的最小值.

I'm quite new to Python (and coding in general) and trying a small project that links my code to a MySQL database using pymysql. In general everything has gone smoothly but i'm having difficulty with the following function to find the min of a variable column.

def findmin(column):
    cur = db.cursor()
    sql = "SELECT MIN(%s) FROM table"
    cur.execute(sql,column)
    mintup = cur.fetchone()

如果一切顺利，这将使我得到一个元组，例如(1，)

if everything went smoothly this would return me a tuple with the min e.g. (1,)

但是如果我运行该功能，问题就出在

however the issue is if i run the function:

findmin(column_name)

我必须将列名放在"(即"column_name")中，否则Python会将其视为未知变量.但是，如果我将引号放在column_name周围，则SQL会看到

i have to put column name in "" (i.e. "column_name") else Python sees it as an unknown variable. But if i put the quotation marks round column_name then SQL sees

SELECT MIN("column_name") FROM table

只是返回列标题而不是值. 有什么想法可以解决这个问题吗?

which just returns the column header not the value. Any ideas how i can get round this?

感谢您的帮助！

推荐答案

问题很可能是使用%s作为列名.这意味着 SQL驱动程序将在插补变量时尝试转义该变量，包括引号，这不是您想要的，例如列名，表名等.

The issue is likely the use of %s for the column name. That means the SQL Driver will try to escape that variable when interpolating it, including quoting, which is not what you want for things like column names, table names, etc.

在SELECT，WHERE等中使用值时，您确实想使用%s防止 SQL注入并启用引号其他东西.

When using a value in SELECT, WHERE, etc. then you do want to use %s to prevent SQL injections and enable quoting, among other things.

在这里，您只想使用纯python进行插值.这也意味着没有绑定元组传递给execute方法.

Here, you just want to interpolate using pure python. That also means no bindings tuple passed to the execute method.

def findmin(column):
    cur = db.cursor()
    sql = "SELECT MIN({0}) FROM table".format(column)
    cur.execute(sql)
    mintup = cur.fetchone()

SQL小提琴显示了SQL的工作原理:

SQL fiddle showing the SQL working:

http://sqlfiddle.com/#!2/e70a41/1

这篇关于使用Python访问具有可变列名的SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！