-1

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.

Rhett
  • 11
  • 4

1 Answers1

1

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.

jwodder
  • 54,758
  • 12
  • 108
  • 124
  • Could you explain why I can use the table name in the second example but not in my version? Could you also link any documentation about this as the documentation I was using said otherwise. See [Here](https://docs.python.org/2/library/sqlite3.html). "Instead, use the DB-API’s parameter substitution. Put ? as a placeholder wherever you want to use a value, and then provide a tuple of values as the second argument to the cursor’s execute() method." – Rhett Oct 13 '17 at 19:51
  • "Could you explain why I can use at table name in the second example but not in my version?" — Are you referring to the `SELECT * FROM table_name ...` example? `table_name` there isn't a variable; it's just what I named the example table so it'd be clear that it's a table. Regarding what the docs say, they confirm that you can only use placeholders for SQL *values*, not object names or types. – jwodder Oct 13 '17 at 19:54
  • I am sorry I meant to ask about column name, neither of us is passing table name as a parameter, but mine is giving errors on the column name and your is not. I was not sure I understood what the difference was between our two uses except for yours being as select statement. – Rhett Oct 13 '17 at 20:03
  • In my second example, the `?` in `column_name = ?` is meant to be replaced with a value; e.g., `cursor.execute('SELECT * FROM table_name WHERE column_name = ?', (42,))` will execute `SELECT * FROM table_name WHERE column_name = 42`. – jwodder Oct 13 '17 at 20:04
  • Understood, so why is mine not the equivalent? I wish to insert a 42 for the ? in my statement where the column name needs to go. – Rhett Oct 13 '17 at 20:08
  • Because column names are not SQL values; they're identifiers, which cannot be parameterized. – jwodder Oct 13 '17 at 20:09
  • I think I understand, thank you for the clarifications. – Rhett Oct 13 '17 at 20:13