pysqlite:占位符替代列或表名?

Using pysqlite I am making a procedure to do something with some data. The same kind of operation is done on similar fields in multiple tables and columns, so I thought I could parameterize the sql statement as shown below:

我使用pysqlite做了一个处理一些数据的过程。同样的操作在多个表和列中的类似字段中进行，因此我认为可以将sql语句参数化如下所示:

def foo():
  column = 'c'
  table = 't'
  row = 1
  # preferred approach, gives syntax error
  c.execute('SELECT ? FROM ? WHERE id=?', (column, table, row))
  # sanity check, works fine
  c.execute('SELECT c FROM t WHERE id=?', (row))
  # workaround, also works, but is this the right way?
  c.execute('SELECT % FROM % WHERE id=?' % (column, table), row))

The error I get is not very helpful (sqlite3.OperationalError: near "?": syntax error), but I get the point: Pysqlite does not appreciate placeholders being used in this way.

我得到的错误不是很有帮助(sqlite3)。附近OperationalError:“?”:语法错误)，但是我明白了:Pysqlite不喜欢用这种方式使用占位符。

Can anyone point out what is going on here along with the proper way of doing the above?

谁能指出这里发生了什么以及如何正确地完成上述任务?

2 个解决方案

#1

You simply can not use placeholders for column or table names. I don't have a authoritative citation for this -- I "know" this only from having tried it and from failing. It makes some sense though:

不能为列名或表名使用占位符。我对此没有一个权威的引用——我“知道”这仅仅是因为我尝试过，也因为我失败过。这是有道理的:

If the columns and table could be parametrized, there would be little purpose to preparing (execute-ing) the SQL statement before fetching, since all parts of the statement could be replaced.
如果列和表可以参数化，那么在获取之前准备(执行)SQL语句就没有什么意义了，因为语句的所有部分都可以被替换。
I'm not sure about pysqlite¹, but MySQLdb automatically quotes all string parameters. Column and table names should not be quoted. So it would complicate the parsing required by the driver if it had to decide if a placeholder represented a column or table name versus a value that needs quoting.
我不确定pysqlite1，但是MySQLdb会自动引用所有字符串参数。不应该引用列和表名。因此，如果必须决定占位符是表示列还是表名，还是需要引用的值，就会使驱动程序所需的解析变得复杂。

In short, you've found the right way -- use string formating.

简而言之，您找到了正确的方法——使用字符串格式。

c.execute('SELECT {} FROM {} WHERE id=?'.format(column, table), row))

¹ Not all drivers quote parameters -- oursql doesn't, since it sends SQL and arguments to the server separately.

不是所有的驱动都引用参数——oursql没有，因为它分别向服务器发送SQL和参数。

#2

As @unutbu answered, there is no way to use placeholders for table/column names. My suggestion to do what you are doing now, but to also quote the table names to protect yourself from a table or column that might have an odd name.

正如@unutbu所回答的，没有办法为表/列名称使用占位符。我的建议是做您现在正在做的事情，但也要引用表名，以保护您不受可能具有奇怪名称的表或列的影响。

What does the SQL Standard say about usage of backtick(`)? already explains this to some extent, and in spite of the opinion in that answer, I would say that in your case, quoting is a good idea.

SQL标准对backtick(')的使用有什么规定?已经在某种程度上解释了这一点，尽管在那个答案中有很多观点，我想说，在你的例子中，引用是一个好主意。

#1