python - "%s" % format vs "{0}".format() vs "?" format

16
votes

In this post about SQLite, aaronasterling told me that

  • cmd = "attach \"%s\" as toMerge" % "b.db" : is wrong
  • cmd = 'attach "{0}" as toMerge'.format("b.db") : is correct
  • cmd = "attach ? as toMerge"; cursor.execute(cmd, ('b.db', )) : is right thing

But, I've thought the first and second are the same. What are the differences between those three?

3
pythonstring-formattingpysqlite
sorry for not clarifying in my answer. It already seemed too long ;) - aaronasterling
@aaronasterling : Oh, I just thought this post might be worth one page. Thanks for the answers and help. - prosseek

3 Answers

20
votes
"attach \"%s\" as toMerge" % "b.db"

You should use ' instead of ", so you don't have to escape.

You used the old formatting strings that are deprecated.

'attach "{0}" as toMerge'.format("b.db")

This uses the new format string feature from newer Python versions that should be used instead of the old one if possible.

"attach ? as toMerge"; cursor.execute(cmd, ('b.db', ))

This one omits string formatting completely and uses a SQLite feature instead, so this is the right way to do it.

Big advantage: no risk of SQL injection

6
votes

The first and second produce the same result, but the second method is prefered for formatting strings in newer versions of Python.

However the third is the better approach here because it uses parameters instead of manipulating strings. This is both faster and safer.

3
votes

Because it is not being escaped. If you replaced the b.db with user input, it would leave you vulnerable to SQL injection.