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'

Cory Kramer · Accepted Answer · 2015-06-18 15:11:13Z

5

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

answered Jun 18, 2015 at 15:11

Cory Kramer

119k19 gold badges176 silver badges233 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

SELECT * FROM table_name WHERE sid="{sid}" works in triple-quoted strings ''' because double-quotes " is unescaped?

If you use triple quotes, no you do not need to escape the double quotes (though if you do it won't cause a problem)

there is one more concise solution, use the conversion flag !r: 'SELECT * FROM table_name WHERE sid={sid!r}'.format(sid=sid)

SparkAndShine · Accepted Answer · 2015-06-25 16:34:40Z

0

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

answered Jun 25, 2015 at 16:34

SparkAndShine

18.2k27 gold badges100 silver badges140 bronze badges