Error Passing Variable to SQL Server Query Python

Question

 cursor.execute(""" if exists ( SELECT * from Alert where alert = ? and date = ? and devicename = ? )
 BEGIN
 update Alert set alert = ? where alert = ? and date = ? and devicename = ?
 END
 ELSE
 BEGIN
 INSERT INTO Alert (alert,date,devicename) VALUES (?, ?, ?) """, row[1], row[0], filename[:-4],
 row[1], row[1], row[0], filename[:-4], row[1], row[0], filename[:-4])
 cursor.commit()

The above code is my sql query for SQLServer. It works if I replace the ? with value but if I do it this way, I receive error. Need help. I am using pyodbc library btw.

cursor.execute(""" if exists ( SELECT * from Alert where alert = ? and date = ? and devicename = ? )
pyodbc.ProgrammingError: ('42000', "[42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near ')'. (102) (SQLExecDirectW); [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Statement(s) could not be prepared. (8180)")

• Thom A: Recommended reading: Please stop using this UPSERT anti-pattern

Answer 1

You should be passing a tuple of 10 parameters as the second parameter to cursor#execute():

sql = """IF EXISTS (SELECT 1 FROM Alert WHERE alert = ? AND date = ? AND devicename = ?)
 BEGIN
 UPDATE Alert SET alert = ? WHERE alert = ? AND date = ? AND devicename = ?
 END
 ELSE
 BEGIN
 INSERT INTO Alert (alert, date, devicename) VALUES (?, ?, ?)
 END """
params = (row[1], row[0], filename[:-4], row[1], row[1], row[0], filename[:-4],
 row[1], row[0], filename[:-4])
cursor.execute(sql, params)

• Maghesan: Hey, I've tried this but the same error occurs.
• Tim Biegeleisen: You were also missing an END for the insert block. Try adding that, q.v. my updated answer.
• Tim Biegeleisen: Also note that it appears that you want upsert functionality on SQL Server. Try researching for this for a possibly easier way.

Answer 2

As I note in the comments, I would suggest not using an EXISTS to check if the row exists, but instead, check if the UPDATE actually updated anything. This means that you only need to run a query against the table dbo.Alert once if you do UPDATE the table. You can read more about it here: Please stop using this UPSERT anti-pattern This also lowers the number of parameters you need to pass.

As for the error, this isn't because of the lack of tuples; you can pass parameters outside of a tuple if you want, and the problem is purely due to your lack of an END. You don't, however, actually need the BEGIN/END here, as you are only running a single statement, so this can shortened to the following:

cursor.execute("""UPDATE dbo.Alert WITH (UPDLOCK, SERIALIZABLE) SET alert = ? WHERE alert = ? AND date = ? and devicename = ?;
 IF @@ROWCOUNT = 0
 INSERT INTO dbo.Alert (alert,date,devicename) VALUES (?, ?, ?);""", row[1], row[1], row[0], filename[:-4], row[1], row[0], filename[:-4])
cursor.commit()

Though your parameters also seem wrong here. You want to UPDATE the value of alert to row[1] when alert has the value row[1]. If this is true then the row will be UPDATEd with it's own value, and if it isn't a new row will be INSERTed; something isn't right there as the UPDATE is pointless. Perhaps alert = ? shouldn't be in the WHERE (which means you'll need to lower the number of parameters in cursor.execute too.

You might be better off, to avoid repetition of parameters, to define variables in your SQL and pass the values to those. Something like this (data types are guessed):

cursor.execute("""DECLARE @alert int = ?, @date date = ?, @Devicename varchar(20) = ?;
 UPDATE dbo.Alert WITH (UPDLOCK, SERIALIZABLE) SET alert = @alert WHERE alert = @alert AND date = @date and devicename = @devicename;
 IF @@ROWCOUNT = 0
 INSERT INTO dbo.Alert (alert,date,devicename) VALUES (@alert, @date, @devicename);""", row[1], row[0], filename[:-4])
cursor.commit()

Answer 3

Use String Formatting such as

sql = "select col1,col2 from DB where col1 >= {} AND col2 <= {}".format(val1,val2)"

which outputs

>>> val1,val2=100,200
>>> sql = "select col1,col2 from DB where col1 >= {} AND col2 <= {}".format(val1,val2)
>>> sql
'select col1,col2 from DB where col1 >= 100 AND col2 <= 200'

here's a quick link on how to customize parameters and set their orders https://www.geeksforgeeks.org/python-string-format-method/