Sqlite3. Substitution of names in query.

[Matteo]

On Oct 30, 7:10 am, Lacrima <lacrima.ma... at gmail.com> wrote:
> Hello!
>
> I use sqlite3 module for my sqlite database. I am trying to substitute
> table name in sql query.
>
> >>> import sqlite3
> >>> con = sqlite3.connect('mydb')
> >>> cur = con.execute("select * from table where name='Joe'")
>
> That's ok
>
> >>> cur = con.execute("select * from table where name=?", ('Joe',))
>
> That's ok too
>
> >>> cur = con.execute("select * from ? where name=?", ('table', 'Joe'))
>
> Traceback (most recent call last):
>   File "<string>", line 1, in <fragment>
> sqlite3.OperationalError: near "?": syntax error
>
> So what's wrong now?
> Is it impossible to substitute table names, using DB API?
> If so, what is a way to make table name substitutions? Are string
> operations like
> 'select * from %s where...' % tablename
> ok in this case?
>
> Thanks in advance!

I've found myself wanting this ability too, but alas, it is not
possible. SQLite statements are compiled into an intermediate bytecode
so that they can execute very quickly. This bytecode allows for
placeholders to be used for values, so that the same compiled bytecode
can be run for a multitude of values (handy for large INSERTS, of
course) without recompilation.

As I understand it, the bytecode is specific to the table(s) and
columns used in the statement. I don't know the specific mechanism,
but I would suspect that a column name gets converted to an offset
into a row, or to a pointer to a table's column array, or somesuch. In
particular, the code generated is probably drastically different
depending on whether or not a column in a table is indexed or not.
Thus, if a placeholder was used for a column, then the whole statement
would have to be recompiled each time it was run, which would do very
nasty things to efficiency.

So, if you really need that ability, you must use normal python string
interpolation.