Delete sql table rows from python

I can successfully connect my python notebook with sql server this way :

from sqlalchemy import create_engine
import sqlalchemy as sa
import urllib

params = urllib.parse.quote_plus('''DRIVER={SQL Server Native Client 11.0};
                                    SERVER= SERVER_NAME;
                                    DATABASE= DATABASE_NAME;
                                    TRUSTED_CONNECTION=YES;''')

engine = create_engine("mssql+pyodbc:///?odbc_connect=%s" % params,case_sensitive=False)

Let’s take this sample dataframe :

df = pd.DataFrame(dict(Name=['abc','def','ghi'],Date=['2018-08-15','2019-08-15','2020-08-15'],Status=[True,False,True],Max=[0,0,8]))

  Name        Date  Status  Max
0  abc  2018-08-15    True    0
1  def  2019-08-15   False    0
2  ghi  2020-08-15    True    8

I have a sql table with the same columns, named table_name.

Objective :

I want to delete in table_name all the rows where a row in df has the same Name, same Date, same Status, and Max = 0 (only in table_name, not necessary in df)

I tried the following but it doesn’t work :

connection = engine.raw_connection()
for index,row in df.iterrows():
    try:
        cursor = connection.cursor()
        sql_Delete_query = """Delete from table_name where Date = %s AND Name = %s AND Status = %s AND Max = 0"""
        cursor.execute(sql_Delete_query, (row['Date'],row['Name'],row['Status']))
        connection.commit()
    except:
        error = True

Could you please help me to understand what is wrong ?

Answer

Different libraries use different symbols for their placeholders. The one you are using apparently uses ? instead of %s.

sql_Delete_query = """Delete from table_name where Date = ? AND Name = ? AND Status = ? AND Max = 0"""

Advertisement

Answer