Python SQL Server: Update

Summary: in this tutorial, you will learn how to update data in an SQL Server table from a Python program.

This tutorial begins where the bulk copy tutorial left off.

Updating data in a table

Step 1. Create a new update.py file within the project directory.

Step 2. Define a function update_customer_email that updates the customer’s email:

import logging
from connect import create_connection

def update_customer_email(customer_id: int, email: str) -> bool:
    # Connect to the SQL Server
    conn = create_connection()
    if conn is None:
        return False

    # Update the customer email
    with (conn, conn.cursor() as cursor):
        cursor.execute(
            "UPDATE Customers SET Email = %s WHERE CustomerID = %s",
            (email, customer_id),
        )
        conn.commit()
        logging.info(f'{cursor.rowcount} rows updated successfully.')
        return True if cursor.rowcount == 1 else False

Code language: Python (python)

How it works.

First, import the built-in logging module for logging:

import loggingCode language: Python (python)

Second, import the create_connection function from the connect module:

from connect import create_connectionCode language: Python (python)

Third, define a function update_customer_email that accepts a customer ID (customer_id) that identifies the customer to update and the new email of the customer (email):

def update_customer_email(customer_id: int, email: str) -> bool:Code language: Python (python)

The function returns true if it successfully updates the customer’s email or false otherwise.

Fourth, create a connection to the SQL Server and return false if the connection fails:

conn = create_connection()
if conn is None:
    return FalseCode language: Python (python)

Fifth, manage the Connection and Cursor objects in the with statement to avoid manually closing them:

with (conn, conn.cursor() as cursor):Code language: JavaScript (javascript)

Sixth, create a Cursor object and execute the UPDATE statement that updates the email of a customer by ID:

cursor.execute(
    "UPDATE Customers SET Email = %s WHERE CustomerID = %s",
    (email, customer_id),
)Code language: Python (python)

Notice that we use the UPDATE statement with parameterized values to prevent SQL injection attacks.

Seventh, call the commit() method of the Connection object to save the change to the database permanently:

conn.commit()Code language: Python (python)

Finally, log the number of rows updated by accessing the rowcount property of the Cursor object and return True to indicate that the update was successful or False otherwise:

logging.info(f'{cursor.rowcount} rows updated successfully.')
return True if cursor.rowcount == 1 else FalseCode language: Python (python)

Step 3. Modify the main.py module to use the update_customer_email function:

import logging, sys
from update import update_customer_email

# config logging to console
logging.basicConfig(
    stream=sys.stdout, 
    encoding='utf-8', 
    format='%(levelname)s:%(message)s',
    level=logging.DEBUG
)

# update customer email
update_customer_email(1, '[email protected]')Code language: Python (python)

How it works (we’ll focus on the update part).

First, import update_customer_email from the update module:

from update import update_customer_emailCode language: Python (python)

Second, call the update_customer_email function to update the email of customer ID 1 to '[email protected]':

update_customer_email(1, '[email protected]')Code language: JavaScript (javascript)

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:

SELECT * 
FROM customers
WHERE customerID = 1;Code language: Python (python)

Output:

Step 5. Open your terminal and execute the following command to run the main.py file:

python main.pyCode language: Python (python)

Output:

INFO:1 rows updated successfullyCode language: Python (python)

Step 6. Execute the following query again to verify the update:

SELECT * 
FROM customers
WHERE customerID = 1;Code language: Python (python)

Output:

Download the project source code

Summary

Use the execute() method of a Cursor object to execute an UPDATE statement to update data in an SQL Server table.

Was this tutorial helpful?