Sqlite3. Substitution of names in query.

[Robert Kern]

On 2009-11-02 14:47 PM, Lawrence D'Oliveiro wrote:
> In message<mailman.2416.1257062070.2807.python-list at python.org>, Dennis Lee Bieber wrote:
>
>> On Sun, 01 Nov 2009 19:08 +1300, Lawrence D'Oliveiro
>> <ldo at geek-central.gen.new_zealand>  declaimed the following in
>> gmane.comp.python.general:
>>
>>> On the grounds that Python has more general and powerful string
>>> parameter- substitution mechanisms than anything built into any database
>>> API.
>>
>> Really? In the case of MySQLdb, the DB-API /uses/ Pythons string
>> interpolation ...
>
> Only a limited subset thereof. For instance, I'm not aware of any database
> API that lets me do this:
>
>     sql.cursor.execute \
>        (
>              "update numbers set flags = flags | %(setflags)u where projectid = %(projectid)s"
>              "%(match_listid)s and number = %(number)s"
>          %
>              {
>                  "projectid" : SQLString(ProjectID),
>                  "match_listid" :
>                      ("", " and listid = %s" % SQLString(ListID))[ListID != None],
>                  "number" : SQLString(number),
>                  "setflags" : flags,
>              }
>        )

When programmatically generating SQL, I like to use SQLAlchemy. This use case is 
handled with .where() on Update expression objects. Personally, I find 
manipulating the SQLAlchemy expression objects clearer, safer, and more portable 
than building the raw SQL through string interpolation.

-- 
Robert Kern

"I have come to believe that the whole world is an enigma, a harmless enigma
  that is made terrible by our own mad attempt to interpret it as though it had
  an underlying truth."
   -- Umberto Eco