PySQLite Problem

[Steve Holden]

"Jeremy Fincher" <tweedgeezer at hotmail.com> wrote in message
news:698f09f8.0305092108.6aa19057 at posting.google.com...
> 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 :)
>
Me too!

Be careful, though. Strict DBA-compliance would use only two arguments to
the execute(), the first being the SQL statement and the second being a
tuple of the data items to be used in parameter substitution.

regards
--
Steve Holden                                  http://www.holdenweb.com/
Python Web Programming                 http://pydish.holdenweb.com/pwp/