Python，MySQLdb和转义表名? [英] Python, MySQLdb and escaping table names?

问题描述

我可能缺少明显的东西，但是我无法弄清楚我的代码与在MySQLdb在线文档中看到的各种示例有何不同.

I may be missing something obvious, but I can't figure out how my code is different from various examples I see in the online documentation for MySQLdb.

我对python编程还是比较陌生，对perl更有经验.我想做的是尽早养成一定的良好习惯(例如在perl中，我总是以"use strict; use warnings"开头)，因此，我试图确保在独立文件中创建可重用的函数( funct.py)，以节省以后的时间.

I'm fairly new to programming python, more experienced with perl. What I'm trying to do is get into certain good habits early on (like in perl I always start with 'use strict; use warnings'), so I'm trying to ensure I create re-usable functions in an independent file (funct.py) to save me time later on down the line.

我具有用于从表格中选择随机行的功能:

I've got this function for selecting random rows from a table:

def returnRandom(conn,countcol,table,field):
    cursor = conn.cursor()
    cursor.execute('SELECT MAX(%s) FROM %s',(countcol,table))
    maxRow = cursor.fetchone()[0]
    cursor.execute("SELECT MIN(%s) FROM %s",(countcol,table))
    minRow = cursor.fetchone()[0]
    randomId = random.randrange(minRow,maxRow+1,1)
    cursor.execute("SELECT ? FROM ? WHERE id >=? LIMIT 1",field,table,randomId)
    return cursor.fetchone()[0]

被这样称呼:

msg = funct.returnRandom(conn,"id","testtable","data")

不幸的是，它出现以下错误: _mysql_exceptions.ProgrammingError:(1064，您的SQL语法有误；请查看与您的MySQL服务器版本相对应的手册，以获取在第1行的"testtable"附近使用的正确语法))

Unfortunately it errors out with: _mysql_exceptions.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''testtable'' at line 1")

如果我将testtable代替执行行上的％s，则查询将运行，但看起来像在运行

If I put testtable in in place of the %s on the execute line, The query will run but it looks like it runs

SELECT MAX('id') FROM testtable;

哪个当然会返回"id".

which of course returns 'id'.

考虑到这两个问题，它似乎在尝试执行它们时引用了％s条目.我想知道是否有人可以解释我如何停止做这些事情，或者我该如何真正地做我想达到的目标?我希望该函数尽可能通用，因此依赖于调用时传递给它的数据.

Given both of those it looks like it's quoting the %s entries when it tries to execute them. I was wondering if anyone could explain how I get it to stop doing that, or how I should actually being doing what I'm trying to achieve? I want the function to be as generic as possible, so relying on data being passed to it by when it's called.

edit:如果要替换为?，我应该添加.标记:

edit: I should add, if I substitute in ? marks:

....
    cursor.execute('SELECT MAX(?) FROM ?',(countcol,table))
File "/usr/lib/pymodules/python2.7/MySQLdb/cursors.py", line 151, in execute
    query = query % db.literal(args)
TypeError: not all arguments converted during string formatting

推荐答案

您不能将DB-API用于元数据.您将需要在execute()调用之外自行进行替换.

You cannot use DB-API for metadata; you will need to make replacements yourself outside of the execute() call.

query = 'SELECT MAX(%%s) FROM `%s`' % (table,)
cursor.execute(query, (countcol,))

如果table来自外部来源，显然您不应该这样做.

Obviously you should not do this if table comes from an outside source.

这篇关于Python，MySQLdb和转义表名?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！