I'm trying to figure out what the following line does exactly - specifically the %%s part?
cursor.execute('INSERT INTO mastertickets (%s, %s) VALUES (%%s, %%s)'%sourcedest, (self.tkt.id, n))
Any good mini-tutorial about string formatting and inserting variables into strings with Python?
The %% becomes a single %. This code is essentially doing two levels of string formatting. First the %sourcedest is executed to turn your code essentially into:
cursor.execute('INSERT INTO mastertickets (BLAH, FOO) VALUES (%s, %s)', (self.tkt.id, n))
then the db layer applies the parameters to the slots that are left.
The double-% is needed to get the db's slots passed through the first string formatting operation safely.
"but how should one do it instead?"
Tough call. The issue is that they are plugging in metadata (specifically column names) on the fly into a SQL statement. I'm not a big fan of this kind of thing. The sourcedest variable has two column names that are going to be updated.
Odds are good that there is only one (or a few few) pairs of column names that are actually used. My preference is to do this.
if situation1:
stmt= "INSERT INTO mastertickets (this, that) VALUES (?, ?)"
elif situation2:
stmt= "INSERT INTO mastertickets (foo, bar) VALUES (?, ?)"
else:
raise Exception( "Bad configuration -- with some explanation" )
cursor.execute( stmt, (self.tkt.id, n) )
When there's more than one valid combination of columns for this kind of thing, it indicates that the data model has merged two entities into a single table, which is a common database design problem. Since you're working with a product and a plug-in, there's not much you can do about the data model issues.
Having the column names inserted using string formatting isn't so bad so long as they aren't user-provided. The values should be query parameters though:
stmt = "INSERT INTO mastertickets (%s, %s) VALUES (?, ?)" % srcdest
...
cursor.execute( stmt, (self.tkt.id, n) )
%% turns into a single %
It does the same:
cursor.execute('INSERT INTO mastertickets (%s, %s) VALUES (:%s, :%s)' % \
tuple(sourcedest + sourcedest), dict(zip(sourcedest, (self.tkt.id, n))))
Never do that.
