Understanding Unicode & encodings

[Jim]

John Machin wrote:
> Jim wrote:
> > No, I'll bet that you'd like to run something like
> >   self.dcCursor.execute("INSERT INTO track (name, nbr, idartist,
> > idalbum,path) VALUES (%(track)s, %(nbr)s,
> > %(idartist)s,%(idalbum)s,'%(path)s')",
> > {'track':track,'nbr':nbr,'idartist':idartist,'idalbum':idalbum,'path':path})
> > (only without my typos).  That's an improvment for a number of reasons,
> > one of which is that the system will quote for you, for instance in
> > idartist="John's Beer" changing the single quote to two single quotes
> > to suit SQL.
> I see no improvement here.
>
> The OP's code is effectively::
>
>   sql = "INSERT INTO track (name, ..., path) VALUES ('%s', ..., '%s')"
>   value_tuple = (track, ...., path)
>   self.dcCursor.execute(sql % value_tuple)
>
> Your suggested replacement is effectively:
>
>   sql = "INSERT INTO track (name, ...,path) VALUES (%(track)s,
> ...,'%(path)s')"
>   str_fmt_dict = {'track':track, ...,'path':path}
>   self.dcCursor.execute(sql, str_fmt_dict)
>
> Well, that won't run at all. Let's correct the presumed typo:
>
>    self.dcCursor.execute(sql % str_fmt_dict)
I'm sorry, that wasn't a typo.  I was using what the dBapi 2.0 document
calls 'pyformat' (see the text under "paramstyle" in that document).

> Now, the only practical difference is that you have REMOVED the OP's
> explicit quoting of the first column value. Changing the string
> formatting from the %s style to the %(column_name) style achieves
> nothing useful. You are presenting the "system" with a constant SQL
> string -- it is not going to get any chance to fiddle with the quoting.
> However the verbosity index has gone off the scale: each column name is
> mentioned 4 times (previously 1).
Gee, I like the dictionary; it has a lot of advantages.

> I would suggest the standard default approach:
>
>   sql = "INSERT INTO track (name, ..., path) VALUES (?, ..., ?)"
>   value_tuple = (track, ...., path)
>   self.dcCursor.execute(sql, value_tuple)
>
> The benefits of doing this include that the DBAPI layer gets to
> determine the type of each incoming value and the type of the
> corresponding DB column, and makes the appropriate adjustments,
> including quoting each value properly, if quoting is necessary.
I'll note that footnote [2] of the dBapi format indicates some
preference for pyformat over the format above, called there 'qmark'.
But it all depends on what the OP is using to connect to the dB; their
database module may well force them to choose a paramstyle, AIUI.

Anyway, the point is that to get quote escaping right, to prevent SQL
injection, etc., paramstyles are better than direct string %-ing.

Jim