String formatting -- skip one format string

Question

I have the following sql statement:

SELECT_CLAUSE += ", IF(%s IS NULL, -1, %s=%s) AS %s_score" % (
    field, field, '%s', field
)

Basically I want to string-format everything except for the second-to-last format string so it looks like:

field = 'name'
SELECT_CLAUSE += ', IF(name IS NULL, -1, name=%s) AS name_score'

What would be the best way to do this?

Do you mean you don't want to repeat formatting values? you can do for example named formatting parameters: ', IF({field} IS NULL, -1, {field}={value}) AS name_score'.format(field='name', value='1') — Andrej Kesely
– Andrej Kesely, Commented Dec 6, 2019 at 0:00
Well, I think that's a bit more advanced where I'm looking for. I'm basically just trying to keep the '%s' in there so I can format it at a later line of code. — David542
– David542, Commented Dec 6, 2019 at 0:00
You could store this string for example: s = ', IF({0} IS NULL, -1, {0}={1}) AS name_score' and then later do s.format('name', 'value') for formatting it. — Andrej Kesely
– Andrej Kesely, Commented Dec 6, 2019 at 0:06
I want to point out that mostly concatenating SQL string manually is not recommended. As there might be injections, mostly there will be some functions like cursor.execute("SELECT * FROM TABLE WHERE ID == ?", (Id,)) to safely concatenate your SQL string. — Sraw
– Sraw, Commented Dec 6, 2019 at 0:20

David542 · Accepted Answer · 2019-12-06 00:58:32Z

1

Double %% the single % to escape it:

SELECT_CLAUSE += ", IF(%s IS NULL, -1, %s=%%s) AS %s_score" % (
    field, field, field
)

Where is the StringBorg that we direly need? That would make this safe.

edited Dec 6, 2019 at 0:58

David542

112k211 gold badges582 silver badges1.1k bronze badges

answered Dec 6, 2019 at 0:10

Dan D.

75k15 gold badges111 silver badges129 bronze badges

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

2 Comments

David542 Over a year ago

Thanks I ended up using this approach, it was the most similar to what I already had!

David542 Over a year ago

(Note you don't need the extra '%s' in the args when you've already done it in the string like you mentioned.) -- >>> ", IF(%s IS NULL, -1, %s=%%s) AS %s_score" % ('name', 'name', 'name') ', IF(name IS NULL, -1, name=%s) AS name_score'

Mohd · Accepted Answer · 2019-12-06 00:11:27Z

1

You could use str.format() to do that, for example:

field = 'name'
SELECT_CLAUSE += ', IF({name} IS NULL, -1, {name}=%s) AS {name}_score'.format(name=field)

In addition, if you're using python 3.6+ you could use format string by specifying variable name in braces:

field = 'name'
SELECT_CLAUSE += f', IF({field} IS NULL, -1, {field}=%s) AS {field}_score'

edited Dec 6, 2019 at 0:11

answered Dec 6, 2019 at 0:04

Mohd

5,6317 gold badges22 silver badges31 bronze badges

Collectives™ on Stack Overflow

String formatting -- skip one format string

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related