Understanding Unicode & encodings

[John Machin]

Jim wrote:
> 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).

Oh yeah. My mistake. Noticed 'pyformat' years ago, thought "What a good
idea", found out that ODBC supports only qmark, SQLite supports only
qmark, working on database conversions where the SQL was
programatically generated anyway: forgot all about it.

>
> > 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.

Like tersemess? Like wide availibility?

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

And possible performance gains (the engine may avoid parsing the SQL
each time).

*NOW* we're on the same page of the same hymnbook, Brother Jim :-)

Cheers,
John