Using parameters in a CREATE TABLE ... (column DEFAULT ?) clause, in Python sqlite3 db-api

Question

I want to use python to execute a CREATE STATEMENT clause, specifying default values for certain columns using parameter substitution with ? (so that I can safely specify defaults from python types).

It doesn't appear to work, although it works fine when I use parametric statements in a select or similar statement. For example:

As expected:

>>> list(sqlite3.connect('sensemap.sql3').cursor().execute('select ?', (101,)))
[(101,)]

Unexpected:

>>> sqlite3.connect('sensemap.sql3').cursor().execute('create table mytable (mycolumn default ?)', (101,))
Traceback (most recent call last):
  File "<console>", line 1, in <module>
OperationalError: near "?": syntax error

Why is that? Is what I'm attempting possible?

score 1 · Accepted Answer · answered Apr 14 '14 at 09:12

The original SQL statements of all your schema objects are stored in the database (try SELECT sql FROM sqlite_master). With such a simple text value, it is not possible to remember parameter values, so parameters are not allowed in CREATE xxx statements.

Using parameters in a CREATE TABLE ... (column DEFAULT ?) clause, in Python sqlite3 db-api

1 Answers1