PySQLite Problem

[Jeremy Fincher]

alc at PublicPropertySoftware.com wrote in message news:<3EBC5E5D.8BF0BD25 at PublicPropertySoftware.com>...
> I'm doing some work with PySQLite, and pretty good results
> for quite some time, no problems at all.  Then, today I
> hit this one.  The code reads roughly:
> 
>                     SQL = """update nodes
>                                 set nodesequence    = %s,
>                                 nodename            = '%s',
>                                 modifieddatetime    = '%s',
>                                 modifiedby          = '%s'
>                                 where node == %s"""
>                     try:
>                         cursor.execute( SQL % (
>                                     nodeSequence,
>                                     newTitle,
> 				    newTime, 		
>                                     aName,
>                                     aNode ) )
> 
> The nodesequence and  node fields are integer, and the 
> others are strings. Problem happens when newTitle 
> contains unpaired tick marks, as in e.g. O'Reilly.  This 
> throws an exception in PySQLite with a format error for 
> the SQL. 

PySQLite (and any DBAPI-compliant module, actually) will handle all
the quoting for you.  You just give it your SQL, %ses intact, and give
the rest of the arguments at parameters to cursor.execute.  You code
above should look like this:

sql = """UPDATE nodes SET
         nodesequence=%s,
         nodename=%s,
         modifieddatetime=%s,
         modifiedby=%s
         WHERE node=%s""" # Should that be == in your code above?
cursor.execute(sql, nodeSequence, newTitle, newTime, aName, aNode)

PySQLite will handle all the quoting -- you'll note that I don't have
your single quotes in the sql string.

> TIA for not laughing.

Don't worry, I did the exact same thing when I started using Python
and SQL together :)

Jeremy