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 is: 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 product') for i in cursor: print(i)
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 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 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: