Efficient way to format string

Question

i am writing the following sql query:

"select someTableName.somefield, count(*) from someTableName WHERE someTableName.TimeStamp > %s and someTableName.EndTimeStamp < %s group by someTableName.ProviderUsername;", [from_date, to_data]

this query can be used on three tables, so i do not want to fix the table name so what i am doing is like this.

"select %s.somefield, count(*) from %s WHERE %s.TimeStamp > %s and %s.EndTimeStamp < %s group by %s.ProviderUsername;", [tableName, tableName, tableName, from_date, tableName, to_data, tableName]

as you could see in above query i have to use tableName multiple times so i need to provide tableName for each occurance, is there any best way to achieve this??

Edited

i do not have python 2.6 to use str.format and for some reason can't move to latest versionof python , i am using python version 2.5.2, so what is the best solution in this environment?

Be very careful that the variables going in to that string aren't unsanitized user-submitted content; this has SQL injection written all over it. — Wooble
– Wooble, Commented Sep 14, 2011 at 13:24

ed. · Accepted Answer · 2011-09-14 12:23:41Z

5

You can use named variables in string formatting, like this:

"select %(tableName)s.somefield, count(*) from %(tableName)s WHERE %(tableName)s.TimeStamp > %(fromDate)s and %(tableName)s.EndTimeStamp < %(to_data)s group by %(tableName)s.ProviderUsername;" %{'tableName':tableName, 'fromDate':fromDate, 'to_data':to_data}

answered Sep 14, 2011 at 12:23

ed.

1,4039 silver badges10 bronze badges

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

Comments

StackExchange saddens dancek · Accepted Answer · 2011-09-14 12:27:21Z

3

If you have at least Python 2.6, you can use str.format:

s = "select {0}.somefield, count(*) from {0} WHERE {0}.TimeStamp > {1} and {0}.EndTimeStamp < {2} group by {0}.ProviderUsername;"
query = s.format(tableName, fromDate, toDate)

That way you can use the same variable multiple times in the string.

Also note that this is more future-proof than the old % formatting.

edited Sep 14, 2011 at 12:27

answered Sep 14, 2011 at 12:22

StackExchange saddens dancek

3,4121 gold badge29 silver badges37 bronze badges

2 Comments

Aamir Rind Over a year ago

thanks for response, i have edited my post please read again, i using old python version.

StackExchange saddens dancek Over a year ago

@AamirAdnan: well, you got the answer for that case thrice already... :)

Johannes Charra · Accepted Answer · 2011-09-14 12:23:56Z

2

You can substitute with a dictionary

mydict = {'table': whatever, 'startdate': yourstartdate, 'enddate': yourenddate}
sql = "select %(table)s.somefield, count(*) from %(table)s WHERE %(table)s.TimeStamp > %(startdate)s and %(table)s.EndTimeStamp < %(enddate)s group by %(table)s.ProviderUsername;" % mydict

answered Sep 14, 2011 at 12:23

Johannes Charra

30k6 gold badges45 silver badges54 bronze badges

Comments

dugres · Accepted Answer · 2011-09-14 12:42:25Z

0

template = "select %(tableName)s.somefield, count(*) from %(tableName)s WHERE %(tableName)s.TimeStamp > %(fromDate)s and %(tableName)s.EndTimeStamp < %(to_data)s group by %(tableName)s.ProviderUsername;"
query = template % locals()

edited Sep 14, 2011 at 12:42

answered Sep 14, 2011 at 12:30

dugres

13.2k8 gold badges48 silver badges52 bronze badges

Collectives™ on Stack Overflow

Efficient way to format string

4 Answers 4

Comments

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related