Python 2.5 String formatting problem!

Question

I am trying to use the python 2.5 string formatting however I have run into problem in the following example:

values = {
          'url': 'http://blabla.com',
          'link' : 'http://blabla.com',
          'username' : 'user',
          'spot'    : 0,
          'views'    : 10,
          'date'    : 3232312,
          'private' : 1, 

          }

query = """insert into hyves.Image (URL, StaticLink , HostUsername, SpotCount, ViewCount, UploadDate) values ('%(url)','%(link)','%(username)',%(spot),%(views),%(date), %(private) )""" % values

print query

It gives me the following error:ValueError: unsupported format character ''' (0x27) at index 106. Can anyone help me?

Answer 1

Never use string formatting for composing sql queries like that! Use your database module to do the interpolation -- it will do it with correct escaping, so that this doesn't happen to you: http://xkcd.com/327/

In case you want to use that formatting for different things than sql, use %(foo)s (or d, or whatever format you need).

Answer 2

You are missing the format characters, i.e.:

"INSERT INTO ... %(url)s, ..." % values

...if you want to format URL as a string.

Answer 3

You need to specify explicit conversion flags:

query = """insert into hyves.Image (URL, StaticLink , HostUsername, SpotCount, ViewCount, UploadDate) values (%(url)s,%(link)s,%(username)s,%(spot)i,%(views)i,%(date)i, %(private)i )""" % values