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 -- http://mail.python.org/mailman/listinfo/python-list