jeudi 8 octobre 2015

SQLite: Why can't parameters be used to set an identifier?

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.

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

After some more reading, I realized parameters cannot be used for identifiers, only for literal values. The PyMOTW on sqlite3 says

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 says on p. 135 of Using SQLite (ISBN 9780596521189):

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?

Similar SO question:

Aucun commentaire:

Enregistrer un commentaire