On Oct 30, 7:10 am, Lacrima <lacrima.ma...@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. -- http://mail.python.org/mailman/listinfo/python-list