SQLite:为什么不能使用参数来设置标识符? [英] SQLite: Why can't parameters be used to set an identifier?
问题描述
我正在重构一点 side project 使用 SQLite 而不是 python 数据结构，以便我可以学习 SQLite.我一直在使用的数据结构是一个字典列表，其中每个字典的键代表一个菜单项的属性.最终，这些键应该成为 SQLite 表中的列.
I'm refactoring a little side project to use SQLite instead of a python data structure so that I can learn SQLite. The data structure I've been using is a list of dicts, where each dict's keys represent a menu item's properties. Ultimately, these keys should become columns in an SQLite table.
我首先想到可以通过创建单列表、迭代字典键列表并执行ALTER TABLE、ADD COLUMN 以编程方式创建表> 命令如下:
I first thought that I could create the table programmatically by creating a single-column table, iterating over the list of dictionary keys, and executing an ALTER TABLE, ADD COLUMN command like so:
# Various import statements and initializations
conn = sqlite3.connect(database_filename)
cursor = conn.cursor()
cursor.execute("CREATE TABLE menu_items (item_id text)")
# Here's the problem:
cursor.executemany("ALTER TABLE menu_items ADD COLUMN ? ?", [(key, type(value)) for key, value in menu_data[0].iteritems()])
阅读更多内容后，我意识到参数不能用于标识符，只能用于文字值.sqlite3 上的 PyMOTW 说
After some more reading, I realized parameters cannot be used for identifiers, only for literal values. The PyMOTW on sqlite3 says
查询参数可与select、insert 和update 语句一起使用.它们可以出现在查询的任何文字值合法的部分.
Query parameters can be used with select, insert, and update statements. They can appear in any part of the query where a literal value is legal.
Kreibich 在 p. 上说.135 个使用 SQLite (ISBN 9780596521189):
Kreibich says on p. 135 of Using SQLite (ISBN 9780596521189):
但是请注意，参数只能用于替换文字值，例如带引号的字符串或数值.参数不能代替标识符，例如表名或列名.以下 SQL 位无效:
Note, however, that parameters can only be used to replace literal values, such as quoted strings or numeric values. Parameters cannot be used in place of identifiers, such as table names or column names. The following bit of SQL is invalid:
SELECT * FROM ?; -- INCORRECT: Cannot use a parameter as an identifier
我接受不能以这种方式使用位置或命名参数.他们为什么不能?是否有一些我遗漏的一般原则?
I accept that positional or named parameters cannot be used in this way. Why can't they? Is there some general principle I'm missing?
类似的问题:
推荐答案
标识符在语法上很重要，而变量值则不然.
Identifiers are syntactically significant while variable values are not.
在 SQL 编译阶段需要知道标识符，以便编译后的内部字节码表示知道相关的表、列、索引等.仅更改 SQL 中的一个标识符可能会导致语法错误，或者至少是一种完全不同类型的字节码程序.
Identifiers need to be known at SQL compilation phase so that the compiled internal bytecode representation knows about the relevant tables, columns, indices and so on. Just changing one identifier in the SQL could result in a syntax error, or at least a completely different kind of bytecode program.
文字值可以在运行时绑定.变量在编译后的 SQL 程序中的行为本质上是相同的，而不管它们中绑定的值如何.
Literal values can be bound at runtime. Variables behave essentially the same in a compiled SQL program regardless of the values bound in them.
这篇关于SQLite:为什么不能使用参数来设置标识符?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
