Python format strings: 'Truncated incorrect DOUBLE value'

Question

I format a string sql with sid (NOTE: the type of sid is str)

sid = "34"  #type(sid) is string, like 34, 34s
sql = '''SELECT * FROM table_name WHERE sid={sid}'''.format(sid=sid)

The result of print sql is:

SELECT * FROM table_name WHERE sid=34

which is out of my expection (34 vs "34"):

SELECT * FROM table_name WHERE sid="34"

While using this sql as a parameter of cur.execute(sql), encounter the following error, leading to the error output.

Warning: Truncated incorrect DOUBLE value: '88s'

Answer 1

If you want to wrap your value in quotes, you can add that in your string and make sure to escape your " characters with \

>>> 'SELECT * FROM table_name WHERE sid=\"{sid}\"'.format(sid=sid)
'SELECT * FROM table_name WHERE sid="34"'

Answer 2

One more concise solution, use the conversion flag !r:

>>> sid = "34"
>>> sql = 'SELECT * FROM table_name WHERE sid={sid!r}'.format(sid=sid)
>>> sql
"SELECT * FROM table_name WHERE sid='34'"

There are two conversion flags in str.format():

• !s calls str() on the value
• !r calls repr() on the value