Error Passing Variable to SQL Server Query Python

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)

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

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/

• python
• sql-server