I am trying pull in a setting file that creates the tables I need. I started out with this.
cursor.execute("CREATE TABLE IF NOT EXISTS measurement (time DATETIME)")
This works. Moving it to using parameters I tried many different variation on this, but all get roughly the same error.
table_name = scrub_table_name('measurement')
field_name = some_function1('time')
field_type = some_function2('DATETIME')
cursor.execute("CREATE TABLE IF NOT EXISTS {} (? ?)".format(table_name), (field_name, field_type))
The error I get is this.
sqlite3.OperationalError: near "?": syntax error
I get the same if I used the bound version.
cursor.execute("CREATE TABLE IF NOT EXISTS {} (:fn :ft)".format(table_name), {"fn" : field_name, "ft" : field_type})
I get this error.
sqlite3.OperationalError: near ":ffn": syntax error
Just so there is no confusion to my question. Apparently I did need to move the table name to something I self scrub and insert into the string as the sqlite execute will not accept parameters for this part of the query. hence the ob.format(). While I would be interest in being wrong on this, I am mostly interested in how to make the ? of :fn parameters work. I am using Python 3.6.2 with sqlite3.16.0 on a home-brew configured Mac 10.12.6, not that I expect that to change it as I have also tested on a Debian system with the same errors.
Parameterized SQL expressions are only used for parameterizing literal values, not table/column names or data types. For example, the following are valid commands to pass to cursor.execute():
INSERT INTO table_name VALUES (?, ?);
SELECT * FROM table_name WHERE column_name = ?;
UPDATE table_name SET col1 = ? WHERE col2 = ?;
but CREATE TABLE IF NOT EXISTS table_name (? ?) is not, because replacing those ?s with literal values will always produce a syntactically invalid command.