#270 cursor.execute doesn't work with %s for tablenames

[Marco Lechner]

I try to use a variable as tablename for mysql-statements:
tablename = "testtable"
cursor.execute("""
DROP TABLE IF EXISTS %s
""", (tablename,))
But the tablename is expanded to 'testtable' therefore the DROP fails. Is there a way to use variables as tablenames?

fails:
cursor.execute("""
DROP TABLE IF EXISTS 'testtable'
""", (tablename,))
of course works:
cursor.execute("""
DROP TABLE IF EXISTS testtable
""")

Marco

Debian - lenny (MySQLdb 1.2.2)

[Kyle VanderBeek]

This is a WONTFIX (Andy?).

The %s substitution is for value literals only. It would be near impossible to figure out from context whether the %s was supposed to be a table name, column, or literal (such as if you tried to do "SELECT * FROM some_table where %s=%s"; which quoting rules apply where). Therefore this won't be fixed.

If you're doing dynamic SQL statement building like this (where more than just values change) you'll need to do it yourself (carefully, of course).

[Andy Dustman]

It's not supposed to work for DDL statements like CREATE TABLE et al.

[Marco Lechner]

could this be a feature request then - generally to implement a way to substitute also tablenames, columns, ...? Or ist it nearly impossible, undesierable or impractical?

[Andy Dustman]

I don't think it's possible without breaking compatibility with PEP-249. I don't think any other database module does this either. Table names and SQL snippets are not data/parameters.

[Marco Lechner]

so it's undesirable for compatibility reasons (PEP-249). Well that's a good reason. Thnx.

I think this "Bug" can be closed.