Cannot use the sqlite3 parameter substitution with PRAGMA?

Question

I'm trying to change the user_version of an SQLite database via Python 2.6, and I've tried to do the following:

cur.execute( "PRAGMA user_version = ?" , (version,) )

It fails with the following error:

    cur.execute( "PRAGMA user_version = ?" , (version,) )
sqlite3.OperationalError: near "?": syntax error

I've tried the named style of substitution (instead of question marks) but that also fails with the same error.

If I drop a number in there as part of the SQL string or using Python's string operations it all works fine, but I'd rather not do either of those.

So why isn't this working?
And how do I safety insert a number from a variable in to this call?

Answer 1

Only certain values can be parametrized. Even table and column names can not be parametrized. Your experiment shows pragma values also can not be parametrized.

The user_version is expected to be an integer. You can use string formatting and protect yourself at the same time with

cur.execute( "PRAGMA user_version = {v:d}".format(v=version) )

The format {v:d} will raise a ValueError if version is not an integer.