Conversion failed when converting varchar value '{}' to data type int?

Question

I'm very new to SQL, so I'm just getting my bearings with it. I want to delete a row based on an int value, DataID, passed in by some other method. Here is the code:

query = ("""DELETE FROM TradeSupportPortal.dbo.ExchangeDropCopyIgnore
                    WHERE TradeSupportPortal..ExchangeDropCopyIgnore.DropCopyID = '{}'""")

query.format(dataID)

cnxn = pyodbc.connect(self.__connection_string)
cursor = cnxn.cursor()
cursor.execute(query)
cnxn.commit()

However, I get an error message saying "Conversion failed when converting the varchar value '{}' to data type int." I'm not sure why, because dataID is an int. Any help would be appreciated! Thanks!

mgilson · Accepted Answer · 2014-08-19T20:17:54.267

strings are immutable so calling .format on it doesn't change it -- It creates a new string.

You probably wanted to do:

query = query.format(dataId)

to store the new string that got formatted. With that said, there is a better way. You should use your database connector's paramstyle -- It usually looks something like this:

query = """DELETE FROM TradeSupportPortal.dbo.ExchangeDropCopyIgnore
                   WHERE TradeSupportPortal..ExchangeDropCopyIgnore.DropCopyID = '%s'"""

cnxn = pyodbc.connect(self.__connection_string)
cursor = cnxn.cursor()
cursor.execute(query, dataID)
cnxn.commit()

Formatting the string yourself leaves you open to SQL injection attacks.

Conversion failed when converting varchar value '{}' to data type int?

1 Answers1