{"id":4101,"date":"2024-07-23T10:45:06","date_gmt":"2024-07-23T03:45:06","guid":{"rendered":"https:\/\/www.sqlservertutorial.net\/?page_id=4101"},"modified":"2024-07-24T20:38:29","modified_gmt":"2024-07-24T13:38:29","slug":"python-sql-server-update","status":"publish","type":"page","link":"https:\/\/www.sqlservertutorial.net\/python-sql-server\/python-sql-server-update\/","title":{"rendered":"Python SQL Server: Update"},"content":{"rendered":"\n

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

This tutorial begins where the bulk copy tutorial<\/a> left off.<\/p>\n\n\n\n

Updating data in a table #<\/a><\/h2>\n\n\n\n

Step 1. Create a new update.py<\/code> file within the project directory.<\/p>\n\n\n\n

Step 2. Define a function update_customer_email<\/code> that updates the customer’s email:<\/p>\n\n\n

import<\/span> logging\r\nfrom<\/span> connect import<\/span> create_connection\r\n\r\ndef<\/span> update_customer_email<\/span>(customer_id: int, email: str)<\/span> -> bool:<\/span>\r\n    # Connect to the SQL Server<\/span>\r\n    conn = create_connection()\r\n    if<\/span> conn is<\/span> None<\/span>:\r\n        return<\/span> False<\/span>\r\n\r\n    # Update the customer email<\/span>\r\n    with<\/span> (conn, conn.cursor() as<\/span> cursor):\r\n        cursor.execute(\r\n            \"UPDATE Customers SET Email = %s WHERE CustomerID = %s\"<\/span>,\r\n            (email, customer_id),\r\n        )\r\n        conn.commit()\r\n        logging.info(f'{cursor.rowcount}<\/span> rows updated successfully.'<\/span>)\r\n        return<\/span> True<\/span> if<\/span> cursor.rowcount == 1<\/span> else<\/span> False<\/span>\r\n\r\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 built-in logging<\/code> module for logging:<\/p>\n\n\n
import<\/span> logging<\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
from<\/span> connect import<\/span> create_connection<\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
Third, define a function update_customer_email<\/code> that accepts a customer ID<\/code> (customer_id<\/code>) that identifies the customer to update and the new email of the customer (email):<\/p>\n\n\n
def<\/span> update_customer_email<\/span>(customer_id: int, email: str)<\/span> -> bool:<\/span><\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
The function returns true if it successfully updates the customer’s email or false otherwise.<\/p>\n\n\n\n
Fourth, create a connection to the SQL Server and return false if the connection fails:<\/p>\n\n\n
conn = create_connection()\nif<\/span> conn is<\/span> None<\/span>:\n    return<\/span> False<\/span><\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
Fifth, manage the Connection and Cursor objects in the with<\/code> statement to avoid manually closing them:<\/p>\n\n\n
with<\/span> (conn, conn.cursor() as<\/span> cursor):<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Sixth, create a Cursor<\/code> object and execute the UPDATE<\/code> statement that updates the email of a customer by ID:<\/p>\n\n\n
cursor.execute(\n    \"UPDATE Customers SET Email = %s WHERE CustomerID = %s\"<\/span>,\n    (email, customer_id),\n)<\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
Notice that we use the UPDATE<\/code> statement with parameterized values to prevent SQL injection attacks.<\/p>\n\n\n\n
Seventh, call the commit()<\/code> method of the Connection object to save the change to the database permanently:<\/p>\n\n\n
conn.commit()<\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
Finally, log the number of rows updated by accessing the rowcount<\/code> property of the Cursor<\/code> object and return True to indicate that the update was successful or False otherwise:<\/p>\n\n\n
logging.info(f'{cursor.rowcount}<\/span> rows updated successfully.'<\/span>)\nreturn<\/span> True<\/span> if<\/span> cursor.rowcount == 1<\/span> else<\/span> False<\/span><\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
Step 3. Modify the main.py<\/code> module to use the update_customer_email<\/code> function:<\/p>\n\n\n
import<\/span> logging, sys\nfrom<\/span> update import<\/span> update_customer_email\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# update customer email<\/span>\nupdate_customer_email(1<\/span>, 'stephenjohnson@wilson-foster.com'<\/span>)<\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
How it works (we’ll focus on the update part).<\/p>\n\n\n\n
First, import update_customer_email<\/code> from the update module:<\/p>\n\n\n
from<\/span> update import<\/span> update_customer_email<\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
Second, call the update_customer_email<\/code> function to update the email of customer ID 1 to 'stephenjohnson@wilson-foster.com'<\/code>:<\/p>\n\n\n
update_customer_email(1<\/span>, 'stephenjohnson@wilson-foster.com'<\/span>)<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Step 4. Launch SQL Server Management Studio (SSMS), connect to the SQL Server, and execute the following query to retrieve data from customer ID 1:<\/p>\n\n\n
SELECT * \nFROM customers\nWHERE customerID = 1<\/span>;<\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n\n
\"SQL<\/figure>\n\n\n\n
Step 5. Open your terminal and execute the following command to run the main.py<\/code> file:<\/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> rows updated successfully<\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
Step 6. Execute the following query again to verify the update:<\/p>\n\n\n
SELECT * \nFROM customers\nWHERE customerID = 1<\/span>;<\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n\n
\"SQL<\/figure>\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
  • Use the execute()<\/code> method of a Cursor<\/code> object to execute an UPDATE<\/code> statement to update data in an SQL Server table.<\/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
Second, import the create_connection<\/code> function from the connect<\/a> module:<\/p>\n\n\n