Python string formatting with variables - different methods

Question

I am looking at some older Python scripts that I am modernising and within query strings they used this format:

query = " SELECT DISTINCT account from customers..accounts WHERE date = '" + date + "'"
cursor.execute(query)

I would have thought this should be performed this by using the following format:

query = " SELECT DISTINCT account from customers..accounts WHERE date = {}".format(date)
cursor.execute(query)

When would you use the original format? Is there a reason to use it? Does it concatenate? Why would you concat within a SQL query?

Personally I have always run my sybase queries using the module:

cursor.execute("SELECT DISTINCT account from customers..accounts WHERE date = @date", {"@date": date})

When the thing you're formatting is SQL queries, you should never use either - let the DB driver interpolate the values. But yes, + on strings is concatenation. — jonrsharpe
– jonrsharpe, Commented Apr 7, 2021 at 8:23
@jonrsharpe I forgot to add the method I actually use which is using the module / driver. I just wasn't sure why anyone would use the method of + var + within a query... doesn't make sense why would you concat within a query — SimonT
– SimonT, Commented Apr 7, 2021 at 8:38
Why aren't you migrating everything to that (correct) approach? If you want to know more about the trade-offs of the different string formatting options (all wrong in this case) see e.g. stackoverflow.com/q/38722105/3001761, stackoverflow.com/q/34619384/3001761, stackoverflow.com/q/41481263/3001761, stackoverflow.com/q/10043636/3001761, ... — jonrsharpe
– jonrsharpe, Commented Apr 7, 2021 at 8:47

E_net4 · Accepted Answer · 2021-04-07 09:28:27Z

2

Simply change it to the driver method you detailed at the bottom.

I can't answer why it was done that way as it does not make any sense to do it with concatenated strings.

E_net4

30.5k13 gold badges118 silver badges155 bronze badges

answered Apr 7, 2021 at 8:58

riker87

362 bronze badges

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

1 Comment

Yea that's what I'm going to do. I guess no one knows why the previous coder did it this way.