SQLite DBAPI语法问题Python 3.5

Question

我一直在搜索堆栈溢出以及其他一些在线研究，寻找将变量插入SQLite查询的正确语法。但是，这3种受支持的语法都不适用于我的查询

我发现SQLite DB-API支持的三种语法是：

qmark:
curs.execute("SELECT * FROM actors where actor.first_name = ?",("D'Angelo", ))

numeric:
curs.execute("SELECT * FROM actors where actor.first_name = :1", ("D'Angelo", ))

named:
curs.execute("SELECT * FROM actors where actor.first_name = :first_name",{'first_name': "D'Angelo"})

取自http://www.philvarner.com/test/ng-python3-db-api/

但是，当我执行以下操作时，会收到以下错误：

named:

tableListQuery = "SELECT name FROM :dbFile WHERE type='table' ORDER BY Name", {'dbFile': dbFile}
userCursor.execute(tableListQuery)

Pycharm error: Expected type 'str', got 'Tuple[str, Dict[str, Any]]' 

Terminal error:
File "./dedupeDatabase.py", line 15, in Describe
userCursor.execute(tableListQuery)
ValueError: operation parameter must be str
-------------------------------------------------
qmark:

tableListQuery = "SELECT name FROM ? WHERE type='table' ORDER BY Name", (dbFile, )
userCursor.execute(tableListQuery)

Pycharm error: Expected type 'str', got 'Tuple[str, Tuple[Any]]' instead

Terminal error:
File "./dedupeDatabase.py", line 15, in Describe
userCursor.execute(tableListQuery)
ValueError: operation parameter must be str
-------------------------------------------------
numeric:

tableListQuery = "SELECT name FROM :1 WHERE type='table' ORDER BY Name", (dbFile, )
userCursor.execute(tableListQuery)

Pycharm error: Expected type 'str', got 'Tuple[str, Tuple[Any]]'

Terminal error:
File "./dedupeDatabase.py", line 15, in Describe
userCursor.execute(tableListQuery)
ValueError: operation parameter must be str

请提供您对此问题的任何见解

Answer 1

所以你的基本问题就是语法。将tableListQuery定义为"my query string", params定义了一个元组；您不能将该元组直接传递给execute，文档也没有说明您可以传递

您需要按照您引用的原始代码段中所示执行：

tableListQuery = "SELECT name FROM table WHERE type=:dbFile ORDER BY Name"
params = {'dbFile': dbFile}
userCursor.execute(tableListQuery, params) 

现在第二个问题，正如在注释中指出的，是不能将SQL参数用于字段或表名之类的内容；仅适用于WHERE值。您需要使用普通的Python字符串格式

[1]您可以使用*操作符来完成，但这可能会让人困惑