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.

30.5k13 gold badges118 silver badges155 bronze badges

answered Apr 7, 2021 at 8:58

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.