Re: 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"), ("qw*@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 [phone] [phone]
Holden Web LLC http://www.holdenweb.com/