{"id":4137,"date":"2024-07-24T09:13:41","date_gmt":"2024-07-24T02:13:41","guid":{"rendered":"https:\/\/www.sqlservertutorial.net\/?page_id=4137"},"modified":"2024-07-24T21:17:07","modified_gmt":"2024-07-24T14:17:07","slug":"python-sql-server-delete","status":"publish","type":"page","link":"https:\/\/www.sqlservertutorial.net\/python-sql-server\/python-sql-server-delete\/","title":{"rendered":"Python SQL Server: Deleting Data"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to delete data from an SQL Server table in Python.<\/p>\n\n\n\n

This tutorial begins where\u00a0the\u00a0paginating data tutorial <\/a>left off.<\/p>\n\n\n\n

Deleting a row from a table #<\/a><\/h2>\n\n\n\n

Step 1. First, create a delete.py<\/code> file within the project directory and define the delete_customer()<\/code> function:<\/p>\n\n\n

from<\/span> connect import<\/span> create_connection\n\ndef<\/span> delete_customer<\/span>(id: int)<\/span> -> int:<\/span>\n    # Connect to the SQL Server<\/span>\n    conn = create_connection()\n    if<\/span> conn is<\/span> None<\/span>:\n        return<\/span> 0<\/span>\n    \n    # Delete the customer    <\/span>\n    with<\/span> (conn, conn.cursor() as<\/span> cursor):\n        cursor.execute(\"DELETE FROM Customers WHERE CustomerID = %s\"<\/span>, (id,))\n        conn.commit()\n        return<\/span> cursor.rowcount<\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
How it works.<\/p>\n\n\n\n
Second, define a function delete_customer<\/code> that accepts a customer ID and returns the number of rows deleted.<\/p>\n\n\n
def<\/span> delete_customer<\/span>(id: int)<\/span> -> int:<\/span><\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
Third, connect to SQL Server by calling the create_connection()<\/code> function and return 0 if the connection fails:<\/p>\n\n\n
conn = create_connection()\nif<\/span> conn is<\/span> None<\/span>:\n    return<\/span> 0<\/span><\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
Fourth, manage the Connection<\/code> and Cursor<\/code> objects using the with<\/code> statement:<\/p>\n\n\n
with<\/span> (conn, conn.cursor() as<\/span> cursor):<\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
This eliminates the need to close the Cursor<\/code> and Connection<\/code> objects manually.<\/p>\n\n\n\n
Fifth, execute a DELETE<\/code> statement with the id parameter:<\/p>\n\n\n
cursor.execute(\"DELETE FROM Customers WHERE CustomerID = %s\"<\/span>, (id,))<\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
Sixth, delete the data permanently by calling the commit() method of the Connection<\/code> object:<\/p>\n\n\n
conn<\/span>.commit<\/span>()<\/code><\/span>Code language:<\/span> CSS<\/span> (<\/span>css<\/span>)<\/span><\/small><\/pre>\n\n\n
Finally, return the number of rows deleted:<\/p>\n\n\n
return<\/span> cursor.rowcount<\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
Step 2. Modify the main.py<\/code> module to use the delete_customer<\/code> function:<\/p>\n\n\n
import<\/span> logging, sys\nfrom<\/span> delete import<\/span> delete_customer\n\n# config logging to console<\/span>\nlogging.basicConfig(\n    stream=sys.stdout, \n    encoding='utf-8'<\/span>, \n    format='%(levelname)s:%(message)s'<\/span>,\n    level=logging.DEBUG\n)\n\n# Delete customer with id 1<\/span>\ntry<\/span>:\n    row_deleted = delete_customer(1<\/span>)\n    logging.info(f'{row_deleted}<\/span> row(s) deleted.'<\/span>)\nexcept<\/span> Exception as<\/span> e:\n    logging.error(f\"Error fetching customers: {e}<\/span>\"<\/span>)\n       <\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
How it works.<\/p>\n\n\n\n
First, import the delete_customer<\/code> function from the delete module:<\/p>\n\n\n
from<\/span> delete import<\/span> delete_customer<\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
Second, call the delete_customer<\/code> function to delete the customer with ID 1:<\/p>\n\n\n
try<\/span>:\n    row_deleted = delete_customer(1<\/span>)\n    logging.info(f'{row_deleted}<\/span> row(s) deleted.'<\/span>)\nexcept<\/span> Exception as<\/span> e:\n    logging.error(f\"Error fetching customers: {e}<\/span>\"<\/span>)<\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
Step 3. Open your terminal and run the main.py<\/code> module to delete the customer with ID 1:<\/p>\n\n\n
python main.py<\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
INFO:1<\/span> row(s) deleted.<\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
The output indicates that one row was deleted.<\/p>\n\n\n\n
Download the project source code #<\/a><\/h2>\n\n\n\n
Download the project source code<\/a><\/p>\n\n\n\n
Summary #<\/a><\/h2>\n\n\n\n
    \n
  • Execute the DELETE<\/code> statement to delete data from a table in Python.<\/li>\n<\/ul>\n
    \n\t
    \n\t\t
    Was this tutorial helpful?<\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t\n\t\t\t
First, import the create_connection<\/code> function from the connect<\/a> module.<\/p>\n\n\n\n