How to Delete Records in SQL Server using Python

In this tutorial, you’ll see how to delete records in SQL Server directly from Python.

Here are the steps that you may follow.

Steps to Delete Records in SQL Server using Python

Step 1: Install the Pyodbc Library

If you haven’t already done so, install the pyodbc library using the following command (under Windows):

pip install pyodbc

Step 2: Connect Python to SQL Server

There are several items that you’ll need to retrieve before you can connect Python to SQL Server, including the:

  • Server name
  • Database name
  • Table name

For demonstration purposes, let’s use the information below in order to establish the connection:

  • The Server Name is: RON\SQLEXPRESS
  • The Database Name is: TestDB
  • The Table Name (with a dbo schema) is: dbo.Person

In addition, the ‘Person’ table contains the following data:

How to Delete Records in SQL Server using Python

The following syntax can then be used to connect Python to SQL Server for our example (you can check the following guide for the complete steps to connect Python to SQL Server):

import pyodbc 
conn = pyodbc.connect('Driver={SQL Server};'
                      'Server=RON\SQLEXPRESS;'
                      'Database=TestDB;'
                      'Trusted_Connection=yes;')
 
cursor = conn.cursor()

cursor.execute('SELECT * FROM TestDB.dbo.Person')
 
for row in cursor:
    print(row)

Once you run the above code in Python (adjusted to your database connection information), you’ll see the following data:

Data in Python

Step 3: Delete the Records in SQL Server using Python

Let’s suppose that you’d like to delete the last two records from the ‘Person‘ table (i.e., delete the ‘Bill’ and ‘Mia’ records).

Here is the complete Python code to delete those records:

import pyodbc 
conn = pyodbc.connect('Driver={SQL Server};'
                      'Server=RON\SQLEXPRESS;'
                      'Database=TestDB;'
                      'Trusted_Connection=yes;')
 
cursor = conn.cursor()

cursor.execute('''
                DELETE FROM TestDB.dbo.Person 
                WHERE [Name] in ('Bill','Mia')
               ''')

conn.commit()

Don’t forget to add conn.commit() at the end of the code, to ensure that the command would get executed.

Step 4: Verify that the Records were Deleted

For the final step, the following SELECT query can be used in SQL Server in order to verify that the records were deleted:

SELECT * FROM TestDB.dbo.Person

As you can see, the 2 records (for ‘Bill’ and ‘Mia’) are no longer present in the ‘Person’ table:

Delete Records in SQL Server using Python

You can check the following guides for the steps to: