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 may retrieve before you 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: test_database
  • The Table Name (with a dbo schema) is: dbo.product

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

product_id product_name price
1 Computer 800
2 TV 1200
3 Printer 150
4 Desk 400
5 Chair 120
6 Tablet 300

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=test_database;'
                      'Trusted_Connection=yes;')
 
cursor = conn.cursor()

cursor.execute('SELECT * FROM test_database.dbo.product')
 
for row in cursor:
    print(row)

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

(1, 'Computer', 800)
(2, 'TV', 1200)
(3, 'Printer', 150)
(4, 'Desk', 400)
(5, 'Chair', 120)
(6, 'Tablet', 300)

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 ‘product‘ table (i.e., delete the ‘Chair’ record represented by the product_id of 5, and delete the ‘Tablet’ record represented by the product_id of 6).

Here is the complete Python code to delete those records:

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

cursor.execute('''
                DELETE FROM test_database.dbo.product 
                WHERE product_id in (5,6)
               ''')

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 test_database.dbo.product

As you can see, the 2 records (the ‘Chair’ and ‘Tablet’ records) are no longer present in the ‘product’ table:

product_id product_name price
1 Computer 800
2 TV 1200
3 Printer 150
4 Desk 400

You can check the following guides for the steps to: