Newbie Python question about strings with parameters: "%%s"?

Question

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?

Answer 1

%% turns into a single %

Answer 2

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.

Answer 3

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

Answer 4

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

Answer 5

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.