sqlite3 - adding tables and rows via parameters

[Steve Holden]

Vlastimil Brom wrote:
> Hi all,
> I would like to ask about the usage of sqlite3 in python, more 
> specifically about a way to pass table 
> or column names to a SQL commands using parameters.
> All examples I could find use 
> the parameter substitution with "?" for values; is it possible the specify table and column names this way?
> e.g. something like
> curs.execute(u'INSERT OR REPLACE INTO %s(%s) VALUES (?)' % ("people", 
> "email"), ("qwe at asd.zx",))
> (And the similar cases e.g.: CREATE TABLE, ALTER TABLE ... ADD.)
> Unfortunately, I wasn't successful in replacing the string interpolation 
> with the substitution notation; are there maybe any hints, how to do 
> that? Or is it ok to use string interpolation here? (Are these parts of 
> the SQL command vulnerable too?)
> 
> What I am trying to achieve is to build a database dynamically - based 
> on the input data the respective 
> table would be chosen, and the appropriate columns created and filled with the content.
> 
> Thanks in advance for any suggestions - and sorry if I missed something 
> obvious...
>          Vlasta
> 
> 
The thing that will stop you from using a tablename as an argument to a 
parameterized query is that (the) front-ends (I am familiar with) don't 
allow table names to be parameterized ...

The main points of parameterization are

1) To let the driver handle the creation of syntactically correct SQL 
(thereby avoiding , e.g. SQL injection attacks) and

2) To allow the driver to get the back-end to optimize the query by 
developing an execution plan into which the parameters can ve inserted 
at run time, avoiding repeated recompilation of the same query. It's 
this that stops most backends from allowing table names, since the 
optimizations depend upon the table's characteristics and contents.

As far as ? vs %s goes, that depends on the particular module's 
paramstyle. For sqlite3:

 >>> import sqlite3
 >>> sqlite3.paramstyle
'qmark'
 >>>

so you just have to go with what it implements.

regards
  Steve
-- 
Steve Holden        +1 571 484 6266   +1 800 494 3119
Holden Web LLC              http://www.holdenweb.com/