pysqlite:列名或表名的占位符替换? [英] pysqlite: Placeholder substitution for column or table names?

问题描述

使用 pysqlite 我正在制作一个程序来处理一些数据.对多个表和列中的相似字段进行了相同类型的操作，所以我想我可以将sql语句参数化如下:

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.OperationalError: near "?": syntax error)，但我明白了一点:Pysqlite 不喜欢以这种方式使用占位符.

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.

任何人都可以指出这里发生了什么以及执行上述操作的正确方法吗?

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

推荐答案

您根本不能对列名或表名使用占位符.我没有对此的权威引文——我知道"这一点只是因为尝试过和失败.不过还是有一定道理的:

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:

• 如果可以对列和表进行参数化，则几乎没有目的是在获取之前准备(execute-ing)SQL 语句，因为语句的所有部分都可以替换了.
• 我不确定 pysqlite¹，但 MySQLdb 会自动引用所有字符串参数.列名和表名不应被引用.所以如果必须的话，会使驱动程序所需的解析复杂化决定占位符是代表列名还是表名还是需要引用的值.

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

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

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

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

<小时>

¹ 并非所有驱动程序都引用参数——oursql 不会，因为它分别向服务器发送 SQL 和参数.

---

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

这篇关于pysqlite:列名或表名的占位符替换?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！