Re: [pysqlite] statement generation (without concatenating strings)

----- "Eric S. Johansson" <[email protected]> wrote:

> recent discussion about not using string concatenation/substitution to
> generate
> SQL statements is finally sinking into my thick skull.  however, I'm
> not quite
> grocking how to build select statements without them.  Here's what I'm
> generating:
> 
> in this case, I'm generating a select with or without a like clause.
> 
> s1 = select * from <table> where <s2> <just in case>;"
> 
> s2 = (state="red" or state="green") and tpblue_id=? <like option>
> 
> like option = (<field> like '%<string>%) | ""
> 
> I think, if I replace all of the <xx> with :xx  and use a dictionary
> to pass the
> values and, I should be okay assuming pysqlite has the dictionary
> substitution
> options.  I do hate the '?' substitution and counting array elements. 
> Debugging
> is such a pain.
> 
> 
> The second example is a little more complicated...
> 
> s1 = "select * from <table> where <s2> <id>;"
> 
> s2 = "state=<state>"|"key=<key>"
> 
> order = " ORDER BY <order_field>" | ""
> 
> id = " and tpblue_id='<tpblue_ID>' | ""
> e 
> again, it looks like the :xx plus dictionary solution looks like the
> right path.
>  I assume it's okay to build these composite with string concatenation
> as long
> as I don't pass arguments?
> 
> ---eric 

See the named style described here:
http://oss.itsystementwicklung.de/download/pysqlite/doc/sqlite3.html#cursor-objects

It uses named parameters and a dictionary. It won't work for the table name , but you can
do string concatenation to add it.

Adrian Klaver
[email protected]