cursor.execute string interpolation

[bugfreeit]

Hi
I'm not sure this is an issue (for sure I'm not a python expert, so forgive me if I missed somenthing)

if I run:

HOSTNAME = r'xp4buildste\test'
USER = 'sa'
PASSWORD = 'pongo'
SOURCE_DB = 'MY_DB'
TABLE_NAME = 'table1'
con = pymssql.connect(database=SOURCE_DB, host=r'xp4buildste\test', user='sa', password='xxx')
cur = con.cursor()
query = "select COLUMN_NAME from %s.information_schema.columns where TABLE_NAME = '%s'" % (SOURCE_DB, TABLE_NAME,)
print query
cur.execute(query)

everything is fine, but, trying

cur.execute('select COLUMN_NAME from %s.information_schema.columns where TABLE_NAME = %s',(SOURCE_DB, TABLE_NAME))

I get
raise OperationalError, e[0]
pymssql.OperationalError: SQL Server message 170, severity 15, state 1, line 1:
Line 1: Incorrect syntax near 'MY_DB'.

any ideas?

thank you in advance
stefano

[A]

> cur.execute('select COLUMN_NAME from %s.information_schema.columns where TABLE_NAME = %s',(SOURCE_DB, TABLE_NAME))

It won't work that way because the string MY_DB gets quoted in 'apostrophes' before putting into the query string. It is meant for values, not for identifiers. If you need variable identifier, the simplest solution is:
cur.execute('select COLUMN_NAME from [' + SOURCE_DB + '].information_schema.columns where TABLE_NAME = %s', TABLE_NAME)
or - if SOURCE_DB can be entered by a user:
cur.execute('select COLUMN_NAME from [' + SOURCE_DB.replace('[','[[') + '].information_schema.columns where TABLE_NAME = %s', TABLE_NAME)

[A]

Sorry, closing bracket should be quoted, this is the correct version:
cur.execute('select COLUMN_NAME from [' + SOURCE_DB.replace(']',']]') + '].information_schema.columns where TABLE_NAME = %s', TABLE_NAME)

[bugfreeit]

great!
I should have considered this :)
thank you for your help
stefano