Question

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:

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

我发现的错误并不非常有用(sqlite3.Operationalalal 错误:接近“?”: syntax误差,但我看上去:Pysqlite并不欣赏以这种方式使用土地持有人。

谁能指出,在这样做的同时,还有哪些进展?

Answer 1

您根本不能使用排位持有人来填写专栏或表格。我对此没有权威的引言——我“知道”,这只是从审判和失败中来。它具有某种意义:

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

简言之,你找到了正确的方式——使用扼制格式。

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

<>1> 并非所有驾驶员都引用参数——<代码>oursqltt. ,因为它分别向服务器发送了文件卡和论点。

Answer 2

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

友情链接