470,561 Members | 1,915 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,561 developers. It's quick & easy.

Re: sqlite3 - adding tables and rows via parameters

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 +1 571 484 6266 +1 800 494 3119
Holden Web LLC http://www.holdenweb.com/

Jun 27 '08 #1
0 961

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

7 posts views Thread by Miro | last post: by
6 posts views Thread by Jorgen Bodde | last post: by
reply views Thread by Steve Holden | last post: by
reply views Thread by Ben Lee | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.