Error Passing Variable to SQL Server Query Python-CodePudding

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

CodePudding user response：

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)

CodePudding user response：

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

CodePudding user response：

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/