Delete sql table rows from python

Question

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 ?

@Lamu it means that I always have error = True whereas some rows in table_name are expected to be deleted — Ewdlam
– Ewdlam, Commented Aug 13, 2020 at 13:35
Take out the try/except and let the code error, see what it says. — khelwood
– khelwood, Commented Aug 13, 2020 at 13:39

khelwood · Accepted Answer · 2020-08-13 13:49:50Z

2

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

edited Aug 13, 2020 at 13:49

answered Aug 13, 2020 at 13:44

khelwood

59.7k14 gold badges91 silver badges116 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Delete sql table rows from python

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related