Efficient way to format string

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.

开发者_如何学JAVA"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?

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}

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.

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

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