pysqlite: Placeholder substitution for column or t

问题:

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

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?

回答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.
• 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))

---

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

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

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.